PXC 8.0.36-28-1 with caching_sha2_password and different hash

[shigaev.s]

There is a cluster 8.0.36-28.1 of 3 nodes.
By default, the caching_sha2_password plugin is there.

The problem is that when creating a new user, each node has its own password hash.
What it looks like:

1. create a user on 1 node:
   CREATE USER 'sha2user'@'%' IDENTIFIED BY 'qwerty';
2. check the hash on each node:
   1 node:

mysql> select user,host,plugin,authentication_string from mysql.user where user = 'sha2user';
+----------+------+-----------------------+------------------------------------------------------------------------+
| user     | host | plugin                | authentication_string                                                  |
+----------+------+-----------------------+------------------------------------------------------------------------+
| sha2user | %    | caching_sha2_password | $A$005$

Q~1>{a!s%riRZl?"pjsNMud0lsc8AMRfcuyLoA93GuBUun.8gLcS5qi015C |
+----------+------+-----------------------+------------------------------------------------------------------------+
1 row in set (0,00 sec)

2 node:

mysql> select user,host,plugin,authentication_string from mysql.user where user = 'sha2user';
+----------+------+-----------------------+------------------------------------------------------------------------+
| user     | host | plugin                | authentication_string                                                  |
+----------+------+-----------------------+------------------------------------------------------------------------+
| sha2user | %    | caching_sha2_password | $A$005$d3W\-zx▒Y!H)mmf90LtOdN1mA0gLiFVt5NQg/pRnP7g74i64m0WdXd/ |
+----------+------+-----------------------+------------------------------------------------------------------------+
1 row in set (0,00 sec)

3 node:

mysql> select user,host,plugin,authentication_string from mysql.user where user = 'sha2user';
+----------+------+-----------------------+------------------------------------------------------------------------+
| user     | host | plugin                | authentication_string                                                  |
+----------+------+-----------------------+------------------------------------------------------------------------+
| sha2user | %    | caching_sha2_password | $A$005$T1Cyt"Z0^
                                                            !x+BlrMLoI2txdrMKPgI7mNJ1w15qAAzSDaLq0wlgAsbe7 |
+----------+------+-----------------------+------------------------------------------------------------------------+
1 row in set (0,00 sec)

3. caching_sha variables:

mysql> show variables like 'caching_sha2%';
+----------------------------------------------+-----------------+
| Variable_name                                | Value           |
+----------------------------------------------+-----------------+
| caching_sha2_password_auto_generate_rsa_keys | ON              |
| caching_sha2_password_digest_rounds          | 5000            |
| caching_sha2_password_private_key_path       | private_key.pem |
| caching_sha2_password_public_key_path        | public_key.pem  |
+----------------------------------------------+-----------------+
4 rows in set (0,01 sec)

The PCA key is different:
1 node:

*************************** 1. row ***************************
Variable_name: Caching_sha2_password_rsa_public_key
        Value: -----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAx4OmmTwJjTonPNwTn81h
Qx21eO0LtqPOoXXvEfLF1V1V9TfTNdhH97ls/nI1oBTm7srG1vwC0gThII9sgIdG
DGKD7ECReTUecPe33NOtKAZ13w+82asWhVWFlOSISjMsXEmRPO0jgfS4VG3EIl6A
eChmDaD/ktFvz9yQsmEJHs2YgwOOHbEIIvy+JDoZKCB3awUvV0JvFpXJcTISUsBD
cj+UZM3OQ3ctLrMgyYZeuDlKuf08TrtULxmW66Dgslb0B8i5Wcbl6qhRlQwWdQFS
abOA5G3gaSblKzZuUVU/AmNSzh9T9UQyFqV+iGf89QMn/aTsIV6IoFbYCsh5IrCZ
LwIDAQAB
-----END PUBLIC KEY-----

2 node:

mysql> SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G
*************************** 1. row ***************************
Variable_name: Caching_sha2_password_rsa_public_key
        Value: -----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA5jjtmoRCSOJTvhY4Gt5q
