How to reset object permissions to default?

Question

I've just been added to a project using Postgresql 11.4 where database security has been an afterthought. As part of tightening things up, I've used ALTER DEFAULT PERMISSIONS to set sane ACLs on any new objects (schemas, tables, functions, sequences) created. However, there are hundreds of existing objects already in this database with wacko permissions.

Is there a command that will reset the permissions of an object (or group of objects) to the defaults (as seen with \ddp)? Something like the equivalent of REASSIGN OWNED for object ownership, but instead for acls.

Answer 1

The only way I can think of is directly modifying the system catalog tables.

To remove all permissions from s.tab, you can

UPDATE pg_class
SET relacl = NULL
WHERE oid = 's.tab'::regclass;

But then you also have to remove the dependencies between the table and all roles which had privileges on the table:

DELETE FROM pg_shdepend
WHERE classid = 'pg_class'::regclass
  AND refclassid = 'pg_authid'::regclass
  AND objid = 's.tab'::regclass
  AND deptype = 'a';

Needless to say, this is very dangerous, and I cannot guarantee that I didn't forget anything…