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

MySQL & MariaDB Percona XtraDB Cluster 8.x
, ,
1

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

2

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

3

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?

4

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.

5

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.

6

@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.

7

@matthewb I’m also getting a lot of log entries of:

Plugin mysql_native_password reported: mysql_native_password is deprecated and will be removed in a future release. Please use caching_sha2_password instead.

PXC is deployed on version 8.0.35 (latest) from scratch. When a user has mysql_native_password log is getting filled up. I’ve changed all users to “caching_sha2_password” including the user “mysql.pxc.sst.role” with:

ALTER USER 'mysql.pxc.sst.role'@'localhost' IDENTIFIED WITH caching_sha2_password;

This works great and the log entries stop.

However, when crashing a node and rejoining, a user “mysql.pxc.sst.user@localhost” is created with mysql_native_password and an unknown (to me) password. This seems to be an internal user that is created for SST.

Log with mysql_native_password deprecation messages was in 3 weeks grown to 12GB (fewer users with mysql_native_password makes the log smaller).

I guess the creation of user mysql.pxc.sst.user@localhost needs to be changed from mysql_native_password to caching_sha2_password. The mysql.pxc.sst.user@localhost user only seems to be created on the crashed node when it’s rejoined the cluster.

8

@Lars_Erik_Dangvard_J I found in the code where the user is created:

Notice that the method is not specified, which means it will use the server’s default. You need to configure default_authentication_plugin=caching_sha2_password in your my.cnf and restart the server.

9

Great!

Also found out that MySQL 8 has an error suppression list where MY-013360 can be suppressed (the mysql_native_password deprecation message).

I don’t know if changing default_authentication_plugin will impact older applications using mysql_native_password?

The following also stops the logging madness in case someone (by accident) creates a user with mysql_native_password:

mysql> set global log_error_suppression_list='MY-013360';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@log_error_suppression_list;
+------------------------------+
| @@log_error_suppression_list |
+------------------------------+
| MY-013360                    |
+------------------------------+
1 row in set (0.00 sec)
10

This only affects newly created accounts. If you don’t specify which auth method you want when you CREATE USER, it will pick this default. You can always change legacy stuff back to the old way.

1 Like