Proxysql: ProxySQL-2 cannot connect to PXC using the user with a caching_sha2_password plugin.

[rameshvs02]

ProxySQL-2 cannot connect to PXC using the user with a caching_sha2_password plugin. Is there any additional configuration to support the caching_sha2_password plugin?

Remote connection is working fine

[vagrant@proxysql-local ~]$ mysql -usbtest -ppassw0rd -h192.168.100.10 -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@hostname  |
+-------------+
| node1-local |
+-------------+
[vagrant@proxysql-local ~]$


ProxySQL connection is failing with Access Denied error.

[vagrant@proxysql-local ~]$ mysql -usbtest -ppassw0rd -h127.0.0.1 -P6033  -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000) at line 1: Access denied for user 'sbtest'@'192.168.100.40' (using password: YES)
[vagrant@proxysql-local ~]$


[vagrant@proxysql-local ~]$ proxysql --version
ProxySQL version 2.0.10-27-g5b31997, codename Truls
[vagrant@proxysql-local ~]$


[vagrant@proxysql-local ~]$ mysql -uadmin -padmin -P6032 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
[]..]

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from runtime_mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| sbtest   | *74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3 | 1      | 0       | 10                |                | 0             | 0                      | 0            | 0       | 1        | 10000           |         |
| sbtest   | *74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3 | 1      | 0       | 10                |                | 0             | 0                      | 0            | 1       | 0        | 10000           |         |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
2 rows in set (0.00 sec)

mysql> \q


PXC User:

mysql> select user,host,plugin from mysql.user where user in('sbtest');
+--------+------+-----------------------+
| user   | host | plugin                |
+--------+------+-----------------------+
| sbtest | %    | caching_sha2_password |
+--------+------+-----------------------+
1 row in set (0.00 sec)

mysql>

[nasamitnick]

ProxySQL doesn't support yet caching_sha2_password.
use mysql_native_password
https://github.com/sysown/proxysql/wiki/MySQL-8.0

[rameshvs02]

@nasamitnick The wiki page also says.
"Both notes above are not valid anymore for recent versions of ProxySQL 2.0"
ProxySQL-2 support caching_sha2_password for monitoring user. But the 'Access denied' issue is with application user.

[renecannao]

This is probably easy to fix:

• store the password in mysql_users as clear text (not hashed, as hashes are only for mysql_native, SHA1(SHA1(password)))
• set admin-hash_passwords=false

We will verify that

[alpes214]

@rameshvs02
Lets assume that we have the following configuration initially
Admin configuration:

MySQL [(none)]> select * from global_variables where variable_name like '%hash_passwords%';
+----------------------+----------------+
| variable_name        | variable_value |
+----------------------+----------------+
| admin-hash_passwords | true           |
+----------------------+----------------+
1 row in set (0.01 sec)

Runtime:

MySQL [(none)]> select username, password, default_hostgroup, active from runtime_mysql_users where username='sbtest';
+----------+-------------------------------------------+-------------------+--------+
| username | password                                  | default_hostgroup | active |
+----------+-------------------------------------------+-------------------+--------+
| sbtest   | *2AFD99E79E4AA23DE141540F4179F64FFB3AC521 | 0                 | 1      |
| sbtest   | *2AFD99E79E4AA23DE141540F4179F64FFB3AC521 | 0                 | 1      |
+----------+-------------------------------------------+-------------------+--------+
2 rows in set (0.00 sec)

Memory:

MySQL [(none)]> select username, password, default_hostgroup, active from mysql_users where username='sbtest';
+----------+----------+-------------------+--------+
| username | password | default_hostgroup | active |
+----------+----------+-------------------+--------+
| sbtest   | sbtest   | 0                 | 1      |
+----------+----------+-------------------+--------+
1 row in set (0.00 sec)

Also assume that we have configured user in mysql server:

$ mysql -uroot -px -h127.0.0.1 -P6033 -e "select user, host, plugin from mysql.user where user='sbtest'"
+--------+------+-----------------------+
| user   | host | plugin                |
+--------+------+-----------------------+
| sbtest | %    | caching_sha2_password |
+--------+------+-----------------------+

With this user/password configuration proxysql cannot access mysql.

$ mysql -u sbtest -psbtest -h127.0.0.1 -P6033 -e 'select @@hostname'
ERROR 1045 (28000) at line 1: Access denied for user 'sbtest'@'localhost' (using password: YES)

It is possible to reconfigure proxysql so that user will be able to access mysql server using caching_sha2_password plugin through proxysql. According to the documentation https://github.com/sysown/proxysql/wiki/Password-management

When admin-hash_passwords=true , password are automatically hashed at RUNTIME only when running LOAD MYSQL USERS TO RUNTIME . Passwords in mysql_users tables are yet not automatically hashed.

First, we set admin-hash_password to false

MySQL [(none)]> update global_variables set variable_value='false' where variable_name='admin-hash_passwords';
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> load admin variables to runtime; save admin variables to disk;
Query OK, 0 rows affected (0.00 sec)
Query OK, 35 rows affected (0.12 sec)

Second, we do LOAD MYSL USERS TO RUNTIME to have text passwords in runtime.

MySQL [(none)]> load mysql users to runtime; save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.26 sec)

After executing these commands user can access mysql through proxysql using caching_sha2_password

$ mysql -u sbtest -psbtest -h127.0.0.1 -P6033 -e 'select @@hostname'
+------------+
| @@hostname |
+------------+
| s89830     |
+------------+

[rameshvs02]

@val214 Yes it is working with admin-hash_passwords=false. But we are exposing the password in plain text. It will be great if ProxySQL supports the caching_sha2_password algorithm to access the server.

[utdrmac]

I came here too because the official documentation states: "Both notes above are not valid anymore for recent versions of ProxySQL 2.0" which implies that proxysql 2.0 can handle caching_sha2_password connections, but that is certainly not the case. Can the documentation be updated to reduce confusion?

I kept seeing the following in proxysql2 error logs:

2020-03-24 22:40:05 mysql_connection.cpp:815:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:28 , MyDS:28) , 2059: Plugin sha256_password could not be loaded: lib/mariadb/plugin/sha256_password.so: cannot open shared object file: No such file or directory.

I do not have mariadb installed, so I don't understand why that is visible. Using the above plaintext workaround is good for demonstration purposes but certainly not valid for production use.

[utdrmac]

Can the docs be updated to reflect the current status of MySQL8/caching_sha2?

Both notes above are not valid anymore for recent versions of ProxySQL 2.0

As noted above in this bug report, this is not a valid statement.