Understanding Cloud SQL connections

Like a lot of Cloud Engineers, I started my cloud journey on AWS. I got quickly comfortable with Amazon Relational Database Service(RDS). Although it is a PaaS service, it is trivial to control connections using Security Groups or Network ACLs — concepts which easily carry over if you have worked with other AWS services that interact with the VPC.

When I first got an opportunity to work with GCP, I was expecting that same ease of use of RDS to translate over to Cloud SQL. I quickly discovered that networking in GCP and AWS differ significantly, which was made evident when I tried to connect to a Cloud SQL instance.

In this article, I want to bring some clarity on how you can connect to your Cloud SQL instances in GCP.

Connection options

A Cloud SQL instance can be configured with a Public IP and a Private IP. This means there are two distinct network paths you can use to connect to your Cloud SQL instance —

1. a Private VPC-only network path, or
2. a Public internet-accessible network path.

A thing to observe here is that unlike the RDS instances where you have Security Groups acting as firewalls for incoming connections, VPC Firewalls do not target Cloud SQL instances in GCP. Instead, we authorize connections. There are 3 ways to authorize a connection:

1. Authorized Networks - This is a list of IPs allowed to connect to Cloud SQL instances.
2. Cloud SQL Auth proxy - It uses IAM to authorize a connection from the client. Connections are automatically encrypted.
3. Self-managed SSL/TLS certificates - these only allow connections based on specific public keys.

A big distinction between the network paths is the necessity of having the above authorization measures in place. Based on the network path you choose, you might need to authorize the connection. Regardless of the network path you choose, you will need to authenticate the connection via a SQL database user.

I will summarize this fact in the table below. This has almost always helped me to easily configure or troubleshoot Cloud SQL connectivity.

Additionally, for Private IP connections, you may need to set up an Authorized Network only if you are trying to directly connect to that instance (without Auth proxy) and the client's IP is a non-RFC 1918 address.

Cloud SQL Auth Proxy

Here's a quick note on the purpose of Cloud SQL Auth Proxy, as it was a bit confusing to me initially when I compared to it how Identity-Aware Proxy(IAP) works.

Cloud SQL Auth Proxy only authorizes a client connection using an IAM principal and wraps the connection in a secure tunnel to the Cloud SQL instance.

However, if the client is unable to connect to the Cloud SQL instance due to the network topology, using Cloud SQL Auth Proxy will not bypass this requirement and let the client establish the connection anyway. For example, a GCE client trying to access a private Cloud SQL instance must be able to access the VPC associated with the Cloud SQL instance private IP in the first place. Only then can Cloud SQL Auth Proxy try and establish the connection based on the IAM principals' authorization.

This becomes apparent when we see how the proxy works. From the GCP documentation:

The Cloud SQL Auth proxy uses a secure tunnel to communicate with its companion process running on the server. Each connection established through the Cloud SQL Auth proxy creates one connection to the Cloud SQL instance.

Exploration 🥾

Let's walkthrough some common scenarios and explore how a Cloud SQL connection can established with ease using the above information. Do note that we're primarily exploring connectivity aspect. I would not recommend launching resources with these configurations in practice.

1. Connect to a public Cloud SQL instance from local machine.

This is a pathway that you might choose to use when working with a development database. Although not super common, this is a useful scenario to explore. A private database instance is almost always a strict security requirement in most companies; this use-case will be explored later.

Let's create a public Cloud SQL instance sql-instance-public-ip from Cloud Shell (which will also act as the client in this demo).

gcloud sql instances create sql-instance-public-ip \
  --assign-ip \
  --database-version=MYSQL_8_0 \
  --cpu=1 --memory=4GB \
  --region=us-central1 \
  --no-backup \
  --no-deletion-protection

gcloud sql users create testuser \
  --instance=sql-instance-public-ip \
  --password=testpassword \
  --type=BUILT_IN

The --assign-ip flag will create a Public IP for the instance, which will allow connections directly from the internet. For our SQL instance, it is 35.192.117.97, as shown below:

Referring to our table, we can see that for Public IP connections, we need both connection authorization and authentication.

