Finish allowing use of SSL to connect to database

Hey @DaveD I am a bit lost on what the issue is about, I understand the table and that one is clear but what is the overall problem?

Hi, The work done in ticket 1137 will as of v5.29 allow for the minimum to start using an encrypted connection to the mysql database by updating some settings in civicrm.settings.php and the CMS config. I have WIP docs PR to go along with that. But it requires an already installed civi and only works with things you can click around in the UI. There are some remaining tasks to make it fully work, e.g. for install, upgrades, cron/external scripts, etc.

Ah thanks that makes it clear.

added git:civicrm-core ~88 triaged labels

mentioned in commit 0dcdc386

mentioned in commit 37111981

Forgot to list some PRs:

civicrm-setup: https://github.com/civicrm/civicrm-core/pull/18107 (merged)

Autodetect during installation: https://github.com/civicrm/civicrm-core/pull/18264

For scripts: https://github.com/civicrm/civicrm-core/pull/18125 (closed temporarily since waiting to see what happens with extern urls)

mentioned in commit b959a1ad

mentioned in issue #2726 (closed)

@DaveD this is still open - should it be

Docs link

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

A pull request related to this issue has been opened with the title: #1926 Add docs link to civicrm.settings.php.template

added has-pull-request label

No there's still some outstanding things that don't work if using ssl for mysql, like calling scripts. But you can get up and running.

Hi,

Thank you for raising an issue to help improve CiviCRM. As you may know, this issue has not had any activity for quite some time, so we have closed it.

We would like you to help us to determine if this issue should be re-opened:

If this issue was reporting a bug, can you attempt to reproduce it on a latest version of CiviCRM?
If this issue was proposing a new feature, can you verify if the feature proposal is still relevant? Did it get the concept-approved label? Have other people also shown interest? Could it be implemented as an extension?

If the answer to either question is yes, please feel free to comment or re-open the issue. Please also consider:

Is it something that you could help implement, either by sending a patch or hiring someone who can?
Would you be willing to fund the work, either through a CiviCRM partner, or the Core Team?

Thank you for your help and contributions to CiviCRM.

P.S. This is an automated message, see infra/gitlab issue 20. We understand that automatic responses are annoying, but given the number of open issues as the project evolves, we need a bit of help to triage and prioritise the most relevant issues.

closed

reopened

I've been trying to reproduce a failure reported in https://civicrm.stackexchange.com/q/48465. It would be good to compare more details between working/non-working environments.

Here's my environment:

MySQL 5.7.37, PHP 7.4.29, Drupal 7.94, CiviCRM 5.79.beta1, cv 0.3.58
General setup process:
- Preliminary Drupal
  1. Install MySQL/PHP/etc stack (civicrm-buildkit/nix@master)
  2. Install D7 without Civi
- Drupal with basic MySQL-SSL
  1. Generate new MySQL PKI using this script. This allows me to define resolvable values of hostname/commonName for use in the certificates (CA + server + client).
  2. Update my.cnf to use the new certificates/keys ([mysqld] uses server-key; [client] uses client-key). Restart MySQL.
  3. Update settings.php to use SSL options.
  4. Confirm D7 works.
- CiviCRM with basic MySQL-SSL
  1. Install CiviCRM
  2. Confirm that civicrm.settings.php DSN has SSL options passed-through
- Ensure SSL is being used
  1. Force MySQL to require SSL (set global require_secure_transport = 1;)
With this baseline, I can then revise settings.php ($databases) and civicrm.settings.php (CIVICRM_DSN) to apply various valid+invalid options.
- Set valid MySQL-SSL options. See if web-pages work. See if cv CLI works.
- Set invalid MySQL-SSL options (e.g. typo in certificate path; mismatched hostname). See if web-pages fail (as they should). See if cv CLI fails (as it should).

So I specifically tried adapting the formulas for CIVICRM_DSN and $databases configurations from https://civicrm.stackexchange.com/q/48465 -- these formulas do look generally valid, and they did work for me. Specifically, the worked with

