Access AWS RDS without Password - use IAM

Backstory

Part of setting up or debugging a database requires to get into the db itself. In general we use password to access into it. But we faced a situation where the db authentication was set to use AWS Secrets Manager secret and we did not have access to it. So, if you are in a situation like we were, or you are a DevOps who just does not feel comfortable sharing the password directly to the developers, you can use IAM based access to AWS RDS DB. Let's see how we can do it.

Step1: Create RDS DB

While creating RDS Database, under Choose a database creation method select Full Configuration. I will create a postgres db today. You can see the list of supported regions and dbs for IAM auth from this list.

I am keeping the default settings, but go ahead as per your need. Under Connectivity section, check Public Access to `Yes' since we will access the db from a local machine. If the db will be accessed from by an ec2 from the same VPC, then the db can be kept as private. But for our case, we need it public.

Now we need a security group (firewall rule to allow db port from any machine). Go to ec2 -> Security Groups -> Create Security Group and allow the db port 5432 to be accessed by any IP -

And under the same Connectivity section, attach the Security Group to the db.

Scroll down and Under Database authentication section and select Password and IAM database authentication option -

That's about it. Now We can just create the database.

Wait a while for a it to start.

Step2: Setup IAM role/policy

First get the ARN and resource ID of the databse, click on the database and under Configuration tab you will find the ARN and resource ID of the database -

Since we are doing this for developers (users), we will create a policy and attach to the user. Let's create a an IAM policy.

Please mind the db db-resource-id and db-username section, it's written as arn:aws:rds-db:<region>:<account-id>:dbuser:<db-resource-id>/<db-username> . In my case the db_user is postgres` . So the policy looks like this -

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "rds-db:connect"
      ],
      "Resource": [
        "arn:aws:rds-db:ap-southeast-1:980305500084:dbuser:db-GMICPKT7J4JMSEYSHVWUFEN7VQ/postgres"
      ]
    }
  ]
}

Now click next and name the policy, then create it. I gave it this name - rds-iam-access .

Now from iam -> Users select your user and click add permissions to add the policy.

Select the policy and click add permission to attach the policy to the user. And that's about it.

If you want to give access to other services such as to your bastion host or k8s pods, create a role selecting respecting service.

Step3: Connect with db using IAM

Prerequisite

Get the DB endpoint from the Connectivity and Security tab -

We need to allow the db user to support IAM auth first. So first time, we will login with password as usual (or using secrets manager if that's your case).

# psql "postgresql://<db-user>:<db-password>@<db-endpoint>:<port>/<db-name>"

psql "postgresql://postgres:[e-mail]:5432/postgres"

and run this -

#GRANT rds_iam TO <user>;

GRANT rds_iam TO postgres;

Generate token using the db endpoint from the cli. Run this from your terminal, change db endpoint, port and user as per your need -

# aws rds generate-db-auth-token --hostname Endpoint --port Port_number --username DB_username

# get token on terminal 
# aws rds generate-db-auth-token --hostname database-1.cdkgmeaiqn6n.ap-southeast-1.rds.amazonaws.com --port 5432 --username postgres

# get the token in an env (preferred)
TOKEN=$(aws rds generate-db-auth-token \
  --hostname database-1.cdkgmeaiqn6n.ap-southeast-1.rds.amazonaws.com \
  --port 5432 \
  --region ap-southeast-1 \
  --username postgres)

# echo $TOKEN

This returns a token that is valid for 15 minutes. Meaning you can connect to the DB within minutes, but it won't affect the db session duration.

Option 1: Use psql

Use psql tool to connect to the db from the terminal -

#psql "host=<rds-endpoint> port=5432 dbname=<db-name> user=<db-user> sslmode=require password=<IAM_AUTH_TOKEN>"

PGPASSWORD="$TOKEN" psql "postgresql://[e-mail]:5432/postgres?sslmode=require"

BAM! I'm in -

Now, some of you are not happy with terminal so ...

Option 2: Use GUI client (i.e. PgAdmin)

Simple, just like you used to access any other db, just paste the token in place of the password.

Go to Parameters tab and set Enable ssl to require -

Save it. Now use it. As simple as that. And just like that I'm in from the PgAdmin tool too -

Last thought

Remember, PgAdmin can't generate token. So, after a session times out you have to generate another token to get access to the db. A token is valid for only 15 minutes, that's the window to connect. But after the connection is established, it won't hamper the session, meaning, you can use a token to talk to the db forever.. until the session ends somehow.
It may seem annoying to some, but I see the secure feature here. I know the next time what I will do when a developer asks for db credentials, do you?