DEV Community

loading...
Cover image for How To Drop A Postgres Role/User With privileges

How To Drop A Postgres Role/User With privileges

Vu Dao
Awesome Devops || AWS SA || CloudOpz
・1 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
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 !