CODEWITHSUNDEEP
postgresql
SQLSERVERDAWG
SQLSERVERDAWG

Reputation: 67

Dropping role in PostgresSQL

I am having the same issue dropping a role. Did so much research on google but no help.

SQL Error [2BP01]: ERROR: role "readonly" cannot be dropped because some objects depend on it Detail: privileges for default privileges on new relations belonging to role rohit in schema public

Here is what I did to drop role readonly.

REVOKE USAGE ON SCHEMA public FROM readonly;
REVOKE ALL ON SCHEMA public FROM readonly;


REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM readonly;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM readonly;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM readonly;

  
REVOKE CONNECT ON DATABASE kozuchi_db FROM readonly;
REVOKE CONNECT ON DATABASE parc_prod FROM readonly;
REVOKE CONNECT ON DATABASE postgres FROM readonly;


DROP USER readonly;

Upvotes: 2

Views: 3278

Answers (3)

Laurenz Albe
Laurenz Albe

Reputation: 247235

According to your error message, there are some default privileges set for the role. Remove them with

ALTER DEFAULT PRIVILEGES FOR ROLE rohit IN SCHEMA public
   REVOKE ALL ON TABLES FROM readonly;

Upvotes: 4

aveLestat
aveLestat

Reputation: 430

Try this

DROP OWNED BY readonly;
DROP ROLE readonly;

Upvotes: 3

Lohit Gupta
Lohit Gupta

Reputation: 1081

There should be some objects created in your Postgres cluster with the readonly user.

Check in the database objects and change owner of those objects, then try deleting the user.

Upvotes: 0

Related Questions