Terraform to manage AWS RDS PostgreSQL databases, users and owners

README.md

Terraform vs PostgreSQL RDS

Thanks to new resources (postgresql_default_priviledges) and some fixes Terraform can now add new databases and manage ownership / access to them.

This example creates new database and two users. You can use owner to create new tables and run migrations and user for normal read/write access to database.

main.tf

resource "random_id" "password-owner" {
  byte_length = 12
}

resource "random_id" "password-user" {
  byte_length = 12
}

variable "db" {
  default = "my-db"
}

resource "postgresql_role" "user" {
  name            = "${var.db}_user"
  password        = "${random_id.password-user.hex}"
  login           = true
  create_database = "false"
}

resource "postgresql_role" "owner" {
  name            = "${var.db}_owner"
  password        = "${random_id.password-owner.hex}"
  login           = true
  create_database = "false"
}

resource "postgresql_database" "db" {
  name       = "${var.db}"
  owner      = "rdsadmin"
  lc_collate = "en_US.UTF-8"
  lc_ctype   = "en_US.UTF-8"
  encoding   = "UTF8"
}

resource "postgresql_default_privileges" "priv-table-for-user" {
  database    = "${var.db}"
  owner       = "${var.db}_owner"
  role        = "${var.db}_user"
  schema      = "public"
  object_type = "table"
  privileges  = ["ALL"]
  depends_on  = ["postgresql_database.db", "postgresql_role.owner", "postgresql_role.user"]
}

resource "postgresql_default_privileges" "priv-sequence-for-user" {
  database    = "${var.db}"
  owner       = "${var.db}_owner"
  role        = "${var.db}_user"
  schema      = "public"
  object_type = "sequence"
  privileges  = ["ALL"]
  depends_on  = ["postgresql_database.db", "postgresql_role.owner", "postgresql_role.user"]
}

resource "postgresql_default_privileges" "priv-table-owner" {
  database    = "${var.db}"
  owner       = "rdsadmin"
  role        = "${var.db}_owner"
  schema      = "public"
  object_type = "table"
  privileges  = ["ALL"]
  depends_on  = ["postgresql_database.db", "postgresql_role.owner"]
}

resource "postgresql_default_privileges" "priv-sequence-owner" {
  database    = "${var.db}"
  owner       = "rdsadmin"
  role        = "${var.db}_owner"
  schema      = "public"
  object_type = "sequence"
  privileges  = ["ALL"]
  depends_on  = ["postgresql_database.db", "postgresql_role.owner"]
}

output "user_pass" {
  sensitive = true
  value     = "${random_id.password-user.hex}"
}

output "owner_pass" {
  sensitive = true
  value     = "${random_id.password-owner.hex}"
}

terraform.tf

provider "postgresql" {
  host     = "10.1.1.13"
  port     = 5432
  database = "postgres"
  username = "rdsadmin"
  password = "dupa.8"
  connect_timeout = 120
  superuser       = false
}