DROP ROLE refuses because the role still owns objects or has default privileges
ERROR: role "doomed_role" cannot be dropped because some objects depend on itShort answer
DROP ROLE fails while the role is referenced anywhere in the cluster. Run REASSIGN OWNED BY the role TO a successor, then DROP OWNED BY the role, in every database of the cluster. DROP OWNED also removes the role's default privilege entries. Then DROP ROLE succeeds.
Fix it fast
1Find out which cause you have
Read the DETAIL lines of the error. They name the objects that block the drop. Then see which default privileges are set for the role.
DROP ROLE doomed_role; -- then, in each database: \ddp2Match what you see, then jump to its fix
- 1
DETAIL: owner of table ...The role owns tables, views, schemas or other objectsFix - 2
The error continues after REASSIGN OWNED, and the DETAIL line talks about privileges instead of ownershipThe role has grants on objects it does not own, or default privilegesFix - 3
The error repeats after the cleanup, and the DETAIL names objects in another databaseOnly one database was cleanedFix - 4
The role should stay owner of its objects, but its ALTER DEFAULT PRIVILEGES entries block the dropYou only want to remove the default privilege, not everythingFix
- 1
What does the error look like?
ERROR: role "doomed_role" cannot be dropped because some objects depend on itDETAIL: owner of table bobs_table
Cause 1 of 4
The role owns tables, views, schemas or other objects
1Check
A role cannot be dropped while it owns objects in any database of the cluster. Ownership is per database, so the objects you see depend on which database you are connected to.
SELECT current_database();
SELECT schemaname, tablename FROM pg_tables WHERE tableowner = 'doomed_role';2Fix
Move everything the role owns to a successor role. Run it in each database that has objects owned by the role.
REASSIGN OWNED BY doomed_role TO successor_role;3Verify
The owned-objects query returns no rows.
SELECT schemaname, tablename FROM pg_tables WHERE tableowner = 'doomed_role';Confirmed by postgresql.org
Cause 2 of 4
The role has grants on objects it does not own, or default privileges
1Check
REASSIGN OWNED only changes owners. It does not touch privileges the role holds on other people's objects. Entries made with ALTER DEFAULT PRIVILEGES also tie the role in; \ddp lists them.
\ddp2Fix
Run DROP OWNED after REASSIGN OWNED, never before. It revokes the role's privileges in the current database and removes its default privilege entries.
REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;3Verify
\ddp no longer lists the role.
\ddpConfirmed by postgresql.org
Cause 3 of 4
Only one database was cleaned
1Check
REASSIGN OWNED and DROP OWNED cannot reach other databases. A role stays blocked while any database in the cluster still references it.
\l
\c other_db2Fix
Repeat both commands while connected to each database, then drop the role from any of them. DROP OWNED does not drop databases or tablespaces the role owns, so reassign or drop those yourself.
\c other_db
REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
DROP ROLE doomed_role;3Verify
DROP ROLE finishes without an error.
\duConfirmed by postgresql.org
Cause 4 of 4
You only want to remove the default privilege, not everything
1Check
Default privileges can be reversed one by one, instead of running DROP OWNED. Use FOR ROLE for the role that creates objects, and IN SCHEMA if the entry was per schema.
\ddp2Fix
Revoke the same grant with ALTER DEFAULT PRIVILEGES. A per-schema REVOKE can only undo a per-schema GRANT; a global default needs the form without IN SCHEMA.
ALTER DEFAULT PRIVILEGES FOR ROLE doomed_role IN SCHEMA myschema REVOKE SELECT ON TABLES FROM webuser;3Verify
\ddp no longer shows the entry.
\ddpConfirmed by postgresql.org
How do I stop it coming back?
- Give application objects to a group role that nobody drops, and let people log in through member roles.
- Write ALTER DEFAULT PRIVILEGES for the owner role, not for personal accounts.
- Before dropping a person's role, run the REASSIGN OWNED, DROP OWNED, DROP ROLE sequence in each database as a short script.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
PostgreSQL says must be owner of relation when you alter a table or change its owner
ERROR: must be owner of relation
Only a table's owner or a superuser may alter it, grant on it, or change its owner.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix