Connecting Cloud SQL - Public IP + SQL Proxy

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 connecting through the Google SQL Proxy. A common use-case for this would be if you’ve got a on-premise application or appliance that wants to connect to your SQL database managed by the Cloud. This is often a first step to lift and shift situations from all on-premise to all in the Cloud.

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 cloud console, but if you know how using gcloud on the command line, 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

Confirm that under the Connectivity label that it says Public IP enabled

• If it doesn’t, expand the section, and check the box for Public IP
• Don’t worry about the network stuff in there, we’re using the proxy so we don’t need to worry about it for this tutorial

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

Download the proxy for your platform:

Linux 64-bit: wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
Linux 32-bit: wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.386 -O cloud_sql_proxy

Macos 64-bit: curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64
Macos 32-bit: curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.386

On Windows, just clicking the link should download it, or you can right click on the link and select “Save as” and download the exe.

• Win-32: https://dl.google.com/cloudsql/cloud_sql_proxy_x86.exe
Win-64: https://dl.google.com/cloudsql/cloud_sql_proxy_x64.exe

For Linux and Macos, run chmod +x cloud_sql_proxy

For Windows, rename the executable to cloud_sql_proxy.exe, it’s fine if you don’t, but just remember any commands linked later in this tutorial will need to have the executable name changed to match

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
• This role grants the get and list access for Cloud SQL instances in the project. Note, this is NOT user access to the database itself. That’s handled normally using the GRANT query.

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. The JSON key needs to be somewhere accessible to where you’re running the proxy

Click DONE to finish creating the service account

Time to start the proxy

Back to here. Click into your newly created instance (which should be done instantiating by now)

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, if you’re using MySQL then it should be tcp:3306, and if you’re using SQL Server, then it should be tcp:1433 by default.
• 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 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!