+EpUdD4+LhXpTKkQo5YdjnXMEnw9SRgIROGUEfIFiSd/kY+RhfL2w1k/2wB6LkmD
5EIIkKM4hdJ3mzxU2xbT1TAAjsfDCJOZdl+lR5MUkO7cRQOQWf7s8QtLOfuzwovs
4Ag4h2jzBEGfvSt3clBekd0c39F922/rFJZuxCihOFlI3yYtzBwXUY7yGvCDetUg
AY1l2zR1bwOH9RWL2hlHHDRE52xMrAnITrcHdGwdjwcGpRnsP9EpI1tLdJYc91Eb
7jm4STQfxsj7c5PTCUjyuPsDDjHxLxSiP3Z3YC6XiCfNtxcBG7NGHr+K7W025jHf
JQIDAQAB
-----END PUBLIC KEY-----

3 node:

mysql> SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G
*************************** 1. row ***************************
Variable_name: Caching_sha2_password_rsa_public_key
        Value: -----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAyGgKBQZNAvG/cVr6F/LK
olowOZbw1BkGfHL6QWWxPVIPOjLRkIhpS2wfPTy1AzJ1m/qFnAQaN4ojzjr4TojA
DGBVrr39JhkkCIxp+YwfJ29p8+R98kJJazCd1KG+VhHaBXeS0JvrsAffsSfT39NB
y229E0Eb8z0PRrWF6PsjaGc9Iev9LxA8OxvXBBL54IM3XXymgYgNrgWXBofK4SpU
6b8vfCxasuWoDeMnavOdUMM4tbmspO1HrTw8jIZFOd3UBICd/nLwnEy0sHbn+D/J
7ZwACygsM20YP1wSuY2qSUwfApnzzmzSXsQk1+xVk/cG0cY06SUvTQXuEHxkggE1
CwIDAQAB
-----END PUBLIC KEY-----

ProxySQL is installed in front of the nodes and the mysql_users table contains users with a password hash (password field).
This is a problem, there must be one hash.

I believe this is due to the default caching_sha2_password_auto_generate_rsa_keys=ON variable. Which, according to the documentation, automatically generates a PCA key pair on the server.

We recently had an update from version 8.0.33-25.1 to 8.0.36-28 (04/03/2024).
All users there still use the mysql_native_password plugin, which is outdated and seems to have been removed in version 9.0.
How to safely and correctly update users to the caching_sha2_password plugin and solve the problem with different hash and PCA keys?
Will it be enough to take the already generated pair of PCA keys and rewrite them on nodes 2 and 3? And set the variable caching_sha2_password_auto_generate_rsa_keys=OFF?
We don’t use SSL.

We need to solve this before updating on the production environment, so as not to break the operation of the cluster.
I’m currently testing this in a test environment.

[shigaev.s]

There is an entry in the logs:

2024-07-09T12:23:16.115975Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-07-09T12:23:16.116011Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-07-09T12:23:16.116264Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory.
2024-07-09T12:23:16.116457Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.

This is even on a clean install with bootstrap.

[shigaev.s]

I found an article on ProxySQL https://www.percona.com/blog/caching_sha2_password-support-for-proxysql-is-finally-available/ with support for caching_sha2_password.

[matthewb]

@shigaev.s

seems to have been removed in version 9.0.

Yes. Officially native_password is deprecated in 8.0 and fully removed in 9.0.

@shigaev.s

Will it be enough to take the already generated pair of PCA keys and rewrite them on nodes 2 and 3? And set the variable caching_sha2_password_auto_generate_rsa_keys=OFF?

Yes, you can do that.

@shigaev.s

We don’t use SSL.

You should

[shigaev.s]

Thanks for your feedback!
I’ll try updating the PCA key pair and check the changes on the ProxySQL side.
I’ll be back later.

[shigaev.s]

I tried replacing a pair of PCA keys and restarted each node, now they are the same on all nodes.
Conclusion
SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G is the same.
I created a new user, but the hash is still different.

mysql> CREATE USER 'test.sha2'@'localhost' IDENTIFIED BY 'qwerty';
Query OK, 0 rows affected (0,02 sec)

1 node:

