On Mon, 4 Jan 2021 at 10:12, Andrew Dunstan <and...@dunslane.net> wrote:
> > On 1/1/21 11:44 AM, Tom Lane wrote: > > Isaac Morland <isaac.morl...@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.