I created a MysQL RDS instance on AWS. I can connect to MysQL using my own user who was the one that created the instance like that:
mysql -h aws-host -P 3306 -u MyUser -p
I need to give access to Mysql to other users who are already register on AWS.
Manual: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.DBAccounts.html
The User creation is working but when I log out from my user and try to logging again with the new user it does not work.
I'm having the problem that the user can not connect to Mysql because of Authentication plugin error.
Command:
mysql -h aws-host -P 3306 -u newUser -p
when I type the password I get:
Error: ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled
The password is correct because it's the one I use to loggin on aws with that user and IAM DB Authentication is enable, I thought IM and RDS should share passwords or I'm doing something wrong ??
The cause of the issue to be the DB auth token and absence of SSL in your connection string. When using IAM database authentication, network traffic to and from the database is encrypted using Secure Sockets Layer (SSL)
DB auth token is generated with "generate-db-auth-token" and SSL requires using a root certificate which is available here. Once the file is available on the machine, the following syntax can be used to authenticate:
RDSHOST="myinstance.***********.rds-us-east-1.amazonaws.com"
TOKEN="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 3306 --username test )"
mysql --host=$RDSHOST --port=3306 --enable-cleartext-plugin --ssl-ca=[full path]rds-combined-ca-bundle.pem --ssl-mode=VERIFY_IDENTITY --user=test --password=$TOKEN
Here is the official documentation for connecting to MySQL Database with IAM DB Authentication