Add support for `COM_CHANGE_USER` for `caching_sha2_password`

[JavierJF]

• A clear description of the issue

Currently there is no support for COM_CHANGE_USER and caching_sha2_password as a consequence of the broader limitation of Auth Switch Support for caching_sha2_password. The default behavior (and the one taken by MySQL as a default) when a client issues a COM_CHANGE_USER is responding with an Auth Switch Request to the client, and then proceeding with the exchange proper of the selected authentication method (see COM_CHANGE_USER). This is currently unsupported by ProxySQL as stated in the following section of the official documentation.

• ProxySQL version

Latest.

• The steps to reproduce the issue

Any attempts to perform a mysql_change_user while using caching_sha2_password should result Access denied error. A simple TAP test reproducing the issue is attached that also could be use for testing the future fix. Executing the test now gives:

# 2024-08-20 18:33:32.864248  Changing default auth_plugin   plugin=caching_sha2_password
# 2024-08-20 18:33:32.869530  Change user failed   err=ProxySQL Error: Access denied for user 'changeuser1'@'127.0.0.1' (using password: YES)

change_user_libmysql.cpp.zip

• The full ProxySQL error log (default location: /var/lib/proxysql/proxysql.log)

ProxySQL error log during reproduction execution:

2024-08-20 18:33:32 main.cpp:213:main_check_latest_version_thread(): [INFO] Latest ProxySQL version available: 2.6.3-107-gcdfcfdc
2024-08-20 18:33:32 ProxySQL_Admin.cpp:1027:is_admin_command_or_alias(): [INFO] Received LOAD MYSQL USERS TO RUNTIME command
2024-08-20 18:33:32 ProxySQL_Admin.cpp:11559:__refresh_users(): [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0x9AC730F813BFAA2E', with epoch '1724171612'
2024-08-20 18:33:32 ProxySQL_Admin.cpp:1027:is_admin_command_or_alias(): [INFO] Received LOAD MYSQL USERS TO RUNTIME command
2024-08-20 18:33:32 ProxySQL_Admin.cpp:11559:__refresh_users(): [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0x85259E3E3CE5E9F4', with epoch '1724171612'
2024-08-20 18:33:32 ProxySQL_Admin.cpp:1989:admin_handler_command_set(): [INFO] Received SET command for mysql-default_authentication_plugin
2024-08-20 18:33:32 ProxySQL_Admin.cpp:1027:is_admin_command_or_alias(): [INFO] Received LOAD MYSQL VARIABLES TO RUNTIME command
2024-08-20 18:33:32 ProxySQL_Admin.cpp:7578:flush_GENERIC_variables__checksum__database_to_runtime(): [INFO] Computed checksum for 'LOAD MYSQL VARIABLES TO RUNTIME' was '0x67109E106C28DF7B', with epoch '1724171612'
2024-08-20 18:33:32 MySQL_Session.cpp:5781:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE_WrongCredentials(): [ERROR] ProxySQL Error: Access denied for user 'changeuser1'@'127.0.0.1' (using password: YES)

Update:

This case has been documented as a current limitation on the official doc.