I assume that my usecase is certainly specific but don’t we usually say that unicorns are common in web businesses?

The context is pretty simple: I drive tech aspects of Netflix News website (a french news site dedicated to Netflix programs) and, in order to make traffic grow faster, I want to setup a WordPress plugin to manage newsletter subscribers and to send these newsletters. To achieve that goal, I have selected Mailpoet, a well-known plugin for mass mailing management, well integrated in WordPress workflow.

This is why I naughtly installed the plugin and discovered that I am not a common user. Due to the high traffic of Netflix News, I had to split web server and database server 6 months ago. My servers are hosted on VPS by Amazon Web Services. Considering the traffic between my servers is private but not sufficiently secured (I have not setup tunneling to keep some configuring easyness), I decided to make SSL mysql connexions mandatory. I can tell you that since this decision my sysadmin job is more complicated because I have to find ways to make SSL work with all sorts of services hosted on my server (it has been pretty easy with WordPress but not really with Wikimedia). But I can confirm today that this is a good and maybe the only one solution to leverage security between my servers.

When installing Mailpoet, I have not realized that I was face one of rare WordPress plugins that require PDO access in order to establish connexion with database. But I understand that because it is done in a way of finding the better performance with a huge data amount to process. And the second fact is that Mailpoet is using Symfony and Doctrine in order to abstract his data model, a setup wich is well working with mysql_pdo driver. A small weakness in the way the database connexion constructor is built is that he is importing global database credentials from WordPress wp-config but not the special variables concerning SSL.

I have searched on stackoverflow and some other places but I have found anything about the way of fixing it. I have stayed in front of that problem for a moment because Mailpoet returns an error message on activation when he tries to establish the connexion with my remote server and not explaining why it really fails. The help given in the error message was saying that may local connexion was not properly configured, but I was knowing that was no my case… This is why I decided to take the time to give you this tips. Below, this is what you have to do to activate Mailpoet with a remote SSL server:

Configure SSL on database server

1. On your terminal, input this command line:

   sudo mysql_ssl_rsa_setup --uid=mysql

2. Output should like this if keys have not been already created or should be empty if they already exist:

   Generating a 2048 bit RSA private key
   ...................................+++
   .....+++
   writing new private key to 'ca-key.pem'
   -----
   Generating a 2048 bit RSA private key
   ......+++
   .................................+++
   writing new private key to 'server-key.pem'
   -----
   Generating a 2048 bit RSA private key
   ......................................................+++
   .................................................................................+++
   writing new private key to 'client-key.pem'
   -----

3. Check created keys with this command line:

   sudo find /var/lib/mysql -name '*.pem' -ls

4. Output should look like this if keys have been weel created:

      256740      4 -rw-r--r--   1 mysql    mysql        1078 Mar 17 17:24 /var/lib/mysql/server-cert.pem
      256735      4 -rw-------   1 mysql    mysql        1675 Mar 17 17:24 /var/lib/mysqlsql/ca-key.pem
      256739      4 -rw-r--r--   1 mysql    mysql         451 Mar 17 17:24 /var/lib/mysqlsql/public_key.pem
      256741      4 -rw-------   1 mysql    mysql        1679 Mar 17 17:24 /var/lib/mysqlsql/client-key.pem
      256737      4 -rw-r--r--   1 mysql    mysql        1074 Mar 17 17:24 /var/lib/mysqlsql/ca.pem
      256743      4 -rw-r--r--   1 mysql    mysql        1078 Mar 17 17:24 /var/lib/mysqlsql/client-cert.pem
      256736      4 -rw-------   1 mysql    mysql        1675 Mar 17 17:24 /var/lib/mysqlsql/private_key.pem
      256738      4 -rw-------   1 mysql    mysql        1675 Mar 17 17:24 /var/lib/mysqlsql/server-key.pem

5. In order to confirm that SSL is always used by mysql external clients, you have to make it mandatory. To do that, edit mysql config file:

   sudo nano /etc/mysql/my.cnf

6. You my.cnf file should like this after editing:

   !includedir /etc/mysql/conf.d/
   !includedir /etc/mysql/mysql.conf.d/
   
   [mysqld]
   # Require clients to connect either using SSL>
   # or through a local socket file
   require_secure_transport = ON

7. Now, you have to restart your mysql server daemon in order to activate SSL and apply new configuration:

   systemctl restart mysql

Configure SSL on web server

In order to allow secured connexions to the database server, you have to install keys on web server. Without that, connexions will be refused.

1. On web server, create a folder to store the keys:

   mkdir /etc/mysql/ssl

2.  
3. Set privileges for the new folder:

   chmod 700 /etc/mysql/ssl

4. On mysql server, display first key to replicate:

   sudo cat /var/lib/mysql/ca.pem

5. Output should look like this:

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

6. On web server, create a new file in order to store the key:

   nano /etc/mysql/ssl/ca.pem

7. Paste the key copied, including start and end tags BEGIN CERTIFICATE and END CERTIFICATE and save it.
8. On mysql server, display second key to replicate:

   sudo cat /var/lib/mysql/client-cert.pem

9. Output should look like this:

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

10. On web server, create a new file in order to store the key:

    nano /etc/mysql/ssl/client-cert.pem

11. Paste the key copied, including start and end tags BEGIN CERTIFICATE and END CERTIFICATE and save it.
12. On mysql server, display third key to replicate:

    sudo cat /var/lib/mysql/client-key.pem

13. Output should look like this:

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

14. On web server, create a new file in order to store the key:

    nano /etc/mysql/ssl/client-key.pem

15. Paste the key copied, including start and end tags BEGIN CERTIFICATE and END CERTIFICATE and save it.

Into Mailpoet plugin

Servers setup is done, now we have to dive into plugin code and tweak Mailpoet plugin to activate SSL

1. Install plugin but not activate it
2. Edit file <path to your website>/wp-content/plugins/mailpoet/lib/Doctrine/ConnectionFactory.php
3. Replace this:

   return [
     PDO::MYSQL_ATTR_INIT_COMMAND => 'SET ' . implode(', ', $driver_options),
   ];

4. By this:

   return [
     PDO::MYSQL_ATTR_INIT_COMMAND => 'SET ' . implode(', ', $driver_options),
     PDO::MYSQL_ATTR_SSL_CA => '/etc/mysql/ssl/ca.pem',
     PDO::MYSQL_ATTR_SSL_CERT => '/etc/mysql/ssl/client-cert.pem',
     PDO::MYSQL_ATTR_SSL_KEY => '/etc/mysql/ssl/client-key.pem',
     PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
   ];

5. Save the file
6. Activate Mailpoet
7. Enjoy !

I assume that Mailpoet’s developper will shortly fix that in order to extend clients usability scope. This is really necessary because this solution is a provisory fix. On each Mailpoet plugin update, you will have to redo the last part of this tutorial called « Into MailPoet plugin » because update will overwrite your code editing. If you don’t do that, Mailpoet will not work anymore.