Setup Mailpoet with a remote SSL database connexion

par | 13 Déc 2019 | Développement | 5 commentaires

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

Mailpoet banner

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.

 

5 Commentaires

  1. Jan

    Hi Simon,

    I’m a developer here at MailPoet, and I’ve been looking into this, but the truth is we find this a little bit problematic. It comes from the fact that WordPress itself doesn’t support SSL client connection settings for MySQL (without WPDB being overridden or its core files edited). In the MailPoet plugin, we do use PDO at the moment, but that is rather an implementation detail – we use the same parameters that are set on WPDB. Now, considering our options here:

    1. Unfortunately, we can’t fetch all the SSL parameters from a (possibly extended) WPDB and apply them to our PDO because those settings are passed to underlying mysqli, and there is no way to read them from there.

    2. We could detect if MYSQLI_CLIENT_SSL is defined and then use default system certs. It’s not a complete solution though – there’s no way to specify any other cert path than the default ones.

    3. We could allow modifying the PDO connection option officially. We don’t like that very much – any new filter prevents us from any future changes, and (except for SSL), it’s also not a good idea to let users modify our connection – as already said, we want to mimic the settings of WPDB. And lastly, we may try to switch to WPDB connection one day.

    4. We could add some new global constants or config options. We could maybe support those proposed for WordPress itself https://core.trac.wordpress.org/ticket/28625. But the ticket is stalled, and we don’t know if they will ever support it. Perhaps I’d suggest pinging WordPress developers themselves below this ticket. As soon as WordPress supports this, we’ll be happy to add it.

    This certainly is a specific use case and I hope you can see that this may not be as simple as it looks. We may revisit this in the future and we hope the feature will make it into WordPress itself.

    Best,
    Jan

    Réponse
    • Simon JANVIER

      Hello Jan,

      Thank you very much for having taken the time to analyze my propositions and my comments. I understand that supporting ssl for mysql is raising many questions because of it seems that the usage of this feature is very limited.

      I would like answer on some points by the way.

      I agree on the fact that WordPress is supporting SSL only as mysqli option is restrincting your ability to use it as parameter. This is although true that the only way I have found to make ssl work with a remote database server is to keep default certs. But my goal was to encrypt data transit in a private cloud hosting network. The requiring of high complexity encryption was low.

      You are writing that today you are using PDO instead WPDB. I have supposed, by analyzing your code, that you where using PDO because it could give you better performances with high query rate. Is it the case?

      If it is not, I don’t really understand why are you not using fully the WordPress API to interact with your tables. If you could do that, you could code without thinking how your users are hosting their WordPress installations. WordPress config files and hoster staff skills could do the reste in order to make it work in this case.

      It is really rare to face of plugins which have non standard architecture in WordPress plugin repository. But I am sure that have your reasons to do that.

      Thank you Jan to have taken the time to try to find a way to solve my problem 😉

      Simon

      Réponse
  2. Karen Burgen

    Hi Simon,

    I just wanted to say thank you!!!

    All my databases are on remote SQL servers with secure_transport enabled and I could never get MailPoet to install.

    About once a year I take a look to see if anyone knows a fix and I found your suggestion to MailPoet and followed the link to your site..

    I didn’t have to do the majority of steps cause SSL was already configured but I edited the ConnectionFactory.php file and bingo.

    I can’t thank you enough. No one else knew how to fix it!

    Karen

    Réponse
  3. Michael Nelson

    Thank you for this write-up, it really helped! To get it to work for my environment, I had to remove the client-cert and client-key lines. If anyone else followed this write-up and still didn’t get it to work, that’s one more thing you could try.

    Réponse

Soumettre un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

Journaux de bord récents