AWS Database Blog

IAM authentication with Amazon RDS for MariaDB

In 2018, we introduced the ability to use AWS Identity and Access Management (IAM) authentication to manage database access for Amazon Relational Database Service for MySQL (Amazon RDS for MySQL), Amazon Relational Database Service for PostgreSQL (Amazon RDS for PostgreSQL), Amazon Aurora MySQL-Compatible Edition, and Amazon Aurora PostgreSQL-Compatible Edition. This feature allows you to use IAM as a centralized identity store to manage access to your databases, and you can continue to use familiar client-side tools such as MySQL Workbench and DBeaver to log in with IAM credentials instead of using a password.

AWS announced support for IAM authentication for Amazon RDS for MariaDB. In this post, we show you how to create an RDS for MariaDB database, set up the IAM policies, and connect to the database with IAM.

Benefits of IAM authentication

AWS Identity and Access Management (IAM) provides fine-grained access control across all of AWS. With IAM, you can specify who can access which services and resources, and under which conditions. With IAM policies, you manage permissions to your workforce and systems to ensure least-privilege permissions. IAM streamlines the process of developing and creating access policies to your AWS resources, including databases.

Previously, you needed to set up the authentication on the MariaDB database instance itself and store the password either on AWS Secrets Manager or on a third-party secrets management tool. However, in organizations that use different toolsets, passwords can often become out of sync across the different tools when it comes time to rotate the password. IAM authentication provides a streamlined security posture by allowing access management from a centralized location. With IAM authentication, Amazon RDS generates an authentication token that can serve as credentials to log in to the database. Benefits of IAM authentication for Amazon RDS for MariaDB include:

  • Encrypted network traffic using Secure Socket Layer (SSL) or Transport Layer Security (TLS)
  • Centralized management of credentials on IAM
  • Temporary tokens for application authorization using IAM credentials that can be invalidated to maintain least privilege access control

Solution overview

In this post, we show how to create an RDS for MariaDB database and enable IAM authentication on it. Next, we show how to set up the IAM credentials and connect to the RDS for MariaDB instance using IAM through different interfaces.

The following diagram gives a high-level overview of the process to log into your RDS for MariaDB instance using IAM authentication using an Amazon Elastic Compute Cloud (Amazon EC2) instance running a MariaDB client.

Prerequisites

For this solution, we assume that you have the following infrastructure already set up:

  1. An Amazon Virtual Private Cloud (Amazon VPC) with a public and private subnet to host an Amazon EC2 and RDS for MariaDB instance.
  2. An EC2 instance to log into your RDS for MariaDB instance.
  3. MariaDB Client installed on the EC2 instance.
  4. For GUI options, install DBeaver, or MySQL Workbench on a Windows/MacOS machine that has access to the VPC.
  5. Security groups that allow access from the Amazon EC2 instance to the RDS for MariaDB instance.
  6. Have appropriate permissions to modify IAM permissions and policies.
  7. Have appropriate permissions to modify and create EC2 and RDS instances.

Create an RDS for MariaDB instance with IAM authentication

To connect to the RDS for MariaDB database, you need to allow ingress from your workstation to the security group attached to your instance. For instructions on setting up an RDS for MariaDB instance, see Create RDS for MariaDB Instance. We recommend that you run this solution in a non-production or development account. If you’re running this in your own AWS account, there are associated charges for creating the RDS for MariaDB instance and the Amazon EC2 instance to log in to the database. The IAM authentication feature is an option you can enable while creating the database. We show you how to modify the existing instance to enable the feature later in the post.

To create a new RDS for MariaDB instance on the AWS Management Console with IAM authentication capabilities, you must choose an RDS for MariaDB instance on version 10.6.5 and above.

Next, select Password and IAM database authentication under Database authentication.

After you verify the configuration, choose Create database to create your new RDS for MariaDB database. For more information, refer to the documentation on creating a MariaDB DB instance.

Enable IAM database authentication for an existing RDS for MariaDB instance

If you have an existing RDS for MariaDB instance, IAM database authentication is disabled by default. To enable it, the existing RDS for MariaDB instance needs to be on version 10.6.5 and above. You can use the console, AWS Command Line Interface (AWS CLI), or Amazon RDS API to enable the feature.

To enable this on the console, you need to modify the instance that you created.

  1. On the Amazon RDS console, select your instance and choose Modify.
  2. In the Database authentication section, select Password and IAM database authentication.

You’re redirected to a new page, where you can confirm the changes and either apply the changes immediately or to apply them during a scheduled maintenance window.

  1. Select the option that best fits your workload requirements.
  2. Choose Modify DB instance.

It takes a few minutes for the changes to propagate to the instance. This action doesn’t restart the database. After the change has been propagated, you’re ready to use the IAM database authentication feature.

You can also perform this change from the AWS CLI. The following command enables IAM database authentication on the instance and applies the change immediately:

aws rds modify-db-instance --db-instance-identifier <instance identifier> --enable-iam-database-authentication --region <region> --apply-immediately

To learn more, refer to enabling and disabling IAM database authentication.

Connect via IAM authentication to your RDS for MariaDB instance

To connect to your RDS for MariaDB instance, you need to authorize an IAM user or role to connect. You can do this by attaching an IAM policy to the user or role.

Next, we show you an example of how to create an IAM user and use it to connect to the RDS for MariaDB instance. We use an Ubuntu 18.04 instance as our bastion host to connect to the database.

Create a MariaDB user for IAM authentication

