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@gmx.net   http://funkturm.homeip.net/~peter
> 
> 
> 

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