Re: System catalog representation of access privileges

Tom Lane writes:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > pg_privilege (
> >     priobj oid,            -- oid of table, column, function, etc.
> >     prigrantor oid,        -- user who granted the privilege
> >     prigrantee oid,        -- user who owns the privilege
>
> What about groups?

Either integrated into prigrantee or another column prigroupgrantee.  One
of these would always be zero or null, that's why I'm not sure if this
isn't a waste of space.

> What about wildcards?  We already allow
> "grant <priv> to PUBLIC (all)", and it would be nice to be able to do
> something like "grant <on everything I own> to joeblow"

Public would be prigrantee == 0.  About <everything I own>, how is this
defined?  If it is "everything I own and will ever own" then I suppose
priobj == 0.  Although I admit I have never seen this kind of privilege
before.  It's probably better to set up a group for that.

> Alternatively, since you really only need two bits per privilege,
> perhaps a pair of BIT (VARYING?) fields would be a more effective
> approach.  BIT VARYING would have the nice property that adding a new
> privilege type doesn't force initdb.

This would be tricky to index, I think.

> I don't really believe this indexing scheme is workable.  Need to think
> some more.  Possibly the syscache mechanism will not do, and we need a
> specially indexed privilege cache instead.

Maybe just an index on (object, grantee) and walk through that with an
index scan.  This is done in some other places as well (triggers, I
recall), but the performance is probably not too exciting.

However, last I looked at the syscache I figured that it would be
perfectly capable of handling non-unique indexes if there only was an API
to retrieve those values.  Storing and finding the entries didn't seem to
be the problem.  Need to look there, probably.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter