In this blog post, we will see how to persist the password inside the ProxySQL mysql_users table in hashed format only. Also, even if someone stored the password in cleartext, we see how to change those into the hashed format easily.
Here we are just highlighting one of the scenarios during work on the client environment where we noticed that the ProxySQL mysql_users table had more than 100 user entries, but some of them were available/inserted into the clear text password, whereas some were inserted properly into hashed entries.
Before just explaining those simple commands that were used to fix those clear text entries into the hashed entry quickly, let’s see some more information about the ProxySQL mysql_users table and the password formats.
Password formats inside ProxySQL
ProxySQL is capable of storing passwords in two different formats within the mysql_users.password field, whether in-memory or on-disk. These formats include plain text and hashed passwords.
Plain text passwords are extremely vulnerable to unauthorized access, as anyone with access to the database or configuration files can easily read them. While storing these files in a secure location can mitigate some security concerns, there is still a risk of data breaches. Hashed passwords, on the other hand, are stored in the same format as passwords in the MySQL server’s “mysql.user.password” (before MySQL 8.0 version) or “mysql.user.authentication_string” column (since MySQL 8.0 version using the mysql_native_password plugin), providing an added layer of security.
In ProxySQL, any password that begins with an asterisk (*) is considered to be a hashed password.
The Admin interface of ProxySQL lacks a PASSWORD() function. Therefore, any passwords stored within ProxySQL are preserved in the format in which they were originally inserted. This format may either be plain text or a hashed value.
Note: In general, ProxySQL doesn’t support the user created using the caching_sha2_password plugin password, once the same mysql.user.authentication_string is stored inside the mysql_users.password column. Still, there is a workaround for using those user accounts that are created inside the database using the caching_sha2_password plugin by inserting the clear text password entries inside the ProxySQL mysql_users.password column, but that is not recommended as per security best practices to keep clear text password entries inside the ProxySQL. Hence, we could say, ProxySQL and MySQL communication better support users that are created with the mysql_native_password plugin inside the database.
For more details, please check this blog post ProxySQL Support for MySQL caching_sha2_password and the official ProxySQL documentation Information about MySQL 8.0 – ProxySQL .
So, to explain this scenario, here we created four different test DB user accounts inside the database with the mysql_native_password plugin.
From a database node:
| Username | Password (In Clear Text) |
| test1 | test1 |
| test2 | test2 |
| test3 | test3 |
| test4 | test4 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql [localhost:8028] {msandbox} ((none)) > select user,host,authentication_string,plugin from mysql.user where user like 'test%'G *************************** 1. row *************************** user: test1 host: localhost authentication_string: *06C0BF5B64ECE2F648B5F048A71903906BA08E5C plugin: mysql_native_password *************************** 2. row *************************** user: test2 host: localhost authentication_string: *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E plugin: mysql_native_password *************************** 3. row *************************** user: test3 host: localhost authentication_string: *F357E78CABAD76FD3F1018EF85D78499B6ACC431 plugin: mysql_native_password *************************** 4. row *************************** user: test4 host: localhost authentication_string: *D159BBDA31273BE3F4F00715B4A439925C6A0F2D plugin: mysql_native_password 4 rows in set (0.00 sec) |
From ProxySQL:
Here we will insert the user accounts into the mysql_users tables in mixed clear text format as well as in hash format.
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 27 28 29 30 31 32 33 34 | ProxySQL_Admin> INSERT INTO mysql_users(username,password) VALUES ('test1','test1'), ('test2','*7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E'),('test3','test3'), ('test4','*D159BBDA31273BE3F4F00715B4A439925C6A0F2D'); Query OK, 4 rows affected (0.00 sec) ProxySQL_Admin> select distinct username,password from mysql_users where username like 'test%'; +----------+-------------------------------------------+ | username | password | +----------+-------------------------------------------+ | test1 | test1 | | test2 | *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E | | test3 | test3 | | test4 | *D159BBDA31273BE3F4F00715B4A439925C6A0F2D | +----------+-------------------------------------------+ 4 rows in set (0.00 sec) ProxySQL_Admin> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec) ProxySQL_Admin> SAVE MYSQL USERS TO DISK; Query OK, 0 rows affected (0.01 sec) ProxySQL_Admin> select distinct username,password from mysql_users where username like 'test%'; +----------+-------------------------------------------+ | username | password | +----------+-------------------------------------------+ | test1 | test1 | | test2 | *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E | | test3 | test3 | | test4 | *D159BBDA31273BE3F4F00715B4A439925C6A0F2D | +----------+-------------------------------------------+ 4 rows in set (0.00 sec) |
Below are some other ProxySQL configurations done to verify the ProxySQL’s mysql_users table password is working fine to establish a connection with the MySQL database.
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 27 | ProxySQL_Admin> select hostgroup_id,hostname,port,status from mysql_servers; +--------------+-----------+------+--------+ | hostgroup_id | hostname | port | status | +--------------+-----------+------+--------+ | 10 | localhost | 8028 | ONLINE | +--------------+-----------+------+--------+ 1 row in set (0.00 sec) ProxySQL_Admin> select rule_id,active,proxy_port,match_digest,destination_hostgroup,retries,apply from mysql_query_rulesG *************************** 1. row *************************** rule_id: 1048 active: 1 proxy_port: 6033 match_digest: ^SELECT.*FOR UPDATE destination_hostgroup: 10 retries: 3 apply: 1 *************************** 2. row *************************** rule_id: 1050 active: 1 proxy_port: 6033 match_digest: ^SELECT.*$ destination_hostgroup: 10 retries: 3 apply: 1 2 rows in set (0.00 sec) |
Let’s check the database connectivity via ProxySQL using these DB user accounts.
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 | for i in {1..4}; do mysql -h 127.0.0.1 -utest$i -ptest$i -P6033 -e"select current_user(),version();";done mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+-----------+ | current_user() | version() | +-----------------+-----------+ | test1@localhost | 8.0.28 | +-----------------+-----------+ mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+-----------+ | current_user() | version() | +-----------------+-----------+ | test2@localhost | 8.0.28 | +-----------------+-----------+ mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+-----------+ | current_user() | version() | +-----------------+-----------+ | test3@localhost | 8.0.28 | +-----------------+-----------+ mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+-----------+ | current_user() | version() | +-----------------+-----------+ | test4@localhost | 8.0.28 | +-----------------+-----------+ |
Here above, the main problem is that our mysql_users tables have some plain text passwords visible for some user accounts (test1 & test3), which we don’t want to keep in clear/plain text password format. Instead, all password entries should be available in the hashed password format.
There is one way to fix this problem: drop those clear text password entries for user accounts, get the password hash for those user passwords generated from the MySQL database prompt using the PASSWORD() function, and later insert those actual hashed entries inside the mysql_users table to fix the issue.
But as earlier mentioned, if our mysql_users table had a lot of (>100) entries, fixing those passwords manually can be a tedious job.
Note: Here it is assumed we are not using the percona-scheduler-admin client, which has the feature to sync your user accounts directly with the database nodes in the ProxySQL mysql_users table.
So for this case, let’s see the next section, where we will understand how the admin-hash_passwords variable will help us to solve this problem and persist only hashed password entries inside the ProxySQL’s mysql_users table.
ProxySQL’s admin-hash_passwords variable
ProxySQL version 1.2.3 has included a new global boolean variable called admin-hash_passwords, which is enabled by default to support hashed passwords. If admin-hash_passwords=true, passwords will be automatically hashed during runtime when executing the LOAD MYSQL USERS TO RUNTIME command. However, passwords stored in the mysql_users tables will not be automatically hashed.
Nevertheless, it is possible to hash these passwords both in-memory and on-disk by copying users from RUNTIME using commands such as “SAVE MYSQL USERS FROM RUNTIME” after executing LOAD MYSQL USERS TO RUNTIME and then saving the updated information using SAVE MYSQL USERS TO DISK.
Let’s persist the hashed password inside 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 25 26 27 28 29 30 31 32 33 34 35 | ProxySQL_Admin> select @@admin-hash_passwords; +------------------------+ | @@admin-hash_passwords | +------------------------+ | true | +------------------------+ 1 row in set (0.00 sec) ProxySQL_Admin> select distinct username,password from mysql_users where username like 'test%'; +----------+-------------------------------------------+ | username | password | +----------+-------------------------------------------+ | test1 | test1 | | test2 | *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E | | test3 | test3 | | test4 | *D159BBDA31273BE3F4F00715B4A439925C6A0F2D | +----------+-------------------------------------------+ 4 rows in set (0.00 sec) ProxySQL_Admin> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.01 sec) ProxySQL_Admin> select distinct username,password from mysql_users where username like 'test%'; +----------+-------------------------------------------+ | username | password | +----------+-------------------------------------------+ | test1 | test1 | | test2 | *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E | | test3 | test3 | | test4 | *D159BBDA31273BE3F4F00715B4A439925C6A0F2D | +----------+-------------------------------------------+ 4 rows in set (0.00 sec) |
Currently, passwords are hashed at RUNTIME, but they are not hashed on the mysql_users table. To hash them inside the mysql_users table as well, we need to run the “SAVE MYSQL USERS FROM RUNTIME” command.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | ProxySQL_Admin> SAVE MYSQL USERS FROM RUNTIME; Query OK, 0 rows affected (0.00 sec) ProxySQL_Admin> select distinct username,password from mysql_users where username like 'test%'; +----------+-------------------------------------------+ | username | password | +----------+-------------------------------------------+ | test1 | *06C0BF5B64ECE2F648B5F048A71903906BA08E5C | | test2 | *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E | | test3 | *F357E78CABAD76FD3F1018EF85D78499B6ACC431 | | test4 | *D159BBDA31273BE3F4F00715B4A439925C6A0F2D | +----------+-------------------------------------------+ 4 rows in set (0.00 sec) |
The command “SAVE MYSQL USERS TO DISK“ can now be used to store/persist the hashed passwords on the disk.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | ProxySQL_Admin> SAVE MYSQL USERS TO DISK; Query OK, 0 rows affected (0.01 sec) ProxySQL_Admin> select distinct username,password from mysql_users where username like 'test%'; +----------+-------------------------------------------+ | username | password | +----------+-------------------------------------------+ | test1 | *06C0BF5B64ECE2F648B5F048A71903906BA08E5C | | test2 | *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E | | test3 | *F357E78CABAD76FD3F1018EF85D78499B6ACC431 | | test4 | *D159BBDA31273BE3F4F00715B4A439925C6A0F2D | +----------+-------------------------------------------+ 4 rows in set (0.00 sec) |
Let’s verify the database connectivity via ProxySQL using these DB user accounts.
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 | for i in {1..4}; do mysql -h 127.0.0.1 -utest$i -ptest$i -P6033 -e"select current_user(),version();";done mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+-----------+ | current_user() | version() | +-----------------+-----------+ | test1@localhost | 8.0.28 | +-----------------+-----------+ mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+-----------+ | current_user() | version() | +-----------------+-----------+ | test2@localhost | 8.0.28 | +-----------------+-----------+ mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+-----------+ | current_user() | version() | +-----------------+-----------+ | test3@localhost | 8.0.28 | +-----------------+-----------+ mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+-----------+ | current_user() | version() | +-----------------+-----------+ | test4@localhost | 8.0.28 | +-----------------+-----------+ |
Conclusion
Using the admin-hash_passwords feature can be extremely beneficial when there are mixed-format password entries in the mysql_users table. By saving the RUNTIME entries, which consist of hashed passwords, to disk and persisting only these entries in the mysql_users table of ProxySQL, we can easily simplify the management of hashed password entries. Furthermore, to ensure that only hashed password entries are stored within ProxySQL, it is imperative to create database user accounts using the mysql_native_password plugin.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
