July 2023: This post was reviewed for accuracy.

Amazon Relational Database Service (Amazon RDS) enables you to use AWS Identity and Access Management (IAM) to manage database access for Amazon RDS for MySQL DB instances and Amazon Aurora MySQL DB clusters. Database administrators can associate database users with IAM users and roles. By using IAM, you can manage user access to all AWS resources from a single location, avoiding issues caused by permissions that are out of sync on different AWS resources. You can use IAM authentication instead of a password with familiar client tools.

In this post, I show you how to use IAM authentication with tools you might already be using to connect to your Aurora MySQL cluster. The steps that I outline will work equally well on your Amazon RDS for MySQL instance. You can follow along using the provided scripts to provision resources and configure your environment for IAM authentication.

I then walk through connecting to the cluster using either the mysql command line tool or SQL Workbench/J using IAM credentials with reusable scripts. You can find all the code samples used in this post in this GitHub repository.

Prerequisites

If you want to use the mysql command line tool, download the certificate from the Amazon S3 bucket that’s identified in the Amazon RDS documentation. The mysql command line tool requires this certificate to initiate a Secure Sockets Layer (SSL) connection.

To use SQL Workbench/J to connect to a MySQL database for the first time, you must configure the JDBC driver. If this is your first time using SQL Workbench/J, follow the steps in the SQL Workbench/J documentation to configure the driver.

Setting up

You can use your own existing Aurora MySQL cluster or Amazon RDS for MySQL database and enable IAM authentication, or you can create a new one. In my testing, I created a new Aurora MySQL cluster using an AWS CloudFormation template. I created a new IAM user and database user, and then I configured IAM authentication for my database user. If you want to use the scripts I created, you can scroll to the section titled Run all as a bash script.

Create a database

If you don’t already have an Aurora MySQL cluster or Amazon RDS MySQL instance, you need to create one. Your database must be configured with a security group that allows ingress from your client machine. If you already have an Aurora MySQL database that you want to work with, you can skip this step.

If you don’t have one, you can provision an Aurora MySQL cluster through the AWS Management Console, AWS CLI, AWS SDK, or by using an AWS CloudFormation template. Included in the set of code for this post is an AWS CloudFormation template that creates an Aurora MySQL cluster with two instances, a primary and a read replica across two Availability Zones. If you download the code samples, you can provision your cluster using the AWS CloudFormation template create-databases.yaml. Or, follow the steps in Creating an Amazon Aurora DB Cluster in the Amazon RDS User Guide.

Enable IAM authentication

By default, IAM database authentication is disabled on DB instances and DB clusters. You can enable IAM database authentication (or disable it again) using the console, the AWS CLI, or the Amazon RDS API. The steps for enabling IAM authentication on your database, including the console steps, are listed in Enabling and Disabling IAM Database Authentication.

To enable IAM authentication from the command line, you need to know your cluster name. You can find the name on the Amazon RDS console or in the output values of the completed AWS CloudFormation template.

The following command enables IAM authentication on the cluster iamauth-databasecluster-abcdefg222hq:

aws rds modify-db-cluster \
    --db-cluster-identifier "iamauth-databasecluster-abcdefg222hq" \
    --apply-immediately \
    --enable-iam-database-authentication

IAM resources

For the purposes of this post, I attached a policy with an action of rds-db:connect to a single IAM user, as shown in the following diagram.

You can construct other Amazon Resource Names (ARNs) to support various access patterns and attach the policies to multiple users or roles. Read more about other access patterns in Creating and Using an IAM Policy for IAM Database Access.

Policy

To allow an IAM user or role to connect to your DB instance or DB cluster, you must create an IAM policy. Then you attach the policy to an IAM user or role. You construct the policy document from four key pieces of data:

• The AWS Region of your cluster
• Your account number
• The DB resource ID
• Your database user name

{
  "Version" : "2012-10-17",
  "Statement" :
  [
    {
      "Effect" : "Allow",
      "Action" : ["rds-db:connect"],
      "Resource" : ["arn:aws:rds-db:us-east-1:123456789012:dbuser:cluster-11AABB87JJCCTZUFR551111111/mydbuser"]
    }
  ]
}

You specify an ARN that describes one database account in one DB instance using the following format:

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

