[google-cloud-sql-discuss] Re: Cannot delete created role (Postgres 11)

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!

On Wednesday, April 8, 2020 at 10:27:21 PM UTC+3, Katayoon (Cloud Platform Support) wrote:

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? 

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.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/212d5a38-87ee-4b65-8a19-31bae8053669%40googlegroups.com.