mysql> select user,host,plugin,authentication_string from mysql.user where user = 'test.sha2';
+-----------+-----------+-----------------------+------------------------------------------------------------------------+
| user      | host      | plugin                | authentication_string                                                  |
+-----------+-----------+-----------------------+------------------------------------------------------------------------+
| test.sha2 | localhost | caching_sha2_password | $A$005$Cl"[,}! 5}z:2eK"p9KiJlpJvzs2TrMselZb8481HMWdzKs8NnsOVxzK3o4 |
+-----------+-----------+-----------------------+------------------------------------------------------------------------+
1 row in set (0,00 sec)

2 node:

mysql> select user,host,plugin,authentication_string from mysql.user where user = 'test.sha2';
+-----------+-----------+-----------------------+------------------------------------------------------------------------+
| user      | host      | plugin                | authentication_string                                                  |
+-----------+-----------+-----------------------+------------------------------------------------------------------------+
W2K6TSffu.N9KaRsTdX7Qi.k.IsC6eVEtvkwNd.A4TD |rd | $A$005$!L79eH5b]fOrO-
+-----------+-----------+-----------------------+------------------------------------------------------------------------+
1 row in set (0,00 sec)

3 node:

mysql> select user,host,plugin,authentication_string from mysql.user where user = 'test.sha2';
+-----------+-----------+-----------------------+------------------------------------------------------------------------+
| user      | host      | plugin                | authentication_string                                                  |
+-----------+-----------+-----------------------+------------------------------------------------------------------------+
| test.sha2 | localhost | caching_sha2_password | $A$005$8_
                                                           =JyTKd!&I\▒- gcxGst40rW90LB5tsHoxc8PLRxXrJ4cDeG7aHgrvNbA |
+-----------+-----------+-----------------------+------------------------------------------------------------------------+
1 row in set (0,00 sec)

What’s wrong? Maybe something else needs to be changed?

[shigaev.s]

Also, with the same .pem on the nodes, the hash is different.
Could it be some kind of salt?
But then it is not possible to work with such a plugin in a cluster together with ProxySQL.
If you stay on mysql_native_password, then this will make further updating impossible.

[matthewb]

Are you sure this is actually an issue? Have you tested this? I run PXC+ProxySQL in our training labs all the time, and have never come across an issue like this before.

[shigaev.s]

How does it work for you? Is it the same with different hash of the user? How do you add it to ProxySQL?
This behavior occurs on an existing cluster and a test cluster with a clean deployment.

Judging by the documentation, the client libraries used in PHP do not have support for the caching_sha2_password plugin, which negates the transition to this plugin.

PHP: the PDO_MySQL and ext/mysqli extensions do not support caching_sha2_password . In addition, when used with PHP versions before 7.1.16 and PHP 7.2 before 7.2.4, they fail to connect with default_authentication_plugin=caching_sha2_password even if caching_sha2_password is not used.

[matthewb]

@shigaev.s

Is it the same with different hash of the user?

I never looked that closely because it’s never been an issue.

PHP 7.4 supports caching_sha2: mysql - Does mysqli have support for caching_sha2_password in PHP 7.4? - Stack Overflow
Additionally, PHP 7.2 EOL’d over 6 years ago, and 7.4 EOL’d almost 5 years ago. You shouldn’t be on such old versions. 8.3 is the current PHP.

[shigaev.s]

Yes, we have PHP 8.2 and 8.3 versions.

In the ProxySQL documentation for caching_sha2_password you need to add a hash, which is taken from the users table.
But each node has a different hash.
How did you add in this case?

[shigaev.s]

Hello!
Deployed a new cluster along with a ProxySQL.
Created a new user with caching_sha2_password.
On the ProxySQL I set the variable mysql-default_authentication_plugin = 'caching_sha2_password'; - without setting the variable does not work for authorization with caching_sha2_password
Added a user to the proxy using the HEX/UNHEX function.

I checked the connection under a new user on each node, and then through a proxy.
It works.

No problem. I was confused by the different hashes, but it turned out to be a salt, which does not affect authorization. Those. you can take a hash from any node or even salt + password and add it to the ProxySQL and it works.
Sorry for the trouble and thanks for the feedback.