Connecting Cloud SQL - Public IP + IP Allowlist

Hi friends!

This blog is going to walk you through, step-by-step instructions on how to connect to your Cloud SQL instance using the public IP option, and allowing only specific IP addresses to connect. This is arguably the least secure way to create and use a Cloud SQL instance. Everyone has their own tolerances in terms of what they might find safe to do, and for me, I’d never do this in production. Testing, proof of concept, prototyping, all that, sure, absolutely…as long as no sensitive data ends up in that database. For production, you’ll definitely want to shift to a more secure option like using the Google SQL Proxy.

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.

Guide:

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

• Verify that the Public IP is checked
• Click the Add network button in the Public IP section

You need to find the IP address of where you’ll be connecting from. For this tutorial, probably the IP of the machine you’re on (unless you’re ambitious and doing this from a Cloud machine).

• Easiest is if you can open a browser from the machine and go to whatsmyip.org
• If you can’t, because it’s a headless machine, or you’re SSH’d in, using DNS lookup with something like dig is also a good way
• dig @resolver1.opendns.com ANY myip.opendns.com +short -4

Once you have the IP address, put it into the Network box to whitelist that specific connection

Quick cliff notes on CIDR notation (there’s a link there, but I found the link less helpful about specifics of what you need to put in to get things working without wanting to understand a lot of detail about networking):

• To allow only one IP address, just put the address, or use this format: <ip address>/32
• E.g. 104.132.11.92 or 104.132.11.92/32 are both equivalent and only allow that specific IP address
• To allow a range of IP addresses:
• 104.132.11.0/24 means it will allow 104.132.11.0 to 104.132.11.255
• The number after the slash refers to how much of the IP address to use as a filter. Each number of an IP address is an octet of binary numbers, so having a 24 means use the first three numbers and allow any value for the 4th number. An 8 would mean only use the first number as the filter and allow anything with that first number in the IP through. So for example, 104.132.0.0/16 means allow any IP address which starts with 104.132.xxx.xxx and 104.0.0.0/8 means allow any IP address which starts with 104.xxx.xxx.xxx. And the wide open 0.0.0.0/0 means allow anything from anywhere (Please don’t do that). Be careful with this, as you can unintentionally allow more access than you intend.

Click Done once you’ve added any IP addresses you want to allow

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

Verify connectivity

Grab the connection IP address from your instance details overview page

• It’s in the Connect to this instance section for Public IP address

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

• psql “host=<connection IP from above step> 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 all you need to do is delete the Cloud SQL instance. Run into any problems? Please let me know! Respond in comments below, or reach out to me on Twitter. My DMs are open!