Using AWS IAM Authentication for MySQL or MariaDB with RDS or Aurora
As of this writing, Amazon Web Services (AWS) allows authentication to PostgreSQL, MySQL, and MariaDB RDS or Aurora databases using IAM (Identity and Access Management) authentication. Instead of using a typical database user id and password, when using IAM authentication, the user would instead use an IAM user id and an authentication token to authenticate. The authentication token expires after roughly 15 minutes. If creating new database connections after that 15 minute interval, a new authentication token would need to be generated.
To enable IAM authentication on the MySQL or MariaDB RDS or Aurora instance, users can use the Amazon RDS console. In the Database Authentication section of RDS database instances, choose the "Pasword and IAM database authentication" option when modifying or creating a new DB instance.
After the IAM authentication is enabled on the database, an IAM user would need to be created via the IAM section of the AWS console. The user needs an IAM policy that allows it to connect to the AWS database. More information on creating an IAM policy and an IAM user can be found in the AWS documentation. Essentially, the IAM policy needs to allow the rds-db:connect option for an RDS resource.
After the IAM user is created and a policy is attached to the user giving the user the ability to connect to the RDS or Aurora database, an identical user name must be created on the MySQL or MariaDB RDS / Aurora database. For example, if a user named iam_test_user was created, the same user (iam_test_user) must be created on the RDS/Aurora database. Below are example statements for creating IAM users on MySQL / MariaDB.
MySQL / MariaDB Create IAM User
CREATE USER iam_test_user IDENTIFIED WITH AWSAuthenticationPlugin as 'RDS'
After creating the IAM user on the database instance, the user would still need the appropriate grants to be able to perform specific actions such as querying data, inserting data, etc.
For example, to give the iam_test_user all privileges on a database named sample in MySQL or MariaDB, the following command could be executed:
MySQL / MariaDB IAM Grant Statement
GRANT ALL PRIVILEGES ON sample.* TO 'iam_test_user'@'%';
To make authentication with IAM authentication tokens easier, newer versions of RazorSQL allow the user to specify the location of a password file as opposed to typing in a password in RazorSQL. A user could then run an IAM authentication token regeneration script on the user's machine that would regenerate the password file in an interval less than every 15 minutes. RazorSQL would re-read the password file before connecting or reconnecting to the database, thus saving the user from having to re-type the authentication token.
