AWS Database Sepciaist Exam – All about Database

When you configure your application on AWS EC2 to access the RDS PostgreSQL database, you may not want to do the authentication using the password file .pgpass for security reason. If that’s the case, you can authenticate to your DB instance using AWS Identity and Access Management (IAM) database authentication.

IAM database authentication works with MySQL and PostgreSQL. In this article, we’ll only be discussing PostgreSQL.

In order to use IAM database authentication, your PostgreSQL version needs to be 10.6 or higher, 9.6.11 or higher, and 9.5.15 or higher.

With this authentication method, you don’t need to use a password when you connect to a DB instance. Instead, you use an authentication token.

An authentication token is a unique string of characters that Amazon RDS generates on request. Authentication tokens are generated using AWS Signature Version 4.
Each token has a lifetime of 15 minutes.

I’ll take you through the whole procedure of configuring and testing the IAM database authentication.

Before starts the configuration, let’s start by checking out the benefits of using IAM database authentication:

Network traffic to and from the database is encrypted using Secure Sockets Layer (SSL).
You can use IAM to centrally manage access to your database resources, instead of managing access individually on each DB instance.
For applications running on Amazon EC2, you can use profile credentials specific to your EC2 instance to access your database instead of a password, for greater security.

0. Prerequsite

Assume you have an EC2 instance up and running and have access to the RDS postgreSQL instance via traditional database authentication:

[ec2-user@ip-172-31-44-91 ~]$ psql -h mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com -U postgres
Password for user postgres:
psql (12.4, server 12.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>

I’ll be using 3 hosts in the demonstration:

192-168-1-114: This is my management host (laptop) which has been configured with the access keys of my AWS management user.
ip-172-31-44-91: This is EC2 host which needs to connect to RDS postgreSQL with IAM DB authentication. It hasn’t been configured with the access keys yet.
mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com: This is the endpoint of RDS postgreSQL, it is not public accessible. It can only be accessed from the EC2 host.

Now, let’s get started:

1. Enabling IAM Database Authentication on RDS instance

You can do it via AWS console or the AWS CLI. Using AWS console is very straightforward so I will mainly focus on using AWS CLI in this article.

using AWS console

The operation in AWS console is very straightforward. First choose RDS from the services menu, click on the RDS database you’d like to connect to, and look at the configuration tab of the database instance. Check if IAM db authentication has already been enabled. If not, modify the DB instance by clicking on the upper right corner button “Modify”.

Note if you choose the apply the change immediately, a brief outage would occur.

using AWS CLI:

First check the RDS db instance configuration details and look for the value of the element name IAMDatabaseAuthenticationEnabled.

The elements in the output might be too many to search for the one we’re interested. You can specify the attributes using the --query parameter. For example, I limit the number of elements in the output to DBInstanceIdentifier,DBInstanceArn,DBiResourceId,Engine,IAMDatabaseAuthenticationEnabled:

192-168-1-114:~ suya$ aws rds describe-db-instances \
--db-instance-identifier mypg \
--query 'DBInstances[*].[Endpoint,DBInstanceIdentifier,DBInstanceArn,DbiResourceId,Engine,IAMDatabaseAuthenticationEnabled]'

Output:

[
    [
        {
            "Address": "mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com",
            "Port": 5432,
            "HostedZoneId": "Z32T0VRHXEXS0V"
        },
        "mypg",
        "arn:aws:rds:ap-southeast-2:125636162990:db:mypg",
        "db-O3ADPDDPG6LQSOFEALKV6AFUL4",
        "postgres",
        true
    ]
]

The output above shows that the IAMDatabaseAuthenticationEnabled is false, which means it’s not enabled.

To enable it, run the rds modify-db-instance command with option --enable-iam-database-authentication.

192-168-1-114:~ suya$ aws rds modify-db-instance --db-instance-identifier mypg \
--apply-immediately \
--enable-iam-database-authentication

Note: “mypg” is my RDS DB instance identifier.

2. Creating a database account using IAM authentication.

With IAM database authentication, you don’t need to assign database passwords to the user accounts you create.

If you remove an IAM user that is mapped to a database account, you should also remove the database account with the DROP USER statement.

Connect to the DB instance, create database users, and then
grant them the rds_iam role as shown below:

[ec2-user@ip-172-31-44-91 ~]$ psql -h mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com -U postgres postgres

Password for user postgres:
psql (11.5, server 12.3)
WARNING: psql major version 11, server major version 12.
Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> CREATE USER app_user;
CREATE ROLE

postgres=> GRANT rds_iam TO app_user;
GRANT ROLE
postgres=>

3. Creating an IAM Policy and Attach it to an IAM role

Creating an IAM Policy

Create the IAM policy document policy_iam_db_auth.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "rds-db:connect"
            ],
            "Resource": [
                "arn:aws:rds:ap-southeast-2:125677710779:dbuser:db-O3ADPDDPG6LQSOFEALKV6AFUL4/app_user"
            ]
        }
    ]
}

Note:

ap-southeast-2 is the region where my RDS instance located.

125677710779 is my AWS account id.

WGEC7XM2EFC3YNL74OIF7BMAME is the my RDS instance resource id.

app_user is the PostgreSQL database user just created.

Run the aws iam create-policy command to create the IAM policy policy_iam_db_auth.

