Having difficulties in connecting ProxySQL to MariaDB backend via TLSv1.2

[PC]

Hi,

As I understand it, I should be able to connect ProxySQL to a MariaDB database backend via a TLSv1.2 connection; however, I'm having difficulties in doing so.

The setup:

• Application server (Ubuntu 16.04) on 172.16.0.2, with MariaDB (MySQL) 10.1 client and ProxySQL 1.4.3 installed. ProxySQL is configured with default options (admin port on TCP/6032, with MySQL port on TCP/6033 and default credentials). The root CA and client certificate files are stored in /etc/mysql/ssl/
• Database server (Ubuntu 16.04) on 172.16.0.3, with MariaDB 10.1 installed and configured to offer TLSv1.2. The root CA and database certificate files are stored in /etc/mysql/ssl/, and two users are defined ("monitor/monitor" and "test/test")

On the Application server, I configured ProxySQL via

mysql -h 127.0.0.1 -u admin -padmin -P6032 --prompt="Admin> "

as follows:

INSERT INTO mysql_servers(hostgroup_id,hostname,port,use_ssl) VALUES (1,'172.16.0.3',3306,1);
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
UPDATE global_variables SET variable_value='/etc/mysql/ssl/ca-cert.pem' WHERE variable_name='mysql-ssl_p2s_ca';
UPDATE global_variables SET variable_value='/etc/mysql/ssl/db-client-cert.pem' WHERE variable_name='mysql-ssl_p2s_cert';
UPDATE global_variables SET variable_value='/etc/mysql/ssl/db-client-key.pem' WHERE variable_name='mysql-ssl_p2s_key';
UPDATE global_variables SET variable_value='TLSv1.2' WHERE variable_name='mysql-ssl_p2s_cipher';
LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL SERVERS TO DISK;
INSERT INTO mysql_users(username,password,default_hostgroup,use_ssl) VALUES ('test','test',1,1);
LOAD MYSQL USERS TO RUNTIME;
SHOW VARIABLES LIKE 'admin-hash_passwords';
-- Retrieve the partially-hashed password
SAVE MYSQL USERS FROM RUNTIME;
SAVE MYSQL USERS TO DISK;

This results in errors being shown in the error columns of the two select statements:

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 2; SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 2;

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

| hostname    | port | time_start_us    | connect_success_time_us | connect_error                              |

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

| 172.16.0.3  | 3306 | 1510588058983797 | 0                       | SSL connection error: no ciphers available |

| 172.16.0.3  | 3306 | 1510588056984152 | 0                       | SSL connection error: no ciphers available |

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

2 rows in set (0.00 sec)

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

| hostname    | port | time_start_us    | ping_success_time_us | ping_error                                 |

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

| 172.16.0.3  | 3306 | 1510588058984164 | 0                    | SSL connection error: no ciphers available |

| 172.16.0.3  | 3306 | 1510588056984219 | 0                    | SSL connection error: no ciphers available |

+-------------+------+------------------+----------------------+--------------------------------------------+
2 rows in set (0.00 sec)

I can connect the mysql client directly to the database server using TLSv1.2:

mysql -h 172.16.0.3 -u monitor -pmonitor -e "show session status like 'ssl_version';\s"

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

| Variable_name | Value   |

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

| Ssl_version   | TLSv1.2 |

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

--------------

mysql  Ver 15.1 Distrib 10.1.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:          560420

Current database:

Current user:           mon...@172.16.0.2

SSL:                    Cipher in use is DHE-RSA-AES256-GCM-SHA384

Current pager:          stdout

Using outfile:          ''

Using delimiter:        ;

Server:                 MariaDB

Server version:         10.1.28-MariaDB-1~xenial mariadb.org binary distribution

Protocol version:       10

Connection:             172.16.0.3 via TCP/IP

Server characterset:    latin1

Db     characterset:    latin1

Client characterset:    utf8

Conn.  characterset:    utf8

