CloudSQLProxy: the wiser choice than Bastion for tunnelling to CloudSQL

Rakib Al Hasan
AirAsia MOVE Tech Blog
5 min readJun 7, 2021

--

Photo by Mitchell Luo on Unsplash

CloudSQLProxy (or CloudSQLAuthProxy as it is now known) is a secure software, developed & maintained by Google Cloud Platform (GCP) themselves, to connect to your CloudSQL database instances, without having to reveal any IP/Host address of your DB, and without requiring you to AllowList any incoming IP addresses (via “authorized network lists” or “access control lists” etc).

Let’s start with a basic real-world scenario

You have a MySQL CloudSQL Database in your GCP Virtual Private Cloud (VPC).

You have your web application servers inside the same VPC connecting to your database (DB) instance via the database’s private IP address.

And you have your developers (in their offices / at their homes) on the other side of the world over the internet

Developers (and “others”) on the internet

How would your developers connect to your DB instance for data-entry / troubleshooting / monitoring / maintenance / etc?

Your developers can use a MySQL client (like MySQLWorkbench, SequelPro, SQLProStudio, TablePlus, etc) to connect to the public IP / host address of your DB instance

Devs connecting to DB via DB Public IP

But it’s a terrible idea to leave your DB’s public IP /host address exposed to the Internet - anybody in the Whole Wide World (yes, www) can misuse it.

A more secure way is to use a form of AllowListing by maintaining something called Authorized Networks - think of it like an Access Control List (ACL) for CloudSQL provided by GCP itself.

Devs authorized to connect to DB via a list of Authorized Networks

This looks good. Only the IP addresses (or perhaps IP ranges) of the people you explicitly authorize will be able to connect to your CloudSQL DB instances - no one else.

However, the list of Authorized Networks comes with its own problems too.

  • Your developers’ IP addresses may change if they are connecting from different remote places at different times.
  • Their IP addresses may change even from the same place, if (let’s say) their home WIFI routers are connecting via DHCP to the internet.
  • People are always joining and leaving your company. Therefore, you need to find a way to constantly add/remove IP entries from this list.

Enter Bastion Hosts

One practice that companies follow is to provide a bastion host for the developers to connect to their infra through. That way, the bastion host maintains a hard line of defense in one place with strict controls and close supervision of accesses by all company employees. Meanwhile, the infra-specific ACLs need to AllowList just this one bastion host.

Devs connect to bastion. Then only bastion is authorized to connect to DB.

Reduced maintenance on the Authorized Networks list. Yay!

However, while this takes away the puzzle of security & maintenance from the ACL for sure, it introduces a new problem - is the bastion host itself secure?

Is the bastion itself secure enough?

Very soon you will find yourself dealing with a never-ending maintenance headache and wish that your life had more meaning. :D

Photo by Christian Erfurt on Unsplash

Enter CloudSQLProxy

CloudSQLProxy (or CloudSQLAuthProxy as it is now known) aims to solve all that. It is a secure software that you run in your localhost.

  • It establishes a secure end-to-end connection (read tunnel) between your computer and the CloudSQL DB instance running in GCP data centers.
  • It exposes a specified port number in your localhost that your local applications & MySQL clients can connect to.
  • It is developed and maintained by the creators of CloudSQL themselves - so there is that sense of trust in its security and design.
  • Nobody needs to know (or share) any public (or private) IP (or host) addresses for you to be able to connect to the DB
  • It only uses IAM roles to grant access to users & applications - no need to maintain any AllowList of IP addresses using Authorized Networks list etc.
  • Works reliably every time whether from work, home, school or park (with or without VPN).
Devs connect to DB via CloudSQLProxy software on localhost

You do, however, need to ensure a few important things:

  • Your devs need to know a specific “connection string” (contains no sensitive contents) when starting the CloudSQLProxy software in their localhosts.
  • Your devs may need to be granted the Cloud SQL Client IAM role (roles/cloudsql.client) via GCP IAM- not recommended.
  • Your devs may need to belong to a Google group that has been granted the Cloud SQL Client IAM role - recommended.
  • Your devs may need a key file to a ServiceAccount that has been granted the Cloud SQL Client IAM role - not recommended.

Not just for humans

While the CloudSQLProxy works great to grant your devs access to your CloudSQL DBs, it is not limited to just your devs. In fact, it is also the recommended way by Google for your applications & tools to connect to the live database as well.

Devs and Apps - all connect to DB via CloudSQLProxy running in their localhosts

For this to work, you need to ensure 2 main things:

  • Your server needs to know a specific “connection string” (no sensitive contents) when starting the CloudSQLProxy software in its localhost.
  • Your server may need a key file to a ServiceAccount that has been granted the Cloud SQL Client IAM role - only needed for apps that are not running inside GCP infra.

Even easier for apps running in GCP infra

For apps that are running inside GCP infra, however, the underlying host machines (eg: VM instances, GKE cluster nodes, Cloud Functions, Cloud Runs, etc) already have a ServiceAccount attached to them implicitly. Hence, no need to reveal any key information (read sensitive information) to anyone or anywhere 🥳. You only need to grant the Cloud SQL Client IAM role to the built-in ServiceAccounts via GCP IAM.

Stay Tuned

This article mainly covered the “WHAT” & the “WHY” of CloudSQLProxy. Stay tuned for the next article How to use CloudSQLProxy in Google Cloud Platform? that will cover the “HOW” of CloudSQLProxy. It will contain more technical & hands-on materials with scripts & code samples 🙂

--

--

Rakib Al Hasan
AirAsia MOVE Tech Blog

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