When you create an RDS for MariaDB instance, you also specify an admin user name and password that allows you to log in to the instance. This user has elevated access to start defining users, permissions, and objects on the database instance. With this level of permissions on one user, it’s important to store the credentials in a secure place. You can use this admin user to create other RDS for MariaDB users in the database who require IAM authentication.

To create an RDS for MariaDB user that uses IAM authentication, log in to the instance and use the following command:

CREATE USER <db-user-name> IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS'

You can check if this user has been created through the following command:

SELECT * FROM mysql.user WHERE User='<db-user-name>'

At this point, you have created the user in the database. However, the user has no permissions and needs to be granted permissions in the database. You can use the GRANT or REVOKE commands in MariaDB to give the appropriate permissions to the user.

Create an IAM user

Next, we create an IAM user. You can use the console or AWS CLI to do so. In this post, we show you how to do this using the AWS CLI.

In the AWS CLI, you must have the appropriate permission to create an IAM user. You must use a user that has the CreateUser action allowed in their policy.

Create the IAM user by issuing the following command in the AWS CLI:

aws iam create-user --user-name <IAM user>

You can also create the access key for the IAM user through the AWS CLI using the following command:

aws iam create-access-key --user-name <IAM user>

Note the access key and secret access key; you need these to generate the appropriate token to access your database.

You can verify that the user was created via the IAM console by navigating to the Users page.

The following is an example with the user mydbuser created through the AWS CLI. You can verify this through the AWS CLI by running the following code:

aws iam list-users

You should see a resulting output JSON that looks similar to the following:

"Users": [
            {
                "Path": "/",
                "UserName": "mydbuser",
                "UserId": "********",
                "Arn": "arn:aws:iam::********:user/mydbuser",
                "CreateDate": "1970-01-01T00:00:00+00:00"
            }
]

Create an IAM policy

To create an IAM policy, complete the following steps:

  1. On the IAM console, under Access management, choose Policies.
  2. Choose Create policy.
  3. Specify the following statements in your policy:
    1. Effect – Specifying an Allow/Deny on the action
    2. Action – What action can be taken on the instance
    3. Resource – Which Amazon Resource Name (ARN) can the policy be affected on

The following example is a JSON policy that allows the user or role to connect to the MariaDB database:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "rds-db:connect"
            ],
            "Resource": [
                "arn:aws:rds-db:<region>:<account-id>:dbuser:<dbiResourceId>/<db-user-name>"
            ]
        }
    ]
}

To find the DbiResourceID, you can navigate to the Amazon RDS console and select your RDS for MariaDB instance. On the Configuration tab, you should see the resource ID as one of the parameters.

The parameter db-user-name is the name of the database user you created.

  1. After you create your policy, choose the policy to view its details and save the ARN to use in a later step.
  2. Attach the policy to the user to allow the user authorization to connect to the RDS for MariaDB database. You can do this by issuing the following AWS CLI command: 
    aws iam attach-user-policy --policy-arn <policy ARN> --user-name <IAM user name>

You can verify on the IAM Console that this has been attached directly to your IAM user.

Connect to your RDS for MariaDB instance

You can now utilize the policy and user you created in IAM to connect to your RDS for MariaDB instance. You need to generate a signed IAM authentication token to use as the authentication method to connect. To do so, run the following command in the AWS CLI to generate the token. You can also save the callback as an environment parameter if needed.

TOKEN="$(aws rds generate-db-auth-token --hostname <RDS hostname> --port 3306 --region <region> --username <db-user-name>)"

To view the $TOKEN parameter, simply use the echo $TOKEN command.

Using the MariaDB Client, use the token as the password parameter to log in to your MariaDB instance using the following command:

mariadb -h<host-name> -u<db-user-name> -p$TOKEN --ssl=1

You should now be logged in to the RDS for MariaDB instance through the user name you created. The following screenshot shows a similar flow.

Using IAM authentication with a GUI tool

You can also use a database GUI tool such as MySQL Workbench or DBeaver to connect to your RDS for MariaDB instance using IAM authentication. These tools can be running on an EC2 instance or on your workstation a with VPN connection to your VPC. We use DBeaver as an example.

First, you need to create a connection to the database. Next, enter in the appropriate parameters in the Connection Settings section. To set the password, you need to generate the $TOKEN parameter per the previous instructions and copy and paste the $TOKEN authentication string into the Password field.

Next, you need to set the SSL parameters. This enables the connection to use encryption. If you’re verifying server certificates, you need to enter the appropriate settings. In this example, we don’t verify the server certificate.

Now you’re ready to test your connection! You should see the following success message.

Clean up

There are charges associated with creating an RDS for MariaDB instance and the EC2 instance. There is no cost for using IAM. To clean up your resources, make sure that you perform the following steps:

  1. Terminate the EC2 instance as necessary.
  2. Delete the RDS for MariaDB instance as necessary.

Conclusion

In this post, we showed you how to set up an RDS for MariaDB instance and connect to the instance using IAM database authentication. Using IAM authentication helps you authenticate to your database via a centralized identity store. We hope that you can take advantage of this new feature in Amazon RDS for MariaDB for your workloads. As always, we welcome your feedback or comments.

About the Authors

Justin Lim is a Database Specialist Solutions Architect at Amazon Web Services based in Seattle. In his role, Justin focuses on helping startups accelerate their workloads using AWS native databases. Prior to joining AWS, Justin worked as an engineer and graduated from the University of Washington with his masters in Electrical Engineering.

Vijay Karumajji is a Database Specialist Solutions Architect at Amazon Web Services based in Dallas. Vijay specializes in MySQL on Amazon RDS and Amazon Aurora. He works with our customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.