# How to change existing Percona generated user's auth plugin to caching\_sha2\_password

**URL:** <https://forums.percona.com/t/how-to-change-existing-percona-generated-users-auth-plugin-to-caching-sha2-password/27844>\
**Category:** Percona XtraDB Cluster 8.x\
**Tags:** mysql, percona, new-release\
**Created:** [January 17, 2024, 8:39pm UTC](https://forums.percona.com/t/how-to-change-existing-percona-generated-users-auth-plugin-to-caching-sha2-password/27844 "2024-01-17T20:39:01Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![drew\_datajoint](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/drew_datajoint/32/11355_2.png) [@drew\_datajoint](https://forums.percona.com/u/drew_datajoint)\
**Post date:** [January 17, 2024, 8:39pm UTC](https://forums.percona.com/t/how-to-change-existing-percona-generated-users-auth-plugin-to-caching-sha2-password/27844/1 "2024-01-17T20:39:01Z")

</div>

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:

```auto
{"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:

```auto
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

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [January 17, 2024, 9:36pm UTC](https://forums.percona.com/t/how-to-change-existing-percona-generated-users-auth-plugin-to-caching-sha2-password/27844/2 "2024-01-17T21:36:56Z")

</div>

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`

---

<div class="post-metadata">

**Author:** ![drew\_datajoint](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/drew_datajoint/32/11355_2.png) [@drew\_datajoint](https://forums.percona.com/u/drew_datajoint)\
**Post date:** [January 18, 2024, 7:54am UTC](https://forums.percona.com/t/how-to-change-existing-percona-generated-users-auth-plugin-to-caching-sha2-password/27844/3 "2024-01-18T07:54:22Z")

</div>

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:

```auto
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](https://www.percona.com/blog/percona-xtradb-cluster-8-0-new-feature-wsrep_sst_auth-removal/), **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?**

---

<div class="post-metadata">

**Author:** ![drew\_datajoint](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/drew_datajoint/32/11355_2.png) [@drew\_datajoint](https://forums.percona.com/u/drew_datajoint)\
**Post date:** [January 23, 2024, 4:39am UTC](https://forums.percona.com/t/how-to-change-existing-percona-generated-users-auth-plugin-to-caching-sha2-password/27844/4 "2024-01-23T04:39:29Z")

</div>

Hi @matthewb ,  
I’ve tried to manually drop this `mysql.pxc.sst.role`@localhost and manually recreate this role by [here](https://github.com/percona/percona-xtradb-cluster/blob/061fb99693cc0b51ef84cc8f1b8606051557349e/scripts/mysql_system_users.sql#L90) and [here](https://github.com/percona/percona-xtradb-cluster/blob/061fb99693cc0b51ef84cc8f1b8606051557349e/scripts/mysql_system_tables_fix.sql#L1371), 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.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [January 23, 2024, 4:54am UTC](https://forums.percona.com/t/how-to-change-existing-percona-generated-users-auth-plugin-to-caching-sha2-password/27844/5 "2024-01-23T04:54:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![drew\_datajoint](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/drew_datajoint/32/11355_2.png) [@drew\_datajoint](https://forums.percona.com/u/drew_datajoint)\
**Post date:** [January 23, 2024, 8:36pm UTC](https://forums.percona.com/t/how-to-change-existing-percona-generated-users-auth-plugin-to-caching-sha2-password/27844/6 "2024-01-23T20:36:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Lars\_Erik\_Dangvard\_J](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lars_erik_dangvard_j/32/14572_2.png) [@Lars\_Erik\_Dangvard\_J](https://forums.percona.com/u/Lars_Erik_Dangvard_J)\
**Post date:** [March 11, 2024, 7:13pm UTC](https://forums.percona.com/t/how-to-change-existing-percona-generated-users-auth-plugin-to-caching-sha2-password/27844/7 "2024-03-11T19:13:16Z")

</div>

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

```auto
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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [March 11, 2024, 7:33pm UTC](https://forums.percona.com/t/how-to-change-existing-percona-generated-users-auth-plugin-to-caching-sha2-password/27844/8 "2024-03-11T19:33:33Z")

</div>

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

> <https://github.com/percona/percona-xtradb-cluster/blob/release-8.0.35/sql/wsrep_sst.cc#L1176>

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.

---

<div class="post-metadata">

**Author:** ![Lars\_Erik\_Dangvard\_J](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lars_erik_dangvard_j/32/14572_2.png) [@Lars\_Erik\_Dangvard\_J](https://forums.percona.com/u/Lars_Erik_Dangvard_J)\
**Post date:** [March 11, 2024, 8:04pm UTC](https://forums.percona.com/t/how-to-change-existing-percona-generated-users-auth-plugin-to-caching-sha2-password/27844/9 "2024-03-11T20:04:44Z")

</div>

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:

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

```

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

```

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [March 11, 2024, 8:44pm UTC](https://forums.percona.com/t/how-to-change-existing-percona-generated-users-auth-plugin-to-caching-sha2-password/27844/10 "2024-03-11T20:44:53Z")

</div>

> [@Lars\_Erik\_Dangvard\_J](#):
>
> I don’t know if changing default\_authentication\_plugin will impact older applications using mysql\_native\_password?

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.