Most Drupal setup guides assume that the MySQL database is running on the same machine as the application and that all connections occur locally over a socket connection.
Many users may then proceed to move the MySQL database to another machine and have Drupal talk to it over either a local network or private subnet.
However, database traffic is transmitted in plaintext by default and if an attacker manages to compromise the network, they will be able to sniff database traffic as it moves to and fro.
This guide covers the setup of a self-signed SSL certificate on a standalone machine running MySQL 8.0. Setup for managed database services such as Amazon's RDS may be different.
Step 1
Access the machine running the database and navigate to the data directory. If not otherwise specified, this defaults to /var/lib/mysql/.
Step 2
Copy the ca.pem, client-cert.pem, and client-key.pem files to the webserver hosting the PHP application. These can be placed anywhere outside the document root.
Step 3
Ensure the webserver user (www-data) can access the certificate files.
In the folder with the certificate files, run
chown www-data:www-data *
chmod 400 *
Running ls -ltr should return the following results:
These will set the certificate files as read-only by the web server.
Step 4
Configure your PHP application to use the new certificate files.
DRUPAL 8/9
In Drupal, this can be added to the database configuration in settings.php:
'pdo' => [
\PDO::MYSQL_ATTR_SSL_KEY =>'/path-to/client-key.pem',
\PDO::MYSQL_ATTR_SSL_CERT =>'/path-to/client-cert.pem',
\PDO::MYSQL_ATTR_SSL_CA =>'/path-to/ca.pem',
\PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
]
NEXTCLOUD
Nextcloud can be configured by adding the following to config.php:
'dbdriveroptions' => [
\PDO::MYSQL_ATTR_SSL_KEY =>'/path-to/client-key.pem',
\PDO::MYSQL_ATTR_SSL_CERT =>'/path-to/client-cert.pem',
\PDO::MYSQL_ATTR_SSL_CA =>'/path-to/ca.pem',
\PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
]
If using the High Performance Backend binary, you need to connect to the database via a host name instead of an IP address. You can create a host name for the database’s IP address in /etc/hosts and set that as your dbhost in config.php. Restart the service once this is done and it should pick up the new configuration automatically.
Step 5
Edit your MySQL config (/etc/mysql/mysql.conf.d/mysqld.cnf by default) and add the following line:
require_secure_transport=ON
And restart MySQL.
This will reject plaintext connections to the database. Once enabled, your applications will fail to connect to MySQL if they are not configured properly. If you run into this, simply remove the line above to disable enforcement of TLS and double check your configuration.