CODEWITHSUNDEEP
sqlpostgresqlrolesddldatabase-administration
notalentgeek
notalentgeek

Reputation: 5797

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;

Upvotes: 145

Views: 169414

Answers (7)

ken
ken

Reputation: 14545

The answers above are correct; however, they do not explain how to identify the root cause.

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

From the error description, we can see that the issue is with the "privileges" object in database "mydatabase". Thus, first should switch to the database before fixing it.

\c mydatabase;

-- revoke privileges, reassign owned by, then drop the role/user

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657867

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;
  • REASSIGN OWNED changes ownership for all objects currently owned by the role.
  • DROP OWNED then only revokes privileges (ownerships out of the way).

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

Related:

Upvotes: 216

Andrew
Andrew

Reputation: 827

What worked for me on AWS RDS (PostgreSQL 13) is the following:

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>;

In fact, on RDS, AWS doesn't give you full superuser to your master user, so the following REASSIGN command fails:

REASSIGN OWNED BY <olduser> TO <newuser>;

Upvotes: 17

Jay Sithiporn
Jay Sithiporn

Reputation: 91

For people who use AWS Postgresql RDS, you may try following

  1. login to postgres user, then grant owner
postgres=> GRANT target_user to old_user;
GRANT ROLE
  1. Login to target db using user that would like to remove(old_user), then reassign
target_db=> REASSIGN OWNED BY old_user TO target_user;
REASSIGN OWNED
  1. Login back to postgres user, revoke all privileges then drop user
postgres=> REVOKE ALL PRIVILEGES ON DATABASE target_db FROM old_user;
REVOKE
postgres=> DROP USER old_user;
DROP ROLE

Ref. https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-drop-user-role/

Upvotes: 0

Ercio Alendre
Ercio Alendre

Reputation: 111

REVOKE ALL ON SCHEMA "public" FROM "<user>";

Worked for me.

Upvotes: 11

Samuel Anyaele
Samuel Anyaele

Reputation: 500

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.

Upvotes: 38

user1665355
user1665355

Reputation: 3393

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

Upvotes: 0

Related Questions