How to use CloudSQLProxy in Google Cloud Platform?

Photo by Fré Sonneveld on Unsplash
Different applications from different environments can connect to the same CloudSQL DB Instance - each using its own CloudSQLProxy

First things first — the setup!

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:

For other systems:

Step 2: Start the CloudSQLProxy software

Photo by Start Digital on Unsplash

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
  • 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

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

For GKE clusters:

For other systems:

Step 3: Connect to the CloudSQLProxy software

Photo by Roman Kraft on Unsplash
mysql --host 127.0.0.1 --port 1234 --user USERNAME --password

Step 4: Use CloudSQLProxy from GKE clusters

Photo by Arie Wubben on Unsplash
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"

How did this work without any ServiceAccount [SA]?

  • 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.

How to take certain decisions about the above?

Photo by Tingey Injury Law Firm on Unsplash

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

  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?

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?

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.

Conclusion

Photo by Braden Collum on Unsplash

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Syed Rakib Al Hasan

DevOps Engineer, Backend Developer, Cloud Architect, Night time drive-outs & nice hangouts