Last Updated: 2020-05-04

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.

The individual steps are:

What you'll need

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.

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:sa-test@my-project-test.iam.gserviceaccount.com --role roles/cloudsql.client
gcloud iam service-accounts keys create service-account.json --iam-account <SERVICE_ACCOUNT_NAME>@<GCP PROJECT ID>.iam.gserviceaccount.com

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.

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>
gcloud compute zones list

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

The instance takes a couple minutes to start usually.

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>

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 &

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

Reference docs