ProxySQL Error: Access denied for user from client but not in ProxySQL itself

[di-rect]

I'm running into the following issue with connecting to my XtraDB server(s):

2024-06-27 20:06:05 MySQL_Session.cpp:5779:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE_WrongCredentials(): [ERROR] ProxySQL Error: Access denied for user 'mysql-user'@'10.0.15.36' (using password: YES)

My XtraDB servers have a user that is'mysql-user'@'%' and I can connect to the MySQL instances from the ProxySQL server that has all users synced using ./proxysql-admin --syncusers with the right password (sha2 but it happens also with MySQL standard password)

Using mysql -umysql-client -p -P33062 and show databases show me the databases that the user has rights on and I can query tables.

What can possible be wrong here a ProxySQL seems to have issues with my clients and doesn't log much more usable.

[renecannao]

Hi.

I'm running into the following issue with connecting to my XtraDB server(s):

This statement is completely incorrect. You are getting an error when connecting to ProxySQL. "ProxySQL Error" means that the error is originated by ProxySQL, not from the backend.

"handler_status_CONNECTINGCLIENTSTATE_SERVER_HANDSHAKE_WrongCredentials()" also suggest what is the status: connecting client.

In short, the client cannot connect to ProxySQL. If the client is using the correct credentials, then ProxySQL has the wrong credentials.

I would speculate you are hitting a bug with proxysql-admin

[di-rect]

This statement is completely incorrect. You are getting an error when connecting to ProxySQL. "ProxySQL Error" means that the error is originated by ProxySQL, not from the backend.

It's what most peope understand, otherwise I (probably) get the question: can your ProxySQL server connect to your backend ?

So no offence but you got the point.

In short, the client cannot connect to ProxySQL. If the client is using the correct credentials, then ProxySQL has the wrong credentials.

It seems so.

I would speculate you are hitting a bug with proxysql-admin

Would you want me to debug that ? And do you have requirements there ?

The standard MySQL password hashes I started with are exactly the same; so password is/should be OK. The same goes for sha2. Hence I can connect from ProxySQL local port so ProxySQL and query my backend.

[renecannao]

So no offence but you got the point.

I think I get the point, no offense at all. This is why we always ask for full error log, step by step, detailed configuration, and reproducible test case whenever possible. Like in this case, a user may assume the error is of a certain nature, while our experience combined with error log, step-by-step, etc can understand the true nature of the issue.

Would you want me to debug that ? And do you have requirements there ?

I would start from reading the following links:

• https://proxysql.com/documentation/password-management/
• https://proxysql.com/documentation/authentication-methods/ Please pay special attention to password formats, and requirements (like SSL).

Next, It is also important to understand what ProxySQL version you are running. Different versions may have different capabilities (support for caching_sha2_password was introduced only in 2.6.0 ).

Finally, I would check if proxysql-admin is able to sync users correctly. caching_sha2_password are strings that have non-ascii characters, potentially making "copy/paste" erroneous. You could use UNHEX() to verify that mysql_users.password in ProxySQL is the same of mysql.user.authentication_string in MySQL .

To further verify if proxysql-admin , you can also set the password in mysql_users.password manually , using:

• clear text password
• mysql_native_password format : use MYSQL_NATIVE_PASSWORD() available in v2.6.2 , see https://proxysql.com/documentation/password-management/
• caching_sha2_password format : use CACHING_SHA2_PASSWORD() available in v2.6.2 , see https://proxysql.com/documentation/password-management/

[di-rect]

Thank you for the detailed explanation!

I'm stuck there as what I check seems to be right so maybe you can help out further as I re-did and re-did all checks and documents.

For now what is most relevant to start from:

- MySQL:

| %    | gitea-username | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          | 0x                     | 0x                       | 0x                         |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$r1)2ppU'M-}a4Xco523T03oZt3lD/WamZbWD1sVleQF4fWpE32htmA/2 | N                | 2024-06-27 15:52:00   |              NULL | N              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
+------+-------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
1 row in set (0.00 sec)

- ProxySQL

+-------------------+------------------------------------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| gitea-username | $A$005$r1)2ppU'M-}a4Xco523T03oZt3lD/WamZbWD1sVleQF4fWpE32htmA/2 | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            |         |
+-------------------+------------------------------------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+

No worries, there is only one user in ProxySQL like this; also in MySQL because of the wildcard.

This looks good, doesn't it ? But what's next ?

[renecannao]

Hi @di-rect

I don't have enough information to say if this looks good, there are high probability that it is not. The password seems to be 64 characters long, but in reality a caching_sha2_password is 70 character long.

From my previous reply:

caching_sha2_password are strings that have non-ascii characters, potentially making "copy/paste" erroneous. You could use UNHEX() to verify that mysql_users.password in ProxySQL is the same of mysql.user.authentication_string in MySQL .

Because of non-ascii characters, the two strings may "look" the same, but they probably are not.

Export and import is described in https://proxysql.com/documentation/password-management/

[di-rect]

Hi @renecannao

When I compare the hashes with the salt as described in the docs; they are the same; so that is OK.

[renecannao]

Is mysql-default_authentication_plugin set to caching_sha2_password ?

[di-rect]

Is mysql-default_authentication_plugin set to caching_sha2_password ?

It should be using v2.6.3 I thought, checking out!

Edit:

Should be fine:

ProxySQL Admin>  SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-default%';
+-------------------------------------+-----------------------+
| variable_name                       | variable_value        |
+-------------------------------------+-----------------------+
| mysql-default_charset               | utf8                  |
| mysql-default_collation_connection  | utf8_general_ci       |
| mysql-default_max_latency_ms        | 1000                  |
| mysql-default_query_delay           | 0                     |
| mysql-default_query_timeout         | 36000000              |
| mysql-default_schema                | information_schema    |
| mysql-default_authentication_plugin | caching_sha2_password |
| mysql-default_reconnect             | true                  |
| mysql-default_session_track_gtids   | OFF                   |
+-------------------------------------+-----------------------+
9 rows in set (0.009 sec)