• Authentication: The database user testuser was created. This meets the authentication criteria.
• Authorization: As discussed above, we have 3 options to fulfill this requirement: Authorized Network, Cloud SQL Auth Proxy, and self-managed SSL/TLS certs. We can pick any of these options. For this demo, let's go with Authorized Network.

Since I am trying to connect from my Cloud Shell instance, I will grab its public IP and add it to the instance's Authorized Network:

CLIENT_PUBLIC_IP=$(curl -s checkip.dyndns.org | sed -e 's/.*Current IP Address: //' -e 's/<.*$//')/32
gcloud sql instances patch sql-instance-public-ip \
  --authorized-networks=$CLIENT_PUBLIC_IP

Now that we meet both the authorization and authentication criteria, we can test our connectivity using mysql client from our Cloud Shell directly to the public IP (35.192.117.97) of the Cloud SQL instance.

mysql \
  --host=35.192.117.97 \
  --user=testuser \
  --password=testpassword

We were able to connect successfully with the Cloud SQL instance on the public network path!

2. Connect to a private Cloud SQL instance from a Compute Engine instance.

Compared to the previous scenario, you are very likely to deal with private connections. We will attempt to connect to a private Cloud SQL instance with no public connectivity from a Compute Engine instance, which will act as a client with access to the VPC which the Cloud SQL Private IP is connected to.

Network setup

Before we can create a private SQL instance, we need to setup the VPC to enable private services access. This is required due to the way GCP models the SQL instance networking. That discussion will be out of scope for this article, but you can check out the official documentation here to learn more about why the private services access is required.

Anyways, we will create a new VPC for this demo, called connectivity-demo-vpc, and prepare it for the CloudSQL instance. Notice that the subnet has an RFC 1918 CIDR(192.168.1.0/24). As mentioned in the Connection options section, this will allow us to skip the authorization using Authorized Network for private connections entirely, as they are implicitly authorized.

gcloud compute networks create connectivity-demo-vpc \
  --subnet-mode=custom

gcloud compute networks subnets create subnet-a \
  --network=connectivity-demo-vpc \
  --range=192.168.1.0/24 \
  --region=us-central1

gcloud compute addresses create private-services-ips \
  --network=connectivity-demo-vpc \
  --purpose=VPC_PEERING \
  --prefix-length=20 \
  --global

gcloud services vpc-peerings connect \
  --service=servicenetworking.googleapis.com \
  --network=connectivity-demo-vpc \
  --ranges=private-services-ips

gcloud compute routers create nat-router \
  --network=connectivity-demo-vpc \
  --region=us-central1

gcloud compute routers nats create nat-config \
  --router-region=us-central1 \
  --router=nat-router \
  --nat-all-subnet-ip-ranges \
  --auto-allocate-nat-external-ips

The private network is now configured for the Cloud SQL instance. We have also added a Cloud Router and Cloud NAT to allow outbound connections to the Internet from our private Compute Engine instance that will be created later on. They won't be required if your compute instance has a public IP.

Cloud SQL instance

We can now create a private Cloud SQL instance sql-instance-private-ip, that will only be accessible from the above VPC.

gcloud sql instances create sql-instance-private-ip \
  --no-assign-ip \
  --network=connectivity-demo-vpc \
  --database-version=MYSQL_8_0 \
  --cpu=1 --memory=4GB \
  --region=us-central1 \
  --no-backup \
  --no-deletion-protection

The --no-assign-ip flag will ensure no Public IP is assigned to the instance, while the --network flag specifies the VPC from which the Private IP of the Cloud SQL instance will be accessible.

An interesting thing to note here is that the private IP assigned to the Cloud SQL is 10.250.64.3, which does not fall under the subnet range we created for our network! Instead, it falls under an IP range that was automatically assigned when we created the allocated IP range private-services-ips in previous step for private service access. This is, again, a consequence of how GCP models networking for Cloud SQL.

Compute Engine instance

We will set up the Compute Engine instance as the client for the Cloud SQL instance, using the same network we created above in order to allow a private network path between them. In practice, it is useful to setup the compute instance without any external IP in GCP. Depending on the use-case, it might provide better network isolation and added security benefits that come along with it.

That is, in fact, how we will configure the instance for this demo.