TCP port:               3306

Uptime:                 3 days 23 hours 30 min 42 sec

Threads: 5  Questions: 942175  Slow queries: 0  Opens: 37  Flush tables: 1  Open tables: 31  Queries per second avg: 2.740

--------------

If I drop the TLS security down to TLSv1 on both sides, I no longer get errors in the two log tables, and I can connect to the database.

Am I doing something wrong in the setup?

[PC]

I ran some experiments, using tcpdump and Wireshark to get the wire protocol and the timings, and found the following. In both cases, the MariaDB database server sends a greeting packet to the client upon initial connection.

• The MySQL client sends a connection response with its capabilities, and about 2 milliseconds later, sends a TLS Client Hello packet, which is accepted by the database server, and the database connection is fully established.
  
• ProxySQL sends a connection response with its capabilities, and about 200 milliseconds later, the database server closes the connection. (The database connection timeout is set to 5 seconds.)
  

The differences in the two connection responses are the packet response times (2ms vs >200ms), client capabilities being advertised, the maximum packet size, and the collation sequence.

• MySQL client advertises that it supports the MULTI_STATEMENTS and PLUGIN_AUTH_LENENC_CLIENT_DATA extended client capabilities (https://mariadb.com/kb/en/library/1-connecting-connecting/#capabilities), whereas ProxySQL does not.
• MySQL client advertises that its maximum packet size is 0x01000000 bytes (16MB) whereas ProxySQL advertises a maximum packet size of 0x40000000 (1GB). In case it was this that was the problem, as the database is set up for a 16MB maximum packet size, I tried to change the setting in ProxySQL's global variables, but there was no setting for 1GB packets. (Both "mysql-max_allowed_packet" and "mysql-threshold_resultset_size" are both set to 4194304.)
  
• I don't think this is a likely cause as ProxySQL advertises the same maximum packet size when successfully connecting via TLSv1.1 
• MySQL client has "utf8_general_ci" collation, ProxySQL has "latin1_swedish_ci".
  
• This doesn't seem like it should be the cause!

I'm not sure how I would go about verifying that the extended client capability differences are the cause of the issue though..

[René Cannaò]

Hi,

I haven't try this yet, but I think you are mixing ssl_version with ssl_cipher.

TLSv1.2 is ssl_version , while ssl_cipher should looks something like DHE-RSA-AES128-GCM-SHA256 or AES128-SHA256 .

I haven't try it myself: please let me know.

Thanks

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[PC]

Hi René,

Thanks for getting back to me. There doesn't appear to be any way to set the SSL version to use in either ProxySQL as there isn't an "mysql-ssl_p2s_version" global variable defined in the global_variables table. I've tried to inserting this entry, but loading the resulting variables into runtime doesn't copy it across, which is what I would expect if it wasn't already defined. BTW, there isn't any way of explicitly defining the SSL version to use in MariaDB/MySQL; the only way to do it is to define the set of ciphers that they will allow, so to force TLSv1.2, you have to set the ciphers to those that only exist in TLSv1.2; otherwise, it'll accept any version of TLS (can't remember if it'll accept SSLv3 or not now).

I've tried changing the "mysql-ssl_p2s_cipher" value to "TLSv1", which worked (packet captures show that it offered 67 TLSv1 cipher suites, of which the server accepted TLS_DHE_RSA_WITH_AES_256_CBC_SHA), and to "AES256-GCM-SHA384" (which is a TLSv1.2 cipher), which failed with the same issues as above (database server closing connection prematurely, resulting in the "SSL connection error: no ciphers available" errors in the logs). It looks as if the "mysql-ssl_p2s_cipher" variable will take the values that "openssl ciphers" will accept, including the group names; e.g. "TLSv1", "TLSv1.2", "AESGCM" (see https://www.openssl.org/docs/man1.0.2/apps/ciphers.html for the full list).

Interestingly, if I set the SSL cipher to "DHE-RSA-AES256-SHA" in both the MariaDB and ProxySQL and trace the connection packets, the packet traces show that ProxySQL offers TLSv1.0 encryption to the database, whereas if I use the mysql client, setting the SSL cipher to the same (--ssl-cipher="DHE-RSA-AES256-SHA"), it offers TLSv1.2 encryption. This would indicate a fundamental limitation in how ProxySQL is handling TLS (perhaps hard-coding the SSL version to TLSv1?) in the backend database connection.

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

[René Cannaò]

Hi,

To be honest I don't have enough in-depth knowledge of SSL versions and ciphers to quickly identify where is the problem.

I will need some time to investigate this further.

What I noticed tho is that the error "no ciphers available" is a string not present in proxysql itself.

On my system:

$ grep "no ciphers available" /usr/bin/proxysql 

$ ldd /usr/bin/proxysql
$ grep "no ciphers available" /lib/x86_64-linux-gnu/ -r
Binary file /lib/x86_64-linux-gnu/libssl.so.1.0.0 matches

Is it possible that proxysql and mysql client are linked against different versions of libssl ?

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

[PC]

Hi René,

Thanks for taking a look at this.

The "no ciphers available" string comes from the openssl library, and is returned as an error description when the set of ciphers that the client is offering is rejected by the server, or if the server closes the connection early (which it assumes is the same thing). There is only one version of openssl installed on my test systems, and both ProxySQL and the mysql client are dynamically linked to it:

# ldd `which openssl` | grep ssl

        libssl.so.1.0.0 => /lib/x86_64-linux-gnu/libssl.so.1.0.0 (0x00007fd9bc385000)

# grep "no ciphers available" /lib/x86_64-linux-gnu/libssl.so.1.0.0

Binary file /lib/x86_64-linux-gnu/libssl.so.1.0.0 matches

# ldd `which mysql` | grep ssl

        libssl.so.1.0.0 => /lib/x86_64-linux-gnu/libssl.so.1.0.0 (0x00007fe55a0a2000)

# ldd `which proxysql` | grep ssl

        libssl.so.1.0.0 => /lib/x86_64-linux-gnu/libssl.so.1.0.0 (0x00007f21d5a16000)

I get the same on the database server:

# ldd `which mysql` | grep ssl

        libssl.so.1.0.0 => /lib/x86_64-linux-gnu/libssl.so.1.0.0 (0x00007f9b77c65000)

If it helps you diagnose the issue, I've been running tcpdump on the database server to monitor the database connections from the client and then running the resulting packet dumps through Wireshark to decode the packets.

[René Cannaò]

I didn't have the time to investigate this further, but I created an issue to investigate this later:
https://github.com/sysown/proxysql/issues/1247

Thanks

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

[PC]

Thanks for looking at it.

[PC]

Hi René,

I've had a further thought on this; is it possible that you're compiling against an older version of the MySQL client library that doesn't support TLSv1.2?

[René Cannaò]

ProxySQL is statically linked with mariadb-connector-c 2.3.1 .

It is possible that it doesn't support TLSv1.2 , but I didn't have the time to test this.

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

[PC]

That's a strange coincidence; I've just had the lack of TLSv1.2 support in that component fixed by them this week (it was affecting MaxScale). There should be a new release of it with the bug fix in (soon, I hope!). If you can build against the fixed version when it comes out, it'll should fix the issue.

[René Cannaò]

I created an issue some months ago: https://github.com/sysown/proxysql/issues/1126

Compiling against a different version requires a lot of testing and validation, especially because I have applied multiple matches: https://github.com/sysown/proxysql/tree/v1.4.4/deps/mariadb-client-library

It is in my todo list...

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

[PC]

I see. I do understand that there is a non-trivial amount of work involved, but if it's possible to move that todo item up the agenda, it would be much appreciated! (Hopefully, some of the issues that your patches are correcting will be fixed in the newer releases.)