Re: System catalog representation of access privileges

Tom Lane writes:

> Peter Eisentraut <peter_e(at)gmx(dot)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(at)gmx(dot)net http://funkturm.homeip.net/~peter