In the preceding example policy, the following elements are customized from my environment:

• us-east-1 is the AWS Region.
• 123456789012 is the account ID.
• cluster-11AABB87JJCCTZUFR551111111 is the resource ID.
• mydbuser is the database user name in MySQL.

Let’s look more closely at the last two values.

A resource ID is the identifier for the DB instance. This identifier is unique to an AWS Region and never changes. In the example policy, the identifier is cluster-11AABB87JJCCTZUFR551111111. To find a resource ID in the Amazon RDS console, choose the DB cluster that you want, and the resource ID is shown in the Details section.

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

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

A DB user name is the name of the MySQL database account to associate with the IAM authentication. In the example policy, the user name is mydbuser.

Create the policy

To create the policy, I used the AWS CLI create policy command within the bash script create-stack-and-user.sh. The following image shows the create policy command with the name database-login-mydbuser using a string representation of the policy document with quotes escaped.

aws iam create-policy --policy-name database-login-$IAMUSER \
--policy-document "{\"Version\" : \"2012-10-17\", \"Statement\" : [{\"Effect\" : \"Allow\",\"Action\" : [\"rds-db:connect\"],\"Resource\" : [\"arn:aws:rds-db:us-east-1:123456789012:dbuser:cluster-11AABB87JJCCTZUFR551111111/mydbuser\"]}]}"

Attach the policy

After you create an IAM policy to allow database authentication, you need to attach the policy to an IAM user or role. For a tutorial on this topic, see Create and Attach Your First Customer Managed Policy in the IAM User Guide.

I created a new IAM user and then attached my policy to the new IAM user using the following AWS CLI commands. Console password or access keys are not required for this feature. The user from my example has neither.

aws iam create-user --user-name mydbuser

aws iam attach-user-policy \
  --policy-arn arn:aws:iam123456789012:policy/database-login-mydbuser \
  --user-name mydbuser

Create a database user

After you have your IAM user created and your IAM policy attached to the user, you must create a database user with the same name as you specified in the policy. I used the mysql command line tool to connect to the database as the master user and create a user. The following command connects to the Aurora MySQL cluster and runs the SQL in the create-user.sql file.

mysql -h ${AURORAEP%%:*} -P 3306 --user=$MASTERUSER -p < create-user.sql

You can connect as any user that has CREATE USER permissions and execute the following statements:

CREATE USER mydbuser IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
GRANT ALL ON `%`.* TO mydbuser@`%`;

In the first statement, the IDENTIFIED WITH clause allows MySQL to use the AWSAuthenticationPlugin to authenticate the database account (mydbuser). The AS 'RDS' clause maps the mydbuser database account to the corresponding IAM user or role that is specified by the policy. The second statement grants the user permissions on all the databases.

Typically, when you add users to your MySQL database, you can require SSL connections for specific user accounts, or not. When you use IAM authentication, the setting for the user is overridden to require SSL. For more information about the REQUIRE SSL option when creating users, see Security with Amazon Aurora MySQL.

Run all as a bash script

To execute the preceding process using the create-stack-and-user.sh script, take the following steps.

1. Download the repository files locally.
2. In the main function of create-stack-and-user.sh for your environment, edit the variables accountid, stackname, region, and iamuser.
3. Edit the AWS CloudFormation parameter values config/cf-parameters.json for your environment.
4. Edit the config/create-user.sql file with the desired IAM user name. The name must match the iamuser in the script in step 2.
5. Run the create-stack-and-user.sh script from the command line.

The script relies on the AWS CLI, mysql command line tool, and the jq programming language to run. The script launches an AWS CloudFormation stack, and then modifies the running instance to use IAM. After the instance is modified, the script creates a new IAM user and new IAM policy, and then attaches the new policy to the user. Finally, the script adds the IAM user to your new database as a database user to complete the setup processes.

Connecting

With IAM database authentication, you use an IAM authentication token when you connect to your DB instance or DB cluster. An IAM authentication token is a string of characters that you use instead of a password. After you generate an IAM authentication token, it’s valid for 15 minutes before it expires. If you try to connect using an expired token, the connection request is denied. MySQL doesn’t recheck existing connections, so your established connection is not dropped when the token expires.

