postgres built-in user turned uses CLOUD_IAM_SERVICE_ACCOUNT authentication

[Julian Eberius]

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?

[wokmou]

The behavior described is to expected. There is an ongoing feature request [1] for this functionality to be added. 

Note that there are no ETAs or guarantees of implementation for feature requests. All communication regarding this feature request is to be done here[1].

[1] https://issuetracker.google.com/177362572

[Julian Eberius]

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