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

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.

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

resets to pg_init_privs state, or null if no entry there

GRANT DEFAULT PRIVILEGES ON object

add any privileges implied by applicable ALTER DEFAULT PRIVILEGES
settings

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.

regards, tom lane