'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;
sqlpostgresqlrolesddldatabase-administration


Solution 1:[1]

What worked for me was to follow these steps:

  1. Connecting to the database
\c mydatabase
  1. Reassigning ownership
REASSIGN OWNED BY ryan TO <newuser>;

Or/and just deleting the object

DROP OWNED BY ryan;
  1. Executing REVOKE PRIVILEGES
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM ryan;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM ryan;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM ryan;
  1. Dropping the user
DROP USER ryan;

PS: You might not need to execute both Step 2 and 3, just one of the two steps might be usually enough.

Solution 2:[2]

What worked for me on RDS Postgres 13:

REVOKE ALL PRIVILEGES ON DATABASE <my_db> FROM <my_user>;

I also had a similar error where the role was owner for tables so it couldn't be dropped, had to re-assign table owner with:

ALTER TABLE <my_table> OWNER TO <trusted_role>;

Doing a REASSIGN like this didn't work for me on RDS because AWS doesn't give you full superuser to your master user: REASSIGN OWNED BY <olduser> TO <newuser>;

Solution 3:[3]

What worked for me was to recreate template1 database and then drop some role:

$ psql -U postgres postgres
postgres=# update pg_database set datistemplate = false where datname='template1';
UPDATE 1
postgres=# drop database template1;
DROP DATABASE
postgres=# create database template1 template=template0;
CREATE DATABASE
postgres=# update pg_database set datistemplate = true where datname='template1';
UPDATE 1
postgres=# DROP ROLE test;
DROP ROLE

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 fedorqui
Solution 2 Andrew
Solution 3 user1665355

Related Questions