Tag: gcp

Wordpress on Google Cloud SQL

In trying to get a Wordpress install migrated to Google Cloud I ran into a frustrating limitation, that Cloud SQL instances (the Google equivalent of AWS RDS) are not on your private network! There is a feature request to move Cloud SQL onto your private network, as most other Google Cloud services are, but the complaints go back over 3 years since the service was in beta so I'm not holding my breath. The only comment from Google I can find is:

There are some technical reasons why this is not possible currently, but we hope we can do it in the future, it’s a popular request. - Paul Nash (Product Manager, GCE) Nov 27, 2016

This leaves two options, the first is to and get Wordpress to connect to the Cloud SQL instance via public IPv4 and use SSL certificates for encryption. However MySQL SSL certs are quagmire, Wordpress just isn't designed to support certs for database access. Most articles on using SSL require code hacks to core Wordpress files that would be fragile and break during upgrades. There is a 3 year old ticket to add SSL support to db connections, but again, I'm not holding my breath. The most telling status update on that ticket:

Milestone changed from 4.5 to Future Release

The second option is using the Cloud SQL proxy, which is equally a pain in the ass to setup and maintain through inevitable fragility, but at least Wordpress just thinks it is connecting to a local MySQL instance. The proxy traffic will still be encrypted with SSL certificates, but they are automatically handled by the proxy app and renewed hourly.

Expanding on Google's decent-but-unecessarily-complicated proxy guide:

1) Enable the Cloud SQL API from within the GCE interface

2) Install the proxy client, I copied it to: /opt/gcp/cloud_sql_proxy

3) Authentication, there are a few options but since the Wordpress install will be on Google Compute Engine within the same project as our Cloud SQL instance, I'll just let the instance service account provide authentication. A major caveat, Google claims:

the default service account for the Compute Engine instance has the necessary permissions for authenticating the proxy

But this was definitely not true for me. In fact the Cloud API access scopes under my GCE instance details specifically said No Cloud SQL access. Instead I had to create a new service account with proper permissions in the next step.

4) Create a new service account called wordpress-cloudsql and give it the Cloud SQL Client role.

5) Specify instances for the proxy, again there are several options but I'm using the most direct: the -instances flag. Use the fully qualified name of your Cloud SQL instance, ex: example-project:us-west1:prod-db-1.

6) Start the proxy. For ease I'm going to use tcp instead of unix sockets, but if you want to setup sockets Wordpress handles them fine.

Test everything is working via the cli:

./cloud_sql_proxy -instances=example-project:us-west1:prod-db-1=tcp:3306

Hopefully you seem something similar to this:

6/20 15:51:48 Listening on 127.0.0.1:3306 for example-project:us-west1:prod-db-1
6/20 15:51:48 Ready for new connections

Nice. To automate it on CentOS 7 here is my systemd unit file:

[Unit]
Description=Google Cloud Compute Engine SQL Proxy (until GCP DB servers are allowed on private network)
After=network.target

[Service]
Type=simple
User=root
Group=root
Restart=on-failure
ExecStart=/opt/gcp/cloud_sql_proxy \
          -instances=example-project:us-west1:prod-db-1=tcp:3306

[Install]
WantedBy=multi-user.target

7) Update wp-config.php on your GCE instance to point at localhost, since the whole point of the proxy is to make Cloud SQL available locally:

/** MySQL hostname */
define('DB_HOST', '127.0.0.1');

This proxy method also has the benefit of accessing Cloud SQL via your bastion host instead of with SSL certs. For ex using Sequel Pro on macOS choose "SSH Connection" and put your Wordpress GCE instance as ssh_host (which hopefully you're already using ProxyCommand to connect thru your bastion with) and set the mysql_host as 127.0.0.1. Also, in the Cloud SQL dashboard remove every authorized network you may have previously added. Since all the connections are happening inside GCP so you don't need any public ips whitelisted.

That is a whole lotta mess to go through when on AWS you can just contact RDS directly over your private, already encrypted network. I look forward to the day Cloud SQL instances are on your private network and I can put a giant "NO LONGER REQUIRED, PRAISE BE" banner on the top of this page.

© Justin Montgomery. Built using Pelican. Theme is subtle by Carey Metcalfe. Based on svbhack by Giulio Fidente.