Unable to pass IAM authentication to cloud SQL , using service account

[fragile-ds]

Question

I followed instructions step by step from this thread (#2341), but unfortunately, I still couldn’t make it work. Here’s what I did:

1)Created a new service account with the following roles:

roles/cloudsql.admin
roles/cloudsql.instanceUser
roles/iam.serviceAccountTokenCreator

2)Enabled IAM authentication on the Cloud SQL database by setting:
cloudsql.iam_authentication=on

3)Added the service account to Cloud SQL Users:
[email protected] | IAM (service account)

4)Ran Cloud SQL Proxy using the service account:

./cloud-sql-proxy \ --impersonate-service-account=debug-sql@project-dev-428212.iam.gserviceaccount.com \ --auto-iam-authn \ --private-ip \ --address=0.0.0.0 --port=5432 \ project-dev-428212:europe-west1:debug-sql
5)Tried to connect using the same service account:
psql -h 127.0.0.1 -U [email protected] -d db

Received the following error:
psql: error: connection to server at "127.0.0.1", port 5432 failed:
FATAL: password authentication failed for user "[email protected]"

Important Note:

When I try to connect using my IAM user (e.g. [email protected] ), I receive the error:
FATAL: Cloud SQL IAM user authentication failed for user "[email protected]"
However, when I attempt to log in using the service account, the database immediately asks for a password, as if the service account were treated as a regular PostgreSQL user rather than an IAM principal.

I would appreciate any insight into what might be causing this issue. Thank you in advance

Code

No response

Additional Details

No response

[fragile-ds]

@jackwotherspoon

I wanted to share that I finally found the root cause of the issue.

It turns out that the original email of the service account was:
[email protected]

However, after adding the service account as a user in Cloud SQL, its username in the database was truncated to:
[email protected]
(without gserviceaccount.com).

So, the correct command for connecting to the database turned out to be:

psql -h 127.0.0.1 -p 5432 -U [email protected]

Everything works perfectly now!

Thank you very much for your previous help and valuable advice. If I may suggest, highlighting this behavior somewhere in the official Cloud SQL documentation could be very helpful for others facing similar issues.

Best regards!

[jackwotherspoon]

Hi @fragile-ds 😄

Glad you were able to solve the problem. Yes the .gserviceaccount.com is stripped from all IAM service account database users. The reason for this being that Postgres has a 64 character limit for database usernames so to avoid going over it we strip off the suffix to lower the character count.

We do document this for the most part in our Login using IAM Database Authentication page.

If there is a specific page you were looking at that does not have this let me know and I can update it 👍

[fragile-ds]

@jackwotherspoon My fault, sir , missed that moment in documentation-_-
And thx again for ur help and such fast answers )