I've done some digging on this

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

https://github.com/totten/rpow/pull/12

although I also put up 2 PRs that don't change anything (docs)[https://github.com/totten/rpow/pull/11] and (boilerplate)[https://github.com/totten/rpow/pull/10]

The trickiness is testing it locally

I have been able to get rpow working with this change

https://github.com/civicrm/civicrm-buildkit/pull/628

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 https://github.com/totten/rpow/blob/760825a02b733687f5911619b853271fb3d0ac2d/DB/civirpow.php#L56

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

• there is a new local issue I'm hitting where the search extension has been renamed to search_kit. - this is because I'm already running more up-to-date code locally with https://github.com/civicrm/civicrm-core/pull/20124 already merged

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 org.civicrm.search

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 https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/694057
2. to update the dsns in this section of civicrm.settings.php

rpow_init([
  'masters' => ['mysql://....@...?new_link=
true'],
  'slaves' => ['mysqli://...@....
?new_link=true'],
]);

as described here

https://docs.civicrm.org/installation/en/latest/general/mysql_tls/#dsn-settings

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 https://civicrm.frdev.wikimedia.org

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 https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/701224

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));

   Database::parseConnectionInfo();
 }

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

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?

In T280080#9026383, @Eileenmcnaughton wrote:

@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 https://docs.civicrm.org/installation/en/latest/general/mysql_tls/#dsn-settings

'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):

?new_link=true&key=%2Fetc%2Ffundraising%2Fclient-key.pem&cert=%2Fetc%2Ffundraising%2Fclient-cert.pem&ca=%2Fetc%2Ffundraising%2Fcacert.pem'

However I'm stuck on this connection:

rpow_init([
  '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 https://civicrm.frdev.wikimedia.org/ you'll see the error.

@Eileenmcnaughton it looks like it's fixed now, yay!

@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.

Adding to the FY2324Q3 maintenance window.