Bug at ProxySQL version 2.5.2-217-g7f727b3 fails to connect to the backend over local socket when SSL is requred

[seikath]

Descritption:

Non crashing bug:
After an upgrade to 2.5.2-217-g7f727b3 from 2.5.1-90-gbedaa6c ProxySQL fails to connect to the backend over local mysql socket when the backend DB user REQURE SSL connection.
In general, enforcing SSL for the local socket backend connections does not make much sense, reporting the bug just as finding.

Tested on :

1 .CentOS release 6.10 (Final) - with backend 5.5.62-MariaDB
2. Debian GNU/Linux 11 (bullseye) - with backend 10.6.13-MariaDB

Steps to reproduce the issues :

1. Install 2.5.2-217-g7f727b3
2. Set the ProxySQL monitor user/passwd.
3. Create at the backend DB instance the ProxySQL monitor DB user with REQURE NONE clause.
4. Create a backend DB user with REQURE NONE clause;
5. Create the ProxySQL backend DB instance using mysql sock connection and enforce SSL (use_ssl=1).
6. Create the corresponding ProxySQL USER and enforce SSL (use_ssl=1)
7. Load the mysql servers and mysql users to runtime.
8. Check the runtime mysql servers status (ONLINE)
9. Test the DB connection from remote mysql client , it works.
10. ALTER the backend DB user with REQUERE SSL clause / enforece SSL connection.
11. Test the DB connection from remote mysql client , it fails.
12. ALTER the backend DB user with REQUERE NONE clause / enforece SSL connection.
13. Test the DB connection from remote mysql client , it fails.

Example :

ProxySQL-2.5.2-217-g7f727b3:6032:[email protected]:[Wed Jun  7 05:03:09 2023][(none)]> pager egrep "\+|sock|group"
PAGER set to 'egrep "\+|sock|group"'
ProxySQL-2.5.2-217-g7f727b3:6032:[email protected]:[Wed Jun  7 05:03:22 2023][(none)]> select * from runtime_mysql_servers;
+--------------+-----------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------------------+
| hostgroup_id | hostname                    | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment                            |
+--------------+-----------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------------------+
| 10           | /var/run/mysqld/mysqld.sock | 0    | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 1       | 0              | test connection via socket and SSL |
+--------------+-----------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------------------+
5 rows in set (0.00 sec)

ProxySQL-2.5.2-217-g7f727b3:6032:[email protected]:[Wed Jun  7 05:07:55 2023][(none)]> select username,active,use_ssl,default_hostgroup from runtime_mysql_users where username  like 'ivan%' group by username;
+-----------+--------+---------+-------------------+
| username  | active | use_ssl | default_hostgroup |
+-----------+--------+---------+-------------------+
| ivan.s_rd | 1      | 1       | 10                |
+-----------+--------+---------+-------------------+
1 row in set (0.00 sec)

[email protected]:[Wed Jun 07 05:12:32][~]$ pt-show-grants | sed '/ivan.s_rd/!d;/localhost/!d;/PASS/!d' 
GRANT PROCESS, REPLICATION CLIENT, SHOW DATABASES ON *.* TO 'ivan.s_rd'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxx';


-- test connectiion 
mysql  -A --ssl -u ivan.s_rd -p'yyyyyy'  -h example.com   -e 'SELECT @@hostname,@@port; SELECT now() l; \s'
+-----------------------------+--------+
| @@hostname                  | @@port |
+-----------------------------+--------+
|                 example.com |   3305 |
+-----------------------------+--------+

+---------------------+
| NOW()               |
+---------------------+
| 2023-06-07 05:18:21 |
+---------------------+
--------------
mysql  Ver 15.1 Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

Connection id:		121429
Current database:	adminix
Current user:		ivan.s_rd@localhost
SSL:			Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		5.5.62-MariaDB (ProxySQL)
Protocol version:	10
Connection:		exmaple.com via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		6033
Uptime:			21 days 49 min 4 sec

Threads: 1  Questions: 18641  Slow queries: 88
--------------

-- enforce the SSL at the backend 
ALTER USER 'ivan.s_rd'@'localhost' REQUIRE SSL;
FLUSH PRIVILEGES;

-- test again the connection 
mysql  -A --ssl -u ivan.s_rd -p'yyyyyyy'  -h example.com   -e 'SELECT @@hostname,@@port; SELECT now() l; \s'
ERROR 1045 (28000) at line 1: Access denied for user 'ivan.s_rd'@'localhost' (using password: YES)

latest lines from the proxysql log after the loading the mysql servers to runtime, as everyting else is as usual
there is nothing in the proxysql.log until the mysql_connection.cpp:1111 error lines :

