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.
Encountered the same problem, and managed to solve it using the following SQL
This should remove all the rights before removing the user:
This first assigns all database objects to another user, revokes all the privileges, including default privileges, before finally dropping the role itself.