Select Page

“You’re Who?” IAM Database Authentication

Author: Karthik Reddy | | August 21, 2019

MySQL and PostgreSQL

Amazon’s AWS Identity and Access Management (IAM) database authentication tool can authenticate your DB instance. The process works with MySQL and PostgreSQL, and by using this type of authentication, you do not need passwords for connecting to any instance of a DB. Instead, you will be using an authentication token shared and managed by AWS.

What is an authentication token?

Upon request, Amazon’s RDS generates a string of unique characters/Hash values to make up an authentication token, the lifetime of which is limited to 15 minutes. User credentials are not required to be stored on the database, as authentication will be managed externally using IAM. Authentication tokens are generated by using AWS Signature Version 4.

IAM DB authentication benefits:

  • SSL Encryption: A Secure Sockets Layer (SSL) encrypts all the traffic going to and coming from the RDS database.
  • EC2 specific profiles: Profile credentials unique to your EC2 instances both power applications run on that platform and provide secure access to the database.
  • Central managed DB access by IAM: The programming also manages all your database resources, eliminating the necessity of personally accessing each and every instance.

IAM Database Authentication: Disabling and Enabling

On the RDS databases, IAM database authentication is automatically disabled by default. Re-enabling it requires the use of the AWS Management Console, the API or by AWS CLI.

Enabling the IAM authentication for RDS PostgreSQL DB instances requires setting the SSL value to 1; it won’t work when that setting is at 0. Further, any changes will prevent resetting the SSL value back to 0 when a PostgreSQL instance is enabled by IAM authentication.

For an existing DB instance, you can enable or disable the IAM authentication by following these steps:

1. Login to the AWS console

2. Open the Amazon RDS console.

3. Choose the navigation pane and click on the Databases.

4. Choose the RDS DB instance to be changed.

5. Choose Modify.

6. For IAM DB authentication in the database options, choose: “Enable” IAM DB authentication or “Disable.”

Then, click on Continue

7. On making the changes to apply these changes immediately, choose Apply immediately.

8. Select “Modify DB instance.”

Create Policies to Govern IAM Policy for IAM Database Access

To track any IAM user or role that is connected to your RDS DB instances, you should generate a governing IAM policy and ensure that it is attached to that IAM user or role.

Example:

{

"Version": "2019-05-20",

"Statement": [

{

"Effect": "Allow",

"Action": [

"rds-db:connect"

],

"Resource": [

"arn:aws:rds-DB:us-east-1:9874563210:dbuser:db-QWERTYUIOP/db_user"

]

}

]

}

Effect – Specifies ‘Allow’ to grant access to RDS DB instance. If not explicitly specified as an ‘allow’ access, then the access to RDS DB is denied by default.

Action – Specify rds-db:connect to allow the connections to the DB instance.

Resource – Select one database account/username used in this instance of the RDS DB that describes an Amazon Resource Name (ARN).

Once the IAM policy is generated to allow IAM database authentication, be sure to attach it to the IAM user or role.

IAM Authentication Also Creates Database Accounts

Once you’ve created user accounts in the RDS, the IAM database authentication ensures that you don’t need to assign passwords to those accounts, too. Removing users is a two-step process: first, remove them from the IAM and then use the DROP USER statement to also remove them from the database account.

PostgreSQL and IAM Authentication 

PostgreSQL allows you to use IAM authentication by connecting to the RDS DB instance and both creating and granting access to users in the rds_iam role.

CREATE USER db_user WITH LOGIN;

Then rds_iam TO db_user grants access.

MySQL and IAM Authentication

AWSAuthenticationPlugin handles authentication for MySQL.

CREATE your USER db_user to be IDENTIFIED WITH AWSAuthenticationPlugin AS ‘RDS’;

MySQL uses the phrase, ‘IDENTIFIED WITH’ to access the AWSAuthenticationPlugin for authentication of the database account (db_user).

Using IAM Authentication to Connect With to Your DB Instance

With RDS’s IAM database authentication, connecting to the RDS DB instance also generates an authentication token, a string of characters/hash value, to use instead of a password. Its life cycle is also only valid for 15 minutes and expired token connections will be denied.

You can also use the authentication token to Amazon RDS from other AWS services such as AWS Lambda. By using the authentication tokens, you are avoiding writing passwords into your code.

How to Generate an IAM Authentication Token

Example showing how to use the AWS CLI to get a signed authentication token:

aws rds generate-db-auth-token \
 
--hostname rdsmysql. QWERTYUIOP.us-west-1.rds.amazonaws.com \
 
--port 3306 \
 
--region us-west-1 \
 
--username db_user
 
--hostname – The hostname of the RDS DB instances.
 
--port – port number used for connecting to RDS DB instance.
 
--region – The AWS Region (DB instance is running region).
 
--username – The database access account name.

Limitations for IAM Database Authentication with MySQL

There are only 256 new connections per second permitted between MySQL RDS and IAM authentication, which drops to 10 connections per second if using a db.t2.micro instance.

Restrictions for Using PostgreSQL and IAM Database Authentication

The instance type and volume of workload on the RDS database will limit the maximum number of connections per second to the RDS database instances available to you.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS