Select Page

“You’re Who?” IAM Database Authentication

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
Oracle DBA Skills

8 Things Every Beginner Oracle DBA Should Know

A checklist of eight critical skills and areas an entry-level Oracle DBA should be familiar with to succeed on the job.

Patrick Gates | November 30, 2016

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine over one hundred logins in the source server, you need to migrate them to the destination server. Wouldn’t it be awesome if we could automate the process?

JP Chen | October 1, 2015

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