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.
[x] ProxySQL version
Latest.
[x] 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)
[x] 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.
Currently there is no support for
COM_CHANGE_USERandcaching_sha2_passwordas a consequence of the broader limitation ofAuth Switch Supportforcaching_sha2_password. The default behavior (and the one taken by MySQL as a default) when a client issues aCOM_CHANGE_USERis responding with anAuth Switch Requestto 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.Latest.
Any attempts to perform a
mysql_change_userwhile usingcaching_sha2_passwordshould resultAccess deniederror. A simple TAP test reproducing the issue is attached that also could be use for testing the future fix. Executing the test now gives:change_user_libmysql.cpp.zip
/var/lib/proxysql/proxysql.log)ProxySQL error log during reproduction execution:
Update:
This case has been documented as a current limitation on the official doc.