[google-cloud-sql-discuss] Re: postgres built-in user turned uses CLOUD_IAM_SERVICE_ACCOUNT authentication

I meanwhile found the cause for this. I was manually re-assigning ownerships in an existing database, and in this operation ran "GRANT <serviceAccount> TO postgres". This allows to reproduce the behaviour consistently. Grant a role that is configured for IAM to a role that is built-in, and the built-in role will turn into IAM-enabled role which cannot be used for login, as no corresponding service account can exist. Remove the grant via "REVOKE <serviceAccount> FROM postgres" and the postgres user will turn back to a "Built-in".

Not sure if this is a bug, or just me stretching the limits of this Postgres/GCP-IAM bridge?

Thanks anyway,

Julian

On Friday, 12 November 2021 at 19:19:23 UTC+1 Julian Eberius wrote:

Hi everyone,

the default user "postgres", which should always use the authentication type "BUILT-IN", suddenly changed into a user with authentication "CLOUD_IAM_SERVICE_ACCOUNT", see attached screenshot. Consequently, we cannot login using the postgres account anymore, login fails with:

FATAL:  Cloud SQL IAM service account authentication failed for user "postgres"

which makes sense, as there is no matching IAM service account, etc. 

How can such a situation happen without visible cause? How can it be remedied, i.e., how to turn the postgres user back into a "BUILT-IN" user? The API does not seem to provide a way to do this.

Best regards,

Julian Eberius

p.s.: I later created a new account in the Console, and it also turned into a "IAM (service account)" user after a connection via cloud_sql_proxy ( this is not visible in the screenshot). How to stop this effect?

--
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/f19fabd6-a27f-45fc-86fc-9bcd9203bd15n%40googlegroups.com.