ProxySQL recently released version 2.6.0, and going through the release notes, I focused on the following:
Added support for caching_sha2_password!
This is great news for the community! The caching_sha2_password authentication method for frontend connections is now available. This has been a long-awaited feature …
Why?
Because in MySQL 8, caching_sha2_password has been the default authentication method. Starting from MySQL 8.0.34, mysql_native_password is marked as deprecated. It still exists in MySQL 8.2 but could easily be removed at any time.
Since MySQL 5.7 has gone End Of Life, many users have already upgraded to MySQL 8. But for those using ProxySQL, ProxySQL’s lack of support for frontend user’s authentication using caching_sha2_password had been a major concern/blocker.
For some of them, this is a concern because mysql_native_password is deprecated, so they can’t rely on this anymore.
For some others, this is a concern for security reasons, as the only workaround until recently was to either keep using mysql_native_password or keep passwords unencrypted in ProxySQL.
There have been many discussions around that:
- https://github.com/sysown/proxysql/issues/2580
- https://github.com/sysown/proxysql/issues/4418
- https://github.com/sysown/proxysql/issues/2229
- https://github.com/sysown/proxysql/issues/4262
So it’s finally great to see that the new release contains caching_sha2_password support for frontend connections.
I was happy to set up a testing environment and try this out. For the purposes of this blog post, I’ve created a single async replication running Percona Server for MySQL 8. I won’t focus on the deployment details as there are many different ways to create such a replication topology.
As a next step, I installed ProxySQL 2.6.0 on an Ubuntu 22.04.4 host.
1 2 | root@ip-172-31-80-183:~# wget -q https://github.com/sysown/proxysql/releases/download/v2.6.0/proxysql_2.6.0-ubuntu20_amd64.deb root@ip-172-31-80-183:~# |
1 2 3 4 5 6 7 | root@ip-172-31-80-183:~# dpkg -i proxysql_2.6.0-ubuntu20_amd64.deb Selecting previously unselected package proxysql. (Reading database ... 65273 files and directories currently installed.) Preparing to unpack proxysql_2.6.0-ubuntu20_amd64.deb ... Unpacking proxysql (2.6.0) ... Setting up proxysql (2.6.0) ... Created symlink /etc/systemd/system/multi-user.target.wants/proxysql.service → /lib/systemd/system/proxysql.service. |
I’ve logged in to ProxySQL’s admin interface and switched mysql-default_authentication_plugin to caching_sha2_password
1 2 3 4 5 | mysql> set mysql-default_authentication_plugin = 'caching_sha2_password'; Query OK, 1 row affected (0.00 sec) mysql> load mysql variables to runtime; save mysql variables to disk; Query OK, 0 rows affected (0.00 sec) |
At the end, the tricky part of migrating the password hashes from MySQL to ProxySQL took place using this:
* HEX() makes copying and pasting easier than dealing with special characters
In MySQL
1 2 3 4 5 6 7 | source [localhost:22435] {msandbox} ((none)) > SELECT HEX(authentication_string) FROM mysql.user WHERE user='percona'; +----------------------------------------------------------------------------------------------------------------------------------------------+ | HEX(authentication_string) | +----------------------------------------------------------------------------------------------------------------------------------------------+ | 244124303035247746295C166A545D5B77455B4B2F75627D222E6A343357446F724C44656D2F3642337675354E5A506E413145343076616B2F4B635268397A616738564B702F | +----------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) |
In ProxySQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | mysql> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('percona', '', 10); Query OK, 1 row affected (0.00 sec) mysql> UPDATE mysql_users SET password=UNHEX('244124303035247746295C166A545D5B77455B4B2F75627D222E6A343357446F724C44656D2F3642337675354E5A506E413145343076616B2F4B635268397A616738564B702F') WHERE username='percona'; Query OK, 1 row affected (0.00 sec) mysql> select * from mysql_users; +----------+------------------------------------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment | +----------+------------------------------------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+ | percona | $A$005$wF)jT][wE[K/ub}".j43WDorLDem/6B3vu5NZPnA1E40vak/KcRh9zag8VKp/ | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | | +----------+------------------------------------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+ 1 row in set (0.00 sec) mysql> load mysql users to runtime; save mysql users to disk; Query OK, 0 rows affected (0.00 sec) mysql> select username,password from runtime_mysql_users; +----------+------------------------------------------------------------------------+ | username | password | +----------+------------------------------------------------------------------------+ | percona | $A$005$wF)jT][wE[K/ub}".j43WDorLDem/6B3vu5NZPnA1E40vak/KcRh9zag8VKp/ | | percona | $A$005$wF)jT][wE[K/ub}".j43WDorLDem/6B3vu5NZPnA1E40vak/KcRh9zag8VKp/ | +----------+------------------------------------------------------------------------+ |
I was finally able to connect to ProxySQL frontend:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | root@ip-172-31-80-183:~# mysql -u percona -pp3rc0naPr0xy -h 127.0.0.1 -P 6033 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.5.30 (ProxySQL) Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.04 sec) mysql> exit |
Please note that there are some limitations or unsupported scenarios that you should be aware of.
One example involves the Auth Switch functionality. When a client requests authentication with a method different than the one specified by mysql-default_authentication_plugin , authentication will fail. Clients should normally identify the required authentication method during the initial handshake packet.
Scenario one
mysql-default_authentication_pluginand hashed password inmysql_usersare bothcaching_sha2_password- Client requested authentication using something else other than
caching_sha2_password
In the scenario above, ProxySQL will be required to perform an Auth Switch and later use caching_sha2_password authentication. This is currently unsupported.
Scenario two
- User password is hashed using
caching_sha2_passwordand client requested authentication usingcaching_sha2_password mysql-default_authentication_pluginis set to something different thancaching_sha2_password
In this scenario, ProxySQL authentication will fail as an Auth Switch will be performed but ProxySQL has no access to the clear text password.
Further details can be found here.
Introducing support for caching_sha2_password authentication is a major change. There are many variants of client software and libraries, so I’d strongly recommend heavily testing that feature before pushing any change to your production environment.
