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?

Mikiso

Answer 1

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

Fawcette

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. – Mikiso

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? – Eponymous

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. – Fawcette

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] – Winograd

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. – Gilemette

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! – Delorasdelorenzo

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. – Lithea

Don't forget to do the reassign / drop owned by... before drop user... :) – Merrymerryandrew

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~ – Prelect

I tried those suggestions but problem persists. I posted it as separate question in #61169108 – Sherlene

Answer 2

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.

Fortunia

I had to add this one as well: REVOKE ALL PRIVILEGES ON DATABASE db_name FROM username; – Candlewick

Also schema privileges. – Janitor

For schema privileges: revoke USAGE on SCHEMA some_schema from username; – Rhizomorphous

I tried this but problem persists in my case. I posted it as separate question in #61169108 – Sherlene

Answer 3

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 ;

Klaipeda

This works very well. I was doing a similar thing. But your queries cover everytyihg. – Append

I had to add: REVOKE all on database myDatabase from user_mike; – Duncan

Freakin' h#$$ ... thanks for this ... finally got rid of it. – Denn

may need REVOKE ALL PRIVILEGES ON DATABASE mydatab FROM user_mike; – Bordure

Answer 4

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

Geer

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!! – Ilana

Answer 5

This worked for me:

DROP OWNED BY dbuser

and then:

DROP USER dbuser

Mcdermott

Answer 6

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;

Climactic

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. – Declaim

Answer 7

This should work:

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

Worth

Answer 8

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:

1. -- repeat the above commands in each database of the cluster
2. "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."

Howlend

Answer 9

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

Murguia

Answer 10

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

$ dropuser someuser

Alaster

Answer 11

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

1. Drop user mapping
2. Revoke usage on foreign data wrapper.

sample queries

DROP USER MAPPING FOR username SERVER foreignservername

REVOKE ALL ON FOREIGN SERVER foreignservername FROM username

Mistake

Answer 12

For example, you log in to apple database with the user(role) john as shown below. *My answer explains how to enable a normal user to use REASSIGN OWNED and DROP OWNED:

psql -U john apple

Then, you can change all the ownerships of apple database, its schemas and tables from david to peter with the command below. *Be careful, if you run the command below by logging in to other database, only the ownership of apple database is changed but not its schemas and tables so you must log in to apple database to change all the ownerships of apple database, its schemas and tables from david to peter:

REASSIGN OWNED BY david TO peter;

Then, you can drop all the privileges owned by david in apple database with the command below. *Be careful, if you run the command below by logging in to other database, you cannot drop all the privileges owned by david in apple database so you must log in to apple database to drop all the privileges owned by david in apple database:

DROP OWNED BY david;

Finally, you can drop the user(role) david as shown below:

DROP ROLE david;

Or:

DROP GROUP david;

Or:

DROP USER david;

Ouphe

Answer 13

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.

Androw

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.) – Mikiso