Connecting to Cloud SQL: Compute Engine, Private IP and Cloud SQL Proxy

1. Introduction

Last Updated: 2021-05-06

Thinking about the connection

There are many types of applications and frameworks out there. In this codelab, we'll be covering connecting to Cloud SQL from an application that's being run on a managed virtual machine in Google Compute Engine via an internal private IP address using the Cloud SQL Proxy. This is an incredibly secure way to run a stateful application in the Cloud. You're minimizing your exposure to the internet by restricting to only using a private IP, and using the SQL Proxy which handles SSL connectivity for you.

One common use-case for this would be, for instance, an on-premise application that has been shifted to run in the Cloud instead of locally.

What you'll build

This codelab is very minimalistic. The idea is to walk you through the nuts and bolts of the connection piece, without thinking too heavily about the application itself. In a perfect world, connecting to Cloud SQL is just like connecting to any other instance of a SQL database so you should be able to take what you create in this codelab, and apply it to any production application.

The instructions will include both a walk-through of doing things in the GCP console, as well as including the gcloud command equivalents for using on the CLI or automation.

Note: If you're using gcloud to go through this codelab then you'll need to run gcloud init and be sure that you're configured properly to the project you want to use.

The individual steps are:

• Create a minimal Service Account to enable connection to the Cloud SQL instance
• Create a VM on Google Compute Engine (GCE)
• Create a Cloud SQL instance (this tutorial uses Postgres, but works similarly for MySQL or SQL Server)
• Download and run the Cloud SQL Proxy on the GCE instance

What you'll need

• A GCP account you have permissions to enable APIs and create services on

2. Create a service account

Service accounts are used to grant permissions to use different services within your GCP project. For this codelab, we need one in order to grant the Cloud SQL Proxy permission to connect to our Cloud SQL instance.

On the Console

Go to the IAM service accounts page and click the button at the top of the page.

Give your service account a unique name and ID and click CREATE.

On the next page, click the drop-down for Select a role. Filter for "Cloud SQL" and select the Cloud SQL Client role. Click CONTINUE.

Nothing to do on the last page because for this codelab you won't need a bearer token. The service account will be assigned to the Compute Engine instance itself which will grant default credentials for the whole instance.

Depending on the application you end up creating after this codelab, you might need a physical account key which you could get on this page by hitting the CREATE KEY button. For example, if the application needed to be able to authorize multiple GCP projects at once.

Click DONE.

Using gcloud

Create the service account:

gcloud iam service-accounts create <SERVICE_ACCOUNT_NAME> --display-name "<SERVICE_ACCOUNT_NAME> service account"

This creates the service account, but it's currently without any roles/permissions assigned to it. To assign the appropriate role, run:

gcloud projects add-iam-policy-binding <GCP PROJECT ID> --member serviceAccount:<SERVICE_ACCOUNT_NAME>@<GCP PROJECT ID>.iam.gserviceaccount.com --role roles/cloudsql.client

E.g. If your service account is named sa-test when you created it, and your project is my-project-test then the command would be:

gcloud projects add-iam-policy-binding my-project-test --member serviceAccount:[e-mail] --role roles/cloudsql.client

Note: If you were to want a bearer token for this service account for use outside the immediate uses of this codelab (again, you do not need a bearer token for this codelab), you can download the key with this command:

gcloud iam service-accounts keys create service-account.json --iam-account <SERVICE_ACCOUNT_NAME>@<GCP PROJECT ID>.iam.gserviceaccount.com

3. Creating the Compute Engine VM

Google Compute Engine is the managed service where the virtual machine will run which would hold our application we want to connect from. In this codelab, we're not building an application, but we'll confirm connectivity by running psql.

On the Console

Go to the Google Compute Engine page and click the button.

Note: If you don't have any instances yet, there is no Create Instance button, instead it'll just be a Create button in the middle of the screen.

There are many options here, the only things you need to do are:

1. Give your instance a name
2. Change the Machine type to f1-micro
3. Under Identity and API access, change the dropdown for Service account from Default compute service account to the one you created in the earlier step.
4. Click Create all the way at the bottom of the page

Using gcloud

The service account is the full name of the service account you created earlier, so it'll be in the format: <NAME>@<PROJECT>.iam.gserviceaccount.com.

gcloud compute instances create <INSTANCE NAME> --machine-type=f1-micro --zone=us-central1-b --service-account=<SERVICE ACCOUNT FULL NAME>

Note: If you're not in the US, you may want to change your zone to be one that's closer to where you are. You can see all the available zones by running:

gcloud compute zones list

4. Creating the Cloud SQL instance

Cloud SQL is our managed relational databases offering. It supports MySQL, PostgreSQL and SQL Server. For this codelab, we'll create a Postgres database, but the instructions are similar for all three.

