Configure Postgres application users with Terraform for RDS

Question

Terraform allows you to define Postgres master user and password with the options username and password. But there is no option to set up an application postgres user, how would you do that?

Answer 1

The AWS RDS resource is only used for creating/updating/deleting the RDS resource itself using the AWS APIs.

To create users or databases on the RDS instance itself you'd either want to use another tool (such as psql - the official command line tool or a configuration management tool such as Ansible) or use Terraform's Postgresql provider.

Assuming you've already created your RDS instance you would then connect to the instance as the master user and then create the application user with something like this:

provider "postgresql" {
  host = "postgres_server_ip1"
  username = "postgres_user"
  password = "postgres_password"
}

resource "postgresql_role" "application_role" {
  name = "application"
  login = true
  password = "application-password"
  encrypted = true
}

Answer 2

The above two answers requires the host that runs the terraform has direct access to the RDS database, and usually you do not. I propose to code what you need to do in a lambda function (optionally with secrets manager for retrieving the master password):

resource "aws_lambda_function" "terraform_lambda_func" {
  filename      = "${path.module}/lambda_function/lambda_function.zip"
  ...
}

and then use the following data source (example) to call the lambda function.

data "aws_lambda_invocation" "create_app_user" {
  function_name = aws_lambda_function.terraform_lambda_func.function_name
  
  input = <<-JSON
    {
      "step": "create_app_user"
    }
  JSON
    
  depends_on = [aws_lambda_function.terraform_lambda_func]
  provider = aws.primary
}

This solution id generic. It can do what a lambda function can do with AWS API can do, which is basically limitless.

Answer 3

addition to @ydaetskcoR answer, here is the full example for RDS PostgreSQL;

provider "postgresql" {
  scheme    = "awspostgres"
  host      = "db.domain.name"
  port      = "5432"
  username  = "db_username"
  password  = "db_password"
  superuser = false
}


resource "postgresql_role" "new_db_role" {
    name                = "new_db_role"
    login               = true
    password            = "db_password"
    encrypted_password  = true
}

resource "postgresql_database" "new_db" {
  name              = "new_db"
  owner             = postgresql_role.new_db_role.name
  template          = "template0"
  lc_collate        = "C"
  connection_limit  = -1
  allow_connections = true
}