Page MenuHomePhabricator

Enable SSL for CiviCRM DB connections
Open, Stalled, MediumPublic


We have added the functionality on the DB side to require SSL for mariadb connections based on user. We (fr-tech-ops) would like this tested with civicrm on frdev. Then we can enable it there and on production for the required users.

Initially, we will be ok with just using SSL for the connection. If it can be configured, we would like to have it set to verify the DB connection using the host's copy of the CA file.

Event Timeline

I've done some digging on this

I'm pretty sure that all we need to do is

although I also put up 2 PRs that don't change anything (docs)[] and (boilerplate)[]

The trickiness is testing it locally

I have been able to get rpow working with this change

And I've stepped through the code to see it doing the right things in the connection - the way it works is rpow registers a class that extends mysqli so this is the best place to put the debugger breakpoint

What I haven't managed yet in terms of testing locally :

There is a command that will cope with either name & adding it to buildkit install would also help because this extension will be enabled in core by default at some point (it was and then it wasn't so it might not be a one-way street). However cv isn't working correclty for me locally -and I'm having trouble debugging why

cv en --ignore-missing org.civicrm.search_kit

Another local issue is the mailing_stats extension is saying it needs to run the upgrade but then it can't - not sure why

oh, and lastly - I don't have mariadb working with ssl yet - the others were things kinda in my way to figuring that out

@Dwisehaupt I think the ==tldr== of all of that is that probably what we need is

  1. this patch
  2. to update the dsns in this section of civicrm.settings.php
  'masters' => ['mysql://....@...?new_link=
  'slaves' => ['mysqli://...@....

as described here

ie - at minimum add `&ssl=1` to the end of the dsn (if it's already done it's because I added it to see what would happen & nothing did because I hadn't done 1) or at maximum add all the other described variables for cert etc

This is all quite tricky to test locally but perhaps straight foward for you to test on staging

OK. I think it works. Here's what/how I tested:

  • update drupal/sites/default/civicrm.settings.php to have &ssl=1 for the read and write handles
  • update drupal/sites/default/civicrm/extensions/rpow/DB/civirpow.php with the patch you had suggested.
  • set the db users for dev_civicrm and dev_civicrm_read to require ssl at the mysql level
  • ran some contact queries on

Once I had the db users set to require ssl, if the connection wasn't using ssl it should toss a db connection error.

More thorough functional testing will be needed. In addition, we should test the possibility of certificate verification using the &ca= option.

@Dwisehaupt that's probably enough to +2 the patch so we can deploy & then the next testing will be in conjunction wit actually rolling out the ssl config

Actually @Dwisehaupt - it needs to work with & without ssl to +2 it - it's not working without ssl now but I think that is because the db user requires it - so it need to work locally for us without ssl - which it does

@Dwisehaupt tried this switch over just now but we hit a snaffu - there are some places in the code that try to translate the CiviCRM dsn to use in drupal queries. This is old legacy cruft which we would ideally remove like this

However, we could resort to further hacking the db_swither which looks like

if (!Database::getConnectionInfo('civicrm')) {
   require_once conf_path() . '/civicrm.settings.php';
   $url = CIVICRM_DSN === 'civirpow://' ? $GLOBALS['civirpow']['masters'][0] : CIVICRM_DSN;
   $databases['civicrm']['default'] = static::parse_dbi_url(str_replace('?new_link=true', '', $url));


& is currentlya dded the non-std ?ssl=1 to the database name

Jgreen moved this task from Done to Triage on the fundraising-tech-ops board.

Apparently this task wasn't completed and I erroneously closed it back in 2021.

@Jgreen @Dwisehaupt my understanding is that the ourstanding action is DB config - not something on me?

@Jgreen @Dwisehaupt my understanding is that the ourstanding action is DB config - not something on me?

It's not clear to me reading this history on this task how to configure the database, and I'm not having any luck experimenting with adding ?new_link=true or ?ssl=1 to various places that seem plausible.

@Jgreen here is the documentation

'new_link=true' is 'standard' - ie present for ssl or otherwise

ssl=1 is the extra bit - but note the docs specify some more nuanced options

I figured out settings for the Drupal connection:

$databases['donations']['default'] =
  $databases['default']['default'] = array(
    'pdo' => array (
        PDO::MYSQL_ATTR_SSL_KEY =>  '/etc/fundraising/client-key.pem',
        PDO::MYSQL_ATTR_SSL_CERT => '/etc/fundraising/client-cert.pem',
        PDO::MYSQL_ATTR_SSL_CA =>   '/etc/fundraising/cacert.pem',

For the other connections, based on the civi documentation this is what I think should be appended for our environment (client certs + verify server cert):


However I'm stuck on this connection:

  'slaves' => ['mysqli://dev_civicrm_read:<passwd>@frdb1006.frack.eqiad.wmnet/dev_civicrm?new_link=true&key=%2Fetc%2Ffundraising%2Fclient-key.pem&cert=%2Fetc%2Ffundraising%2Fclient-cert.pem&ca=%2Fetc%2Ffundraising%2Fcacert.pem'],

I tested using mariadb client and the credentials and SSL connection are fine. I also tried using the dev_civicrm user and same problem. I don't know if it's because there's something specific to the read-only handling, or it just happens to be the first connection that is tried after drupal. I left the work-in-progress in place, so if you visit you'll see the error.

@Jgreen All the code changes from my side have been deployed to production

DB user portion broken out into a subtask. Moving this to done for the current sprint.

XenoRyet set Final Story Points to 4.
Jgreen closed this task as Resolved.
Jgreen reopened this task as Open.EditedNov 3 2023, 9:09 PM
Jgreen moved this task from Done to In Progress on the fundraising-tech-ops board.

Reopening this because we're stuck getting this to work on the production server.

We have a working configuration on frdev1002. So I imported the bits that changed from settings.php and civicrm.settings.php there.

Like this:

- mysqli://civicrm:<PASSWORD>@fundraisingdb-write.wmnet/civicrm?new_link=true
+ mysqli://civicrm:<PASSWORD>@fundraisingdb-write.wmnet/civicrm?new_link=true&ca=%2Fetc%2Ffundraising%2Fcacert.pem

and this:

$databases['default']['default'] = array(
  'driver' => 'mysql',
+  'pdo' => array (
+      PDO::MYSQL_ATTR_SSL_CA => '/etc/fundraising/cacert.pem',
+  ),

When we tried dropping in these changes on civi1002, we see errors in browser:

messagePDOException: SQLSTATE[HY000] [2002] in lock_may_be_available() (line 167 of /srv/org.wikimedia.civicrm/drupal/includes/


Critical error. Please see server logs for errorID:59e70a859d80Critical error. Please see server logs for errorID:d1f8812e332d

Nothing correlating to the errorID shows up in the ConfigAndLog logs, or in syslog however.

I thought there might be differences in deployed code between the staging vs production civicrm project, so I copied over the as-deployed web tree from frpm1002, and dropped the staging versions of settings.php and civicrm.settings.php. I also tried starting from the production config files (which have significant differences around php settings), changing the database and other credentials for dev. Both configuration sets work fine.

I checked php config, civi1002 and frdev1002 look essentially the same.

Jgreen changed the task status from Open to Stalled.Nov 7 2023, 4:37 PM
Jgreen moved this task from In Progress to Blocked on the fundraising-tech-ops board.