I am unable to drop user as I am getting default privileges error.
postgres=# drop user xyz; ERROR: role "xyz" cannot be dropped because some objects depend on it DETAIL: owner of default privileges on new functions belonging to role xyz
then I checked default privileges in database,
postgres=# ddp Default access privileges Owner | Schema | Type | Access privileges ---- -+------- +----------+------- xyz | | function |
Could you please let me know how to revoke this default privileges?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
You have to grant the “default” default privileges:
ALTER DEFAULT PRIVILEGES FOR ROLE xyz GRANT EXECUTE ON FUNCTIONS TO PUBLIC; ALTER DEFAULT PRIVILEGES FOR ROLE xyz GRANT EXECUTE ON FUNCTIONS TO xyz;
Then you should be able to drop the role.
This is the safe sequence of commands to drop a role:
Run in every involved database of the DB cluster.
REASSIGN OWNED BY xyz TO postgres; DROP OWNED BY xyz;
DROP OWNED also gets rid of all privileges and default privileges!
DROP ROLE xyz;
More detailed explanation: