PostgreSQL Revoke All Permissions from All Users and Roles

Question

I have several tables in Postgres 9.2.23 that I need to update privileges on regularly (daily or multiple times per day). I have a master list maintained elsewhere of who can select that I use to grant privileges. However, I want to make sure that anyone who used to be on the list but has dropped off has all their privileges revoked.

My thought was to do a clean sweep with something like:

revoke all privileges on my_table from [all users/roles]

before I grant select to the authorized users. However, I have not been able to figure out how to do a bulk revoke on all users/roles like that.

I know I can generate a list of users/roles that currently have privileges and go from there, but I am hoping there is a shortcut to bypass this.

Answer 1

There is a canonical answer to this provided by postgres documentation - also related to this post. I had to do a lot of trial and error myself to understand why there isn't a simple REVOKE ALL FROM role solution and the reason comes down to the answer:

REASSIGN OWNED BY doomed_role to surviving_role;
DROP OWNED BY doomed_role;

The reason (or so I have gathered) why this is the solution and you cannot just revoke all permissions, is that the "doomed_role" may be the owner of some entities and therefore they need to be reassigned first before revoking their permissions. So long as you do these commands in this order, it will do what you need to do.

Answer 2

There is a way, but it is not supported and dangerous, because it directly updates the system catalogs.

As superuser, you can do the following:

UPDATE pg_catalog.pg_class
SET relacl = NULL
WHERE oid = 'my_table'::regclass;

That resets the permissions on the object.

The problem is that this leaves some orphaned entries in pg_shdepend, where (among others) dependencies between users and tables are stored, so that you cannot drop a user that still has permissions on an object.

If I made no mistake, you can delete them as follows:

DELETE FROM pg_catalog.pg_shdepend sd
USING pg_catalog.pg_database d, pg_catalog.pg_authid a
WHERE sd.dbid = d.oid
  AND sd.refobjid = a.oid
  AND d.datname = current_database()
  AND sd.classid = 'pg_class'::regclass
  AND sd.refclassid = 'pg_authid'::regclass
  AND sd.objid = 'my_table'::regclass
  AND deptype = 'a';

If that seems too dangerous for you, right you are.

It is better to write a PL/pgSQL procedure that reads out pg_class.relacl, uses aclexplode to get the entries, loops though them and issues individual REVOKE statements.

Or simpler, just loop through all roles and revoke all privileges for everyone except for the owner.

You could safe yourself the whole mess if you don't grant privileges directly to the users, but use roles. Grant privileges to objects to the roles, and assign roles to the users.

That way it is much simpler to manage privileges.