Thread: [GENERAL] How to delete default privileges

[GENERAL] How to delete default privileges

From
Francisco Reyes
Date:
I have a DB where we changed ownership of all objects.

We had:

ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT ON tables
TO dbgroup_ro_group;
ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT ON sequences
TO dbgroup_ro_group;

ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT, UPDATE,
DELETE, INSERT ON tables    TO dbgroup_rw_group;
ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT,
USAGE                  ON sequences TO dbgroup_rw_group;
ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT
EXECUTE                        ON functions TO dbgroup_rw_group;


But now there is a new DB owner and I have run the above, but with
new_dbowner. How can I delete the old default grants? Can't find
examples and don't see it on the documentation either.


Any help would be greatly appreciated.



Re: [GENERAL] How to delete default privileges

From
Jeff Janes
Date:
On Tue, Aug 15, 2017 at 3:02 PM, Francisco Reyes <lists@natserv.net> wrote:
I have a DB where we changed ownership of all objects.

We had:

ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT ON tables    TO dbgroup_ro_group;
ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT ON sequences TO dbgroup_ro_group;

ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT, UPDATE, DELETE, INSERT ON tables    TO dbgroup_rw_group;
ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT, USAGE                  ON sequences TO dbgroup_rw_group;
ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT EXECUTE                        ON functions TO dbgroup_rw_group;


But now there is a new DB owner and I have run the above, but with new_dbowner. How can I delete the old default grants? Can't find examples and don't see it on the documentation either.


Any help would be greatly appreciated.

You just reverse them, changing GRANT...TO into REVOKE...FROM.  When the altered-default is the same thing as the default-default, then the altered-default disappears.   

Cheers,

Jeff