How to delete a role in PostgreSQL

by Daniel Pham
Published: Updated:

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?

how-to-delete-a-role-in-postgresql
ERROR: role “your.role.name” cannot be dropped because some objects depend on it

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 !

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

DevOps Lite is a personal blog specializing in technology with main topics about DevOps, DevSecOps, SRE and System Administrator. Articles are shared for free and contributed to the community.

SUPPORT US

FOLLOW US

Subscribe my Newsletter for new blog posts. Stay updated from your inbox!

© 2021-2024 DevOpsLite.com – All rights reserved.

Please write sources “DevOpsLite.com” when using articles from this website.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More

0
Would love your thoughts, please comment.x
()
x

Adblock Detected

Please support us by disabling your AdBlocker extension from your browsers for our website.