Postgres IAM authentication failed (err: caller does not have permission)

Google Cloud Database
1

I’m trying to set up Automatic IAM [0] to connect to a Cloud SQL Postgres instance locally using Cloud SQL Proxy, and a service account credential file.

Upon connecting to the databse, I receive an error:

FATAL: Cloud SQL IAM user authentication failed for user "me@redacted-project-12345.iam"

I’m starting Cloud SQL Proxy with:

./cloud_sql_proxy -instances redacted-project-12345:us-east1:database=tcp:9470 -credential_file key.json -enable_iam_login

In GCP Logs Explorer, I see:

db=postgres,user=me@redacted-project-12345.iam FATAL: Cloud SQL IAM user authentication failed for user \"me@redacted-project-12345.iam\"
db=postgres,user=me@redacted-project-12345.iam DETAIL: The caller does not have permission\nConnection matched pg_hba.conf line 20: \"local all +cloudsqliamuser cloudsql-iam-user\"

I have verified that:

  • IAM Authentication is ON for Postgres instance I’m connecting to
  • Service Account has sufficient permissions (Cloud SQL Admin, Cloud SQL Instance User, Service Account Token Creator)
  • Database user (me@redacted-project-12345.iam) has sufficient permissions (CONNECT/USAGE/etc.)
  • cloud_sql_proxy is started with -enable_iam_login flag
  • Connection settings are correct per documentation [0] (SSLmode off, user=me@redacted-project-12345.iam, password empty)

Any ideas on what could be causing this?

[0] https://cloud.google.com/sql/docs/postgres/iam-logins

2

Looks like you’ve got all the configuration right. Is the service account key file for the same service account database user?

3

Yes - the json keyfile I’m passing to ./cloud_sql_proxy -credential_file is for the same service account database user.

Output of cloud_sql_proxy, if it helps:

$ ./cloud_sql_proxy -instances redacted-project-12345:us-east1:redacted-db=tcp:9470 -credential_file key.json -enable_iam_login
2022/08/10 00:16:58 Rlimits for file descriptors set to {Current = 8500, Max = 9223372036854775807}
2022/08/10 00:16:58 using credential file for authentication; email=me@redacted-project-12345.iam.gserviceaccount.com
2022/08/10 00:16:59 Listening on 127.0.0.1:9470 for redacted-project-12345:us-east1:redacted-db
2022/08/10 00:16:59 Ready for new connections
2022/08/10 00:16:59 Generated RSA key in 160.87025ms
2022/08/10 00:17:00 New connection for "redacted-project-12345:us-east1:redacted-db" # <-- Establish connection
2022/08/10 00:17:00 refreshing ephemeral certificate for instance redacted-project-12345:us-east1:redacted-db
2022/08/10 00:17:02 Scheduling refresh of ephemeral certificate in 59m2s
2022/08/10 00:17:03 Instance redacted-project-12345:us-east1:redacted-db closed connection # <-- Cloud SQL IAM user authentication failed
4

So it’s a guessing game now as your setup sounds perfectly correct but obviously isn’t. Have you tried going through the process of adding a new IAM user to the database, getting that user’s credentials file, and trying again?

5

I tested this on a fresh instance and could not reproduce it. I’ll debug this further and post updates here.

Thank you for your time!

6

Hi, did you ever figure out what was causing this?

7

all setup looks correct in my config. but getting this error while connecting from GKE → postgress. any idea what is the root cause?