Re: System catalog representation of access privileges

I have added this to the TODO as:

* Allow better control over user privileges [privileges]

and added the thread to TODO.detail.

> Oldtimers might recall the last thread about enhancements of the access
> privilege system. See
>
> http://www.postgresql.org/mhonarc/pgsql-hackers/2000-05/msg01220.html
>
> to catch up.
>
> It was more or less agreed that privilege descriptors should be split out
> into a separate table for better flexibility and ease of processing. The
> dispute was that the old proposal wanted to store only one privilege per
> row. I have devised something more efficient:
>
> pg_privilege (
> priobj oid, -- oid of table, column, function, etc.
> prigrantor oid, -- user who granted the privilege
> prigrantee oid, -- user who owns the privilege
>
> priselect char, -- specific privileges follow...
> prihierarchy char,
> priinsert char,
> priupdate char,
> pridelete char,
> prireferences char,
> priunder char,
> pritrigger char,
> prirule char
> /* obvious extension mechanism... */
> )
>
> The various "char" fields would be NULL for not granted, some character
> for granted, and some other character for granted with grant option (a
> poor man's enum, if you will). Votes on the particular characters are
> being taken. ;-) Since NULLs are stored specially, sparse pg_privilege
> rows wouldn't take extra space.
>
> "Usage" privileges on types and other non-table objects could probably be
> lumped under "priselect" (purely for internal purposes).
>
> For access we define system caches on these indexes:
>
> index ( priobj, prigrantee, priselect )
> index ( priobj, prigrantee, prihierarchy )
> index ( priobj, prigrantee, priinsert )
> index ( priobj, prigrantee, priupdate )
> index ( priobj, prigrantee, pridelete )
>
> These are the privileges you usually need quickly during query processing,
> the others are only needed during table creation. These indexes are not
> unique (more than one grantor can grant the same privilege), but AFAICS
> the syscache interface should work okay with this, since in normal
> operation we don't care who granted the privilege, only whether you have
> at least one.
>
> How does that look?
>
> --
> Peter Eisentraut peter_e(at)gmx(dot)net http://funkturm.homeip.net/~peter
>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026