Connecting to Cloud SQL: Public IP and authorized networks

1. Introduction

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 anywhere. The ability to connect is enabled by authorizing the IPs allowed to connect explicitly. This is arguably the least secure option for connecting to a Cloud SQL database, but is also the easiest to setup and start using.

Sometimes it's necessary to do this in production, but if you can avoid doing this, you should opt for a more secure alternative (e.g. using the Cloud SQL Proxy). This setup is most ideally suited for development and testing.

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 Cloud SQL instance (this tutorial uses Postgres, but works similarly for MySQL or SQL Server) and authorize specific IPs allowed to connect to it

What you'll need

• A GCP account you have permissions to enable APIs and create services on
• The Postgres client installed to verify connectivity (or MySQL client if you want to use MySQL instead of Postgres)

2. 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. Pick a region close to wherever you are
3. 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)
4. Scroll down and click the show configuration options
5. Expand the Connectivity section
6. Confirm that Public IP is checked and that Private IP is unchecked
7. Click the button
8. Get the IP address from where you're connecting from. Easiest is just to do a google search for "What's my IP" and the search results has your public facing IP in it.

The network field uses IPs in CIDR notation, so e.g. if you want to only authorize a single IP address you'd enter it, for example as: 107.72.48.212/32. You can learn more about CIDR notation here.

9. Enter the IP specification into the network field, give it a name if you want, and click Done in the New Network box.
10. 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.

Once the instance is done being created, click into it in the list, and on the overview page, under the Connect to this instance header, copy the public IP address listed there. It won't be there until the instance is fully created even though you can click into the details before it's fully instantiated.

Using gcloud

First, you need to grab the IP address you wish to authorize to connect to the Cloud SQL instance. The easiest way to do this is to open a browser, and search for "What's my IP" and the search results has your public facing IP address. If you don't have the ability to open a browser from where you're doing this though, you can use a utility like dig.

dig @resolver1.opendns.com ANY myip.opendns.com +short -4

You'll need to specify a region for your Cloud SQL instance closest to where you are. You can see the list of regions by running:

gcloud sql tiers list

Each tier is available at only certain regions. For the gcloud part of the tutorial, we're just creating a micro instance, so you could find the regions for this tier available by running specifically (as long as you have grep installed):

gcloud sql tiers list | grep db-f1-micro

Note: Postgres only has a very limited selection of pre-baked tiers that you can create. You can also customize the instance by specifying the --ram and --cpu flags instead of specifying --tier flag like we are in the command below.

The command to create the instance itself then looks like (don't forget to replace <AUTHORIZED_IP> with the IP you got from the browser or dig, and <REGION> with one close to you, and a password for the root user ‘postgres'):

gcloud sql instances create sql-codelab-00 --database-version=POSTGRES_11 --tier=db-f1-micro --region=<REGION> --authorized-networks=<AUTHORIZED_IP> --root-password=<PASSWORD>

This takes a few minutes to complete.

Once it's complete, the output on the CLI will have the instance's PRIMARY_ADDRESS listed. Copy that for the next step.

3. Test the connection and wrapping up

To verify the instance was setup properly, from the machine who's IP address you entered, you can run on the commandline:

psql "host=<IP copied from previous step> 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