How to change existing Percona generated user's auth plugin to caching_sha2_password

Hi there,
I have an existing PXC running with version 8.0.32-24.2, and when I upgraded it to 8.0.35, I got a flood of warning saying:

{"log":"2024-01-17T20:20:59.100521Z 345 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'\n","file":"/var/lib/mysql/mysqld-error.log"}

Then I changed the config authentication-policy=caching_sha2_password, redeployed pxc by helm and manually altered all my users’ auth plugin except the percona generated users, since I’m not sure how to change mysql.pxc.sst.role’s password. The other percona generated users are referencing password from a k8s secret, which I could manually alter them, but I’m not sure if there is a better way:

MySQL [(none)]> select user, plugin from mysql.user where plugin like '%native%';
+--------------------+-----------------------+
| user               | plugin                |
+--------------------+-----------------------+
| monitor            | mysql_native_password |
| operator           | mysql_native_password |
| replication        | mysql_native_password |
| root               | mysql_native_password |
| xtrabackup         | mysql_native_password |
| clustercheck       | mysql_native_password |
| mysql.pxc.sst.role | mysql_native_password |
| root               | mysql_native_password |
+--------------------+-----------------------+
8 rows in set (0.051 sec)

Also, I’m not sure why I have two root users

Hello @drew_datajoint,
In order to migrate from mysql_native_password to the new caching_sha2_password you must know the password for the user.

ALTER USER foo@host IDENTIFIED BY 'password' IDENTIFIED WITH 'caching_sha2_password

Because the passwords are stored as hashes, you cannot simply convert an existing one to the other.

For your root users, change your SELECT to include host column. Remember that a user in MySQL is the unique combination of username@source; root@localhost is a completely different user than root@127.0.0.1

Hi @matthewb ,
Thanks for your help! I understood I need to know the user’s password in order to alter the auth plugin to caching_sha2_password, and that’s what I have already done. The question I have now, is that there are Percona XtraDB Cluster generated users shown in the original post user-plugin list:

MySQL [(none)]> select user,host,plugin from mysql.user where plugin like '%native%';
+--------------------+-----------+-----------------------+
| user               | host      | plugin                |
+--------------------+-----------+-----------------------+
| monitor            | %         | mysql_native_password |
| operator           | %         | mysql_native_password |
| replication        | %         | mysql_native_password |
| root               | %         | mysql_native_password |
| xtrabackup         | %         | mysql_native_password |
| clustercheck       | localhost | mysql_native_password |
| mysql.pxc.sst.role | localhost | mysql_native_password | <---???
| root               | localhost | mysql_native_password |
+--------------------+-----------+-----------------------+
8 rows in set (0.058 sec)

I have password of most of them, but I don’t have the password for this generated user mysql.pxc.sst.role then I don’t know how to alter it.
It seems like this user is created by backup process, referencing from here, since this article mentioned this user’s credentials is randomly generated by Percona, I don’t think there is a way for me to get the password. Is it possible to drop this user and somehow let Percona to regenerate it again?

Hi @matthewb ,
I’ve tried to manually drop this mysql.pxc.sst.role@localhost and manually recreate this role by here and here, but eventually this caused SST script not able to run, and I have to take a restore to roll it back. I’ll keep this role in mysql_native_password until there is a better solution.

Strange. In the 2nd doc you linked, the INSERT uses the caching_sha2_password plugin. If this was an in-place upgrade from 5.7, then yes, it was originally using native_password. A fresh install of PXC8 most likely uses the new sha2.

@matthewb I don’t think it was the case, I have never deployed any 5.7, and neither noticed/changed this role’s auth plugin. But it seems like it was using native_password when PXC 8.0.21 deployed at the very first time. I’ll see if it can help if I take a canary upgrade with a new replication.