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

[rc_]

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

[enocom]

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

[rc_]

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
```

[enocom]

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?

[rc_]

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!

[jkteague]

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

[sreejesh]

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