2023-06-07 04:30:11 [INFO] Checksum for table mysql_servers is 0x4EAFCC500C615E0D
2023-06-07 04:30:11 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { old: 0x93526D6BF9476B2D, new: 0x4EAFCC500C615E0D }, mysql_replication_hostgroups { old:0x0, new:0x0 }
2023-06-07 04:30:11 [INFO] MySQL_HostGroups_Manager::commit() locked for 3ms

2023-06-07 05:23:18 mysql_connection.cpp:1111:handler(): [ERROR] Failed to mysql_real_connect() on 10:/var/run/mysqld/mysqld.sock:0 , FD (Conn:48 , MyDS:48) , 1045: Access denied for user 'ivan.s_rd'@'localhost' (using password: YES).
2023-06-07 05:23:18 mysql_connection.cpp:1111:handler(): [ERROR] Failed to mysql_real_connect() on 10:/var/run/mysqld/mysqld.sock:0 , FD (Conn:48 , MyDS:48) , 1045: Access denied for user 'ivan.s_rd'@'localhost' (using password: YES).
2023-06-07 05:23:18 mysql_connection.cpp:1111:handler(): [ERROR] Failed to mysql_real_connect() on 10:/var/run/mysqld/mysqld.sock:0 , FD (Conn:48 , MyDS:48) , 1045: Access denied for user 'ivan.s_rd'@'localhost' (using password: YES).
2023-06-07 05:23:18 mysql_connection.cpp:1111:handler(): [ERROR] Failed to mysql_real_connect() on 10:/var/run/mysqld/mysqld.sock:0 , FD (Conn:48 , MyDS:48) , 1045: Access denied for user 'ivan.s_rd'@'localhost' (using password: YES).
2023-06-07 05:23:18 mysql_connection.cpp:1111:handler(): [ERROR] Failed to mysql_real_connect() on 10:/var/run/mysqld/mysqld.sock:0 , FD (Conn:48 , MyDS:48) , 1045: Access denied for user 'ivan.s_rd'@'localhost' (using password: YES).
2023-06-07 05:23:18 mysql_connection.cpp:1111:handler(): [ERROR] Failed to mysql_real_connect() on 10:/var/run/mysqld/mysqld.sock:0 , FD (Conn:48 , MyDS:48) , 1045: Access denied for user 'ivan.s_rd'@'localhost' (using password: YES).
2023-06-07 05:23:18 mysql_connection.cpp:1111:handler(): [ERROR] Failed to mysql_real_connect() on 10:/var/run/mysqld/mysqld.sock:0 , FD (Conn:48 , MyDS:48) , 1045: Access denied for user 'ivan.s_rd'@'localhost' (using password: YES).
2023-06-07 05:23:18 mysql_connection.cpp:1111:handler(): [ERROR] Failed to mysql_real_connect() on 10:/var/run/mysqld/mysqld.sock:0 , FD (Conn:48 , MyDS:48) , 1045: Access denied for user 'ivan.s_rd'@'localhost' (using password: YES).
2023-06-07 05:23:18 mysql_connection.cpp:1111:handler(): [ERROR] Failed to mysql_real_connect() on 10:/var/run/mysqld/mysqld.sock:0 , FD (Conn:48 , MyDS:48) , 1045: Access denied for user 'ivan.s_rd'@'localhost' (using password: YES).
2023-06-07 05:23:18 mysql_connection.cpp:1111:handler(): [ERROR] Failed to mysql_real_connect() on 10:/var/run/mysqld/mysqld.sock:0 , FD (Conn:48 , MyDS:48) , 1045: Access denied for user 'ivan.s_rd'@'localhost' (using password: YES).
2023-06-07 05:23:18 mysql_connection.cpp:1111:handler(): [ERROR] Failed to mysql_real_connect() on 10:/var/run/mysqld/mysqld.sock:0 , FD (Conn:48 , MyDS:48) , 1045: Access denied for user 'ivan.s_rd'@'localhost' (using password: YES).

The above setup (connect to the backend over local socket when SSL is requred for the user connection) works on ProxySQL-2.5.1-90-gbedaa6c

Thank you!

[rahim-kanji]

Hello @seikath,

Thank you for reporting the issue. You are absolutely right that enforcing SSL via UNIX socket on the backend server doesn't make sense, and that's why it was disabled in ProxySQL version 2.5.2.

However, it is important to consider that if SSL is required on the backend, it can cause problems. In light of this, we have decided to reinstate SSL support on Unix socket, and it will be available in the next release of ProxySQL.

Thank you for bringing this to our attention, and we appreciate your feedback.