Re: System catalog representation of access privileges

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?  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"

> Since NULLs are stored specially, sparse pg_privilege
> rows wouldn't take extra space.

Unless there get to be a very large number of privilege bits, it'd
probably be better to handle these columns as NOT NULL, so that a fixed
C struct record could be mapped onto the tuples.  You'll notice that
most of the other system tables are done that way.

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.

> 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 )

Using the privilege bits as part of the index won't work if you intend
to allow them to be null.  Another objection is that this would end up
caching multiple copies of the same tuple.  A third is that you can't
readily tell lack of an entry (implying you should use a default ACL
setting, which might allow the access) from presence of an entry denying
the access.  A fourth is it doesn't work for groups or wildcards.

> These indexes are not
> unique (more than one grantor can grant the same privilege), but AFAICS
> the syscache interface should work okay with this,

Unfortunately not.  The syscache stuff needs unique indexes, because it
can only return one tuple for any given request.

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.
        regards, tom lane