Cloud SQL Grant Postgres Breaks Login

[Downchuck]

Granting the role of a service-account to another login user will switch that user to be a "service account" even though it is not one.

Thus for example GRANT ROLE "blah@serviceaccount.tld" to "postgres" will change the postgres user in the UI to be a service account with no ability to manage it.

Anyone bumping up against this?

[FrancoGP]

Hi @Downchuck ,

Welcome to Google Cloud Community!
I'm not completely sure I got the question right, but rather than adding the roles directly wouldn't be a good choice to consider service account impersonation for the relevant access in this case?

[-Rhett]

Hi @Downchuck ,

Welcome to the Google Cloud Community!

In addition to @FrancoGP's suggestion, I want to just mention that we’ve been receiving similar reports on the Issue Tracker.

The current workaround is to use REVOKE <IAM USER ROLE> FROM <NON IAM USER> which will restore the non-IAM user's ability to login to the database normally. You’ll need to have another administrative user who can login to the database to perform this workaround.

As a best practice, we generally recommend using groups instead of individual users when managing roles and permissions as this would be more secure and easier to administer.

For now, I recommend to +1 yourself to the public tracker issue and subscribe for updates. You may also consider creating a new issue. Include steps to replicate, configuration settings, screenshots, and other details of the issue to help us investigate. While there isn’t a specific time frame for resolution, our team may sometimes ask for more details, clarification, or a follow up. Once we've fixed an issue in production we'll indicate this and then update and close the bug.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.