How to use CloudSQLProxy in Google Cloud Platform?

Photo by Fré Sonneveld on Unsplash

Google’s CloudSQLProxy software (or CloudSQLAuthProxy as they recently started calling it) helps you set up a secure end-to-end connection between your computer and Google’s CloudSQL database servers.

If you’d like to learn more about the “WHY” and the benefits of using CloudSQLproxy, check out my previous article of this series: CloudSQLProxy - the wiser choice than tunneling to CloudSQL via bastion.

Different applications from different environments can connect to the same CloudSQL DB Instance - each using its own CloudSQLProxy

First things first — the setup!

We will first see the various ways how CloudSQLProxy software can be started. We will then see, how to decide between these various ways. The decision factors will depend on things like ServiceAccount [SA] Key Files, cloud IAM roles, public/private IPs, etc.

Step 1: Download the CloudSQLProxy software

“We all understand better with some pizza. Here. Have a slice.”. Photo by Quin Engle on Unsplash

For Mac OS 64 bit systems:

curl -o cloud_sql_proxy \
  https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64
chmod +x cloud_sql_proxy

For Linux 64 bit systems:

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 \
  -O cloud_sql_proxy
chmod +x cloud_sql_proxy

For Dockerized systems (as of the time of this writing):

docker pull gcr.io/cloudsql-docker/gce-proxy:1.22.0

For GKE clusters:

An elaborate section is dedicated below.

For other systems:

Check out https://cloud.google.com/sql/docs/mysql/connect-admin-proxy#install

Step 2: Start the CloudSQLProxy software

Photo by Start Digital on Unsplash

You will need to know the INSTANCE_CONNECTION_NAME of the ClouSQL Instance to which your CloudSQLProxy software should connect to. You can find this information from the Overview page of the CloudSQL Instance in the GCP console.

For non-dockerized systems where you do not need SA key file:

./cloud_sql_proxy \
  -instances=INSTANCE_CONNECTION_NAME=tcp:0.0.0.0:1234

The port number 1234 can be anything unused in the host. Your apps/tools/CLI will connect to this port number later. Conventional port numbers (although not required by CloudSQLProxy):

• MySQL: 3306
• Postgres: 5432
• SQLServer: 1433

For non-dockerized systems where you need SA key file:

./cloud_sql_proxy \
  -instances=INSTANCE_CONNECTION_NAME=tcp:0.0.0.0:1234 \
  -credential_file=PATH_TO_SA_KEY_FILE

The port number 1234 can be anything unused in the host. Your apps/tools/CLI will connect to this port number later.

For dockerized systems (as of the time of this writing):

docker run -d \
  -v PATH_TO_SA_KEY_FILE:/config \
  -p 127.0.0.1:1234:5678 \
  gcr.io/cloudsql-docker/gce-proxy:1.22.0 /cloud_sql_proxy \
  -instances=INSTANCE_CONNECTION_NAME=tcp:0.0.0.0:5678 \
  -credential_file=/config

The SA key file is being mounted to a path with the -v flag before it can be used by the -credential_file flag.

The port number 5678 can be anything unused in the container.

The port number 1234 can be anything unused in the host. Your apps/tools/CLI will connect to this port number later.

For GKE clusters:

An elaborate section is dedicated below. Read on.

For other systems:

Check out https://cloud.google.com/sql/docs/mysql/connect-admin-proxy#start-proxy

Step 3: Connect to the CloudSQLProxy software

Photo by Roman Kraft on Unsplash

Once the CloudSQLProxy software is up and running, connecting to the proxy from your apps/tools/CLI is as simple as connecting to localhost:1234

mysql --host 127.0.0.1 --port 1234 --user USERNAME --password

The port number 1234 must be the same as what was used when “starting” the CloudSQLProxy software earlier.

Your apps/tools/CLI can now talk to the CloudSQL DB Instance securely.

Step 4: Use CloudSQLProxy from GKE clusters

Photo by Arie Wubben on Unsplash

We will use the SideCar pattern. You only need to create another container inside the same pod configuration as your existing application container.

Simply add a new container under the containers section in your pod/replicaset/deployment YAML.

containers:
- <other containers here...>
- name: cloud-sql-proxy # can be anything as desired
  image: gcr.io/cloudsql-docker/gce-proxy:1.22.0
  command:
    - "/cloud_sql_proxy"
    # If connecting from a VPC-native GKE cluster,
    # and if both the GKE and CloudSQL are in the same VPC,
    # you can connect to the CloudSQL Instance via its private IP
    # which helps to largely eliminate network costs
    - "-ip_address_types=PRIVATE"
    # The port number 1234 can be anything unused inside the pod.
    # Recommended: MySQL: 3306, Postgres: 5432, SQLServer: 1433
    - "-instances=<INSTANCE_CONNECTION_NAME>=tcp:0.0.0.0:1234"
  # Resource configurations should be adjusted as per needs
  resources:
    requests:
      # The proxy's CPU use scales linearly
      # with the amount of IO between DB & App. 
      cpu:    "100m"
      # The proxy's memory use scales linearly
      # with the number of active connections.
      memory: "128Mi"
    limits:
      cpu:    "200m"
      memory: "256Mi"