On the Console

Go to the Cloud SQL page, and click on the button.

As I mentioned, most of this codelab is generic to any SQL flavor, but for this codelab, choose PostgreSQL.

1. Give your instance an ID
2. Enter a password for the default user (username will be the default for the selected DB, e.g. root for MySQL or postgres for PostgreSQL)
3. If you changed the region of your compute instance, you should also change the region here to match.
4. Scroll down and click the show configuration options
5. Expand the Connectivity section
6. Uncheck the option for Public IP and check the Private IP checkbox
7. Be sure default is selected in the dropdown that shows up when Private IP was checked
8. Scroll down and click Create

If you haven't done anything like this previously in this project, you'll be asked to enable the Service Networking API. Go ahead and click Enable API

The instance takes a couple minutes to start usually.

Note: Because we disabled the Public IP option, you won't be able to connect to the instance from the outside. If you need connectivity from outside as well, just leave the Public IP option checked and I'll add a note in the next section about the proxy.

Using gcloud

Since we don't have any nice prompts to enable APIs from here, we'll need to do it manually

gcloud services enable servicenetworking.googleapis.com

Next, we need to enable private service connectivity on our default VPC network. First step to doing that is to allocate a named range of IP addresses to use for our instance.

gcloud compute addresses create sql-codelab-allocation --global --purpose=VPC_PEERING --prefix-length=24 --network=default

Next up is assigning that range of IPs to our internal services (this takes about a minute).

gcloud services vpc-peerings update --service=servicenetworking.googleapis.com  --network=default     --project=<PROJECT ID> --ranges=sql-codelab-allocation --force

And finally, creating the instance itself is only available currently with the beta version. The combination of --no-assign-ip and --network=default is what enables private IP connectivity. At this time, there's no way to enable both Private IP and Public IP at the same time, so if you need public access, then you'll need to edit the instance post-creation. This takes a few minutes to complete.

gcloud beta sql instances create test-sql-codelab-00 --no-assign-ip --database-version=POSTGRES_11 --tier=db-f1-micro --region=us-central1 --network=default --root-password=<PASSWORD>

5. Setup and run the Cloud SQL Proxy

Once we're connected to the VM, we're going to need an instance connection string. First we'll grab that, then we'll connect to the VM itself via SSH. Instructions for these pieces will be in their respective sections for Console vs. gcloud, and then the rest will be commands run from the shell on the VM in its own section.

On the Console

Go here and click the name of your Cloud SQL instance.

Scroll down a little, and copy the Connection name somewhere for use later.

Go to the Google Compute Engine instances list page, and find the row with your VM.

In the Connect column, click the SSH button and it will open a separate window that securely connects to the virtual machine.

Jump past the Using gcloud section for the rest of the instructions as it's the same for both.

Using gcloud

Replace <INSTANCE NAME> with the name of your Cloud SQL instance:

gcloud sql instances describe <INSTANCE NAME> | grep connectionName

Save the instance connection name for use later.

You'll need to replace the <ZONE> with the zone that was used back when you created the instance. If you didn't change it, then it was set to us-central1-b. And also replace the <INSTANCE_NAME> with whatever you specified previously.

gcloud compute ssh --zone <ZONE> <INSTANCE_NAME>

Once connected to VM

First up, we need to download the proxy. Depends on your operating system. If you didn't change the OS in the VM creation, then it'll be Linux and you can use:

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

If you've switched the operating system, you can go here for the right command to get the proxy for your OS.

To run the proxy, grab the instance connection name you copied from the Cloud SQL instance details to replace <INSTANCE_CONNECTION_NAME>. Also note, the tcp port number may change if you aren't using a Postgres db, or if you've changed the default port the db is listening on.

./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432 &

Note: Normally, the Cloud SQL Proxy requires a credentials file to run, but recall since we're in a Google Compute Engine instance, and we've set the service account we created earlier on the instance, the proxy understands how to use the instance's service account without a credentials file.

6. Test the connection and wrapping up

As mentioned previously, if you have an application you want to stand up in the GCE instance to test, that's fine too of course. For this codelab, we'll install psql and use that client to verify that we can connect to our database.

From the SSH session on the VM:

sudo apt-get install postgresql-client

psql "host=127.0.0.1 port=5432 sslmode=disable user=postgres"

Then specify the default user's password you set up when creating the Cloud SQL instance.

Congratulations! If all has gone well, you should have the Postgres prompt and can run commands against your database.

What's next?

Check out some of these codelabs...

• https://codelabs.developers.google.com/codelabs/cloud-postgresql-gke-memegen/index.html

Reference docs

• https://cloud.google.com/sql/docs
• https://cloud.google.com/compute/docs
• https://cloud.google.com/sql/docs/mysql/sql-proxy