raynix 筆記

How to Connect to Cloud SQL via SSH

TL;DR: here’s a handy bash script which can connect to a private Cloud SQL instance(MySQL in this case) via a bastion host.

Prerequisites:

  • Google Cloud SQL instance with mTLS certs provisioned
  • Google Cloud SDK installed(the gcloud command)
  • a bash shell(better be V5.0+)
  • OpenSSH and MySQL CLI installed

Google Cloud SQL is an RDB as a service very similar to AWS RDS. To secure the connections to the databases in Cloud SQL, I can either use public IP + mTLS + IP whitelists or only use private IP with optional mTLS encryption at transit.

I’m not saying which one is more secure but I personally prefer to use private IP and connect through private networks. Below is a handy bash script I use to connect to a Cloud SQL instance via a bastion host.

#!/bin/bash

set -eu
if [[ $# == 0 ]]; then
  echo "Usage: $0 <cloudsql instance name> -u <mysql user> -p ... [optional mysql cli switches]"
  exit 1
fi

MYSQL_PORT=${MYSQL_PORT:-3306}
INSTANCE=$1
shift

# clean up regardless how the script finishes
trap "rm ca.pem client-cert.pem client-key.pem; ssh -S bastion-socket -O exit my.bastion.dns" EXIT

PROJECT=${PROJECT:-my-gcp-project-id}
echo Setting default GCP project to $PROJECT...
gcloud config set project $PROJECT

echo Retrieve Cloud SQL instance IP...
CLOUDSQL_HOST=$(gcloud sql instances describe $INSTANCE --format='value(ipAddresses[0].ipAddress)')

echo Extracting server CA certificate via cloud sql API...
gcloud beta sql ssl server-ca-certs list \
  --format="value(cert)" \
  --instance=$INSTANCE > ca.pem

# assuming the client-cert has a CN of ${INSTANCE}-client
echo Extracting client certificate via cloud sql API...
gcloud sql ssl client-certs describe ${INSTANCE}-client \
  --instance=$INSTANCE \
  --format="value(cert)" > client-cert.pem

# assuing the client private key is in a GSM secret
echo Extracting client private key from GSM...
gcloud secrets versions access latest \
  --secret ${INSTANCE}-client-key-pem > client-key.pem

echo SSH Tunneling...
ssh -fN -L $MYSQL_PORT:$CLOUDSQL_HOST:3306 \ # standard local port forwarding running in background
  -M -S bastion-socket \ # use a master socket to clean up later
  -o UserKnownHostsFile=/dev/null \ # ignore host key
  -o StrictHostKeyChecking=no \
  my.bastion.dns

echo Running mysql client...
mysql -h 127.0.0.1 -P $MYSQL_PORT \ # connect to the forwarded port
    --ssl-ca=ca.pem \
    --ssl-cert=client-cert.pem \
    --ssl-key=client-key.pem $@ # pass along arguments

🙂

Reference:

admin

, ,