Python app IAM service account authentication via Cloud SQL Proxy

Question

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?

Answer 1

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.