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



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