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

On Fri, 1 Jan 2021 at 11:44, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Isaac Morland <isaac(dot)morland(at)gmail(dot)com> writes:
> > Is it safe and valid to reset to default permissions by doing
> > UPDATE pg_namespace/pg_class/pg_type/pg_proc
> > SET nspacl/relacl/typacl/proacl = NULL WHERE ... to accomplish this?
>
> Not terribly; the main objection is you'd fail to update pg_shdepend.
>

Right, the object would still be recorded as depending on the role, even
though it really didn't any more. I should have considered that.

I think I can fix that by first looping through using aclexplode() and
issuing a REVOKE against every role mentioned, then do a table update to
replace the empty array acl with a NULL. Of course I could also update
pg_shdepend myself but the goal is to minimize direct system table updates.

Thanks for the response.

> > And what do people think, conceptually, of the notion of adding a command
> > to do this without resorting to updating system tables directly?
>
> I'm a little skeptical as to the use-case, particularly once you take
> ALTER DEFAULT PRIVILEGES into account and try to figure out what that
> means. If it means "apply the current default privileges", you could
> easily be "resetting" to a state that never actually prevailed in the
> past.

The use case is to ensure that after doing my GRANTs the permissions are in
a known state, no matter what they were before. Typically, one would follow
a reset command with some GRANTs. So maybe my permissions script contains:

GRANT UPDATE ON TABLE t1, t2 TO u1, u2;

Later, I revise this to:

GRANT UPDATE ON TABLE t1, t2 TO u1;

But the obsolete permissions will still be available to u2. I would like to
be able to put something like this at the top of the permissions script:

RESET PERMISSIONS ON ALL TABLES IN SCHEMA test;

Or in a different context:

RESET PERMISSIONS ON TABLE t1, t2;

Note: I'm not particularly fond of "RESET PERMISSIONS" as the syntax; I
just wrote that as an example of what it might look like.

If the tables are newly created this would have no effect; if they were
existing tables it would change the permissions to what newly created
tables would have.

In the absence of default privileges, I think it's clear that this means
setting the acl column (relacl, proacl, ...) to NULL; with default
privileges, I think it probably means resetting acl to NULL and then
applying the current default privileges as if the object had just been
created by its owner. As you point out, it's possible the object never had
this privilege set, which is an argument against using the word "reset" in
describing the feature. Maybe "GRANT DEFAULT"? But it's weird for GRANT to
actually revoke privileges, as it would for most object types.