How To Drop A Postgres Role/User With privileges

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

mydatabase=# drop user jack;
ERROR:  role "jack" cannot be dropped because some objects depend on it
DETAIL:  owner of view jacktest_view
owner of table jacktest
privileges for default privileges on new relations belonging to role postgres in schema public

postgres=# drop user jack;
ERROR:  role "jack" cannot be dropped because some objects depend on it
DETAIL:  3 objects in database mydatabase

1. Before going to solution, list all privileges of a role (grantee)

mydatabase=# SELECT grantor, grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'jack';
 grantor | grantee | table_schema |  table_name   | privilege_type 
---------+---------+--------------+---------------+----------------
 jack    | jack    | public       | jacktest      | TRUNCATE
 jack    | jack    | public       | jacktest      | REFERENCES
 jack    | jack    | public       | jacktest      | TRIGGER
 jack    | jack    | public       | jacktest_view | TRUNCATE
 jack    | jack    | public       | jacktest_view | REFERENCES
 jack    | jack    | public       | jacktest_view | TRIGGER
(6 rows)

mydatabase=# \ddp+                                                                                                                                                                                                                                                           
            Default access privileges                                                                                                                                                                                                                                                     
  Owner   | Schema | Type  |  Access privileges                                                                                                                                                                                                                                           
----------+--------+-------+---------------------                                                                                                                                                                                                                                         
 postgres | public | table | readonly=r/postgres+                                                                                                                                                                                                                                         
          |        |       | jack=arwd/postgres                                                                                                                                                                                                                                           
(1 row)                                                                                                                                                                                                                                                                                   

2. Quick way to drop the user

• On default database ```

postgres=# REASSIGN OWNED BY jack TO postgres;
postgres=# DROP OWNED BY jack;

- Repeat above step on the database which is showed about `DETAIL:  3 objects in database mydatabase`

mydatabase=# REASSIGN OWNED BY jack TO postgres;
mydatabase=# DROP OWNED BY jack;
mydatabase=# DROP USER jack;

**3. Another way: REVOKE all privileges in the privilege_type list**

postgres=# REVOKE TRUNCATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public FROM jack;

Comments

[Duy Nguy3n]

thank you, it solved my problem.

[🚀 Vu Dao 🚀]

Glad to hear this!

[Blake (Tuan Nguyen)]

Very helpful, thanks !