Cannot delete created role (Postgres 11)

[Mikheev Aleksandr]

I created user observer using postgres user on my PostgreSQL 11 instance and I don't need it anymore. But after all I can't delete it - pgAdmin showing me that my user have connect privileges granted by the cloudsqlsuperuser, and I cannot revoke it to delete my earlier created user account!

somebase=> REVOKE ALL PRIVILEGES ON DATABASE "somebase" FROM observer; 
REVOKE 
somebase=> DROP ROLE observer; 
ERROR: role "observer" cannot be dropped because some objects depend on it DETAIL: privileges for database somebase

I also tried to delete it using gcloud cli and CloudSQL console both and failed. So how to delete it now?

Polybius Tech OÜ is a limited liability company registered in Estonia. Registered number: 14420450. Registered office: Tartu mnt. 43, Tallinn 10128, Estonia.

Any information sent by e-mail by Polybius Tech OÜ ("Polybius") or any of its affiliates is confidential and intended solely for the use of the individual or entity to whom it is addressed. If you are not the named addressee you should not disseminate, distribute or copy this email. If you have received this message in error please delete it and any copies of it and notify the sender immediately. Note that the contents of an attachment to this e-mail may contain software viruses, which could damage your computer system. While Polybius has taken every reasonable precaution to minimise this risk, we cannot accept liability for any damage which you sustain as a result of software viruses. You should carry out your own virus checks before opening the attachment.

[Katayoon (Cloud Platform Support)]

Hi,

As the error message clearly denotes, the reason you cannot delete the user is because that user has objects that depend on it. You first need to drop those objects or reassign them to other user. This thread on Stack Exchange discuss how to find the objects owned by the user. As explained in this Stack Overflow thread, you may try to revoke all the possible privileges with multiple statements like:

REASSIGN OWNED BY observer TO postgres;  -- or TO user

DROP OWNED BY observer;

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM observer;

REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM observer;

REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM observer;

DROP USER observer;

In order to be able to run those commands, you will need to connect to the instance first. Hope this resolves the issue? 

[Aleksandr Mikheev]

Hi! No, it doesn't! Because 'observer' owns no objects, but 'connect' PRIVILEGE!

ERROR:  role "observer" cannot be dropped because some objects depend on it

DETAIL:  privileges for database somebase

I consider it's as a bug, because observer's 'connect' privilege was set by 'cloudsqlsuperuser', and I cannot remove it, because I have no superuser rights, as I have no access to the cloudsqlsuperuser account as well!

[David (Google Cloud Support)]

Since the issue seems to be with the connect privilege, this StackOverflow post may be worth reading as it explains how to revoke the connect privilege. That specific privilege  seems to be given by default upon some object creation to PUBLIC, which may be why you are having difficulties removing it.

If you are having difficulties with the superuser, you should consider using that user, and keep in mind that you can always change its password in the GCP console.

If the recommendation made in StackOverflow does not help and you still believe that this is a bug, as well as further investigation, I suggest that you make a StackOverflow post yourself to perhaps get some support from other users, as well as create an Issue Tracker entry following instructions.

[Lihan Li]

Try these two then delete again.

REVOKE ALL PRIVILEGES ON DATABASE somebase from observer CASCADE;

REVOKE ALL PRIVILEGES ON SCHEMA public from observer CASCADE;

[Aleksandr Mikheev]

Oh, I could be happy to have the superuser privileges once you only give it to me!

Following to https://cloud.google.com/sql/docs/postgres/users 

Because Cloud SQL for PostgreSQL is a managed service, it restricts access to certain system procedures and tables that require advanced privileges. In Cloud SQL, customers cannot create or have access to users with superuser attributes.

That's why I cant revoke CONNECT privileges off the PUBLIC - because cloudsqlsuperuser owns it as well as observer somehow got it from cloudsqlsuperuser - I can't explain! And I have no access to cloudsqlsuperuser account, because:

The postgres user is part of the cloudsqlsuperuser role, and has the following attributes (privileges): CREATEROLE, CREATEDB, and LOGIN. It does not have the SUPERUSER or REPLICATION attributes.

[Olu]

I am not exactly clear about your last message. Are you suggesting that you would like to be granted the SuperUser Role? If that is what you mean, sadly, that is not available at this time. Otherwise, please let me know by clarifying the details shared. 

As you already know, it is expected behavior that Cloud SQL for PostgreSQL restricts the SuperUser access. This is not available for Customers in general. If you have particular requests or changes that you would like implemented, you could always submit a Feature request[1] using the issue tracker link[2]. Please note however, that such feature requests are subject to evaluation by the CloudSQL team and have no ETA for implementation. 

[1]https://cloud.google.com/support/docs/issue-trackers#feature_requests

[2]https://issuetracker.google.com/issues/new