SSL-encypted Database connection for CiviCRM?

Question

I am in the middle of deploying a CiviCRM instance and have decided for scalability and security reasons to separate my web server and database server. I have a set of self-signed certificates installed on both the database server and the web server and Wordpress is using SSL. While CiviCRM is able to connect to the database server, I cannot get it to use SSL, which is a concern since I do not control the data center where both machines reside.

Has anyone dealt successfully with this?

I attempted to alter the URI for the database connection in civicrm.settings.php from:

if (!defined('CIVICRM_DSN')) {
  if (CIVICRM_UF === 'UnitTests' && isset($GLOBALS['_CV']['TEST_DB_DSN'])) {
    define('CIVICRM_DSN', $GLOBALS['_CV']['TEST_DB_DSN']);
  }
  else {
    define('CIVICRM_DSN', 'mysql://USERNAME:PASSWORD@DBHOSTNAME/DATABASENAME?new_link=true');
  }
}

to:

if (!defined('CIVICRM_DSN')) {
  if (CIVICRM_UF === 'UnitTests' && isset($GLOBALS['_CV']['TEST_DB_DSN'])) {
    define('CIVICRM_DSN', $GLOBALS['_CV']['TEST_DB_DSN']);
  }
  else {
    define('CIVICRM_DSN', 'mysql://USERNAME:PASSWORD@DBHOSTNAME/DATABASENAME?new_link=true&useSSL=true&ssl-ca=(/etc/mysql/ssl/cacert.pem)&ssl-cert=(/etc/mysql/ssl/client-cert.pem)&ssl-key=(/etc/mysql/ssl/client-key.pem)');
  }
}

This did not enable SSL, but instead caused me to receive the following error when loading any page:

Initialization Error

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => simpleHandler
        )

    [code] => -24
    [message] => DB Error: connect failed
    [mode] => 16
    [debug_info] =>  [nativecode=No such file or directory]
    [type] => DB_Error
    [user_info] =>  [nativecode=No such file or directory]
    [to_string] => [db_error: message="DB Error: connect failed" code=-24 mode=callback callback=CRM_Core_Error::simpleHandler prefix="" info=" [nativecode=No such file or directory]"]

I checked if this was a permission error (though I suspected not since wordpress was using SSL) by running 'chmod 666' on all of the key files, but continued to recieve the above error.

Anyway, if anyone has any tips or insight into this it would be greatly appreciated. If it helps, I'm running Ubuntu 18.04 w/ PHP 7.2 and the latest version of CiviCRM on Nginx.

Update

I've found a (temporary) solution by making the following edits:

Altered civicrm.settings.php, changing 'mysql' to 'mysqli' in the DSN definitions:

if (!defined('CIVICRM_DSN')) {
  if (CIVICRM_UF === 'UnitTests' && isset($GLOBALS['_CV']['TEST_DB_DSN'])) {
    define('CIVICRM_DSN', $GLOBALS['_CV']['TEST_DB_DSN']);
  }
  else {
    define('CIVICRM_DSN', 'mysqli://USERNAME:PASSWORD@DBHOSTNAME/DATABASENAME?new_link=true');
  }
}

Edited civicrm/civicrm/packages/DB/common.php, changing

var $options = array(
    'result_buffering' => 500,
    'persistent' => false,
    'ssl' => false,
    'debug' => 0,
    'seqname_format' => '%s_seq',
    'autofree' => false,
    'portability' => DB_PORTABILITY_NONE,
    'optimize' => 'performance',  // Deprecated.  Use 'portability'.
);

to

var $options = array(
    'result_buffering' => 500,
    'persistent' => false,
    'ssl' => true,
    'debug' => 0,
    'seqname_format' => '%s_seq',
    'autofree' => false,
    'portability' => DB_PORTABILITY_NONE,
    'optimize' => 'performance',  // Deprecated.  Use 'portability'.
);

Edited civicrm/civicrm/packages/DB/mysqli.php, changing

    if ($this->connection = @mysqli_real_connect(
            $init,
            $dsn['hostspec'],
            $dsn['username'],
            $dsn['password'],
            $dsn['database'],
            $dsn['port'],
            $dsn['socket'],))
    {

to

    if ($this->connection = @mysqli_real_connect(
            $init,
            $dsn['hostspec'],
            $dsn['username'],
            $dsn['password'],
            $dsn['database'],
            $dsn['port'],
            $dsn['socket'],
            MYSQLI_CLIENT_SSL))
    {

This works as far as forcing SSL for the database connection (confirmed by enforcing SSL on the server side and via tcpdump). However, it's quite kludgy to say the least and will have to be redone after each update if common.php or mysqli.php are modified. If anyone more familiar with CiviCRM internals has a more elegant solution, I'd love to hear it.

Answer 1

You don't actually have to replace mysql with mysqli since core already has a "hack" that will do this for you if the php mysqli extension is loaded. https://github.com/civicrm/civicrm-packages/blob/5.15.1/DB.php#L747

For the ssl, it seems reasonable to propose an update to that same function that lets you set the ssl option, either as part of CIVICRM_DSN or a separate CIVICRM_XXX setting in civicrm.settings.php. You can file a proposal at https://lab.civicrm.org/dev/core/issues