Feature Request: Ability to enable SSL for database connection.

As far as I can tell, there is no way within civicrm core to enable SSL connections to a remote database. This would be a major concern for anyone running CiviCRM with separate web and database servers. I've 'solved' this issue for myself by making the changes to civicrm/civicrm/packages/DB/common.php and civicrm/civicrm/packages/DB/mysqli.php detailed here, but it's rather sloppy and may need to be re-done after an update. Would be very useful to be able to set a flag within civicrm.settings.php (or even the initial install page) to enable SSL.

added needs:concept approval triaged type:proposal labels

Looking at the patch in the se post it seems like a reasonable change. Since these are not packages we have written but rather ones we use it would be good to see if there are any upstream fixes before hacking one in

@totten @seamuslee

I don't think the issue is that upstream doesn't support it - it does already in mysqli.php via DB_Common::$options['ssl'], so no hack should be needed there. It's just that civi never sets $options['ssl'], so there's no way to set it at the moment without some kind of hack somewhere.

While the install page might be some work, in terms of supporting ssl my thinking would be it might be as simple as adding 3 lines in CRM_Core_DAO::init(), e.g.

if (defined('SOMETHING')) {
  $options['ssl'] = TRUE;
}

mysqli.php should pick that up and do its thing.

@DaveD or we could just parse ssl from the DSN string?

@eileen Yes although then it might mess up PEAR DB's regex/parsing of the dsn since it doesn't do it that way and isn't expecting it in the dsn. If you want SSL it expects you to set it in $options.

Yeah fair enough - do you have a clear enough idea you could put up a PR?

Probably. The installer I'm not sure. Can you set php DEFINEs before the installer? It might be acceptable to use non-ssl for the install though since there's no private data at that point, unless mysql is set up to require/force SSL (which off the top of my head I'm not sure if you can do but probably).

@toddd Do you mind checking if the one-liner fix of adding $options['ssl'] = TRUE; at this line in CRM/Core/DAO.php also just works for you (instead of the other changes you did)? https://github.com/civicrm/civicrm-core/blob/5.15.1/CRM/Core/DAO.php#L162

Sorry @toddd - previous note references a different $options array - adding these two lines at https://github.com/civicrm/civicrm-core/blob/5.15.1/CRM/Core/DAO.php#L162 seems to trigger ssl connections - can you confirm that also works at your end?

$realoptions = &PEAR::getStaticProperty('DB','options');
$realoptions['ssl'] = TRUE;

@DaveD - after adding the two lines you suggested and rolling back my changes to common.php and mysqli.php, I received the following error:

Initialization Error

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

    [code] => -24
    [message] => DB Error: connect failed
    [mode] => 16
    [debug_info] =>  [nativecode=Access denied for user 'USERNAME'@'HOSTNAME' (using password: YES)]
    [type] => DB_Error
    [user_info] =>  [nativecode=Access denied for user 'USERNAME'@'HOSTNAME' (using password: YES)]
    [to_string] => [db_error: message="DB Error: connect failed" code=-24 mode=callback callback=CRM_Core_Error::simpleHandler prefix="" info=" [nativecode=Access denied for user 'USERNAME'@'HOSTNAME' (using password: YES)]"]
)

Unfortunately, it does not appear that those lines are triggering ssl.

Hmm, thanks for testing @toddd. When I tried running it before I could see it was actually triggering these lines to run: https://github.com/civicrm/civicrm-packages/blob/5.15.1/DB/mysqli.php#L301-L310

So it should initiate SSL, but maybe those lines don't actually work against a mysql that requires ssl (which my dev laptop mysql doesn't - so it might have fallen back to non-ssl).

@DaveD I think I see the issue. The lines I had previously edited in mysqli.php are just below that https://github.com/civicrm/civicrm-packages/blob/5.15.1/DB/mysqli.php#L311-L319, where I had appended MYSQLI_CLIENT_SSL, which relies on the certs set in the mysql config. This is also matches the setup for wordpress which involves appending define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL ); to the wordpress config file.

In any case, I reverted back to the changes you suggested and it appears to be triggering lines 301-310 in mysqli.php, but I believe that section requires values to be set for the individual cert files. I have tried various permutations of dsn within civicrm.settings.php, based off the PEAR DB Documentation, but all fail with the following error code:

Initialization Error

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

    [code] => -24
    [message] => DB Error: connect failed
    [mode] => 16
    [debug_info] =>  [nativecode=mysqli_real_connect(): (HY000/2002):]
    [type] => DB_Error
    [user_info] =>  [nativecode=mysqli_real_connect(): (HY000/2002):]
    [to_string] => [db_error: message="DB Error: connect failed" code=-24 mode=callback callback=CRM_Core_Error::simpleHandler prefix="" info=" [nativecode=mysqli_real_connect(): (HY000/2002):]"]
)

Permutations I have tried are:

define('CIVICRM_DSN', 'mysqli://USER:PASSWORD@HOSTNAME/DATABASE?new_link=true&key=/etc/mysql/ssl/client-key.pem&cert=/etc/mysql/ssl/client-cert.pem&ca=/etc/mysql/ssl/cacert.pem&capath=/etc/mysql/ssl&cipher=AES');

define('CIVICRM_DSN', 'mysqli://USER:PASSWORD@HOSTNAME/DATABASE?new_link=true&key=/etc/mysql/ssl/client-key.pem&cert=/etc/mysql/ssl/client-cert.pem');

