Page MenuHomePhabricator

encrypt fundraising mariadb replication
Closed, ResolvedPublic

Description

Replication traffic between mysql servers is not yet encrypted in fundraising. See T111654.

Event Timeline

Jgreen added a parent task: Restricted Task.Jul 11 2017, 8:38 PM

Debian's stock mariadb (jessie = 10.0.32) is compiled with YaSSL instead of OpenSSL . . .

https://groups.google.com/forum/#!msg/linux.debian.bugs.dist/cSoKYOOdLFo/rU7Ut3taAgAJ

. . . which means you can't restrict connections to TLS 1.1 or 1.2 with the stock package.

paymentsdb replication is encrypted as of today...

For the fundraisingdb's mysql needs to be restarted to enable SSL support, then enable it for replication like so (adjusted):

change master to master_host='payments2001.frack.codfw.wmnet', master_user='replication', master_password='OMGTOPSECRET', master_port=3306, master_log_file='payments2001-bin.000657',master_log_pos=349, master_connect_retry=10,master_ssl=1, master_ssl_verify_server_cert=1;

Here https://mariadb.org/state-ssl-mariadb/ it is stated that the packages in MariaDB's repositories are compiled with OpenSSL (thus supporting TLSv1.1/1.2) and that the binary tarballs are compiled with YaSSL (no TLSv1.1/1.2 support). So now I'm thinking it makes sense to evaluate MariaDB's Jessie/Stretch packages. @jcrespo / @Marostegui do you have any feedback on using those packages?

We create our own mariadb and mysql packages for production- several of they can be installed at the same time, and they don't try to be "smart" about the data on boot. On the contrary, they take more effort to be setup and depend on puppet to do some things. We compile them linking to debian version of libssl (openssl). They also have the best systemd unit I ever saw. However, they are focused on working for the needs of mediawiki production, analytics and wikireplicas. It also allow to backports patches without having to do a full upgrade.

They are called wmf-mariadb101 and wmf-mysql80. You can check them how we use them on our mariadb::core or mariadb::misc roles, for example.

On topic, we have been using TLS based replication for mariadb for almost over 2 years: https://phabricator.wikimedia.org/T111654#3010354

After much pain and suffering, I seem to have MariaDB's 10.0.35 packages working in virtualbox, with OpenSSL and TLSv1.2, and using puppet's CA and host certificates.

I'm not sure I have the right replication settings--if I include master_ssl_verify_server_cert=1 in the change master command, the replication connection fails with "SSL connection error: Failed to verify the server certificate". However connecting with the client with --ssl-verify-server-cert works fine, and all indications from openssl are that the ca/client/server certs are sane.

SSL connection error: Failed to verify the server certificate

Either you haven't restarted mysql, or set the right replication start parameters, or using a different connection string than with the client (e.g. an ip vs a fqdn, etc.)

Definitely restarted mysql, and here's what I've got for replication start params vs client command line:

MariaDB [(none)]> show variables like "%ssl%";
+---------------+----------------------------+

Variable_nameValue

+---------------+----------------------------+

have_opensslYES
have_sslYES
ssl_ca/etc/mysql/cacert.pem
ssl_capath
ssl_cert/etc/mysql/server-cert.pem
ssl_cipher
ssl_crl
ssl_crlpath
ssl_key/etc/mysql/server-key.pem

+---------------+----------------------------+

change master to master_host='frdb1001.frack.eqiad.wmnet', master_user='replication', master_password='what.', master_port=3306, master_log_file='frdb1001-bin.000057',master_log_pos=329, master_connect_retry=10,master_ssl=1,master_ssl_verify_server_cert=1;

...with this outcome:
Last_IO_Error: error connecting to master 'replication@frdb1001.frack.eqiad.wmnet:3306' - retry-time: 10 maximum-retries: 86400 message: SSL connection error: Failed to verify the server certificate

mysql -h frdb1001.frack.eqiad.wmnet -P 3306 -u replication -pwhat. --ssl-ca=/etc/mysql/cacert.pem --ssl-cert=/etc/mysql/server-cert.pem --ssl-key=/etc/mysql/server-key.pem --ssl-verify-server-cert

...with a successful connection.

Note this same config worked fine with the Debian/YaSSL package. I've looked at apparmor, different versions of OpenSSL (Jessie stock vs backport), confirmed that the certs and CA are valid, generated a new CA and certs, double checked DNS PTR/A records, and both MariaDB 10.0 and 10.1. It's quite puzzling.

Oh HO! I think I may have just figured it out...

change master to master_host='frdb1001.frack.eqiad.wmnet', master_user='replication', master_password='ding', master_port=3306, master_log_file='frdb1001-bin.000042',master_log_pos=329, master_connect_retry=10,master_ssl=1,master_ssl_verify_server_cert=1, master_ssl_ca='/etc/mysql/cacert.pem';

Apparently the replication thread isn't getting master_ssl_ca from the global ssl_ca variable?

Check error log both both servers, you may find more info there. If it worked with yassl, maybe you have an incompatibility- I don't think those (openssl and yassl) play well together.

BTW, ssl_cipher is strange, this is what I have:

root@db1067[(none)]> show variables like "%ssl%";
+---------------------+---------------------------------------+
| Variable_name       | Value                                 |
+---------------------+---------------------------------------+
| have_openssl        | YES                                   |
| have_ssl            | YES                                   |
| ssl_ca              | /etc/ssl/certs/Puppet_Internal_CA.pem |
| ssl_capath          |                                       |
| ssl_cert            | /etc/mysql/ssl/cert.pem               |
| ssl_cipher          | TLSv1.2                               |
| ssl_crl             |                                       |
| ssl_crlpath         |                                       |
| ssl_key             | /etc/mysql/ssl/server.key             |
| version_ssl_library | OpenSSL 1.0.2l  25 May 2017           |
+---------------------+---------------------------------------+
10 rows in set (0.01 sec)

I am relatively sure that server does not have [good] openssl active (TLS >= 1.2) (note it is ok for cipher to have a specific one).

Sorry if this distracts you, ignore me if I am not being helpful.

Check error log both both servers, you may find more info there. If it worked with yassl, maybe you have an incompatibility- I don't think those (openssl and yassl) play well together.

BTW, ssl_cipher is strange, this is what I have:

root@db1067[(none)]> show variables like "%ssl%";
+---------------------+---------------------------------------+
| Variable_name       | Value                                 |
+---------------------+---------------------------------------+
| have_openssl        | YES                                   |
| have_ssl            | YES                                   |
| ssl_ca              | /etc/ssl/certs/Puppet_Internal_CA.pem |
| ssl_capath          |                                       |
| ssl_cert            | /etc/mysql/ssl/cert.pem               |
| ssl_cipher          | TLSv1.2                               |
| ssl_crl             |                                       |
| ssl_crlpath         |                                       |
| ssl_key             | /etc/mysql/ssl/server.key             |
| version_ssl_library | OpenSSL 1.0.2l  25 May 2017           |
+---------------------+---------------------------------------+
10 rows in set (0.01 sec)

I am relatively sure that server does not have [good] openssl active (TLS >= 1.2) (note it is ok for cipher to have a specific one).

Sorry if this distracts you, ignore me if I am not being helpful.

Not at all, thanks for looking at it. I'm working with MariaDB's 10.0.35 on both sides so YaSSL isn't in the mix. I'll look into adjusting the protocol/cipher in line with what we have in production.