gcloud compute instances create client-vm \
  --network-interface=network=connectivity-demo-vpc,subnet=subnet-a,no-address \
  --zone=us-central1-c \
  --machine-type=e2-micro \
  --scopes=default,sql-admin \
  --boot-disk-device-name=client-vm \
  --boot-disk-size=10GB \
  --image=projects/ubuntu-os-cloud/global/images/ubuntu-2204-jammy-v20220810

The --network-interface option is used to make the instance private and assign it to the above VPC. We can see that the instance is created without an external IP.

Since the Compute Engine instance does not have an external IP, we will use IAP to access it from outside the network. We will whitelist the IAP service on our network.

gcloud compute firewall-rules create allow-ssh-ingress-from-iap \
  --network=connectivity-demo-vpc \
  --direction=INGRESS \
  --action=allow \
  --rules=tcp:22 \
  --source-ranges=35.235.240.0/20

Additionally, you may require these roles in IAM before you can connect with SSH via an IAP tunnel:

• IAP-Secured Tunnel User
• Service Account User
• Compute Instance Admin (v1)

Connectivity test (Direct)

To test the connectivity, let's refer to the connectivity table again. We can see that for Private IP connections, we only really need to setup authentication. Authorization is optional, and we have avoided the exception for Authorized Network by selecting a subnet with RFC 1918 CIDR.

Therefore, all we really need is a database user for authentication. That should fulfill the connectivity criteria for a private connection.

gcloud sql users create testuser \
  --instance=sql-instance-private-ip \
  --password=testpassword \
  --type=BUILT_IN

We can SSH into the client instance and attempt to connect with the Cloud SQL server on its private IP 10.250.64.3:

sudo apt install mysql-client -y
mysql \
  --host=10.250.64.3 \
  --user=testuser \
  --password=testpassword

Indeed, we were able to connect successfully with the Cloud SQL instance on the private network path!

Add Authorization with Cloud SQL Auth Proxy(Optional)

Although we're able to access the Cloud SQL instance, the connection is not authorized. Let's go a step further and explore how we can use the Cloud SQL Auth proxy to both establish a secure connection to the Cloud SQL instance and authorize the connection with IAM.

There are different ways to use Cloud SQL Auth Proxy, but I find it pretty simple to manage with Docker. For other options, see this.

We start by installing docker in the compute instance.

sudo apt-get install -y ca-certificates curl gnupg lsb-release
sudo mkdir -p /etc/apt/keyrings
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /etc/apt/keyrings/docker.gpg
echo \
  "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.gpg] https://download.docker.com/linux/ubuntu \
  $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
sudo apt-get update
sudo apt-get install -y docker-ce docker-ce-cli containerd.io docker-compose-plugin
sudo usermod -aG docker $USER
docker version

This will install and start the Docker daemon. Download the Cloud SQL Auth Proxy docker image and spawn a container by passing the Connection name of the Cloud SQL instance.

docker pull gcr.io/cloudsql-docker/gce-proxy:1.31.2
CONNECTION_NAME=$(gcloud sql instances describe sql-instance-private-ip --format="value(connectionName)")
docker run -d \
  -p 127.0.0.1:3306:3306 \
  gcr.io/cloudsql-docker/gce-proxy:1.31.2 /cloud_sql_proxy \
  -instances=$CONNECTION_NAME=tcp:0.0.0.0:3306

Cloud SQL Auth Proxy is now tunnelling TCP socket 127.0.0.1:3306 to the Cloud SQL instance. This can be confirmed by trying connect to the server on the local TCP socket.

mysql \
  --host=127.0.0.1 \
  --user=testuser \
  --password=testpassword

We were able to successfully connect to the Cloud SQL instance via Cloud SQL Auth Proxy.

The reason the connection was established is because the Compute Engine instance was already provided the sql-admin access scope when it was created in the previous step, allowing the instance's service account access to be evaluated positive by the Cloud SQL Auth Proxy. Alternatively, you can ensure that the VM service account has the Cloud SQL Client role or higher privilege so that Cloud SQL Auth Proxy can authorize the connection.

I hope this information has given some clarity on how to configure and diagnose connections to Cloud SQL instances in GCP. Feel free to share any suggestions or questions.