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

On 1/4/21 11:15 AM, Isaac Morland wrote:
> On Mon, 4 Jan 2021 at 10:12, Andrew Dunstan <andrew(at)dunslane(dot)net
> <mailto: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
> <mailto: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.

I think there is probably a good case for some sort of "from scratch"
option on GRANT.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com