How to quickly drop a user with existing privileges

Question

I'm trying to make restricted DB users for the app I'm working on, and I want to drop the Postgres database user I'm using for experimenting. Is there any way to drop the user without having to revoke all his rights manually first, or revoke all the grants a user has?

score 191 · Accepted Answer · edited Aug 19 '16 at 13:46

191

How about

DROP USER <username>

This is actually an alias for DROP ROLE.

You have to explicity drop any privileges associated with that user, also to move its ownership to other roles (or drop the object).

This is best achieved by

REASSIGN OWNED BY <olduser> TO <newuser>

and

DROP OWNED BY <olduser>

The latter will remove any privileges granted to the user.

See the postgres docs for DROP ROLE and the more detailed description of this.

Addition:

Apparently, trying to drop a user by using the commands mentioned here will only work if you are executing them while being connected to the same database that the original GRANTS were made from, as discussed here:

https://www.postgresql.org/message-id/83894A1821034948BA27FE4DAA47427928F7C29922%40apde03.APD.Satcom.Local

edited Aug 19 '16 at 13:46

gilad905

2,842
2
16
23

answered Jun 11 '10 at 14:58

Tim Kane

2,599
3
20
19

15

Doing: `CREATE TABLE foo(bar SERIAL); ALTER TABLE foo OWNER TO postgres; CREATE USER testuser; GRANT ALL ON foo TO testuser; DROP USER testuser` gave the error messages: `ERROR: role "testuser" cannot be dropped because some objects depend on it DETAIL: access to table foo`. However, `DROP OWNED BY testuser` did the trick, apparently Postgres considers grants to be droppable objects. – millimoose Jun 14 '10 at 10:39
2

Please clarify, @Tim Kane and millimoose: I really don't want the original tables to be dropped if I GRANT SELECT ON FOO TO TESTUSER and then DROP OWNED BY TESTUSER. I think you're saying that DROP OWNED BY is only dropping the grants but will not drop the object to which the grant was made. Correct? – Andrew Wolfe Dec 01 '14 at 13:53
4

Andrew, best to read the documentation for clarification. DROP OWNED BY *will* drop tables owned by that user. REASSIGN OWNED BY will reassign those tables to a different user. Choose one. – Tim Kane Feb 09 '15 at 09:16
3

If you are worried about DROP OWNED BY taking out too much after doing REASSIGN OWNED when there are privileges still holding on, you can REVOKE ALL ON ALL [TABLES|SEQUENCES|...] IN SCHEMA [schema name] FROM [role] – jla Mar 05 '15 at 20:55
1

Indeed, the DROP OWNED BY command is a bit ambiguous in its meaning and effects. I had to read the doc carefully to get it right. Thanks for the posts guys. – Sébastien Clément Apr 07 '16 at 19:36
After investigating this further, I have one clarification: The solution is to do *either* REASSIGN OWNED or DROP OWNED. Both aren't required. Do the former to move owned resources to a new user, and the latter drops them from the user. After either of these, you're free to drop the user or group. Thanks for this helpful bit! – Joe Lapp Sep 22 '16 at 18:14
This does not work. Even after running both commands, a second database on my server still refers to the role, causing the drop user command to throw the error `role "myrole" cannot be dropped because some objects depend on it`. – Cerin Nov 02 '16 at 01:01
Don't forget to do the `reassign / drop owned by...` before `drop user...` :) – ThinkBonobo Jun 03 '19 at 15:46
The addition is the key point, thank you very much. I've tried `drop owned by` and it didn't work (silently), but as soon as I connected to the database to which the role has permissions, `role can be dropped message displays a list of privileges` and `drop owned by` succeeded, following `drop role` worked~ – Dmitriy Popov Feb 04 '20 at 16:26
I tried those suggestions but problem persists. I posted it as separate question in https://stackoverflow.com/questions/61168608/why-role-cannot-be-dropped-because-some-objects-depend-on-it – Andrus Apr 12 '20 at 08:21

score 93 · Answer 2 · answered Jan 05 '18 at 16:04

The accepted answer resulted in errors for me when attempting REASSIGN OWNED BY or DROP OWNED BY. The following worked for me:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM username;
DROP USER username;

The user may have privileges in other schemas, in which case you will have to run the appropriate REVOKE line with "public" replaced by the correct schema. To show all of the schemas and privilege types for a user, I edited the \dp command to make this query:

SELECT 
  n.nspname as "Schema",
  CASE c.relkind 
    WHEN 'r' THEN 'table' 
    WHEN 'v' THEN 'view' 
    WHEN 'm' THEN 'materialized view' 
    WHEN 'S' THEN 'sequence' 
    WHEN 'f' THEN 'foreign table' 
  END as "Type"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.array_to_string(c.relacl, E'\n') LIKE '%username%';

I'm not sure which privilege types correspond to revoking on TABLES, SEQUENCES, or FUNCTIONS, but I think all of them fall under one of the three.

I had to add this one as well: `REVOKE ALL PRIVILEGES ON DATABASE db_name FROM username;` — Wojciech Jakubas, Oct 17 '18 at 12:56
For schema privileges: `revoke USAGE on SCHEMA some_schema from username;` — Alphaaa, Feb 24 '20 at 17:16
I tried this but problem persists in my case. I posted it as separate question in https://stackoverflow.com/questions/61168608/why-role-cannot-be-dropped-because-some-objects-depend-on-it — Andrus, Apr 12 '20 at 08:21

Preti · Answer 3 · 2019-11-04T11:34:40.940

38

