July 16, 2022

Connect to Google Cloud SQL with Cloud SQL Auth proxy and UNIX Sockets

Google Cloud SQL provides multiple ways for a developer to connect to the cloud-hosted, managed database externally for development or testing purposes.

The Cloud SQL Proxy allows simple, secure connectivity to Google Cloud SQL. It is a long-
running process that opens local sockets (either TCP or Unix sockets) according to the
parameters passed to it. A local application connects to a Cloud SQL instance by using the
corresponding socket.

Google Cloud SQL Auth proxy docs

• Multiple Approaches to Connect Externally to Google Cloud SQL
• Installing Google Cloud SQL Auth proxy
• Configuring Connection String for proxy
• What Are UNIX Sockets?
• Gotchas with Cloud SQL Auth proxy and UNIX Sockets
• Challenge with recent Apple macOS versions and Security settings
• Configuring Shell Scripts to Start/Stop Cloud SQL Auth proxy locally
• Connecting Doctrine to Cloud SQL via proxy
• Conclusion

Multiple Approaches to Connect Externally to Google Cloud SQL

One approach is to create an Authorized Network by providing your IP address. Google Cloud allows your computer to access your database directly once the IP address is assigned. While the easiest to do, this approach can get cumbersome with multiple people or even home Internet connections that often change their public IP addresses.

A second approach is to utilize the Google Cloud SQL Auth proxy. The proxy eliminates the need to create and manage Authorized Networks. It does require some initial setup and configuration. But once complete, the proxy can be started and stopped with a simple shell script on your development machine.

One benefit of using Google Cloud SQL Auth proxy is that it manages the proxy. Keep your binaries up-to-date, and you only have to worry about your client applications and local connection.

Installing Google Cloud SQL Auth proxy

You can install the proxy on Linux, macOS, Windows, and via Docker. Each platform's command line is provided on the official documentation. In addition to local installation, make sure you enable the Cloud SQL Client role within IAM and the necessary account(s).

Configuring Connection String for proxy

There are two ways to connect via the proxy - TCP or UNIX Sockets. For either method, replace project_name, cloud_region (i.e. us-east1), and database_name with your specific values.

Proxy via TCP
./cloud_sql_proxy -instances=project_name:cloud_region:database_name=tcp:3306 

Proxy via UNIX Sockets
./cloud_sql_proxy -instances=project_name:cloud_region:database_name -dir=/cloudsql 

What Are UNIX Sockets?

A UNIX socket is an inter-process communication mechanism that allows bidirectional data exchange between processes running on the same machine. IP sockets (especially TCP/IP sockets) are a mechanism allowing communication between processes over the network.

In some cases, you can use TCP/IP sockets to talk with processes running on the same computer (by using the loopback interface). UNIX domain sockets know that they’re executing on the same system, so they can avoid some checks and operations (like routing); which makes them faster and lighter than IP sockets. 

ServerFault

Gotchas with Cloud SQL Auth proxy and UNIX Sockets

First, if using a Windows-based operating system, UNIX Sockets cannot be used. Linux and Mac support them.

Second, if using UNIX Sockets, there is a limit on length of the path (i.e. "dir=/cloudsql"). This is the reason that if you use UNIX Sockets, you need to keep path short.

Challenge with recent Apple macOS versions and Security settings

During the initial configuration of Cloud SQL Auth proxy and UNIX Sockets on my Mac, I ran into an issue when creating the root directory cloudsql. Since macOS Catalina's release a few years ago, Apple has made the system volume (the root) read-only.

The solution is to create a /etc/synthetic.conf to define a symbolic link such as /cloudsql on the root level. Follow the steps on this blog to create a root-level directory.

Configuring Shell Scripts to Start/Stop Cloud SQL Auth proxy locally

To make things simple, I'd recommend you create a shell script that can be fired easily. Include the "&" at the end of the line. Each new line can contain other database connection strings, if needed. (Don't forget to change the values of project_name, cloud_region, and database_name)

./cloud_sql_proxy -instances=project_name:cloud_region:database_name -dir=/cloudsql  &

Connecting Doctrine to Cloud SQL via proxy

#.env file
DATABASE_NAME=database_name
DATABASE_HOST=localhost
DATABASE_USER=admin
DATABASE_PASSWORD=password
DATABASE_SOCKET=/cloudsql/project_name:cloud_region:database_server

DATABASE_URL="mysql://%env(DATABASE_USER)%:%env(DATABASE_PASSWORD)%@%env(DATABASE_HOST)%/%env(DATABASE_NAME)%?unix_socket=%env(DATABASE_SOCKET)%"

If you use Jetbrain's DataGrip, there is documentation on how to connect via UNIX Sockets.

Conclusion

Using Google Cloud SQL Auth proxy removes a lot of the common challenges associated when connecting from outside of the cloud environment to managed databases.

Setting it up once is all it takes, then for each new project, region, database server, it is the same approach.