How to quickly drop a user with existing privileges

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?

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

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.

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 ;

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

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;

This worked for me:

DROP OWNED BY dbuser

and then:

DROP USER dbuser

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

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

$ dropuser someuser

This should work:

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

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

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

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.