System catalog representation of access privileges

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