Configuring MySQL on AWS RDS

How to configure your MySQL database on AWS RDS to use IAM Authentication

I was surprised by how difficult it was to set up a MySQL database on AWS RDS to use IAM Authentication. IAM Authentication is superior to using connection strings because you don't need a password to connect, therefore you don't need to find a way to securely store and manage that password.

Step 1: Configure RDS database for IAM Authentication

To set up your MySQL installation on AWS RDS you first need to configure your MySQL database in RDS to allow IAM Authentication. At the time of this writing in September of 2024, you do that by navigating to your database in AWS RDS and selecting "Modify". In the Database authentication section, select "Password and IAM database authentication"

After doing so, you need to click the "Continue" button then "Modify DB Instance". The change does not happen immediately. When I did this, I had to repeat the process several times because my change did not seem to take. So you should go back into your database configuration to confirm the change took. Also, I believe the database needs to be rebooted before it takes effect. I recommend that you wait to move on to subsequent steps until you have confirmed, via the Modify UI for the database, that the Database authentication value is set correctly.

Step 2: Create a policy that allows connecting to the database

This step was the most problematic for me and took the longest to figure out. I tried giving my Role all RDS policies I could find, but the only way I could solve the problem was to create a new policy. This is extremely bizarre and I still feel I must have overlooked something. If you know what I missed, please let me know, but this step is the final action I took that allowed my application to work. Create a new policy (because there is not one built into AWS) that allows for "connecting" to an RDS database. In IAM, go to Policies, select "Create policy", select a service which must be "RDS IAM Authentication". Select the checkbox for "All RDS IAM Authentication actions" (there is only one). Select "Any in this account" and "Specific" under Resources specify which AWS Resources can use this policy. Give your policy a meaningful name such as "AWSmissingpolicyForConnectToRDS" and save it.

Step 3 Create a Role in AWS for the policy and services

The great thing, in my opinion, about building applications in AWS or Azure is the ability to security them easily with policies. In this case, you need to now create a role that will have the new policy. In AWS IAM, create a new role or alter an existing role to contain all the policies that your service calling RDS will need. In my case, I will have several Lambdas that need to connect to my RDS MySQL database, so I created a role named 2024LambdasToCallRDS and gave the role two permissions, the permission I created in Step 2 "AWSmissingpolicyForConnectToRDS" and also "AmazonS3ObjectLambdaExecutionPolicy".

Step 4: Assign the role to your services

The last thing you need to do in AWS is to configure your service, in my case a Lambda, to have the role you created in Step 3. So I went to the configuration of my Lambda and to the Permissions tab and assigned my role to the Lambda.

Step 5: Create user in MySQL

You need to create a user that logs in using AWS's IAM. I don't think you can pick an existing user in MySQL that uses a login and password, but I am not sure about that. I created a new user in RDS as shown here with the "IDENTIFIED WITH AWSAuthenticationPlugin" and limited the permissions of that user to the CRUD verbs.

CREATE USER 'myrdsuser'@'%' IDENTIFIED WITH AWSAuthenticationPlugin as 'RDS';

GRANT SELECT, INSERT, UPDATE, DELETE ON reusefull.* TO 'myrdsuser'@'%';