How to authenticate to an Amazon RDS DB instance using IAM credentials

To set up IAM database authentication using IAM roles, follow these steps:

1. Enable IAM DB authentication on the DB instance.

2. Create a database user account that uses an AWS authentication token.

3. Add an IAM policy that maps the database user to the IAM role.

4. Attach the IAM role to the EC2 instance.

5. Generate an AWS authentication token to identify the IAM role.

6. Download the SSL root certificate file or certificate bundle file.

7. Connect to the DB instance using IAM role credentials and the authentication token or an SSL certificate.

Example:

1. Enable IAM DB authentication on the DB instance.

2. Create a database user account that uses an AWS authentication token.

Login using admin user and create an user account. In that example I have created a user name "nazmul". IAM database authentication requires a secure socket layer (SSL) connection. This means that all data transmitted to and from your DB instance is encrypted.

CREATE USER nazmul IDENTIFIED WITH AWSAuthenticationPlugin as 'RDS';

ALTER USER 'nazmul'@'%' REQUIRE SSL;

GRANT all privileges ON *.* TO 'nazmul'@'%';

3. Add an IAM policy that maps the database user to the IAM role.

Here is the format of resource that you will need to consider:

arn:aws:rds-db:region:account-id:dbuser:DbiResourceId/db-user-name

region is the AWS Region for the DB instance.

account-id is the AWS account number for the DB instance.

DbiResourceId is the identifier for the DB instance. This identifier is unique to an AWS Region and never changes.

To find a DB instance resource ID in the AWS Management Console for Amazon RDS, choose the DB instance to see its details. Then choose the Configuration tab. The Resource ID is shown in the Configuration section.

Alternatively, you can use the AWS CLI command to list the identifiers and resource IDs for all of your DB instance in the current AWS Region, as shown following.

aws rds describe-db-instances --query "DBInstances[*].[DBInstanceIdentifier,DbiResourceId]"

Go to Role and click "Create Role"

4. Attach the IAM role to the EC2 instance.

5. Generate an AWS authentication token to identify the IAM role.

IAM database authentication tokens are generated using your AWS access keys. You don't need to store database user credentials. Authentication tokens have a lifespan of 15 minutes, so you don't need to enforce password resets.

# TOKEN="$(aws rds generate-db-auth-token --hostname test.cajktdfhjjbk.eu-west-1.rds.amazonaws.com --port 3306 --region eu-west-1 --username nazmul)"

6. Download the SSL root certificate file or certificate bundle file.

# pwd

/root

# wget https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem

--2021-05-25 14:55:30-- https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem

Resolving s3.amazonaws.com (s3.amazonaws.com)... 52.217.36.198

Connecting to s3.amazonaws.com (s3.amazonaws.com)|52.217.36.198|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: 1456 (1.4K) [binary/octet-stream]

Saving to: ‘rds-ca-2019-root.pem’

rds-ca-2019-root.pem 100%[============================================================================>] 1.42K --.-KB/s in 0s

2021-05-25 14:55:31 (66.7 MB/s) - ‘rds-ca-2019-root.pem’ saved [1456/1456]

# ls rds*

rds-ca-2019-root.pem

7. Connect to the DB instance using IAM role credentials and the authentication token or an SSL certificate.

# mysql --host=test.cajktdfhjjbk.eu-west-1.rds.amazonaws.com --port=3306 --ssl-ca=/root/rds-ca-2019-root.pem --enable-cleartext-plugin --user=nazmul --password=$TOKEN

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 127

Server version: 8.0.20 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>