How to delete a role in PostgreSQL

How to delete a role in PostgreSQL? This short post will show you the way to do it in just a few steps.

What is role in PostgreSQL?

Yeah, it’s really simple for someone who is already working with PostgreSQL.

Simply, a role is a set of rights, so in PostgreSQL, a role is both a user and a group of rights, or it can be a group of other roles.

In the normal case, we usually create a new role for a member, such as a developer (I’m not gonna tell you why we’re not creating a user instead of a role, just a normal case).

If you want to know more about the difference between role and user, just google more for it or read this post from Google.

What is the problem when deleting a role in PostgreSQL?

So we’re going to talk about the problem, right?

I have to write this post to note it for myself because I have faced the same issue when deleting the role.

You may run this command to delete a role from starting.

DROP ROLE "your.role.name";

And you may have gotten this error log. Some guides on the internet may tell you to list all the current tables that have been granted to the role, but there is no need at all.

Error in query:
ERROR: role "your.role.name" cannot be dropped because some objects depend on it
DETAIL: privileges for database yourdatabase

Now, how do I resolve this problem?

Delete a role in PostgreSQL

If you’re facing the error above, then you can follow the steps below to delete a role successfully.

After logging in to your PostgreSQL database (for example, database is yourdatabase), run the below commands; remember to replace your role name and database name.

# Revoke the role "your.role.name" from database, schema public and all tables

REVOKE CONNECT ON DATABASE yourdatabase FROM "your.role.name";
REVOKE ALL ON SCHEMA public FROM "your.role.name";
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "your.role.name";

After running the above commands, you can now delete the role without any errors.

DROP ROLE "your.role.name";

Conclusion

This is just a simple guide for guys who are quite new to PostgreSQL (I’m new too). I just shared all the helpful tips with you guys, and hoping it can help you a bit. Cheers !