Every IAM authentication token must be accompanied by a valid signature, using AWS Signature Version 4. (For more information, see Signature Version 4 Signing Process in the AWS General Reference.) The AWS CLI and the AWS SDK for Java can automatically sign each token you create. You can use the AWS CLI to generate the connection token.

After you have a signed IAM authentication token, you can connect to an Amazon RDS DB instance or an Aurora DB cluster.

Generate a token

For both of the following examples, you can use an AWS CLI call to generate a signed IAM authentication token. The AWS CLI command is generate-db-auth-token and takes the following four parameters as input:

• hostname: The endpoint for the Aurora MySQL cluster
• port: The connection port used when connecting, shown following as 3306
• username: The database account you want to access
• region: The AWS Region where the DB cluster is running

aws rds generate-db-auth-token --hostname $AURORAEP --port 3306 \
--username $IAMUSER --region=$REGION

The token that is generated from this command is hundreds of characters long and can be challenging to input directly to the command line.

aws rds generate-db-auth-token \
--hostname iamauth-databasecluster.cluster-abcdefg222hq.us-east-1.rds.amazonaws.com \
--port 3306 --username mydbuser --region us-east-1

It’s a best practice to capture the token in a variable that you can use when making the connection.

TOKEN="$(aws rds generate-db-auth-token --hostname $AURORAEP --port 3306 --username $IAMUSER --region=$REGION)"

Connect to the cluster using mysql

After you generate the token, connecting to your Aurora MySQL cluster using the mysql command line tool is straightforward. To use this method, you need the SSL certificate file on your client machine.

mysql -h ${AURORAEP%%:*} -P 3306 --ssl-ca=~ /Downloads/rds-ca-2015-root.pem \
--enable-cleartext-plugin --user=$IAMUSER --password=$TOKEN

If you are using the mysql command line tool today, you might recognize most of the preceding command. Let’s look at two aspects that you might not recognize.

• enable-cleartext-plugin: Signals that AWSAuthenticationPlugin must be used for this connection.
• ssl-ca: A file that contains a list of trusted SSL certificate authorities. The preceding certificate is designed to work for all Regions. But if it fails, you might have to download a Region-specific certificate.

Note for MariaDB users:

• The MariaDB client will error with unknown option --enable-cleartext-plugin on as it supports cleartext by default and there is no flag to enable it. Simply run the command without the parameter.
• When using MariaDB, you must use parameter --ssl. MariaDB has a built-in security check that prevents users from submitting cleartext passwords over the wire.
  Example: mysql -h ${AURORAEP%%:*} -P 3306 --ssl-ca=~/Downloads/rds-ca-2015-root.pem --ssl --user=$IAMUSER --password=$TOKEN

If you put everything in a connection script, when you want to initiate a connection to your MySQL cluster, you just execute a bash script similar to the following from the command line:

#!/usr/bin/env bash

STACKNAME="iamauth"
REGION="us-east-1"
IAMUSER="mydbuser"
AURORAEP="$(aws cloudformation describe-stacks --stack-name "$STACKNAME" --region $REGION | jq '.Stacks[].Outputs[] | select(.OutputKey=="AuroraClusterEndpoint")' | jq -r .OutputValue)"
DBNAME="$(aws cloudformation describe-stacks --stack-name "$STACKNAME" --region $REGION | jq '.Stacks[].Outputs[] | select(.OutputKey=="AuroraDatabaseName")' | jq -r .OutputValue)"

# Make sure you have the right region for the token!!
TOKEN="$(aws rds generate-db-auth-token --hostname $AURORAEP --port 3306 --username $IAMUSER --region=$REGION)"

#set -x
mysql -h ${AURORAEP%%:*} -P 3306 --ssl-ca=~/Downloads/rds-ca-2015-root.pem \
--enable-cleartext-plugin --user=$IAMUSER --password=$TOKEN
#set +x

./conn-aurora-iam-auth.sh

The script, when executed automatically, generates the token and connects to the mysql command line tool.

Connect to the cluster using SQL Workbench/J

Connecting to your Aurora MySQL cluster using SQL Workbench/J is slightly different from how you might have previously launched the app. Instead of starting SQL Workbench/J using SQLWorkbenchJ.app, you now start it directly using the .jar file so that you can pass in the token.

