How to provision RDS postgres db users with AWS IAM auth using terraform?

Question

By checking this AWS blog: https://aws.amazon.com/premiumsupport/knowledge-center/users-connect-rds-iam/ I noticed that I need to create a DB user after login with the master username and password:

CREATE USER {dbusername} IDENTIFIED WITH AWSAuthenticationPlugin as 'RDS';

I can see terraform has mysql_user to provision mysql db users: https://www.terraform.io/docs/providers/mysql/r/user.html

However, I couldn't find postgres_user. Is there a way to provision postgres user with IAM auth?

brietsparks brietsparks · Accepted Answer · 2020-07-25T23:38:47

In Postgres, a user is called a "role". The Postgres docs say:

a role can be considered a "user", a "group", or both depending on how it is used

So, the TF resource to create is a postgresql_role

resource "postgresql_role" "my_replication_role" {
  name             = "replication_role"
  replication      = true
  login            = true
  connection_limit = 5
  password         = "md5c98cbfeb6a347a47eb8e96cfb4c4b890"
}

To enable IAM user to assume the role, follow the steps in the AWS docs.

From those instructions, you would end up with TF code looking something like:

module "db" {
  source = "terraform-aws-modules/rds/aws"
  // ...
}

provider "postgresql" {
 // ...
}

resource "postgresql_role" "pguser" {
  login    = true
  name     = var.pg_username
  password = var.pg_password
  roles = ["rds_iam"]
}

resource "aws_iam_user" "pguser" {
  name = var.pg_username
}

resource "aws_iam_user_policy" "pguser" {
  name = var.pg_username
  user = aws_iam_user.pguser.id
  policy = <<EOF
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "rds-db:connect"
      ],
      "Resource": [
        "arn:aws:rds-db:${var.region}:${data.aws_caller_identity.current.account_id}:dbuser:${module.db.this_db_instance_resource_id}/${var.pg_username}"
      ]
    }
  ]
}
EOF
}

How to provision RDS postgres db users with AWS IAM auth using terraform?

1 Answers