Connecting to CloudSQL

To ensure the security of the Cloud SQL instances (Postgres in this case, but this method should work for other DBs as well), public IP has to be disabled, and TLS must be enforced. This poses a few issues when one wants to connect for operational tasks. This guide shows you the recommended way to do this on GAP using Google’s SQL Auth Proxy.

To simplify connecting to the Cloud SQL instance from a local machine, you can use a CLI tool that automates the process, instead of running the below steps manually. Check the GitHub repository emartech/google-cloud-sql-cli for installation and usage instructions.

A few initial configuration steps must be taken before one can go on opening a pod to connect.

• SQL Admin API needs to be enabled on the project hosting the instance (gcloud does it if you run gcloud sql instances list --project <project>)
• Get the connection name
  • gcloud sql instances describe --project <project> <instance> --format json | jq '.connectionName'
  • or manually: <project>:<region>:<instance>
  • example: sap-gap-example-p:europe-west3:example
• A service account must exist that has
  • Cloud SQL Client IAM role in the project hosting your postgres instance
• on GAP a service account must exist that is configured for workload identity with the above mentioned service account

There are two options for one-off database connection:

• run the CloudSQL proxy in GAP, port-forward it to local machine and use whatever client to interact with the database
• run the CloudSQL proxy and a psql in a pod on GAP with interactive shell and use psql cli directly in the pod

This method uses a SQL Auth Proxy on GAP and port-forwarding to your local machine:

kubectl run -i --rm -n <NAMESPACE> --annotations="cluster-autoscaler.kubernetes.io/safe-to-evict=true" --overrides='{
  "apiVersion": "v1",
  "spec": { 
    "serviceAccount": "<SERVICE_ACCOUNT>",
    "securityContext": {
      "runAsUser": 1000,
      "fsGroup": 1000
    },
  },
}' \
--image=gcr.io/cloudsql-docker/gce-proxy:latest psql-client-proxy -- /cloud_sql_proxy -ip_address_types=PRIVATE -instances=<CONNECTION_NAME>=tcp:5432

If you have set up IAM authentication, you have to add the -enable_iam_login flag to the command:

kubectl run -i --rm --tty -n <NAMESPACE> --annotations="cluster-autoscaler.kubernetes.io/safe-to-evict=true" --overrides='{
  "apiVersion": "v1",
  "spec": { 
    "serviceAccount": "<SERVICE_ACCOUNT>",
    "securityContext": {
      "runAsUser": 1000,
      "fsGroup": 1000
    },
  },
}' \
--image=gcr.io/cloudsql-docker/gce-proxy:latest psql-client-proxy -- /cloud_sql_proxy -enable_iam_login -ip_address_types=PRIVATE -instances=<CONNECTION_NAME>=tcp:5432

This will still create an interactive pod, so pressing Ctrl+C will stop and delete the container. And as usual, if there is an error, an investigation and manual deletion are necessary.

As for the port-forwarding:

kubectl port-forward -n <NAMESPACE> psql-client-proxy 5432

After this it is possible to connect using the 127.0.0.1:5432 on the local machine. In case of IAM authentication the user is your-service-account@your-project.iam.

The following command will exec into a shell on the postgres image with the SQL Auth Proxy running as a sidecar. From the shell one can connect to postgres as usual replacing the host with 127.0.0.1.

• SERVICE_ACCOUNT with workload identity bound to the account that got permission above
• CONNECTION_NAME the connectionName extracted above (PROJECT:REGION:INSTANCE)
• NAMESPACE your namespace

kubectl run -i --rm --tty -n <NAMESPACE> --annotations="cluster-autoscaler.kubernetes.io/safe-to-evict=true" --overrides='
{
  "apiVersion": "v1",
    "spec": {
      "serviceAccount": "<SERVICE_ACCOUNT>",
      "securityContext": {
        "runAsUser": 1000,
        "fsGroup": 1000
      },
      "containers": [
        {"name": "psql-client-proxy",
          "image": "postgres",
          "stdin": true,
          "stdinOnce": true,
          "tty": true,
          "command": ["/bin/shell"]
        }, {
          "name": "sql-proxy",
          "image": "gcr.io/cloudsql-docker/gce-proxy:latest",
          "command": ["/cloud_sql_proxy", "-ip_address_types=PRIVATE", "-instances=<CONNECTION_NAME>=tcp:5432"]}]
    }
}' --image=postgres psql-client-proxy

If you have set up IAM authentication, you have to add the -enable_iam_login flag to the command:

kubectl run -i --rm --tty -n <NAMESPACE> --annotations="cluster-autoscaler.kubernetes.io/safe-to-evict=true" --overrides='
{
  "apiVersion": "v1",
    "spec": {
      "serviceAccount": "<SERVICE_ACCOUNT>",
      "securityContext": {
        "runAsUser": 1000,
        "fsGroup": 1000
      },
      "containers": [
        {"name": "psql-client-proxy",
          "image": "postgres",
          "stdin": true,
          "stdinOnce": true,
          "tty": true,
          "command": ["/bin/bash"]
        }, {
          "name": "sql-proxy",
          "image": "gcr.io/cloudsql-docker/gce-proxy:latest",
          "command": ["/cloud_sql_proxy", "-enable_iam_login", "-ip_address_types=PRIVATE", "-instances=<CONNECTION_NAME>=tcp:5432"]}]
    }
}' --image=postgres psql-client-proxy

Run in the shell something like: psql -h 127.0.0.1 -p 5432 …

If you are using IAM login: psql -h 127.0.0.1 -p 5432 -u <your-service-account@your-project.iam> -d <your-database>