Why role cannot be dropped because some objects depend on it

8.4k views Asked by At

Database "mydb" is owned by role "mydb_owner".

User "currentuser" tries to delete role "roletodelete" from this database using

revoke all on all tables in schema public,firma1 from "roletodelete" cascade;
revoke all on all sequences in schema public,firma1 from "roletodelete" cascade;
revoke all on database mydb from "roletodelete" cascade;
revoke all on all functions in schema public,firma1 from "roletodelete" cascade;
revoke all on schema public,firma1 from "roletodelete" cascade;
revoke mydb_owner from "roletodelete" cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES from "roletodelete";
GRANT "roletodelete" TO "currentuser";
reassign owned by "roletodelete" to mydb_owner;
drop owned by "roletodelete";
drop role "roletodelete";

I ran it as superuser and got

    ERROR: role "roletodelete" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new relations belonging to role currentuser in schema public
privileges for default privileges on new relations belonging to role currentuser schema firma1

How to create script which is guaranteed to delete role ?

This script already contains:

revoke all on schema public,firma1 from "roletodelete" cascade;

Why postgres complains that privileges for schema public depend on this role if they are revoked ? How to fix this?

There are lot of questions and answers about this like:

Cannot drop PostgreSQL role. Error: `cannot be dropped because some objects depend on it`

PostgreSQL - how to quickly drop a user with existing privileges

https://dba.stackexchange.com/questions/155332/find-objects-linked-to-a-postgresql-role

I put recommendations from them to deletion script above but problem persists.

1

There are 1 answers

3
alexanderpas On BEST ANSWER

Encountered the same problem, and managed to solve it using the following SQL

This should remove all the rights before removing the user:

REASSIGN OWNED BY <user> TO <other-user>;

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON TABLES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON SEQUENCES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON FUNCTIONS FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ROUTINES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON TYPES FROM <user>;

REVOKE GRANT OPTION FOR ALL PRIVILEGES ON SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA <schema> FROM <user>;

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON TABLES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON SEQUENCES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON FUNCTIONS FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON ROUTINES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON TYPES FROM <user>;

REVOKE ALL PRIVILEGES ON SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA <schema> FROM <user>;

DROP ROLE <user>;

This first assigns all database objects to another user, revokes all the privileges, including default privileges, before finally dropping the role itself.