0

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.

0

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…

  • Thanks, but that seems a bit too risky. I guess I can open a feature request... – JoeNahmias Aug 9 at 17:58
  • Yes, I wouldn't do that either. You can code something on the client side to make the repetitive work less. – Laurenz Albe Aug 12 at 5:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.