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

On Mon, 4 Jan 2021 at 10:12, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> On 1/1/21 11:44 AM, Tom Lane 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.
>
> And apart from that I'm generally resistant to anything that requires
> direct manipulation of the catalog. One of many reasons is that there is
> no guarantee that it will have the same shape in the next release. I
> normally encourage people strongly to look for other solutions.
>

So am I. That's why I asked before proceeding.

As far as I can tell, it is not possible to fully reset permissions using
GRANT/REVOKE even querying the system tables to figure out which
permissions exist; the closest one can get is to set explicit (non-NULL)
acls that have the same effect as the default (NULL) acls; and doing so
requires duplicating the logic used within the system to determine the
permissions that apply to an object with a blank (NULL) acl.