AWS RDS IAM Authentication with Terraform

Question

I am trying to set up my infrastructure properly with no passwords or keys laying around. AWS RDS has an option to do so, by enabling users(applications) to authenticate with generated tokens.

However, in the documentation, one of the steps(this one) requires running a query in the Postgres database to create an user and grant him specific permissions:

CREATE USER test_rds WITH LOGIN;
GRANT rds_iam TO test_rds;

I want to provision entire stack with Terraform. I have looked up some "hacks" to run the query (here) after RDS instantiation by either using:

resource "null_resource" "db_setup" {
  depends_on = ["aws_db_instance.your_database_instance",   "aws_security_group.sg_allowing_external_access"]

    provisioner "local-exec" {
 // run shell commands to manually psql into the db

or:

resource "aws_instance" "web" {


  provisioner "remote-exec" {
    inline = [
   // run shell commands to manually psql into the db

but both of them require creating master-password and somehow delivering it inside the "scripts".

Is it possible to do that with Terraform cleanly, with no hardcoded passwords getting passed around?

I would love to provision the database and enable only specific EC2/ECS instances with correct permissions to access it, without any passwords in my git repository.

Answer 1

Once you enable IAM authentication for an RDS database user/role, you are no longer able to use password based authentication for that user/role.

This means you can either use a less secure password or even just generate a random password (using the random_id resource) that you use to set the master password and first use to authenticate so that you can grant the rds_iam permissions to the master user and any other users you create.

While this password will end up in the state file (even if randomly generated), as mentioned, once the rds_iam grant has been applied then you won't be able to use this password to login to your database.

Answer 2

To provision an RDS database user ready for IAM authentication, the following terraform configuration can be added:

resource "postgresql_role" "db_user" {
  name  = "db_userx"
  login = true
  roles = ["rds_iam"]
}

The code above makes use of the cyrilgdn/postgresql provider. If you configure the provider using your aws_db_instance properties for its connection parameters as shown below, then the correct order of dependencies is enforced.

provider "postgresql" {
  host            = aws_db_instance.web.address
  port            = aws_db_instance.web.port
  database        = "postgres"
  username        = aws_db_instance.web.username
  password        = aws_db_instance.web.password
  sslmode         = "require"
  connect_timeout = 15
  superuser       = false # postgres user is not a true superuser in RDS
}

The RDS User Guide now includes the following:

For PostgreSQL, if the IAM role (rds_iam) is added to the master user, IAM authentication takes precedence over Password authentication so the master user has to log in as an IAM user