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.
Best Answer
The only way I can think of is directly modifying the system catalog tables.
To remove all permissions from
s.tab
, you canBut then you also have to remove the dependencies between the table and all roles which had privileges on the table:
Needless to say, this is very dangerous, and I cannot guarantee that I didn't forget anything…