Back in 2019, I made a post titled Migrating Wordpress to App Engine - Configuring GCP, where I showed you how you could migrate or set up Ghost in App Engine. Naturally, Cloud SQL is used to host the database for the Ghost installation running in App Engine. I was never happy having an insecure connection to Cloud SQL, as well as having unused databases, so I scrapped the old instance and rebuilt a new one from scratch.

Let’s go through how to do this.

Before we get started

• Create a Cloud SQL instance and configure the required user(s)
• Create a database for your Ghost installation
• [Optional] Install MySQL Workbench ¹

Enabling SSL connections

The first activity we need to complete is to enable or turn on SSL connections. Enabling SSL connections can be activated by:

1. Navigating to your Cloud SQL instance
2. Click on Connections
3. Scroll down to the SSL section
4. Click on Allow secured connections

Once enabled, you’ll only be able to connect to your SQL instance by using client certificates provided in the console, on top of your usual credentials. The certificates are in the SSL section, just below the section where you enabled SSL connections.

1. Create a new client certificate and download all certificates

Please ensure you securely store your certificates. If both the client and private certificates are lost or compromised, you will have to regenerate or create a new client certificate.

Connecting to your SQL instance

Now that you’ve enabled SSL connections, created a new client certificate and downloaded the certificates, you can connect to your SQL instance using MySQL Workbench. If you are using a different SQL client, the parameters and values may be slightly different. To connect to your SQL instance using MySQL Workbench:

1. Open MySQL Workbench application
2. Fill in the following fields
   • Hostname
   • Username
   • SSL key file
   • SSL cert file
   • SSL CA file
3. Click on Test connection or OK

Upon clicking Test connection or OK, you should have connected to your Cloud SQL instance.

Troubleshooting failed connections

• Double-check the IP address you’re connecting to
• Check if you’ve whitelisted your public IP address, under Connections > Public IP > Authorized networks
• Double-check you’ve selected all certificates

Configuring Ghost

Simple, we only need to add a few lines into the config.*.json file. We need to add the SSL config to the database connection section. We’ll be adding the following config.

{
  "ssl": {
    "cert": "ghost_database_cert",
    "ca": "ghost_database_ca",
    "key": "ghost_database_key"
  }
}

When the SSL configuration is added to the config.*.json file, it’ll look something like this.

{
  "url": "https://www.example.com",
  "server": {
    "host": "0.0.0.0",
    "port": "8080"
  },
  "database": {
    "client": "mysql",
    "connection": {
      "host": "127.0.0.1",
      "user": "sql_user_name",
      "password": "sql_user_password",
      "database": "sql_database_name",
      "ssl": {
        "cert": "ghost_database_cert",
        "ca": "ghost_database_ca",
        "key": "ghost_database_key"
      }
    }
  },
  "paths": {
    "contentPath": "content/"
  },
  "useMinFiles": true,
  "logging": {
    "level": "info",
    "rotation": {
      "enabled": true
    },
    "transports": ["stdout"]
  }
}

Now we need to add the Cloud SQL certificates to each key in the config file that we’ve just added. To do this, we need to change our certificates, so they are on a single line, not multiple lines.

When you open the file into a text editor, you’ll see a file like this.

-----BEGIN CERTIFICATE-----
MIIFTDCCBDSgAwIBAgISA0sYYfahZDP.....
......
......
......
-----END CERTIFICATE-----

What we need to do is add \n to the end of each line, and to remove the next line, so there is only a single line. Your file will then look like this.

-----BEGIN CERTIFICATE-----\nMIIFTDCCBDSgAwIBAgISA0sYYfahZDP.....\n......\n......\n......\n-----END CERTIFICATE-----

Once your certificates are reduced to a single line and include the newline regular expression, you can add the certificates as values, to your config.

Run npm start and Ghost will start up.

Resources

• [Google documentation] Troubleshooting Cloud SQL issues
• [Google documentation] Configuring SSL/TLS certificates for Cloud SQL
• [Google documentation] Connecting to Cloud SQL instances