Unable to Upgrade MySQL on RDS Due to Authentication Method Restrictions

Question

Hello everyone,

I received a notification from AWS stating that our current database version (MySQL 8.0.34) is being deprecated, and we must upgrade to at least 8.0.40. However, we’ve encountered a problem during the upgrade process.

When attempting either an in-place upgrade or creating a new blue/green deployment, RDS runs several pre-upgrade checks. One of these checks—“Check for deprecated or invalid user authentication methods”—flags any MySQL users still using `mysql_native_password`, which has been superseded by `caching_sha2_password`. This is preventing the upgrade from proceeding.

Typically, we could simply update the authentication method by running:

```
ALTER USER 'rdsrepladmin'@'%' IDENTIFIED WITH caching_sha2_password BY 'PASSWORD';
```

However, we cannot do this for two specific users:
- `rdsrepladmin@%`
- `mysql.sys@localhost`

When attempting to change their authentication methods, we receive the following error:

```
1132 - Access denied: ALTER USER rdsrepladmin, Time: 0.123000s
```

It appears these users were created by AWS when the instance was provisioned, and we do not have the necessary permissions to alter them ourselves.

Has anyone experienced this issue or found a workaround? We are currently blocked from completing the upgrade due to this limitation.

Thank you in advance for any guidance you can provide.

— Mark —

re:Post Agent · Accepted Answer

Mark, I understand your frustration with the upgrade process for your RDS MySQL instance. Unfortunately, you've encountered a known limitation with Amazon RDS for MySQL.

As of RDS for MySQL version 8.0.34 and higher, the default authentication plugin is set to mysql_native_password, and this setting cannot be changed by users. This is a deliberate restriction imposed by AWS for RDS MySQL instances.

The users you mentioned, 'rdsrepladmin' and 'mysql.sys', are indeed system users created and managed by AWS. You don't have permissions to alter these users directly, which is why you're receiving the "Access denied" error when trying to change their authentication method.

Currently, there isn't a direct workaround for changing the authentication plugin for these system-managed users. AWS doesn't support changing the Authentication Plugin for RDS MySQL instances.

However, this shouldn't prevent you from upgrading your RDS instance. The pre-upgrade check you're seeing is likely a warning rather than a blocker for the upgrade process. AWS is aware of this situation and has designed the upgrade process to handle these system users appropriately.

My recommendation would be to proceed with the upgrade despite this warning. If the upgrade still fails, you should contact AWS Support for assistance, as they may need to provide a specific solution for your case.

For your application users, you can still use the ALTER USER command to change their authentication method to caching_sha2_password if desired, but this isn't strictly necessary for the upgrade process.

Remember, while changing the default_authentication_plugin setting isn't possible, you can still create new users with the caching_sha2_password plugin if needed for your application.

If you continue to face issues with the upgrade process, don't hesitate to open a support ticket with AWS. They can provide more specific guidance based on your exact RDS configuration and requirements.  
**Sources**  
[How do I change the Authentication Plugin for users mysql.sys, mysql.session, etc, on AWS RDS MySQL? | AWS re:Post](https://repost.aws/questions/QUKzU4WYPpSNGzBSwvbF7RtQ/how-do-i-change-the-authentication-plugin-for-users-mysql-sys-mysql-session-etc-on-aws-rds-mysql)  
[Precheck descriptions reference for Aurora MySQL - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.upgrade-prechecks.descriptions.html)

Mark · Answer

For those experiencing what looks like the same problem, it was actually a low storage issue in the end, I increased the storage of my db instance and it seems to work.

The logs actually stated this but i didn't scroll down far enough as i thought the errors stopped at the authentication issue.

:facepalm

Unable to Upgrade MySQL on RDS Due to Authentication Method Restrictions

Add your answer

Relevant content