Why role cannot be dropped because some objects depend on it

Question

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.

Outline

Scipt in question already contains reassign owned by "roletodelete" to mydb_owner . How to try it more? – Outline

Then maybe that role owns objects in another database – Gringo

It should not have. It was created and used in this database only. But there are total 30 databases, maybe it is used by mistake. How to check this ? Maybe some query on common system tables can show where role is still used ? – Outline

I ran it as superuser and got more details DETAIL: privileges for default privileges on new relations belonging to role someotherrole in schema public privileges for default privileges on new relations belonging to role someotherrole in schema firma1 How to drop them also ? – Outline

Answer 1

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.

Urology

Why you dont use drop owned by <user> ? Your code is required only if user has privileges in other database to clear it before dropping since drop owned by drops only in current database. – Outline

If you happen to forget to REASSIGN before you DROP, this will lead to potential catastrophic data loss. being explicit using REVOKE will prevent this issue. – Urology

this was the only thing that worked for me out of quite a few answers, thank you – Bassett