Connecting GKE worloads to Cloud SQL databases

Creating the SQL database instance

At the time of writing, Google Cloud Platform supports MySQL, PostgreSQL and SQL Server as managed SQL database services. Use your favorite tool to create the SQL database instance and the other associated resources (users, databases...).

With Terraform :

• To create the SQL database instance, we can use the Terraform : google sql database instance resource.

• To create database users, we can use the Terraform : google sql user resource. If the created user is of type BUILT_IN (the default one) with a defined username/password, it will be granted the cloudsqlsuperuser role that gives him the permission to read/write all databases and create other users.

• To create a database inside the SQL database instance we can use the Terraform : google sql database resource.

We want the GKE cluster access the SQL database instance through a private IP. To achieve that, we need to do the following :

• Create a Google SQL managed network associated with the VPC on which the GKE cluster subnetwork resides
• That Google SQL network is managed by google so private IPs ranges are chosen automatically
• A peering link between all subnets of the VPC and the managed network for SQL instances will be automatically created
• When creating the SQL database instance, we have to specify a VPC. The VPC to specify is the one already peeared with the managed network for Google SQL database instances. That way all subnets inside the VPC will be able to communicate privately with SQL databases

Not very intuitive I now, but that’s how this works. The GKE cluster we use must be configured as a VPC native one.

Creating an IAM service account

The IAM service account we create will be used to make our cloud-sql-proxy pods connect to the SQL database instance thanks to workload identity. Use your favorite tool to create the IAM service account.

With Terraform :

• We can use the Terraform : service account resource.

• We then need to grant the cloudsql.client role to the service account in order to permit connexions to SQL database instances. We can use the Terraform : service account roles/permissions resource to grant the required permissions to the service account.

Workload identity will make the Kubernetes service account attached to our cloud-sql-proxy pods, inherit the permissions of the IAM service account, and therefore allow the cloud-sql-proxy pods to connect to the SQL database instance.

Our app container will then connect to the SQL database instance through the cloud-sql-proxy container. In fact, we will have one pod with two containers : one for the app, one for cloud-sql-proxy.

Configuring workload identity

Whithout workload identity, we will have to export the IAM service account key, create a Kubernetes generic secret from that key, make that key available as a file inside our cloud-sql-proxy pods, and then tell cloud-sql-proxy to use that file as its credentials when trying to connect to the SQL database instance.

Before configuring workload identity, we have to make sure the following pre-requistes are met :

• Workload identity activated on the GKE cluster. Can be achieved with :
  • the gcloud CLI : --workload-pool=PROJECT_ID.svc.id.goog option during cluster creation or update. Full command here
  • the Google Cloud console : here is a link detailing the steps to perform
• Workload identity activated on the GKE nodepools. Can be achieved with the gcloud CLI once workload identity is activated on the cluster :
  • --workload-metadata=GKE_METADATA option during nodepool creation or update. Full command here

Now we will configure workload identity in order to make our GKE service account act as the IAM service account. We need the following elements for that :

• IAM_SA_ID : the ID of the IAM service account our GKE service account will act as. Will be in the form :
  • <service-account-name>@<gcp-project-id>.iam.gserviceaccount.com
• GKE_SA_NAME : the name of the GKE service account that will act as the IAM service account. The GKE service account has to be annotated with the IAM_SA_ID as shown below :

annotations:
  iam.gke.io/gcp-service-account: <IAM_SA_ID>

• GKE_NAMESPACE : the GKE namespace containing our resources (app pods, service account...)
• PROJECT_ID : the GCP project ID containing our resources (the GKE cluster, the IAM service account...)

Once we have the necessary elements properly configured, the workload identity two ways binding between the IAM and the GKE service account can be activated with the following gcloud CLI command. Make sure to export the required environment variables before running the command :

$ gcloud iam service-accounts add-iam-policy-binding $IAM_SA_ID \
   --role roles/iam.workloadIdentityUser \
   --member "serviceAccount:${PROJECT_ID}.svc.id.goog[${GKE_NAMESPACE}/${GKE_SA_NAME}]"

After that, our GKE service account will act as the IAM service account and therefore inherits its permissions. Pods using that Kubernetes service account will be able to perform actions the IAM service account is allowed to.

Connecting GKE pods to the SQL database instance

First we have to make sure the Cloud SQL Admin API is activated on our GCP project.

Once that's done, and workload identity properly configured, the following example manifest could be used to connect our app containers to the SQL database instance through cloud-sql-proxy.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: myapp
  namespace: myapp
  labels:
    app: myapp
spec:
  replicas: 1
  selector:
    matchLabels:
      app: myapp
  template:
    metadata:
      labels:
        app: myapp
    spec:
      serviceAccountName: myapp # the Kubernetes service account inheriting 
                                # the IAM service account disposing of the
                                # cloudsql.client role. That permission
                                # inheritance is possible thanks to the workload
                                # identity freature we configured. 
      containers:
      - name: cloud-sql-proxy
        # Available cloud-sql-proxy versions that 
        # can be used as the container image tag :
        # https://github.com/GoogleCloudPlatform/cloud-sql-proxy/releases
        image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.0.0
        args:
          - "--private-ip" # use the private IP of the SQL 
                           # database instance to connect
          - "--port=3306"  # The port on which the proxy will listen on
                           # for connections that will be forwarded to the
                           # configured SQL database instance. The default
                           # listen host is 0.0.0.0 (all network interfaces)
          - "<THE_SQL_DATABASE_INSTANCE_CONNECTION_NAME>"
        securityContext:
          runAsNonRoot: true
        resources:
          requests:
            memory: "500Mi"
            cpu:    "0.6"
      - name: myapp
        image: myapp/myapp:0.0.1
        ports:
          - containerPort: 80
        env:
          - name: database_host
            value: "127.0.0.1"  # IP used to reach te SQL database instance
                                # through the cloud-sql-proxy container as the
                                # cloud-sql-proxy container is a sidecar container
                                # of the current pod
          - name: database_port
            value: "3306"       # The port the cloud-sql-proxy is listening on
                                # for forwarding connections to the
                                # SQL database instance
          - name: database_user
            value: "myapp"
          - name: database_name
            value: "myapp"
          - name: database_password
            valueFrom:
              secretKeyRef:
                name: myapp-database
                key: myapp_database_password
        resources:
          limits:
            cpu: 0.5
            memory: "500Mi"
          requests:
            cpu: 0.5
            memory: "500Mi"