SSL configuration of ProxySQL configuration

11. SSL configuration of proxysql configuration

Note: during the writing time of the article from April 201904 to may 201905, subsequent official updates in github were not written
~
~

SSL Support

1, SSl settings [SSL configuration for backends]

Starting with version v1.2.0e, ProxySQL supports SSL connections to the backend. An attempt to configure SSL on an older version will fail.

1. Important:

1) Only back-end SSL in v1.x is supported. Before v2.x, clients could not connect to ProxySQL using SSL.
2) Starting from v1.4.5, since ProxySQL uses mariadb-connector-c-2.3.1, only SSL/TLSv1.0 is supported: https://mariadb.com/kb/en/library/mariadb-connector-c-300-release-notes/
3) In ProxySQL v2.x, mariadb-connector-3.0.2 is used, which supports SSL/TLSv1.0, TLSv1.1 and TLSv1.2. This applies to front-end and back-end connections.

2. Preparations to enable SSL

To enable SSL connections, you need to prepare as follows:
1) Update the SSL status value in mysql_servers.use_ssl for the server to use SSL;
2) Update associated global variables (only required in ProxySQL v1.x version, not ProxySQL v2.x)

3. Enable SSL settings for the server

If you want to use both SSL and non SSL to connect to the same server, you need to configure the same server in two different host groups and define access rules.
For example, to configure SSL on a server:

Admin> SELECT * FROM mysql_servers;
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname  | port  | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 1            | 127.0.0.1 | 21891 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 2            | 127.0.0.1 | 21892 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 2            | 127.0.0.1 | 21893 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
3 rows in set (0.00 sec)

Admin> UPDATE mysql_servers SET use_ssl=1 WHERE port=21891;
Query OK, 1 row affected (0.00 sec)

Admin> SELECT * FROM mysql_servers;
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname  | port  | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 1            | 127.0.0.1 | 21891 | ONLINE | 1      | 0           | 1000            | 0                   | 1       | 0              |
| 2            | 127.0.0.1 | 21892 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 2            | 127.0.0.1 | 21893 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
3 rows in set (0.00 sec)

Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT * FROM runtime_mysql_servers;
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname  | port  | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 1            | 127.0.0.1 | 21891 | ONLINE | 1      | 0           | 1000            | 0                   | 1       | 0              |
| 2            | 127.0.0.1 | 21892 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 2            | 127.0.0.1 | 21893 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
3 rows in set (0.00 sec)

At this stage, in ProxySQL v1.x, attempting to connect to port 21891 of host 127.0.0.1 does not yet use SSL because keys and certificates are not configured. Instead of SSL, the connection can be established normally. In ProxySQL v2.x,
If use_ssl = 1, all new connections will use SSL (meaning MySQL's built-in key / certificate).

4. To configure keys and certificates for SSL connections:

Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql%ssl%';
+--------------------+----------------+
| variable_name      | variable_value |
+--------------------+----------------+
| mysql-ssl_p2s_ca   | (null)         |
| mysql-ssl_p2s_cert | (null)         |
| mysql-ssl_p2s_key  | (null)         |
+--------------------+----------------+
3 rows in set (0.00 sec)

Admin> SET mysql-ssl_p2s_cert="/home/vagrant/newcerts/client-cert.pem";
Query OK, 1 row affected (0.00 sec)

Admin> SET mysql-ssl_p2s_key="/home/vagrant/newcerts/client-key.pem";
Query OK, 1 row affected (0.00 sec)

Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql%ssl%';
+--------------------+----------------------------------------+
| variable_name      | variable_value                         |
+--------------------+----------------------------------------+
| mysql-ssl_p2s_ca   | (null)                                 |
| mysql-ssl_p2s_cert | /home/vagrant/newcerts/client-cert.pem |
| mysql-ssl_p2s_key  | /home/vagrant/newcerts/client-key.pem  |
+--------------------+----------------------------------------+
3 rows in set (0.01 sec)

Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

When the above configuration is complete, SSL will be used for all new connections to port 21891 of host 127.0.0.1.

5. Verification

To verify that SSL is working properly between ProxySQL and MySQL, and to check that the SSL CIPHER(SSL key) connected to ProxySQL is working properly, run the SHOW SESSION STATUS LIKE "Ssl_cipher" command,
For example:

mysql -h127.0.0.1 -P6033 -uroot -psecret -e 'SHOW SESSION STATUS LIKE "Ssl_cipher"'
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| Ssl_cipher    | ECDHE-RSA-AES256-SHA |
+---------------+----------------------+

2, [SSL configuration for frontends]

Available from 2.0, but disabled by default.

1. Configuration introduction

To enable SSL for front-end connections, you need to enable MySQL have_ssl = true. Once this variable is enabled, ProxySQL will automatically generate the following files in datadir(/var/lib/proxysql):
proxysql-ca.pem
proxysql-cert.pem
proxysql-key.pem

Note: if you want to use predefined configurations, you can replace them with your own files.
Also note that SSL will only be used for new connections after the MySQL have? SSL = true variable is updated and LOAD MYSQL VARIABLES TO RUNTIME is executed.

To verify that SSL works properly and check the SSL cipher (key) used between the MySQL client and ProxySQL, you can connect to ProxySQL and execute the \ s command.
For example:

mysql -h127.0.0.1 -P6033 -uroot -psecret -e'\s' | grep -P 'SSL|Connection'
SSL: Cipher in use is DHE-RSA-AES256-SHA
Connection: 127.0.0.1 via TCP/IP

2. Supported protocols

SSLv2
SSLv3
TLSv1
TLSv1.1
TLSv1.2

3. Supported key types

DHE-RSA-AES256-GCM-SHA384
DHE-RSA-AES256-SHA256
DHE-RSA-AES256-SHA
DHE-RSA-CAMELLIA256-SHA
AES256-GCM-SHA384
AES256-SHA256
AES256-SHA
CAMELLIA256-SHA
DHE-RSA-AES128-GCM-SHA256
DHE-RSA-AES128-SHA256
DHE-RSA-AES128-SHA
DHE-RSA-SEED-SHA
DHE-RSA-CAMELLIA128-SHA
AES128-GCM-SHA256
AES128-SHA256
AES128-SHA
SEED-SHA
CAMELLIA128-SHA
DES-CBC3-SHA

~
~

complete!

Tags: MySQL SSL MariaDB Session

Posted on Tue, 28 Apr 2020 06:11:46 -0400 by imamferianto