DEV Community

Cover image for How To Drop A Postgres Role/User With privileges
🚀  Vu Dao 🚀
🚀 Vu Dao 🚀

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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)                                                                                                                                                                                                                                                                                   

Enter fullscreen mode Exit fullscreen mode

2. Quick way to drop the user

  • On default database
postgres=# REASSIGN OWNED BY jack TO postgres;
postgres=# DROP OWNED BY jack;
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode

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

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

Discussion (3)

Collapse
routinggames profile image
Duy Nguy3n

thank you, it solved my problem.

Collapse
vumdao profile image
🚀 Vu Dao 🚀 Author

Glad to hear this!

Collapse
tuannguyen91vn profile image
Blake (Tuan Nguyen)

Very helpful, thanks !