0

Bug Description

Hi! I'd like to authenticate as a service account to Google Cloud SQL locally using the CloudSQL Proxy. This is done via a Kubernetes sidecar container pattern with the serviceaccount JSON credentials mounted and in GKE with the workload identity feature. However whenever I connect to my Cloud SQL postgresql server via the proxy from my app, it still requests a username and password?

My serviceaccount has the following roles: roles/cloudsql.client, roles/cloudsql.instanceUser and roles/cloudsql.connect. My Cloud SQL database has the IAM permissions flag enabled.

The following does not work and results in the following psycopg2 error:

fe_sendauth: no password supplied

e.g. in Python:

 def getDBConnection(self, dbHost: str, dbPort: int, dbName: str, dbUser: str): dbConn = None try: dbConn = psycopg2.connect(host=dbHost, port=dbPort, database=dbName, user=dbUser) dbConn.execute('SELECT 1') except Exception as e: ... return dbConn

I've also tried without user.

Example code (or command)

 imagePullPolicy: Always image: gcr.io/cloudsql-docker/gce-proxy:latest name: cloudsql-proxy command: ['/cloud_sql_proxy', '-instances=xxx:europe-west1:yyy=tcp:3126'] livenessProbe: tcpSocket: host: '127.0.0.1' port: 3126 failureThreshold: 5 initialDelaySeconds: 5 periodSeconds: 3 env: - name: GOOGLE_APPLICATION_CREDENTIALS value: /sa.json volumeMounts: - mountPath: /sa.json name: sajson readOnly: true resources: requests: cpu: 0.5 memory: 0.5Gi limits: cpu: 0.5 memory: 0.5Gi securityContext: readOnlyRootFilesystem: true privileged: false runAsNonRoot: true allowPrivilegeEscalation: false capabilities: drop: [all] seccompProfile: type: RuntimeDefault

How should I authenticate to Cloud SQL via the IAM service account that is assigned to my Pod? I see examples using username and password authentication but that defeats the point of Workload Identity and IAM service account authentication?

Improve this question
1
  • Do you still have this issue? Commented Jun 1, 2021 at 21:54

1 Answer 1

Reset to default
0

You can use Cloud SQL's IAM Database Authentication feature to create a database account that can only be accessed by a specific IAM identity. It's compatible with the Cloud SQL Auth proxy, so you can modify your existing setup to use it with Workload Identity.

Improve this answer
2
  • Hmm, odd, I am using that feature but it still requests a username and password. ``` resource "google_sql_user" "db_sa" { name = "${split("@", google_service_account.db_sa.email)[0]}@${var.project_id}.iam" project = var.project_id instance = google_sql_database_instance.mydb.name type = "CLOUD_IAM_SERVICE_ACCOUNT" } ``` Commented Apr 15, 2021 at 17:34
  • 1
    If you check out the page on how to log in when using the proxy, you'll see you still need to specify the email of the IAM account you want to use (as the username) but you can leave the password blank. You also need to use the enable-iam-login flag. cloud.google.com/sql/docs/postgres/iam-logins#using_with_the Commented Apr 15, 2021 at 22:15

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.