On 1/1/21 11:44 AM, Tom Lane wrote: > Isaac Morland <isaac.morland@gmail.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.
