Generated CA certificate rejected by MySQL client with VERIFY_CA

[skeggse]

I've been playing around with using the prebuilt certificates, and suspect that I either have a grave misunderstanding of public key crypto or that the CA certificate should have CA=TRUE instead of CA=FALSE.

On some systems, mysql --ssl-mode=VERIFY_CA fails with ERROR 2026 (HY000): SSL connection error: error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed. While diagnosing this problem, I tried replacing the FALSE with a TRUE in the proxysql binary from the proxysql/proxysql:2.1.0 docker container, and it resolved the error I was seeing. It looks like this was changed in #2627, and I'm wondering what the cases where you want CA:FALSE are.

• ProxySQL version: 2.1.0-544-g17a4b4a7
• OS version: Debian 9.13 (from Docker container; Linux 5c72934c8310 5.10.25-linuxkit #1 SMP x86_64 GNU/Linux)
• MySQL client versions:
  • mysql Ver 8.0.23 for osx10.15 on x86_64 (Homebrew) (rejected CA)
  • mysql Ver 14.14 Distrib 5.7.32, for osx10.15 (x86_64) using EditLine wrapper (rejected CA)
  • mysql Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using EditLine wrapper (accepted CA)

Steps to reproduce

1. Run the proxysql/proxysql:2.1.0 container
2. Issue the SET mysql-have_ssl='true';LOAD MYSQL VARIABLES TO RUNTIME; admin commands
3. mysql --ssl-mode=VERIFY_CA --ssl-ca=.../path/to/proxysql-ca.pem -h <host> -P 6032 -uadmin -padmin

/var/lib/proxysql/proxysql.log

2021-04-22 03:32:54 [INFO] ProxySQL version 2.1.0-544-g17a4b4a7
2021-04-22 03:32:54 [INFO] Detected OS: Linux 5c72934c8310 5.10.25-linuxkit #1 SMP Tue Mar 23 09:27:39 UTC 2021 x86_64
2021-04-22 03:32:54 [INFO] ProxySQL SHA1 checksum: e1e71d5615cfedf7de2c56278e8981ed5b5d8ad4
2021-04-22 03:32:54 [INFO] Starting ProxySQL
2021-04-22 03:32:54 [INFO] Sucessfully started
2021-04-22 03:32:54 [INFO] Angel process started ProxySQL process 459
2021-04-22 03:32:54 [INFO] Loaded built-in SQLite3
Standard ProxySQL MySQL Logger rev. 2.0.0714 -- MySQL_Logger.cpp -- Mon Jan  4 21:22:57 2021
Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Mon Jan  4 21:22:57 2021
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Mon Jan  4 21:22:57 2021
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Mon Jan  4 21:22:57 2021
Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Tue Jan 12 19:34:07 2021
2021-04-22 03:32:54 [INFO] ProxySQL SHA1 checksum: e1e71d5615cfedf7de2c56278e8981ed5b5d8ad4
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Tue Jan 12 19:34:07 2021
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Mon Jan  4 21:22:57 2021
2021-04-22 03:32:54 [INFO] Dumping mysql_servers_incoming
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2021-04-22 03:32:54 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2021-04-22 03:32:54 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2021-04-22 03:32:54 [INFO] New mysql_group_replication_hostgroups table
2021-04-22 03:32:54 [INFO] New mysql_galera_hostgroups table
2021-04-22 03:32:54 [INFO] New mysql_aws_aurora_hostgroups table
2021-04-22 03:32:54 [INFO] MySQL_HostGroups_Manager::commit() locked for 1ms
Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Mon Jan  4 21:22:57 2021
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Mon Jan  4 21:22:57 2021
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Fri Jan  8 21:37:02 2021
2021-04-22 03:32:54 [INFO] Latest ProxySQL version available: 2.1.1-40-g1c2b7e4
2021-04-22 03:33:48 MySQL_Thread.cpp:4838:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.17.0.1:58682
2021-04-22 03:34:00 MySQL_Session.cpp:4939:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'admin'@'172.17.0.1' (using password: NO)
2021-04-22 03:34:35 MySQL_Session.cpp:4939:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'admin'@'172.17.0.1' (using password: NO)
2021-04-22 03:34:40 MySQL_Session.cpp:4939:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'admin'@'172.17.0.1' (using password: NO)
2021-04-22 03:34:45 MySQL_Session.cpp:4939:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'admin'@'172.17.0.1' (using password: NO)
2021-04-22 03:34:52 MySQL_Thread.cpp:4838:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.17.0.1:58702
2021-04-22 03:34:57 MySQL_Thread.cpp:4838:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.17.0.1:58706
2021-04-22 03:44:26 MySQL_Thread.cpp:4838:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.17.0.1:58730
2021-04-22 03:45:32 MySQL_Thread.cpp:4838:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.17.0.1:58734
2021-04-22 03:45:51 MySQL_Thread.cpp:4838:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.17.0.1:58738

[JavierJF]

Hi @skeggse,

this was indeed changed by mistake by me in the PR you mention. There is no reason for which we want the CA certificate to hold CA:FALSE. Thanks you for spotting it, the behavior will now be changed to mimic the behavior exhibit by MySQL in their self generated certificates via PR #3417.

Thank you.

[skeggse]

I imagine it might be a bit before a 2.2.0 image shows up on Docker hub. Could you link me to the Dockerfile used for the proxysql/proxysql image so I can build it myself in the meantime? I thought it would be this one but that image is ubuntu-based and the one on Docker hub is debian-based.