Yes I double checked.
There are 5 users as following
| user | plugin |
|---|---|
| strapi_admin | mysql_native_password |
| mysql.infoschema | caching_sha2_password |
| mysql.session | caching_sha2_password |
| mysql.sys | caching_sha2_password |
| rdsadmin | mysql_native_password |
I even tried changing the other 3 users from caching_sha2_password to mysql_native_password.
It still shows me ER_NOT_SUPPORTED_AUTH_MODE
I cannot use any other db as my client has specifically decided it would be either mongo or mysql.
And Strapi just dropped support for MongoDB. So MySQL it is.