Here's what's finally worked for me :

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschem FROM user_mike;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschem FROM user_mike;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA myschem FROM user_mike;
REVOKE ALL PRIVILEGES ON SCHEMA myschem FROM user_mike;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschem REVOKE ALL ON SEQUENCES FROM user_mike;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschem REVOKE ALL ON TABLES FROM user_mike;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschem REVOKE ALL ON FUNCTIONS FROM user_mike;
REVOKE USAGE ON SCHEMA myschem FROM user_mike;
REASSIGN OWNED BY user_mike TO masteruser;
DROP USER user_mike ;

edited Nov 04 '19 at 11:34

answered Nov 04 '19 at 11:28

Preti

381
3
3

This works very well. I was doing a similar thing. But your queries cover everytyihg. – Shiplu Mokaddim Jun 05 '21 at 10:02
1

I had to add: `REVOKE all on database myDatabase from user_mike;` – Peroxy Mar 24 '22 at 14:14
Freakin' h#$$ ... thanks for this ... finally got rid of it. – NeilG Jun 02 '22 at 04:38
may need `REVOKE ALL PRIVILEGES ON DATABASE mydatab FROM user_mike;` – WEBjuju Sep 26 '22 at 21:27

score 18 · Answer 4 · answered Mar 01 '13 at 16:17

18

Also note, if you have explicitly granted:

CONNECT ON DATABASE xxx TO GROUP ,

you will need to revoke this separately from DROP OWNED BY, using:

REVOKE CONNECT ON DATABASE xxx FROM GROUP

answered Mar 01 '13 at 16:17

Eric E

191
1
3

I had tried everything above and it just still wasn't working for me, until I scrolled down just a little bit further to this, so now I have some hair left. Some. :D thank you!! – Mitch Kent Jan 18 '19 at 16:28

score 15 · Answer 5 · answered Mar 17 '21 at 07:59

15

This worked for me:

DROP OWNED BY dbuser

and then:

DROP USER dbuser

answered Mar 17 '21 at 07:59

Hrvoje

13,566
7
90
104

score 10 · Answer 6 · answered May 02 '19 at 21:54

I had to add one more line to REVOKE...

After running:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM username;

I was still receiving the error: username cannot be dropped because some objects depend on it DETAIL: privileges for schema public

I was missing this:

REVOKE USAGE ON SCHEMA public FROM username;

Then I was able to drop the role.

DROP USER username;

You may also need to revoke privileges for 'SCHEMA pg_catalog' if you have, for example, created a user for pg_rewind which has privileges over functions like pg_read_binary_file. — GreenReaper, Apr 14 '20 at 01:35

score 7 · Answer 7 · answered Jul 01 '21 at 12:22

7

This should work:

REVOKE ALL ON SCHEMA public FROM myuser;
REVOKE ALL ON DATABASE mydb FROM myuser;
DROP USER myuser;

answered Jul 01 '21 at 12:22

yucer

4,431
3
34
42

score 5 · Answer 8 · answered Sep 16 '19 at 14:15

There is no REVOKE ALL PRIVILEGES ON ALL VIEWS, so I ended with:

do $$
DECLARE r record;
begin
  for r in select * from pg_views where schemaname = 'myschem'
  loop
    execute 'revoke all on ' || quote_ident(r.schemaname) ||'.'|| quote_ident(r.viewname) || ' from "XUSER"';
  end loop;
end $$;

and usual:

REVOKE ALL PRIVILEGES ON DATABASE mydb FROM "XUSER";
REVOKE ALL PRIVILEGES ON SCHEMA myschem FROM "XUSER";
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschem FROM "XUSER";
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschem FROM "XUSER";
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA myschem FROM "XUSER";

for the following to succeed:

drop role "XUSER";

score 3 · Answer 9 · answered Mar 10 '20 at 06:10

3

In commandline, there is a command dropuser available to do drop user from postgres.

$ dropuser someuser

answered Mar 10 '20 at 06:10

SuperNova

25,512
7
93
64

score 2 · Answer 10 · answered Oct 01 '21 at 13:00

https://dbtut.com/index.php/2018/07/09/role-x-cannot-be-dropped-because-some-objects-depend-on-it/

Checked and there was no ownership for any object in db and later realised it may be due to foreign data wrapper mapping created for user and grant permission.

So two actions were required

Drop user mapping
Revoke usage on foreign data wrapper.

sample queries

DROP USER MAPPING FOR username SERVER foreignservername

REVOKE ALL ON FOREIGN SERVER foreignservername FROM username

score 2 · Answer 11 · answered Mar 15 '22 at 18:10

The Postgres documentation has a clear answer to this - this is the ONLY sanctioned answer:

REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
-- repeat the above commands in each database of the cluster
DROP ROLE doomed_role;

Key points:

-- repeat the above commands in each database of the cluster
"it's typically necessary to run both REASSIGN OWNED and DROP OWNED (in that order!) to fully remove the dependencies of a role to be dropped."

score -27 · Answer 12 · edited Dec 09 '15 at 07:11

-27

I faced the same problem and now found a way to solve it. First you have to delete the database of the user that you wish to drop. Then the user can be easily deleted.

I created an user named "msf" and struggled a while to delete the user and recreate it. I followed the below steps and Got succeeded.

1) Drop the database

dropdb msf

2) drop the user

dropuser msf

Now I got the user successfully dropped.

edited Dec 09 '15 at 07:11

YoungHobbit

13,254
9
50
73

answered Dec 09 '15 at 07:03

Meshach Marimuthu

1
2

5

This is an unbelievably slash-and-burn approach, since it would have required me to recreate the database schema for every iteration of my work. (Which involved having fine-grained permissions on an existing database schema; i.e. it's best if the database schema remains untouched.) – millimoose Dec 09 '15 at 22:54

How to quickly drop a user with existing privileges

12 Answers12

Linked