How to use --auto-iam-authn in my deployment.flile and also getting Access denied for user <user_iam>@'cloudsqlproxy~ip'

[Levi-coder07]

Question

How to use --auto-iam-authn correctly and ensure that it works?

Code

command:
          - "/cloud-sql-proxy"
          - "--private-ip"
          - "--auto-iam-authn"      
          - "--structured-logs"
          - "--port=3306"
          - "devops-challenge-2024:us-central1:db-bookings"

Additional Details

The proxy works like a charm with the KSA binded with GSA but when i want to enter with the sa-backend-iam with --auto-iam-authn i get the error
Access denied for user 'sa-backend-iam'@'cloudsqlproxy~179.63.4.243' (using password: YES)

[jackwotherspoon]

@Levi-coder07 Thanks for raising an issue on the Cloud SQL Proxy!

TLDR; Only the SA used to start the Cloud SQL Proxy can login to the database with IAM AuthN

For Cloud SQL IAM Database AuthN to work correctly the IAM principal used to invoke/start the Proxy must match that of the IAM principal being used as the IAM database user.

The proxy works like a charm with the KSA binded with GSA but when i want to enter with the sa-backend-iam with --auto-iam-authn i get the error

If you are starting the Proxy with a Kubernetes service account impersonating an IAM service account, only this SA will be able to login via the Proxy with IAM database authN.

There are two potential solutions to this issue:

1. Start the Proxy as sa-backend-iam and then you will be able to successfully login as sa-backend-iam
2. Start the Proxy with the KSA using service account account impersonation flag to impersonate sa-backend-iam, this will allow you to login as sa-backend-iam

How to use the Proxy with SA impersonationn:

cloud-sql-proxy/cmd/root.go

Lines 193 to 218 in 9cb444c

	Service Account Impersonation
	The Proxy supports service account impersonation with the
	--impersonate-service-account flag and matches gclouds flag. When enabled,
	all API requests are made impersonating the supplied service account. The
	IAM principal must have the iam.serviceAccounts.getAccessToken permission or
	the role roles/iam.serviceAccounts.serviceAccountTokenCreator.
	For example:
	./cloud-sql-proxy \
	--impersonate-service-account=impersonated@my-project.iam.gserviceaccount.com
	my-project:us-central1:my-db-server
	In addition, the flag supports an impersonation delegation chain where the
	value is a comma-separated list of service accounts. The first service
	account in the list is the impersonation target. Each subsequent service
	account is a delegate to the previous service account. When delegation is
	used, each delegate must have the permissions named above on the service
	account it is delegating to.
	For example:
	./cloud-sql-proxy \
	--impersonate-service-account=SERVICE_ACCOUNT_1,SERVICE_ACCOUNT_2,SERVICE_ACCOUNT_3
	my-project:us-central1:my-db-server

Let me know if you need any further clarification or assistance, happy to help more 😄

[fragile-ds]

@jackwotherspoon So u mean , if I run proxy using my service account , like this :
./cloud-sql-proxy --address=0.0.0.0 --port=5432 --auto-iam-authn --private-ip --credentials-file=fragile-sql.json --structured-logs toss-a-coin-dev-XXXX:europe-west1:XX-load

and when try to connect using my IAM user psql -h 127.0.0.1 -U [email protected] -d db_name

and face the the error:

psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: Cloud SQL IAM user authentication failed for user "[email protected]"

It is expected behaviour , because my IAM user is not equal to sa which I use to run the proxy ?

[jackwotherspoon]

It is expected behaviour , because my IAM user is not equal to sa which I use to run the proxy ?

@fragile-ds This is exactly correct, it is expected behaviour.

In the example you provided you will only be able to login using IAM service account principal from fragile-sql.json and not your IAM user [email protected].

The IAM Principal (SA or User) used to invoke/start the Proxy must equal the IAM Principal used to login to the database with IAM database AuthN.

[jackwotherspoon]

Going to close this out for the time being, if required feel free to re-open 😄

[fragile-ds]

@jackwotherspoon U saved my evening , best of us, thx a lot)