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

Question

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;

Erwin Brandstetter · Accepted Answer · 2021-10-06T23:39:22.537

187

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 postgres;  -- or some other trusted role
DROP OWNED BY ryan;

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?!)

Drop a role with privileges _{(with a function to generate commands for all relevant DBs)}
Find objects linked to a PostgreSQL role

edited Oct 06 '21 at 23:39

answered Jul 10 '18 at 04:37

Erwin Brandstetter

605,456
145
1,078
1,228

I get this error even when the user doesn't own anything. It merely has GRANT permission on a few tables owned by another user. Therefore this solution won't work because there's nothing to reassign. – Cerin May 07 '20 at 20:09
@Cerin: Did you to see the quoted sentence about `DROP OWNED`? – Erwin Brandstetter May 07 '20 at 20:40
Thank you, one question though -- why you started with `REASSIGN`? – astrowalker Jul 24 '20 at 13:22
1

@astrowalker: I added more explanation above. – Erwin Brandstetter Jul 24 '20 at 14:30
I get "[42501] ERROR: permission denied to reassign objects" error thought use admin account to execute query. – Tomas Sep 22 '20 at 10:36
@Tomas: Is the "admin account" actually a superuser? (`SHOW IS_SUPERUSER`; while connected as the role.) – Erwin Brandstetter Sep 22 '20 at 21:46
@ErwinBrandstetter, thanks! Could you take this comment `-- repeat both in ALL databases where the role owns anything or has any privileges!` out of code block so it would appear more like instruction, not comment about the command? I spent few minutes wondering&experimenting before i noticed it ;) – atsu85 Aug 05 '21 at 15:14
@atsu85: I clarified a bit. – Erwin Brandstetter Aug 05 '21 at 21:07
4

Neither reassigning OWNED nor dropping them worked for me. Still got the same error when running from a superuser. – rovyko Jan 23 '22 at 19:12
@rovyko: It's not "either / or", it's `REASSIGN OWNED ...` *and* `DROP OWNED ...` in every involved database of the same cluster before you can reliably `DROP ROLE ...` – Erwin Brandstetter Jan 23 '22 at 21:14
For those of you who stumble onto this answer when working with Google Cloud SQL, note that it is not currently possible for `REASSIGN OWNER` to work. See https://stackoverflow.com/a/52244674/288341 and https://cloud.google.com/sql/docs/postgres/users#superuser_restrictions – ubiquibacon Aug 26 '22 at 00:34

score 33 · Answer 2 · edited May 11 '22 at 14:53

33

What worked for me was to follow these steps:

Connecting to the database

\c mydatabase

Reassigning ownership

REASSIGN OWNED BY ryan TO <newuser>;

Or/and just deleting the object

DROP OWNED BY ryan;

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;

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.

edited May 11 '22 at 14:53

fedorqui

275,237
103
548
598

answered Feb 05 '19 at 04:42

Samuel Anyaele

450
5
6

3

Use `REVOKE CONNECT ON DATABASE db_name FROM role_name;` and `REVOKE ALL ON SCHEMA public FROM role_name;` to revoke connect and usage privileges respectively. [Source](https://stackoverflow.com/a/28849656/1647238) – William Ardila Oct 15 '20 at 19:23
Above comment works! – Sunding Wei Dec 20 '22 at 04:41

score 11 · Answer 3 · answered Sep 16 '21 at 19:48

11

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

answered Sep 16 '21 at 19:48

Andrew

587
5
6

I had an issue exactly with this stack. My solution is here: https://dba.stackexchange.com/a/301785/224108 – RicHincapie Oct 27 '21 at 22:27

score 7 · Answer 4 · answered Jul 19 '22 at 19:23

7

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

Worked for me.

answered Jul 19 '22 at 19:23

Ercio Alendre

71
1
6

IMHO this is just a part of Andrews answer. – ahuemmer Jul 22 '22 at 13:06
@ahuemmer `REVOKE ALL PRIVILEGES ON DATABASE` is not the same as `REVOKE ALL ON SCHEMA` – Ercio Alendre Jul 23 '22 at 19:31
I'm aware of that, nevertheless the effect would be the same. It also occurs in Samuel Anyaeles answer the way you do it, yours is only a shorter form. – ahuemmer Jul 25 '22 at 06:27

score 0 · Answer 5 · answered Aug 26 '20 at 08:30

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

score 0 · Answer 6 · answered Oct 12 '22 at 07:14

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

login to postgres user, then grant owner

postgres=> GRANT target_user to old_user;
GRANT ROLE

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

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/

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

6 Answers6

Linked