Then your application (which is running in another container inside the same pod) will just need to connect to DB host =localhost:1234 - your DB username / DB password / DB name values for the connection remain unchanged.

How did this work without any ServiceAccount [SA]?

The above YAML of a pod/replicaset/deployment will work JUST FINE without having to specify any SA key file at all:

• if your k8s cluster is a GKE cluster
• and if your GKE cluster’s SA (every GKE cluster has one) has been granted the roles/cloudsql.client IAM role to connect to the CloudSQL instance.

Your containers will implicitly use the cluster’s built-in SA and nobody needs to generate (or distribute) any separate SA key file for this to work.

For more options and more examples (with and without SA key file), check out this GCP doc on “Running the Cloud SQL proxy as a sidecar”.

How to take certain decisions about the above?

The three questions you should ask yourself are the following.

Photo by Tingey Injury Law Firm on Unsplash

Question 1: Do you need a ServiceAccount [SA] key fIle?

How you would authorize your CloudSQLProxy software to connect to your CloudSQL DB instance depends on a few factors. Are you connecting:

1. for your application or for your CLI/GUI tools?
2. from your localhost or from a cloud-based server?
3. from GCP infra or from some other cloud provider?
4. from a VM or from a Kubernetes cluster?
5. etc

The rule of thumb is:

• If you are connecting from a machine where you can do gcloud auth login (eg: your local development machine) - then you do not need a SA. Note that this, however, will not apply for CloudSQLProxy being run from Docker containers in your computer because programs inside docker containers do not run in the same environment as the host computer.
• If you are connecting from a machine that has built-in SA (eg: most GCP services like GCE VM, GKE Cluster, Cloud Functions, Cloud Runs, etc - then you do not need a SA key file. These GCP services have built-in SA and you just need to authorize those built-in SA’s access to your CloudSQL instances.
• If none of the above applies - for example: running from a remote server that is not in GCP (on-prem or another cloud), or running from a docker container (inside a VM or localhost), etc - then you will need a SA key file.

Question 2: Do you need any Cloud IAM role?

Anyone or anything (with or without a SA key file) trying to connect with a CloudSQL Instance must be granted the roles/cloudsql.client IAM role for the GCP project that is hosting the CloudSQL Instance

The rule of thumb is:

• If you are connecting from a machine where you can do gcloud auth login, then your google user account (or a google group that you belong to) must be granted the IAM role. P.S. granting on the group is recommended than granting on the individual user.
• If you are connecting from a machine where you need a SA (regardless of with or without a key file), then that SA must be granted the IAM role.

Question 3: Does the CloudSQL instance need a Public IP?

Your CloudSQL instance will always have a Private IP. But whether you should assign it a Public IP or not depends on whether there will be anyone (or anything) connecting to it from outside its VPC.

The rule of thumb is:

• If the parties connecting to the DB are from outside the VPC of the CloudSQL Instance, then Public IP is required
• If the parties connecting to the DB are from within the same VPC as the CloudSQL instance itself, then Public IP is not required. In such cases, you can include a flag like -ip_address_types=PRIVATE in the CloudSQLProxy start command for it to use private IPs.

Note, however, that you will never need to share that Public / Private IP with anyone (or anything) at all. The advantage of connecting via the CloudSQLProxy software is that it will take care of abstracting away any IP address (public or private). However, the instance itself needs to have a Public/Private IP for the CloudSQLProxy software to be able to create a connection with it. That’s the only reason why a Public / Private IP is needed - but nobody needs to know what its value is.

Conclusion

Photo by Braden Collum on Unsplash

It may all seem a bit overwhelming at the beginning if you are coming from a “just-specify-the-host-IP-and-get-it-done-with” background. However, that can end up making your DB instance susceptible to external attacks if your DB instance is not hardened / secured / firewalled / armored correctly.

With CloudSQLProxy, it allows you to establish a secure connection between your intended users/tools/apps and the DB servers end-to-end while greatly reducing the surface of attack & abuse on your DB servers by externals.

For more information on the “WHY” of CloudSQLProxy, check out my previous article on “CloudSQLProxy - the wiser choice than tunneling to CloudSQL via bastion”.