Re: Safety/validity of resetting permissions by updating system tables

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > I think there is probably a good case for some sort of "from scratch"
> > option on GRANT.
>
> Maybe my head's not screwed on straight this morning, but it seems
> to me that any such action would typically be revoking permissions
> not adding them, so that it'd be more naturally framed as a REVOKE
> option.

Except that actually resetting permissions on some object to NULL ends
up actually grant'ing out rights in some cases- specifically
particularly dangerous cases like functions...

> There's still the question of exactly what "from scratch" means.
> Do we really want it to just reset the acl column to null, forcing
> the object to the wired-in defaults? Might be better to reset to
> whatever pg_init_privs has, if anything. Also, what about the
> effects of any applicable ALTER DEFAULT PRIVILEGES settings?
>
> Maybe we could go with two commands (spelling subject to bikeshedding):
>
> REVOKE ALL NONSTANDARD PRIVILEGES ON object

If we're going to invent something that actually REVOKE's all rights for
an object, it should actually do so- including in cases where the
object's default set includes privileges. In other words, I'd say we
would want to have:

REVOKE ALL RIGHTS ON object

and that would then set NULL for objects which don't have any GRANT'd
out rights by default but then for functions and other objects which
*do* have a default set of GRANT'd out rights, it'd actually set up an
ACL which explicitly removes those rights. If I'm understanding
correctly, the point of this feature is to allow for something like:

REVOKE ALL RIGHTS ON object;
GRANT whatever ON object TO whomever;

and have the result mean that only 'whomever' has any rights on the
object (other than the owner). Simply going to NULL doesn't do that-
you'd have to *also* explicitly do:

REVOKE EXECUTE ON FUNCTION myfunc FROM public;

Which doesn't seem ideal.

> resets to pg_init_privs state, or null if no entry there

An option which resets to pg_init_privs set shouldn't be saying 'REVOKE
ALL' or such but should instead be 'RESET PRIVILEGES' or something along
those lines.

> GRANT DEFAULT PRIVILEGES ON object
>
> add any privileges implied by applicable ALTER DEFAULT PRIVILEGES
> settings

This does seem like a useful, albeit mostly independent, feature.

> A different way to look at it, which I think is what the OP had
> in mind, is that the existing behaviors are sufficient if you can
> say "REVOKE ... FROM ALL". Or, maybe we need that too.

REVOKE ... FROM ALL also seems like a useful capability.

Thanks,

Stephen