Every day we use dozens if not hundreds of applications connecting to some kind of data repository. This simple step is normally executed over the network and, given so, it is subject to possible sniffing with all the possible related consequences.
Given that, it is normally better to protect your connection using data encryption like SSL, or at the minimum, make the information you pass to connect less easy to be intercepted.
At the same time, it is a best practice to not store connection credentials in clear text, not even inside a table in your database. Doing that is the equivalent of writing your password on a sticky note on your desk. Not a good idea.
Instead, the main options are either transforming the passwords to be less identifiable via hashing or storing the information in an external centralized vault.
In MySQL, the passwords are transformed to not be clear text, and several different plugins are used to authenticate the user. From version 8, MySQL uses caching_sha2_password as the default authentication plugin. The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics of caching_sha2_password, it is the preferred authentication plugin as of MySQL 8.0 and is also the default authentication plugin rather than mysql_native_password.
In this regard, recently I got the same question again “Can we use ProxySQL with MySQL 8 authorization mechanism?”, and I decided it was time to write this short blog post.
The short answer is “Yes you can”, however, do not expect to have full caching_sha2_password support.
This is because ProxySQL does not fully support the caching_sha2_password mechanism internally and, given that, a “trick” must be used.
So, what should we do when using MySQL 8 and ProxySQL?
In the text below we will see what can be done to continue to use ProxySQL with MySQL and Percona Server for MySQL 8.
Note that I have used the Percona proxysql_admin tool to manage the users except in the last case.
It is a nice tool that helps you to manage ProxySQL and in regards to a user, it also manages and synchronizes users from your Percona Server for MySQL or MySQL instance.
In the following examples:
Proxysql is on 192.168.4.191
User name/password is msandbox/msandbox
Using hashing
By default, MySQL comes with caching_sha2_password and if I create the user name msandbox I will have:
1 2 3 4 5 6 | DC1-1(root@localhost) [(none)]>select user,host, authentication_string,plugin from mysql.user order by 1,2; +----------------------------+--------------------+------------------------------------------------------------------------+-----------------------+ | user | host | authentication_string | plugin | +----------------------------+--------------------+------------------------------------------------------------------------+-----------------------+ | msandbox | % | $A$005$Z[z@l'O%[Q5t^ EKJDgxjWXJjDpDEUv91oL7Hoh/0NydTeCzpV.aI06C9. | caching_sha2_password | <---- this user +----------------------------+--------------------+------------------------------------------------------------------------+-----------------------+ |
Then I use percona_scheduler_admin to sync the users:
1 2 3 4 5 6 7 8 9 10 11 12 13 | ./percona-scheduler-admin --config-file=config.toml --syncusers Syncing user accounts from PXC(192.168.4.205:3306) to ProxySQL Removing existing user from ProxySQL: msandbox Adding user to ProxySQL: msandbox Synced PXC users to the ProxySQL database! 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 | +------------+------------------------------------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+-----------------------------+ | msandbox | $A$005$Z[z@l'O%[Q5t^ EKJDgxjWXJjDpDEUv91oL7Hoh/0NydTeCzpV.aI06C9 | 1 | 0 | 100 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | | +------------+------------------------------------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+-----------------------------+ |
And set the query rules:
1 2 3 4 | insert into mysql_query_rules (rule_id,proxy_port,username,destination_hostgroup,active,retries,match_digest,apply) values(1048,6033,'msandbox',100,1,3,'^SELECT.*FOR UPDATE',1); insert into mysql_query_rules (rule_id,proxy_port,username,destination_hostgroup,active,retries,match_digest,apply) values(1050,6033,'msandbox',101,1,3,'^SELECT.*$',1); load mysql query rules to run;save mysql query rules to disk; |
Now I try to connect, passing by ProxySQL:
1 2 | # mysql -h 192.168.4.191 -P6033 -umsandbox -pmsandbox ERROR 1045 (28000): ProxySQL Error: Access denied for user 'msandbox'@'192.168.4.191' (using password: YES) |
My account will fail to connect given the failed authentication.
To fix this I need to drop the user and recreate it with a different authentication plugin in my MySQL server:
1 2 3 4 5 6 7 8 9 10 | drop user msandbox@'%'; create user 'msandbox'@'%' identified with mysql_native_password BY 'msandbox'; grant select on *.* to 'msandbox'@'%'; select user,host, authentication_string,plugin from mysql.user order by 1,2; +----------+--------------------+-------------------------------------------+-----------------------+ | user | host | authentication_string | plugin | +----------+--------------------+-------------------------------------------+-----------------------+ | msandbox | % | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | mysql_native_password | +----------+--------------------+-------------------------------------------+-----------------------+ |
At this point, I can re-run:
1 | ./percona-scheduler-admin --config-file=config.toml --syncusers |
And if I try to connect again:
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 | # mysql -h 192.168.4.191 -P6033 -umsandbox -pmsandbox 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 6708563 Server version: 8.0.28 (ProxySQL). <---------------------------- Connecting to proxysql Copyright (c) 2000, 2022, 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 global variables like 'version%'; +-------------------------+------------------------------------------------------------------------------------+ | Variable_name | Value | +-------------------------+------------------------------------------------------------------------------------+ | version | 8.0.25-15.1 <--- Percona/MySQL version | | version_comment | Percona XtraDB Cluster binary (GPL) 8.0.25, Revision 8638bb0, WSREP version 26.4.3 | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_compile_zlib | 1.2.11 | | version_suffix | .1 | +-------------------------+------------------------------------------------------------------------------------+ 6 rows in set (0.02 sec) |
This is the only way to keep the password hashed in MySQL and ProxySQL.
Not using hashing
What if you cannot use mysql_native_password for the password in your MySQL server?
There is a way to still connect, however, I do not recommend it as it is highly insecure, but for completeness, I am going to illustrate it.
First of all, disable password hashing in Proxysql:
1 | update global_variables set Variable_Value='false' where Variable_name='admin-hash_passwords'; |
At this point, instead, sync the user you can locally create the user like:
1 2 3 4 5 6 7 8 | insert into mysql_users (username,password,active,default_hostgroup,default_schema,transaction_persistent,comment) values ('msandbox','msandbox',1,100,'mysql',1,'generic test for security'); mysql> select * from runtime_mysql_users where username ='msandbox'; +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------------------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------------------------+ | msandbox | msandbox | 1 | 0 | 100 | mysql | 0 | 1 | 0 | 1 | 0 | 10000 | | generic test for security | | msandbox | msandbox | 1 | 0 | 100 | mysql | 0 | 1 | 0 | 0 | 1 | 10000 | | generic test for security | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------------------------+ |
As you can see, doing that will prevent the password from being hashed, and instead, it will be clear text.
At this point, you will be able to connect to MySQL 8 using the caching_sha2_password, but the password is visible in ProxySQL.
Let me repeat, I DO NOT recommend using it this way, because for me it is highly insecure.
Conclusion
While it is still possible to configure your user in MySQL to connect using ProxySQL, it is obvious that we have a gap in the way ProxySQL supports security.
The hope is that this gap will be filled soon by the ProxySQL development team. Also if you are looking at the past issues, this seems pending for years now.
References
https://proxysql.com/documentation/mysql-8-0/
https://github.com/sysown/proxysql/issues/2580
Upgrade your Libraries: Authentication Plugin ‘caching_sha2_password’ Cannot be Loaded
