raynix 筆記

Cloud SQL MySQL: Best Password is No Password

Security for web applications has come a long way. In the case of database passwords, there is a lot of approaches to keep them safe:

  • Use a random generated password, so it won’t be easily brute-forced
  • Each app has its own user and password, so if 1 app got comprised the bad actor won’t easily get access to the other app’s database
  • Encrypt the password and put it in a secret manager, such as Vault or Google Secret Manager
  • Rotate the password once in a while

But isn’t it the best if there’s no need for a password at all? With IAM authentication for Cloud SQL, the password can be avoided after all. Here’s how to do it in a few steps:

Preparation

Setup Workload Identity for GKE Service Account

To let an app deployed in a GKE cluster access its Cloud SQL MySQL instance(assuming they’re in the same project for the sake of simplicity, but they can be even in different projects), the Kubernetes service account attached to this app needs to be able to authenticate as the GCP service account we prepared earlier, simply put it looks like:

app -> Kubernetes service account -> workload identity -> GCP service account -> Cloud SQL

The diagram looks one-directional but Workload Identity actually needs to be setup from both ends. For the GCP service account, it can be done via Terraform:

resource "google_service_account" "service_account" {
  project      = "dummy-project-id"
  account_id   = "mysql-app1"
  display_name = var.display_name
}

resource "google_service_account_iam_binding" "workload_identity_user" {
  service_account_id = google_service_account.service_account.email
  role               = "roles/iam.workloadIdentityUser"
  # just use default namespace and default service account for this example
  members            = ["serviceAccount:${var.project}.svc.id.goog[default/default]"]
}

The Kubernetes service account, default/default in the example above, needs an annotation to complete the Workload Identity:

apiVersion: v1
kind: ServiceAccount
metadata:
  annotations:
    iam.gke.io/gcp-service-account: [email protected]
  name: default
  namespace: default

Create the MySQL IAM user for the service account

gcloud sql users create [email protected] \
  --instance=dummy-project-id:my-gcp-region:my-cloudsql-mysql-instance-name \
  --type=cloud_iam_service_account

# Note: if it's for PostgreSQL, the username becomes [email protected] because limitations from PostgreSQL

Deploy Cloud SQL Proxy + MySQL Containers for Testing

Here’s a simple Kubernetes Deployment to run a pod with 2 containers: Cloud SQL Proxy and MySQL. The proxy will use the default service account and delegate login request from the MySQL container to the Cloud SQL instance.

apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: test
  name: test
  namespace: default
spec:
  replicas: 1
  selector:
    matchLabels:
      app: test
  template:
    metadata:
      labels:
        app: test
    spec:
      containers:
      - image: mysql:5.7
        name: mysql
        # we only need to use the mysql cli as client, so no need to run the server
        command:
          - sleep
          - infinity
      - image: gcr.io/cloudsql-docker/gce-proxy:1.33.1
        name: cloudsql-proxy
        command:
          - /cloud_sql_proxy
          - -instances=my-gcp-project-id:my-gcp-region:my-cloudsql-mysql-instance-name=tcp:0.0.0.0:3306
          - -ip_address_types=PRIVATE
          - -enable_iam_login
      # this is example for cloud-sql-proxy v2
      - image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.1.1
      	name: cloudsql-proxy2
        args:
          - --private-ip
          - --auto-iam-authn
          - --address=0.0.0.0
          - my-gcp-project-id:my-gcp-region:my-cloudsql-mysql-instance-name?port=3306

The Test

To run the test, I get an interactive shell of the MySQL container using kubectl exec command and then use mysql client to connect to the Cloud SQL MySQL instance via Cloud SQL Proxy. Since the proxy container is in the same pod, it’s reachable at 127.0.0.1 or localhost.

laptop> kubectl exec -n default -ti -c mysql test-xxxxx-xxxxx -- sh
$ mysql -h 127.0.0.1 -u default -P 3306 --enable-cleartext-plugin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8792
...

This means, an app running the the default service account in default namespace can now authenticate to the MySQL server without using a password, as the password is actually a token supplied automatically by the Cloud SQL Proxy. The token can also be retrieved manually, if for any testing purpose, using:

laptop> gcloud sql generate-login-token
ya29.c.b0AT7lpjDZPUhVn0GJQ...(this is usually very long)

What about Authorization?

At as March 2023, authorization still needs to be done natively. ie. GRANT SELECT...

🙂

admin

, ,