How To Drop A Postgres Role/User With privileges

How To Drop A Postgres Role/User With privileges

ยท

3 min read

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

Alt Text

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;