Connecting Cloud SQL - GCE + Private IP and Proxy

Hi friends!

This blog is going to walk you through creating a GCE (Google Compute Engine) virtual machine to run a sample application that will connect to a Cloud SQL instance as securely as possible. Via private IP (Cloud SQL instance won’t have a public IP available to reduce attack surface) and using the Proxy to ensure all the SSL connectivity is handled for us. Don’t take this as gospel of “This is how you should do it” because the real world has requirements. Sometimes those requirements mean you don’t get to do all the things, or even SHOULD do all the things. If you’re running a quick database for a personal project like studying your backyard’s microclimate, you probably don’t need to go through all these steps to super secure your database to this level. But storing customers’ PII? Definitely.

If you want to know a bit more background and context around connectivity to Cloud SQL, check out my intro to connectivity blog post. That post also has links to more step-by-step posts around different use-cases and methods, as well as why you might want to pick one method over another. All of these posts assume that you’ve already got your own Google Cloud Platform (GCP) project with billing set up. If you don’t, head here to get started with a project, or here to set up billing for the project.

Creating the VM

So first thing is to create a VM. Lots of ways to do it, I’m going to walk through a step-by-step on a micro f1 instance. If you’ve already done this, feel free to skip to the next section.

Head to Google Compute Engine

If you already have VMs, then click the CREATE INSTANCE button at the top.

If you don’t, then click the blue Create button in the dialog.

There are a bajillion options here. For the time being, just be sure to give it a unique name, change the Machine type to f1-micro (cheapest one), then click the CREATE button at the bottom of the page.

Once it’s online (shouldn’t be long), easiest way to connect to it is on the instance list, in the Connect column, hit the drown down menu and select Open in browser window.

This will go through some initial setup like transferring the SSL keys to the VM for connecting, etc. It’s less than a minute usually.

To test connectivity, I use either psql or mysql CLI depending on the type of database created, so you’ll want to install one or the other, whichever matches the type of database you’re wanting to create.

• PostgreSQL: sudo apt-get install postgresql-client
• MySQL: sudo apt-get install mysql-client
• SQL Server: As of the timing of this blog, we just went beta with SQL Server as well, so if you’re familiar and want to use that, check out the docs for SQL Server here. It gives info on installing it, and my colleague wrote a blog about SQL Server specifically here.

Create Cloud SQL instance

I’ll walk through doing it in the console, but if you know how using gcloud that’s totally cool too.

Go here. If you have instances already, then click the CREATE INSTANCE button in the top nav bar:

If you don’t have any yet, then click the Create instance blue button in the dialog.

Pick your database flavor, for this tutorial which type you make doesn’t make a difference which type you choose.

Set an instance ID, a root password, and then expand the Show configuration options

Expand the section for Connectivity

• Uncheck the public IP option, and check the private IP option
• Click the ENABLE button to enable the APIs necessary to connect privately. It may take a minute once you click until you can do more, as it’s enabling additional permissions to be able to use the internal networking

I’m not going to get deep into VPCs (Virtual Private Clouds) here, but I do talk about them a bit in this blog post, and the official docs go into good detail. For now, know we left the GCE instance in the default VPC (I didn’t even walk through all the options possible for GCE, they’re intense, and beyond scope for this blog post).

This is relevant, because even if you’ve created other networks, in the Associated networking dropdown, leave it on the default option.

Click the Create button and you’ll be returned to the instance list and should see your instance

• You can click into the instance, and see the banner that says it’s not ready yet.

• This can take a few minutes, so while it’s doing that, we’ll move onto getting the proxy all set to go

Setting up the Google SQL Proxy

Go here to enable to Cloud SQL Admin API (needed for proxy to connect to your project)

• If you’ve already enabled it, you’ll see a MANAGE button, if you haven’t, then you’ll see the ENABLE button

Back to your SSH window on your GCE instance, and download the proxy

• Linux 64-bit: wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
• Run chmod +x cloud_sql_proxy

Now we need to create a service account to grant proxy access to Cloud SQL

Go here, and click the CREATE SERVICE ACCOUNT 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

On this next page, click the CREATE KEY button

• Leave it on JSON and click CREATE. This will download the key to your local machine.
• Click DONE to finish creating the service account

The JSON key needs to be somewhere accessible to where you’re running the proxy. Easiest way, is if you’ve connected to your GCE instance with the Open in browser window, the settings menu in the upper right of that window has an Upload file option which makes it easy to get your service account key onto the VM

Navigate to your service account file you just created, and upload it to the VM. It will have uploaded to your home directory, so if you’ve created a tmp folder to work in, you’ll want to move the service account file into that dir, or just remember where you put it.

Run the proxy

Back to here. Once your instance is done provisioning (it might not be done yet), click into it.

In the Connect to this instance section, copy the Instance connection name, it’ll look something like myproject:us-central1:myinstance

Start the proxy with this command:

• ./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432 -credential_file=<PATH_TO_SERVICE_ACCOUNT_FILE> &
• Note the port is for Postgres specifically, if you’re using MySQL then it should be tcp:3306
• It’s okay to change these ports, particularly if you have something already running on the port (like you’re running PostgreSQL locally for example). If you do though, remember that in the next section you need to specify the --port flag with whatever you specify in order to verify connectivity.

Verify connectivity

Easiest way to verify connectivity is by using something like psql:

• psql “host=127.0.0.1 port=5432 sslmode=disable user=postgres” and then enter the password you specified at database creation
• Note that even though setting the sslmode=disable, the Google SQL proxy provides the encrypted connection

Wrap-up

To clean this up, you need delete the Cloud SQL instance here, and shut down the Compute Engine instance here.

Run into any problems? Please let me know! Respond in comments below, or reach out to me on Twitter. My DMs are open!