Introduction

Google Cloud SQL Proxy provides secure access to Cloud SQL Second Generation instances without having to whitelist IP addresses or configure SSL. Cloud SQL Proxy provides several important benefits:

  • Secure connections: The proxy automatically encrypts traffic to and from the database using TLS 1.2 with a 128-bit AES cipher; SSL certificates are used to verify client and server identities.
  • Easier connection management: The proxy handles authentication with Cloud SQL, removing the need to provide static IP addresses.

The proxy does not provide a new connectivity path; it relies on existing IP connectivity. For example, you cannot use the proxy to connect with an instance using Private IP unless the proxy is using a VPC network that has been configured for private services access.

How the Cloud SQL Proxy works

The Cloud SQL Proxy works by having a local client, called the proxy, running in the local environment. Your application communicates with the proxy with the standard database protocol used by your database. The proxy uses a secure tunnel to communicate with its companion process running on the server.

The following diagram shows how the proxy connects to Cloud SQL:

What Problem does Cloud SQL Proxy Solve?

Typically, developers do not setup SSL for Cloud SQL connections. This is very insecure as the database credentials are transmitted in the clear over port 3306. The proxy solves this problem by encrypting connections and provides automatic authorization without needing to hard-code the Cloud SQL Server IP address as only 127.0.0.1 is required for access.

Cloud SQL Proxy also solves the problem of whitelisting IP addresses. If you have autoscaling setup, you will not know what the IP addresses are for new instances. With the proxy, you just specify 127.0.0.1 for the Cloud SQL server in your application.

Prerequisites

  • The Cloud SQL Admin API must be enabled.
  • The Compute Engine service account has been assigned one of the following roles:
    • Project Owner
    • Project Editor
    • Cloud SQL Client
    • Cloud SQL Editor
    • Cloud SQL Admin
  • The Compute Engine instance must have a public IPv4 address. I will cover private IP in a separate article.
  • This article supports Debian 9 and Ubuntu 16/18. Other Linux operating systems have similar steps.

Download the Cloud SQL Proxy

In your home directory, create a working directory:

Download the proxy:

Make the proxy executable:

Configure as a Service (TCP)

The simplest method is to configure the proxy to use TCP. The other method shown below uses Unix Sockets.

Create a new file cloud-sql-proxy.service with the following contents. Replace INSTANCE_CONNECTION_NAME with the Cloud SQL instance connection name.

The instance connection name will look similar to this: myprojectid:us-central1:wordpress-sql. The format is PROJECT_ID : REGION : INSTANCE.

You will find the Instance connection name in the “Instance details” page of the Google Cloud Console under “Connect to this instance”.

Copy this file to /etc/systemd/system/cloud-sql-proxy.service

Enable the Cloud SQL Proxy to autostart when the Compute Engine starts:

Reboot your Compute Engine instance and verify that the service is running after restart:

Configure as a Service (Unix Sockets)

Create a new file cloud-sql-proxy.service with the following contents. Replace INSTANCE_CONNECTION_NAME with the Cloud SQL instance connection name.

The instance connection name will look similar to this: myprojectid:us-central1:wordpress-sql. The format is PROJECT_ID : REGION : INSTANCE.

You will find the Instance connection name in the “Instance details” page of the Google Cloud Console under “Connect to this instance”.

Copy this file to /etc/systemd/system/cloud-sql-proxy.service

Create the working directory for the proxy:

Enable the Cloud SQL Proxy to autostart when the Compute Engine starts:

Reboot your Compute Engine instance and verify that the service is running after restart:

Test Cloud SQL Proxy

Install the MySQL Client:

TCP Configuration:

Connect to the Cloud SQL Instance. You will need your MySQL database user and password.

Unix Sockets Configuration:

WordPress

WordPress normally stores the MySQL database parameters in wp-config.php. Make a backup copy of this file. Then edit the file to use the proxy. Typical locations for this file are:

  • /var/www/html
  • /opt/bitnami/apps/wordpress/htdocs

Step 1: Change the MySQL hostname

TCP Configuration:

Look for this line in wp-config.php:

Change to:

Unix Sockets Configuration:

Note: WordPress and Unix Sockets does not work. The following should work but does not.

FIXED: The problem was using 127.0.0.1 instead of localhost. PHP/MySQL Client uses 127.0.0.1 for TCP and localhost for Unix Sockets.

Look for this line in wp-config.php:

Change to:

Reboot the server and verify that WordPress is functioning correctly.

Step 2: Remove the server’s IP address from the Cloud SQL Whitelist

Your goal should be to only use Cloud SQL Proxy for connections to Cloud SQL. Make a note of your WordPress server’s IP address and remove that IP address from Cloud SQL.

Log in to the Google Cloud Console. Go to Cloud SQL. Select your instance. Go to the “CONNECTIONS” tab. Under Authorized networks take a screenshot for backup. Remove your server’s IP address under Authorized networks. Make a note of any other addresses listed. Update those systems to use Cloud SQL Proxy. Then come back and remove them as well.

Reboot the server and verify that WordPress is functioning correctly.

Step 3: Create a new MySQL user

If you have been using Cloud SQL without SSL, now is the time to create a new database user. Delete the old user once you have switched to Cloud SQL Proxy.

Log in to the Google Cloud Console. Go to Cloud SQL. Select your instance. Go to the USERS tab. Click the “Create user account” button. Enter the user name and password.

Edit wp-config.php and change these lines to use the new MySQL user and password.

Reboot the server and verify that WordPress is functioning correctly.

Supporting Multiple Cloud SQL Instances

The proxy supports multiple Cloud SQL instances. For TCP, you must specify different port numbers for each instance. For Unix Sockets, you can specify different socket names to identity different Cloud SQL instances.

TCP:

Unix Sockets:

Debugging Problems

Enabling verbose output

TCP:

Add the command-line option -verbose=true to the file /etc/systemd/system/cloud-sql-proxy.service.

Unix Sockets:

Once everything is working, change the flag to false.

Cloud SQL Proxy Status

The first step is to check the Cloud SQL Proxy status:

The normal running status will be like this:

Look for two items. On line 3: “running” and on line 9 “Ready for new connections. If there are errors, review to determine a probable cause.

Wrong Cloud SQL Instance Connection Name

If you have the wrong instance connection name configured in cloud-sql-proxy.service, you will see an error like this:

Check the Cloud SQL Proxy status:

This error message shows that the wrong Project ID is configured:

This error messages shows that the wrong instance name is configured or you do not have permissions to access the Cloud SQL instance:

The Compute Engine Service Account does not have Cloud SQL Permissions

When creating a Compute Engine instance, you need to enable one of the following under the instance “Identity and API access” screen in Compute Engine:

  • Set access for each API: Cloud SQL: Enabled
  • Allow full access to all Cloud APIs

I generated the following error by setting Cloud SQL: None

Credits

I write free articles about technology. Recently, I learned about Pexels.com which provides free images. The image in this article is courtesy of Dids at Pexels.