define('CIVICRM_DSN', 'mysqli://USER:PASSWORD@HOSTNAME/DATABASE?new_link=true&key=/etc/mysql/ssl/client-key.pem&cert=/etc/mysql/ssl/client-cert.pem&ca=/etc/mysql/ssl/cacert.pem');

define('CIVICRM_DSN', 'mysqli://USER:PASSWORD@HOSTNAME/DATABASE?new_link=true&key=client-key.pem&cert=client-cert.pem&ca=cacert.pem&capath=/etc/mysql/ssl');

Thanks @toddd I had looked at your previous use of the MYSQLI_CLIENT_SSL flag just it seemed like the lines above were the ones intended for ssl use. I agree that you should be able to set the key and cert etc params in the dsn.

I'm going to take a break from this for a bit.

Also making a note that to be a general solution in Civi there is also CIVICRM_LOGGING_DSN and also CIVICRM_UF_DSN. While it's likely if you're using ssl for one you'd use it for the others, and so probably not a blocker, if they're different then, well, they might be different. At that point it might be worth investigating whether it's true or not that adding made-up params to the dsn will confuse PEAR::DB's own use of it, so that the flag could be part of the dsn. i.e. would this line be a problem: https://github.com/civicrm/civicrm-packages/blob/5.15.1/DB/common.php#L735

@JoeMurray raised this question on Mattermost and @DaveD pointed here.

To my reading of DB_mysqli::connect() and https://php.net/mysqli_real_connect, it seems pretty sensible to add MYSQLI_CLIENT_SSL as you originally did. To wit: in the codepath for SSL connections, set MYSQLI_CLIENT_SSL. But omit it from the other codepath for non-SSL connections. (Why did the original writers omit MYSQLI_CLIENT_SSL? Guess: because MySQL <5.7.3 will auto-negotiate up, but MySQL >5.7.3 requires a firm SSL preference.

It's a bit strange that the DB classes split SSL options across two spaces (eg $dsn['ca'] or $dsn['key'] vs $options['ssl']). The toggle should be determined by whoever sets the DSN. Maybe $options['ssl'] could be set by inspecting the the DSN? e.g. Riffing on Dave's snippet:

$options = &PEAR::getStaticProperty('DB','options');
$options['database'] = $dsn;
$options['ssl'] = (bool) preg_match(';[\?&](key|cert|ca|capath|cipher|ssl)=;', $dsn);

SSL would auto-activate if the DSN has any crypto options.

There is some more discussion at https://github.com/civicrm/civicrm-core/pull/17694.

Have put up 2 minimal PRs. There may be another spot or two that's needed as noted in the previous PR like CRM_Utils_File, but this gets you into the civi dashboard at least.

https://github.com/civicrm/civicrm-packages/pull/298
https://github.com/civicrm/civicrm-core/pull/17706 (requires 298 first)

Configuration

For mysql:

1. Outside the scope here to fully describe but briefly create a server certificate and key and put appropriate entries in my.ini or equivalent.
2. To make sure you are using SSL to verify this is working you can do ALTER USER username@localhost REQUIRE SSL;. Obviously replace username and localhost as appropriate.

In drupal settings.php in the $databases array, add a pdo section:

    // Simplest, and doesn't require a client certificate, but doesn't verify the server certificate.
    'database' => 'dbname',
    'username' => 'user',
    'password' => 'pass',
    'host' => 'localhost',    
    'pdo' => array(
        PDO::MYSQL_ATTR_SSL_CA => true,
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => FALSE,
    ),

    // OR //

    // Verifies the cert, but note that the DATABASE SERVER certificate would have to have a CN that matches exactly
    // the hostname you are using in $databases[...]['host'].
    'pdo' => array(
        PDO::MYSQL_ATTR_SSL_CA => '/path/to/ca.crt',
    ),

    // OR //

    // Note the client certificate would have to be reachable from the WEB SERVER.
    'pdo' => array(
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => FALSE,
        PDO::MYSQL_ATTR_SSL_KEY => '/path/to/your.key',
        PDO::MYSQL_ATTR_SSL_CERT => '/path/to/your.crt', // if self-signed and want to also verify server cert see below
    ),

    // OR //

    // Ditto, and also the same note about hostname as above.
    'pdo' => array(
        PDO::MYSQL_ATTR_SSL_CA => '/path/to/ca.crt', // can be same as your.crt if self-signed
        PDO::MYSQL_ATTR_SSL_KEY => '/path/to/your.key',
        PDO::MYSQL_ATTR_SSL_CERT => '/path/to/your.crt',
    ),

In civicrm.settings.php in the DSN's, either:

1. Any or all of key|cert|ca|capath|cipher as described in the pear docs, urlencoded, e.g. spaces in paths are %20 and slashes are %2F.
   e.g. mysqli://user:pass@localhost/dbname?new_link=true&key=%2Fpath%2Fto%2Ffile.key&cert=%2Fpath%2Fto%2Ffile.crt&ca=%2Fpath%2Fto%2Fca.crt

// OR //

2. None of the above and instead use ssl=1 (which is not an official PEAR::DB option it's just something made up to make it work without client certificates).
   mysqli://user:pass@localhost/dbname?new_link=true&ssl=1

closed

changed milestone to %5.29.0

https://github.com/civicrm/civicrm-core/pull/17706 is merged

mentioned in commit 752d4ee6

Just noting I still need to do a PR to update CRM_Utils_File and the docs PR.

mentioned in issue #1926