How To Drop A Postgres Role/User With privileges
Published: 2020-12-17 08:50:43

**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](https://dev-to-uploads.s3.amazonaws.com/i/44u68bx3p1xhuxznu8c4.png)

**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;
```