Understanding Cloud SQL connections
Tags: cloud, gcp, cloudsql
Published: 2022-08-25 18:55:41

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.

| Connection     | Private IP | Public IP |
| -------------- | :--------: | :-------: |
| Authorization  | Optional   | Required  |
| Authentication | Required   | Required  |

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](https://cloud.google.com/sql/docs/mysql/sql-proxy#how-works):

> 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).
```bash
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:

![Cloud SQL instance created with public IP](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wd2inf9h2h51yueyeera.jpg)

Referring to our table, we can see that for Public IP connections, we need both connection authorization and authentication.
- <u>Authentication</u>: The database user `testuser` was created. This meets the authentication criteria.
- <u>Authorization</u>: 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:

```bash
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
```

![Added client's public IP to Authorized Networks](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bl58oegpybxjikzx05vj.jpg) 

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.
```bash
mysql \
  --host=35.192.117.97 \
  --user=testuser \
  --password=testpassword
```

![Connection to public Cloud SQL instance successful](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7nt8asg69veuyl5206ez.jpg)
 
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](https://cloud.google.com/sql/docs/mysql/private-ip#overview) 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](#connection-options) section, this will allow us to skip the authorization using Authorized Network for private connections entirely, as they are implicitly authorized.

```bash
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.

```bash
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.

![Cloud SQL Instance created with no public IP](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wl4kzds5jlu2wgb6cig9.jpg) 

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.

```bash
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.

![Compute Engine instance created with no External IP](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w8vw7vbgyupej9k7a3md.jpg)

Since the Compute Engine instance does not have an external IP, we will use [IAP](https://cloud.google.com/iap/docs/using-tcp-forwarding) to access it from outside the network. We will whitelist the IAP service on our network.

```bash
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
```
![IAP whitelisted on port 22 to allow SSH from outside the VPC](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2rnpz9c1wjjpp1wlm420.jpg)

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.

```bash
gcloud sql users create testuser \
  --instance=sql-instance-private-ip \
  --password=testpassword \
  --type=BUILT_IN
```
![Cloud SQL database user created](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yvsmy2l47hjwjjvchf1d.jpg)

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

```bash
sudo apt install mysql-client -y
mysql \
  --host=10.250.64.3 \
  --user=testuser \
  --password=testpassword
```
![Connection to private Cloud SQL instance successful without any authorization](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kk18fi4333qkzdp2olz7.png)

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](https://cloud.google.com/sql/docs/mysql/connect-admin-proxy#install).

We start by installing `docker` in the compute instance.

```bash
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.

```bash
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 docker container spawned](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/62hj51iknryu2lo7l7b6.png)
 
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.

```bash
mysql \
  --host=127.0.0.1 \
  --user=testuser \
  --password=testpassword
```
![Connection to Cloud SQL instance established through Cloud SQL Auth Proxy](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9ivpc1u097vz1s2gr7ru.png)

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.