Web-based requests
cv (default bootstrap)
cv with alternate bootstrap options (CIVICRM_BOOT=Auto://. or CIVICRM_SETTINGS="Auto")

The next thing we should try to do: control for the versions of major items in toolchain (MySQL, PHP, Drupal, CiviCRM, cv), and see where they differ in the working/non-working environments.

Per Lance's comment, I retested in a few closer environments:

ID	MySQL	PHP	Drupal	CiviCRM	cv	Outcome
1	5.7.44	8.0.27	7.98	5.51.3	0.3.58	cli error observed by lance
2	5.7.37	7.4.29	7.94	5.79.beta1	0.3.58	cli ok observed by totten
3	5.7.37	8.0.19	7.98	5.51.3	0.3.58	cli ok observed by totten
4	5.7.44-local	8.0.28	7.98	5.51.3	0.3.58	cli ok observed by totten

Those are the exact same versions of Drupal, CiviCRM, cv. The PHP versions follow pretty close (8.0.27 vs 8.0.19+8.0.28). The mysqld versions look numerically close but are slightly suspicious (more later).
Generally, my gut says there's some other/unidentified variable, and I think the best thing is to get more error-data about the failed connection.
The most ordinary thing would be some discrepancy in the PKI (certificates or keys). But it sounds like there have been some controls for that possibility.
This is speculative, but there may also be subtler differences in the builds of PHP/MySQL. These normally don't matter, and it's usually far-fetched, but we don't have anything better... so just exploring some thoughts in that area...
- MySQL 5.7.43 officially switched openssl 1.1=>3.0. That sounds like a major change, and it's relevant area. But that should mostly affect internal APIs and maybe some less-common cipher-suites. You wouldn't expect it to manifest this way.
- Two builds of PHP/MySQL may use the same versions -- but have different compilation flags or different dependencies, eg
  - Compilation flags for pdo
  - Compilation flags for mysqli
  - Linked version of openssl
  - Linking to mysqlnd vs libmysqlclient (and its version)
- I usually pull binaries from nixpkgs. But they officially stopped shipping mysql57 circa 5.7.37. To try 5.7.44, I improvised a custom build. That seems to work, but I suspect it's still using openssl 1.1.
- There can be edge-cases where different SSL implementations react differently to the same *.pem file. (Say... a singular cert works fine in both implementations, but a concatenated cert only works in some. Or perhaps the signing algorithm is newer.)

Hmm, I just noticed the docs for PHP mysqlnd include a comparison with libmysqlclient, and they list "SSL" support as a benefit of mysqlnd. Another far-fetched theory:

The php-fpm build could be newer/different from the php-cli build. Then...
You could see SSL working on php-fpm (with mysqlnd)
You could see SSL failing on php-cli (with libmysqlclient)

ping @lance_compuco (tldr: try getting more debug messages; double-check that the php-fpm/php-cli binaries are from the same distributor/packager/version)

Stepping back to consider overall ticket.

When I spoke to @seamuslee a week ago, he thought some specific issues (from the original description) were fixed. Since I've been doing some testing, I thought I'd capture what has worked (or has not worked) for me:

Issue	5.51.3	5.79.beta 1
Run web installer with D7 on MySQL-SSL. Propagate settings to Civi.	OK	OK
Set MySQL to force-require SSL (to confirm SSL is really used)	OK	OK
Run cv CLI (eg `setting:get` or `php:eval`)	OK	OK
Enable an extension with `*.sql` schema files (eg `cv en inlay` circa `baea17d05adf9` - which fires `CRM_Utils_File::sourceSQLFile`)	OK	OK
Run `bin/cron.php` via HTTP	Failure	Failure
Set `civicrm.settings.php` to give incorrect SSL options. Debug why it fails.	Awkward	Awkard

So I think there are of 3-ish outstanding things:

CRM_Utils_System::authenticate as used by bin/cron.php still fails. (I can reproduce this. However, I've only reproduce it in bin/ and extern/, which are kind of niche areas.)
Error reporting about SSL is pretty opaque. If there's a problem, it just says connect failed with no more logs or hints. (Here's a quick hack to give better info. And here's another discussion about more releasable mitigations...)
There's the StackExchange report (related subthread) about a failure while using CV CLI. (I cannot reproduce this; it seems like a good example where you need more debug info...)

Related pull requests:

Run bin/cron.php (et al) via HTTP: See https://github.com/civicrm/civicrm-core/pull/31912
Error reporting about SSL is too opaque: See https://github.com/civicrm/civicrm-core/pull/31911

mentioned in commit a1dc0fe5

Finish allowing use of SSL to connect to database

Child items ...

Activity

thing	comment
CRM_Utils_File	This block seems to do almost the same thing as CRM_Core_DAO::init(), but not completely, but I think also has a "bug" where it wipes the sql_mode instead of updating it. This is called from e.g. the upgrade process, but it's not clear yet why that would require its own thing, since civi is already installed. Will test this out and see.
CRM_Utils_System::authenticate	It calls DB::connect assuming it's already available, and then does nothing with it, and then a couple milliseconds later the UF class does it again. Is this just leftover from refactoring, maybe as suggested in the comment a couple lines above. Additionally, only drupal 7 seems to use it, but it's not clear if it's really necessary and why can't it just use cms functions in the cms class, which it does in drupal 8 and the others.
install/	Would like to leave this out if the future plan is to move to a civicrm-setup variant. Otherwise it means adding fields to the form for example.