Safety/validity of resetting permissions by updating system tables

From: Isaac Morland <isaac(dot)morland(at)gmail(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Safety/validity of resetting permissions by updating system tables
Date: 2021-01-01 05:51:06
Message-ID: CAMsGm5d0Zf-MjXtrDeyy3OJUWCkdE-UW1r0rsdppr2qm-Jc4nQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have long maintained permissions in my databases by having a script which
assigns all the permissions. I have tended to start with invocations
something like this:

REVOKE ALL ON ALL TABLES IN SCHEMA ... FROM ...;

... where the appropriate schemas and roles are listed. This is intended to
ensure that the permissions actually in effect exactly match those in the
permissions script file. However, the commands I'm using don't guarantee a
true reset to default permissions. What I really want is to guarantee that
after running the permissions script I will have exactly the same
permissions whether I am starting from a freshly initialized database (from
script files containing table definitions etc.) or from an existing
database (potentially with obsolete grants or other history).

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? Do I
need to take locks or inform some component that I have updated permissions?

And what do people think, conceptually, of the notion of adding a command
to do this without resorting to updating system tables directly?

Note: I don't use ALTER DEFAULT PRIVILEGES; my pg_default_acl is empty. So
for my immediate question default privileges can be ignored; but in the
context of adding a command for privilege resetting we would have to think
about how to handle default privileges.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2021-01-01 06:07:50 Re: Key management with tests
Previous Message Bruce Momjian 2021-01-01 04:50:47 Key management with tests