Skip to content

IAM access to PostgreSQL databases

For background information on the database choices, please read the RFC for RDBMS in the Golden Path.

IAM access to PostgreSQL

The iam_database_authentication_enabled flag is set to true in both the postgres_aurora and postgres_aurora_serverless module supplied as part of Golden Path. The AWS documentation explains more about the benefits and limitations when using IAM access to your database, please review this before continuing.

Future

This first version of the documentation will set up the database user and connect from a dedicated EC2 instance to show the step-by-step required to connect to your database. Connecting from localhost with IAM access will be covered later once #65 is done.

Prerequisites

  • A PostgreSQL database and a user capable of adding new users (as the one supplied by Golden Path setup)
  • EC2 instance - for demonstrating the IAM functionality only. This will be void once Connection to PostgreSQL from localhost is done
  • Create a EC2 instance in the correct VPC where your DB cluster is set up
    • Ubuntu is fine
    • Choose the VPC where your infrastructure is set up
    • Choose a public subnet to get a public IP address
  • Remember to create a keypair and download it
  • Go to the VPC where you created your EC2 instance and update the security group for the instance
    • Allow inbound SSH from your IP address
    • Allow outbound rules to all outbound
  • Update PostgreSQL cluster to allow connection from EC2: Actions > Set up EC2 connection
    • This will set up a security group that connects the EC2 instance with your RDS instance
    • Choose the EC2 instance you just created
  • SSH to the box:
    • ssh -i "YOUR-KEY-FILE.pem" ubuntu@X.Y.Z.A. Keep this connection open for the step by step below
    • Install PostgreSQL client: sudo apt-get install postgresql-client-14

Step by step

Enable IAM authentication

  • If the IAM DB authentication flag under cluster Configuration is set to Enabled: no action is needed
  • Otherwise: run terraform get -update and tf apply to enable IAM authentication

Connect to PostgreSQL

From the EC2 instance: Connect with the user set up by Golden Path, this user have rights to add another user and grant access

psql --host=YOUR-DATABASE-HTTP-ENDPOINT \
  --port=5432 \
  --username=db_username \
  --password \
  --dbname=postgres
  • Values for db_username and db_password are located in AWS Parameter Store
  • The default database name is postgres if nothing else is configured. Do not confuse with DB instance ID

Create IAM user

Create a new user for your database. In this case mydbuser will be used as an example, but the username should reflect the usage you intend for this user to have.

CREATE USER mydbuser WITH LOGIN;

Grant the special rds_iam role to the new user:

GRANT rds_iam TO mydbuser;

Grant access to database

You can grant different levels of access to the user, execute one of the following statements (or any other grants based on your requirement):

Full access to schema

GRANT USAGE ON SCHEMA schema_name TO mydbuser;

Only access to one table

GRANT SELECT ON table_name TO mydbuser;

Access to all tables

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO mydbuser;

Policy document

Given the following policy document in rds-connect-mypostgres-cluster-policy.json (update with the correct ARN for your user):

{
   "Version": "2012-10-17",
   "Statement": [
      {
         "Effect": "Allow",
         "Action": [
             "rds-db:connect"
         ],
         "Resource": [
             "arn:aws:rds-db:eu-west-1:[account-id]:dbuser:[DbiResourceId]/mydbuser"
         ]
      }
   ]
}

To find the DbiResourceId for your Resource:

aws rds describe-db-instances \
  --query "DBInstances[*].[DBInstanceIdentifier,DbiResourceId]"

Create the policy:

aws iam create-policy \
  --policy-name rds-connect-mypostgres-cluster-policy \
  --policy-document file://rds-connect-mypostgres-cluster-policy.json

Create role

Create a role and attach the following policy stored in assume-role-policy.json:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AssumeRolePolicy",
      "Effect": "Allow",
      "Principal": {"AWS": "[account-id]"},
      "Action": "sts:AssumeRole"
    }]
}

aws iam create-role \
  --role-name rds-assume-mydbuser-mypostgres-cluster \
  --assume-role-policy-document file://assume-role-policy.json

Attach the rds-connect-mypostgres-cluster-policy created to that role:

aws iam attach-role-policy \
  --policy-arn arn:aws:iam::[account-id]:policy/rds-connect-mypostgres-cluster-policy \
  --role-name rds-assume-mydbuser-mypostgres-cluster

Now there is a role that can be assumed at a later point. You can check that everything is in order by running:

aws sts assume-role \
  --role-arn "arn:aws:iam::[account-id]:role/rds-assume-mydbuser-mypostgres-cluster" \
  --role-session-name assume-mydbuser-mypostgres-cluster

Connect with IAM to PostgreSQL

Switch to your local machine where you have awscli set up and run the following:

export RDSHOST="YOUR-DATABASE-HTTP-ENDPOINT"

export PGPASSWORD="$(aws rds generate-db-auth-token \
--hostname $RDSHOST \
--port 5432 \
--region eu-west-1 \
--username mydbuser)"

echo $PGPASSWORD

Copy the PGPASSWORD for later usage.

Download the certificate for RDS

On the EC2 instance where you will connect to PostgreSQL, download the certificate:

curl -XGET https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem > rds-ca-2019-root.pem

Connect to RDS

Export PGPASSWORD from your local machine to the EC2 instance:

export PGPASSWORD='Password-copied-from-earlier-command'

and connect to PostgreSQL:

psql -h YOUR-DATABASE-HTTP-ENDPOINT \
  -p 5432 \
  "sslmode=verify-full sslrootcert=rds-ca-2019-root.pem dbname=postgres user=mydbuser"