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.
