When attempting to remove a user role from PostgreSQL it is possible to get an error. This error will be received if the role attempting to be removed still has access privileges for instance SELECT privileges on specific tables. Below I describe the error as well as how to remove access privileges for each table as well as a link to a previous article we wrote that will loop through every table within a specified database and revoke privileges.
Error Removing Login Role From PostgreSQL Database Via PGAdmin:
Error Removing Login Role From PostgreSQL Database Via PSQL CLI:
- postgres=# drop role user;
- ERROR: role "user" cannot be dropped because some objects depend on it
- DETAIL: 117 objects in database db1
- 106 objects in database db2
- 116 objects in database db3
- postgres=#
As you can see above in both PGAdmin and via the Postgres CLI you cannot remove a login role until all of the objects, which in this case are SELECT privileges, are remove. To do this you can either issue REVOKE commands via the PostgreSQL CLI as shown below or you can use he script we wrote and noted in this article.
Revoke Access Privilege From PostgreSQL User Via PSQL CLI:
- somedatabase=# REVOKE SELECT ON sometable FROM user;
- REVOKE
- somedatabase=#
In the above command you will first need to make sure you login to the PostgreSQL command line for the proper database. Then when you issue the revoke command change sometable to the proper table name and user to the proper username.
Also be sure to check out the article we published titled “How To Remove Role Privileges Created By The PostgreSQL Grant Command, Shell Script To Remove PostgreSQL Priviledges“.