java -jar ~/workbench/sqlworkbench.jar \
-url=jdbc:mysql://$AURORAEP:3306/$DBNAME?verifyServerCertificate=false\&useSSL=true\&requireSSL=true \
    -driver=com.mysql.jdbc.Driver \
    -username=$IAMUSER \
    -password=$TOKEN \
    -libdir= /Users/wendyneu/workbench \
    -driverjar=/Users/wendyneu/workbench/mysql-connector-java-5.1.45-bin.jar

A detailed description of the values passed into the command follows.

• url: The JDBC connection URL consisting of the cluster name, database name, and SSL options.
• driver: The full class name of the driver. It requires correct spelling and is case sensitive.
• username: The database user name.
• password: The generated token.
• libdir: The full path name to the directory containing the JDBC driver .jar file. It cannot be a relative path.
• driverjar: The full path name to the .jar file containing the JDBC driver. It cannot be a relative path.

If you put everything in a connection script, when you want to initiate a connection to your MySQL cluster, you just execute a bash script similar to the following from the command line:

#!/usr/bin/env bash
#
#   Script to launch SQL Workbench from a command line and connect to
#   an Aurora MySQL database cluster using IAM authentication
#
STACKNAME="iamauth"
REGION="us-east-1"
IAMUSER="mydbuser"
AURORAEP="$(aws cloudformation describe-stacks --stack-name "$STACKNAME" --region $REGION | jq '.Stacks[].Outputs[] | select(.OutputKey=="AuroraClusterEndpoint")' | jq -r .OutputValue)"
DBNAME="$(aws cloudformation describe-stacks --stack-name "$STACKNAME" --region $REGION | jq '.Stacks[].Outputs[] | select(.OutputKey=="AuroraDatabaseName")' | jq -r .OutputValue)"

# Make sure you have the right region for the token!!
TOKEN="$(aws rds generate-db-auth-token --hostname $AURORAEP --port 3306 --username $IAMUSER --region=$REGION)"

#set -x
java -jar ~/workbench/sqlworkbench.jar \
    -url=jdbc:mysql://$AURORAEP:3306/$DBNAME?verifyServerCertificate=false\&useSSL=true\&requireSSL=true \
    -driver=com.mysql.jdbc.Driver \
    -username=$IAMUSER \
    -password=$TOKEN \
    -libdir= /Users/wendyneu/workbench \
    -driverjar=/Users/wendyneu/workbench/mysql-connector-java-5.1.45-bin.jar
#set +x
./wbconn-aurora-iam-auth.sh

The script, when executed automatically, generates the token and launches the SQL Workbench/J GUI and connects to your database.

You can read more about the other command line options for SQL Workbench/J in the SQL Workbench/J documentation.

Limitations

There are some limitations when you use IAM database authentication. The following is from the IAM authentication documentation. To read the full article, see IAM Database Authentication for MySQL and Amazon Aurora.

With IAM database authentication, you are limited to a maximum of 256 new connections per second. If you are using a db.t2.micro instance class, the limit is 10 connections per second.

When you use IAM database authentication, your application must generate an IAM authentication token. Your application then uses that token to connect to the DB instance or cluster. If you exceed the maximum new-connection-per-second limit, the extra overhead of IAM database authentication can cause connection throttling.

We recommend the following:

• Use IAM database authentication as a mechanism for temporary, personal access to databases.
• Don’t use IAM database authentication if your application requires more than 256 new connections per second.
• Use IAM database authentication only for workloads that can be easily retried.

Conclusion

There are a lot of advantages to using IAM authentication with your Amazon RDS for MySQL and Amazon Aurora MySQL databases. Network traffic to and from the database is encrypted using SSL. You can associate new and existing database users to IAM users and roles, managing the credentials via IAM, without needing to manage users in the database. For applications that run on Amazon EC2, for greater security, you can use EC2 instance profile credentials to access the database instead of a password. This post showed you how you can use IAM authentication instead of a password with tools such as the mysql command line tool and SQL Workbench/J. You can adapt these instructions and processes to other tools.

About the Author

Wendy Neu has worked as a Data Architect with Amazon since January 2015. Prior to joining Amazon, she worked as a consultant in Cincinnati, OH, helping customers integrate and manage their data from different unrelated data sources.