Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

DROP ROLE refuses because the role still owns objects or has default privileges

ErrorERROR: role "doomed_role" cannot be dropped because some objects depend on it

Short 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.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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:
    \ddp
  2. 2Match what you see, then jump to its fix

    1. 1DETAIL: owner of table ...The role owns tables, views, schemas or other objectsFix
    2. 2The 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. 3The error repeats after the cleanup, and the DETAIL names objects in another databaseOnly one database was cleanedFix
    4. 4The 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

What does the error look like?

  • ERROR: role "doomed_role" cannot be dropped because some objects depend on it
  • DETAIL: 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

Did this fix it?

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.

\ddp

2Fix

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.

\ddp

Confirmed by postgresql.org

Did this fix it?

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_db

2Fix

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.

\du

Confirmed by postgresql.org

Did this fix it?

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.

\ddp

2Fix

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.

\ddp

Confirmed by postgresql.org

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.