MySQL Passwords in ProxySQL

ProxySQL is a protocol aware proxy. Because ProxySQL performs routing based on traffic, when a client connects it cannot yet identify a destination HG, therefore ProxySQL needs to authenticate the client.
For this reason, it needs to have some information related to the password of the user: enough information to allow the authentication.

ProxySQL also needs this information to later establish connections to backends, or issue CHANGE_USER within already established connections.

The 3 layers configuration architecture applies also for users information.
ProxySQL stores users information in table mysql_users:

  • an object MySQL_Authentication() is responsible to store this information at runtime;
  • main.mysql_users is the in-memory database;
  • disk.mysql_users is the on-disk database.

In mysql_users tables, both in-memory and on-disk, the credentials are stored in columns username and password.

Password formats

Password can be stored in 2 formats in mysql_users.password , no matter if in-memory or on-disk:

  • plain text
  • hashed password

Passwords in plain text are simple as that, very easy to read. If the database and config files are kept in a safe location the security concern is limited, yet present. Hashed passwords have the same format of the passwords in MySQL server, as stored into column mysql.user.password.

ProxySQL considers a password starting with * has a hashed password.

Hashed passwords and authentication

In MySQL and in ProxySQL, a hashed password is SHA1(SHA1('clear_password')) . From a hashed password it is not possible to derive a plain text password.
When a client connects to ProxySQL, it is able to authenticate it using the hashed password.
During the first client authentication, ProxySQL can derive a partially hashed password: SHA1('clear_password') . This information is internally stored at runtime and allows ProxySQL to connect to backends.

How to input new passwords

The Admin interface of ProxySQL does not have any PASSWORD() function. This means that:

  • passwords are stored in the format they are inserted, either in plain text or hashed
  • while inputting password in the Admin interface, it is not possible to derive a hashed password from a plain text password (yet you can run SELECT PASSWORD('password') in MySQL server and copy paste the result)

Variable admin-hash_passwords

To facilitate the support of hashed passwords, ProxySQL v1.2.3 introduced a new global boolean variable, admin-hash_passwords, enabled by default.
When admin-hash_passwords=true , passwords are automatically hashed at RUNTIME only when running LOAD MYSQL USERS TO RUNTIME .
Passwords in mysql_users tables are yet not automatically hashed.
Nonetheless, it is easily possible to hash the passwords in mysql_users table, both in-memory and on-disk. It is enough to copy users from RUNTIME, for example running SAVE MYSQL USERS FROM RUNTIME after LOAD MYSQL USERS TO RUNTIME, and then SAVE MYSQL USERS TO DISK (recommended).

Here’s an example:

Admin> SELECT * FROM mysql_users;
Empty set (0.00 sec)

Admin> INSERT INTO mysql_users(username,password) VALUES ('user1','password1'), ('user2','password2');
Query OK, 2 rows affected (0.00 sec)

Admin> SELECT username,password FROM mysql_users;
+----------+-----------+
| username | password  |
+----------+-----------+
| user1    | password1 |
| user2    | password2 |
+----------+-----------+
2 rows in set (0.00 sec)

Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT username,password FROM mysql_users;
+----------+-----------+
| username | password  |
+----------+-----------+
| user1    | password1 |
| user2    | password2 |
+----------+-----------+
2 rows in set (0.00 sec)

At this stage, passwords are hashed at runtime, but still not hashed on mysql_users. To hash them also on mysql_users :

Admin> SAVE MYSQL USERS FROM RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT username,password FROM mysql_users;
+----------+-------------------------------------------+
| username | password                                  |
+----------+-------------------------------------------+
| user1    | *668425423DB5193AF921380129F465A6425216D0 |
| user2    | *DC52755F3C09F5923046BD42AFA76BD1D80DF2E9 |
+----------+-------------------------------------------+
2 rows in set (0.00 sec)

The hashed passwords can now be saved to disk running SAVE MYSQL USERS TO DISK .

Note: admin-hash_passwords is an admin- variable, not a mysql- variable. This is because it affects the behaviour of Admin.
This detail is important because to apply changes in admin-hash_passwords you need to run LOAD ADMIN VARIABLES TO RUNTIME and not LOAD MYSQL VARIABLES TO RUNTIME .