Page MenuHomePhabricator

encrypt fundraising database client->server communication
Open, Stalled, MediumPublic

Description

Mysql client->server communication is not yet encrypted in fundraising.

  • civicrm role
    • /etc/mysql/my.cnf
    • drupal/sites/default/civicrm.settings.php
    • drupal/sites/default/settings.php
    • /etc/dedupe.yaml
    • /etc/fundstatgen.py (see T350067)
    • /etc/paypal-audit.yaml
    • /etc/silverpop_export.yaml
  • fundraising_db
    • mariadb service users
    • mariadb staff users
  • frdev role
    • /etc/mysql/my.cnf
    • drupal/sites/default/civicrm.settings.php
    • drupal/sites/default/settings.php
    • /etc/dedupe.yaml (not used)
    • /etc/paypal-audit.yaml (probably not used)
    • individual user .my.cnf needs ssl config for statler and other programs that import it
  • frdb-staging role
    • mariadb service users
    • mariadb staff users
  • analytics role
    • /etc/mysql/my.cnf
    • /etc/fundraising/django_settings.py
    • /etc/superset/supserset.py
    • /etc/fundraising/analytics.conf
    • individual user .my.cnf
  • frdb-analytics role
    • mariadb service users
    • mariadb staff users - we need a CA-cert solution for tunneled connections from laptops

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Jgreen added a parent task: Restricted Task.Jul 11 2017, 8:38 PM
Jgreen renamed this task from encrypt fundraising mysql client->server communication to encrypt fundraising database client->server communication.Jun 21 2018, 6:23 PM
Dwisehaupt moved this task from Backlog to In Progress on the fundraising-tech-ops board.
Dwisehaupt subscribed.

SSL capability is available on all dbs. Working though the different tools we have to ensure they use (or can use) ssl connectivity. Then moving onto larger projects such as superset, civi, etc.

Set of commits that have rolled out ssl connectivity on connections for tools using dbs:

17e67c03 Enable ssl for mysql connections with aide tools
3ddf3678 Enable ssl for mysql connections in pfp
ba969b91 Document future use of ssl for mysql in check_fundraising_jobs
85d31e96 Revert "Add ssl connection by default for dump_database"
be8e101b Add ssl connection by default for dump_database
a875afb4 Add ssl connection by default for compact_innodb_table
0131d4a6 Have schema_dumpler default to ssl connections
8fa87562 Add puppet cert files for mysql use on siem role

A chunk of connections will shift once we can complete T246823 and get one a module that will be in use for stretch and buster. I have a diff available that could add ssl connectivity if the testing is successful with pymysql.

This will be of good use when we do the update to pull in this code: https://github.com/civicrm/civicrm-core/pull/17706

payments hosts were shifted to using ssl for replication with buster upgrades completed in T256146

Mentioned in SAL (#wikimedia-fundraising) [2021-03-23T22:33:36Z] <dwisehaupt> pushing 60f9baaf50b to fundraising hosts which will enable ssl by default for mysql client connections that use the host my.cnf file - T170321

Change 676343 had a related patch set uploaded (by Jgreen; author: Jgreen):

[wikimedia/fundraising/analytics@master] Enable mariadb SSL support based on new config option.

https://gerrit.wikimedia.org/r/676343

Pushed this for make_grants to allow the ability to require ssl on user db connections. Not currently enabled for any accounts yet.

[frack::puppet::private] e69d624 Add requires option to user definitions

Replication user on payments db set to require ssl. Grants script pushed and run on payments1001. Replication stopped and started on a subset of hosts and the connection continues. At the mysql.user table level, the change is seen in the ssl_type value.

Before:
+-------------+----------+
| User        | ssl_type |
+-------------+----------+
| replication |          |
| replication |          |
+-------------+----------+
After:
+-------------+----------+
| User        | ssl_type |
+-------------+----------+
| replication | ANY      |
| replication | ANY      |
+-------------+----------+

Replication user on fundraising db set to require ssl. Grants script pushed and run on frdb1004. Replication stopped and restarted on a set of hosts and the connection succeeded and replication continues. mysql.user table shows updated ssl_type value.

Change 676343 merged by Jgreen:

[wikimedia/fundraising/analytics@master] Enable mariadb SSL support based on new config option.

https://gerrit.wikimedia.org/r/676343

Grant scripts updated to use 'create user or replace' (available since 10.1.3) which will allow us to run the scripts and just update the user accounts without the need to drop and recreate grants if we so desire. Tested with my user account and runsgood.

Note: Can't roll forward on require ssl for the fr_stats db user until we sort out the older python2 DjangoBannerStats code and its connections.

civicrm and civi_read db users have been set to require ssl after the completion of steps in T280080. Tested and working in production.

Removing task assignee due to inactivity as this open task has been assigned for more than two years. See the email sent to the task assignee on August 22nd, 2022.
Please assign this task to yourself again if you still realistically [plan to] work on this task - it would be welcome!
If this task has been resolved in the meantime, or should not be worked on ("declined"), please update its task status via "Add Action… 🡒 Change Status".
Also see https://www.mediawiki.org/wiki/Bug_management/Assignee_cleanup for tips how to best manage your individual work in Phabricator. Thanks!

Claiming to take care of the remaining DjangoBannerStats part of this.

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

DjangoBannerStats and corresponding mysql user now require SSL

Jgreen changed the task status from Open to In Progress.
Jgreen moved this task from Done to In Progress on the fundraising-tech-ops board.

Reopening because we haven't switched over individual (non-service) users yet.

Jgreen triaged this task as Medium priority.Apr 12 2023, 7:02 PM
Jgreen updated the task description. (Show Details)

Now that the civi code has been fixed in T280080, we can force ssl requirements for civi code uses. This isn't urgent. May want to roll it during a maint window to ensure we don't have unintended consequences.

Moved to "Maint Window" on workboard so we can try to get civi switched during the upcoming window.

Jgreen updated the task description. (Show Details)
Jgreen updated the task description. (Show Details)
Jgreen updated the task description. (Show Details)
Jgreen updated the task description. (Show Details)
Jgreen removed Jgreen as the assignee of this task.Nov 6 2023, 8:51 PM
Jgreen updated the task description. (Show Details)
Jgreen changed the task status from In Progress to Stalled.Nov 7 2023, 4:37 PM
Jgreen changed the status of subtask T280080: Enable SSL for CiviCRM DB connections from Open to Stalled.
Jgreen moved this task from Maint Window to Blocked on the fundraising-tech-ops board.