postgresqlrolesddldatabase-permissions

PostgreSQL drop role with default privileges

I am attempting to drop a role and have severed all ties to it that I can locate, but there is one lingering issue I cannot resolve. When I run this:

drop role hank

It tells me:

ERROR:  role "hank" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new functions belonging to role brandon in schema alteryx
privileges for default privileges on new relations belonging to role brandon in schema alteryx

This DDL exists on the schema:

ALTER DEFAULT PRIVILEGES IN SCHEMA alteryx
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
TO hank;

ALTER DEFAULT PRIVILEGES IN SCHEMA alteryx
GRANT EXECUTE ON FUNCTIONS TO hank;

And when I execute the revoke on them, the command is successful, but the privileges remain intact.

I have scoured the DDL and can't locate how to resolve this without attempting a drop-cascade.

Any guidance is welcome.

Solution

  • You have to run the following two statements to get rid of the default privileges that block you:

    ALTER DEFAULT PRIVILEGES FOR ROLE brandon IN SCHEMA alteryx
   REVOKE ALL ON TABLES FROM hank;
ALTER DEFAULT PRIVILEGES FOR ROLE brandon IN SCHEMA alteryx
   REVOKE ALL ON FUNCTIONS FROM hank;