192-168-1-114:~ suya$ aws iam create-policy \
--policy-name policy_iam_db_auth \
--policy-document file://policy_iam_db_auth.json

{
    "Policy": {
        "PolicyName": "policy_iam_db_auth",
        "PolicyId": "ANPAR2QD7WWXJ2NQ5WY7Y",
        "Arn": "arn:aws:iam::125636162990:policy/policy_iam_db_auth",
        "Path": "/",
        "DefaultVersionId": "v1",
        "AttachmentCount": 0,
        "PermissionsBoundaryUsageCount": 0,
        "IsAttachable": true,
        "CreateDate": "2020-10-01T02:52:06+00:00",
        "UpdateDate": "2020-10-01T02:52:06+00:00"
    }
}

Creating an IAM user

Run command aws iam create-user to create the IAM user user_iam_db_pg. The IAM user has access to only those databases that the database user does.

192-168-1-114:~ suya$ aws iam create-user \
--user-name user_iam_db_pg

{
    "User": {
        "Path": "/",
        "UserName": "user_iam_db_pg",
        "UserId": "AIDAR2QD7WWXKI5U3U7RP",
        "Arn": "arn:aws:iam::125636162990:user/user_iam_db_pg",
        "CreateDate": "2020-10-01T03:15:59+00:00"
    }
}

Attaching the IAM Policy to the IAM user

192-168-1-114:~ suya$ aws iam attach-user-policy \
--policy-arn arn:aws:iam::125636162990:policy/policy_iam_db_auth \
--user-name user_iam_db_pg

4. Generating temporary token

By using a token, you can avoid placing a password in your code. Run command aws rds generate-db-auth-token on the host with the proper credentials configured. It can be the EC2 host you’d like to connect to the RDS database, or a different management host.

[A] Generating temporary token on the same EC2 host

if your EC2 host has been configured with programmatic user credentials which allows you to access AWS services from command line via AWS CLI tool. You can run command aws rds generate-db-auth-token on the EC2 host directly as shown below. Otherwise, please go to Step 4B.

[ec2-user@ip-172-31-44-91 ~]$ export RDSHOST="mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com"
[ec2-user@ip-172-31-44-91 ~]$ export PGPASSWORD="$(aws rds generate-db-auth-token \
--hostname $RDSHOST \
--port 5432 \
--region ap-southeast-2 \
--username app_user)"

Note

The user credential configured on EC2 host must have appropriate permissions granted. Otherwise, even though you are able to generate the temporary token successfully in this step, you may encounter error when trying to connect to the database using psql:
- “psql: error: could not connect to server: FATAL: PAM authentication failed for user “app_user””
For troubleshooting purpose, you may grant AdministratorAccess to the user whose credential is configured on EC2 host to rule out this possibility.

[B] Generating temporary token on a different management host:

Go to the management host with proper credentials and run aws rds generate-db-auth-token command to generate the temporary token.

192-168-1-114:~ suya$ export RDSHOST="mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com"

192-168-1-114:~ suya$ aws rds generate-db-auth-token \
--hostname $RDSHOST \
--port 5432 \
--region ap-southeast-2 \
--username app_user

mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com:5432/?Action=connect&DBUser=app_user&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAR2QD7WWXCURPFSLL%2F20201001%2Fap-southeast-2%2Frds-db%2Faws4_request&X-Amz-Date=20201001T034124Z&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Signature=d4a48085b7e3f4e9926edfdb6d792be3d37a875f6b4008339d2d2291af643540

Go to the EC2 host which requires the DB connection and paste the temporary token to set OS variable $PGPASSWORD:

[ec2-user@ip-172-31-44-91 ~]$ export RDSHOST="mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com"

[ec2-user@ip-172-31-44-91 ~]$ export PGPASSWORD="mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com:5432/?Action=connect&DBUser=app_user&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAR2QD7WWXCURPFSLL%2F20201001%2Fap-southeast-2%2Frds-db%2Faws4_request&X-Amz-Date=20201001T032622Z&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Signature=d7bca2217b7a0dc572638e063de7c50c10e3b23e4b2245f9b4253237280421e1"

5. Test connection: Using IAM DB Authentication

After you have a signed IAM authentication token, you can connect to an Amazon RDS DB instance using this token.

[ec2-user@ip-172-31-44-91 ~]$ psql "host=$RDSHOST port=5432 sslmode=require dbname=postgres user=app_user"

psql (12.4, server 12.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>

Now, the IAM DB authentication is configured successfully and we can connect to the RDS PostgreSQL using the temporary token instead of a database password.

Something else…

There’re scenarios where this temporary token (expires after 15 minutes) might not be able to meet your requirement. For example, when the application uses connection pooling. I’ll cover this topic in another article.

References:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html

https://aws.amazon.com/blogs/database/using-iam-authentication-to-connect-with-pgadmin-amazon-aurora-postgresql-or-amazon-rds-for-postgresql/

	micheliafigo on High memory usage of a SQL sta…
	Venkatesh Raghavan on High memory usage of a SQL sta…
	micheliafigo on Oracle Database 11.2.0.4 Insta…
	N on Oracle Database 11.2.0.4 Insta…
	Oracle Database 11.2… on Enterprise Manager Cloud Contr…

Category: AWS Database Sepciaist Exam

Hands-on Lab: Access RDS PostgreSQL from EC2 instance without password – How to configure IAM DB Authentication