sqlpostgresqlrolesddldatabase-administration

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

I was trying to delete PostgreSQL user:

DROP USER ryan;

I received this error:

Error in query:
ERROR: role "ryan" cannot be dropped because some objects depend on it
DETAIL: privileges for database mydatabase

I looked for a solution from these threads:

Still have the same error.

This happens after I grant all permission to user "ryan" with:

GRANT ALL PRIVILEGES ON DATABASE mydatabase ON SCHEMA public TO ryan;
Solution

  • DROP USER (or DROP ROLE, same thing) cannot proceed while the role still owns anything or has any granted privileges on other objects.

    Get rid of all privileges with DROP OWNED (which isn't too obvious from the wording). The manual:

    [...] Any privileges granted to the given roles on objects in the current database and on shared objects (databases, tablespaces) will also be revoked.

    So the reliable sequence of commands to drop a role is:

    REASSIGN OWNED BY ryan TO placeholder_role;  -- some trusted role
DROP OWNED BY ryan;

    Rather not re-assign to a superuser, which could lead to unintended privilege escalation. (Think of SECURITY DEFINER functions ...)

    Run both commands in every database of the same cluster where the role owns anything or has any privileges!
    And finally:

    DROP USER ryan;

    Alternatively, you can skip REASSIGN OWNED. Then DROP OWNED will (also) drop all objects owned by the user. (Are you sure?!)

    Related: