Re: System catalog representation of access privileges

Peter Eisentraut wrote:

> I have devised something more efficient:
>
> pg_privilege (
> priobj oid, -- oid of table, column, 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... */
> )
>
> "Usage" privileges on types and other non-table objects could probably be
> lumped under "priselect" (purely for internal purposes).
>

That looks quite nice. I do have 3 quick questions though. First, I
assume that the prigrantee could also be a group id? Or would this
system table represent the effective privileges granted to user via
groups? Second, one nice feature of Oracle is the ability to GRANT roles
(our groups) to other roles. So I could do:

CREATE ROLE clerk;
GRANT SELECT on mascarm.deposits TO clerk;
GRANT UPDATE (mascarm.deposits.amount) ON mascarm.deposits TO clerk;

CREATE ROLE banker;
GRANT clerk TO banker;

Would any part of your design prohibit such functionality in the future?

Finally, I'm wondering if "Usage" or "System" privileges should be
another system table. For example, one day I would like to (as in
Oracle):

GRANT SELECT ANY TABLE TO foo WITH ADMIN;
GRANT CREATE PUBLIC SYNONYM TO foo;
GRANT DROP ANY TABLE TO foo;

Presumably, in your design, the above would be represented by 3 records
with something like the following values:

This would be a "SELECT ANY TABLE" privilege (w/Admin):

NULL, grantor_oid, grantee_oid, 'S', NULL, NULL, NULL, NULL, ...

This would be a "CREATE PUBLIC SYNONYM" privilege:

NULL, grantor_oid, grantee_oid, 'c', NULL, NULL, NULL, NULL, ...

That means that the system would need an index as:

index ( prigrantee, priselect )

While I'm not arguing it won't work, it just doesn't "seem" clean to
shoe-horn the system privileges into the same table as the object
privileges.

I've been wrong before though :-)

Mike Mascari
mascarm(at)mascari(dot)com