Page MenuHomePhabricator

encrypt fundraising database client->server communication
Open, Needs TriagePublic

Description

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

Event Timeline

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 added a subscriber: Dwisehaupt.

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.