How to destroy Postgres databases owned by other users in RDS using Terraform?

Question

I managed to nicely get Terraform creating databases and roles in an RDS Postgres database but due to the stripped down permissions of the rds_superuser I can't see an easy way to destroy the created databases that are owned by another user.

Using the following config:

resource "postgresql_role" "app" {
  name                = "app"
  login               = true
  password            = "foo"
  skip_reassign_owned = true
}

resource "postgresql_database" "database" {
  name  = "app_database"
  owner = "${postgresql_role.app.name}"
}

(For reference the skip_reassign_owned is required because the rds_superuser group doesn't get the necessary permissions to reassign ownership)

leads to this error:

Error applying plan:

1 error(s) occurred:

* postgresql_database.database (destroy): 1 error(s) occurred:

* postgresql_database.database: Error dropping database: pq: must be owner of database debug_db1

Terraform does not automatically rollback in the face of errors.
Instead, your Terraform state file has been partially updated with
any resources that successfully completed. Please address the error
above and apply again to incrementally change your infrastructure.

Using local-exec provisioners I was able to grant the role that owned the database to the admin user and the application user:

resource "aws_db_instance" "database" {
  ...
}

provider "postgresql" {
  host            = "${aws_db_instance.database.address}"
  port            = 5432
  username        = "myadminuser"
  password        = "adminpassword"
  sslmode         = "require"
  connect_timeout = 15
}

resource "postgresql_role" "app" {
  name                = "app"
  login               = true
  password            = "apppassword"
  skip_reassign_owned = true
}

resource "postgresql_role" "group" {
  name                = "${postgresql_role.app.name}_group"
  skip_reassign_owned = true

  provisioner "local-exec" {
    command = "PGPASSWORD=adminpassword psql -h ${aws_db_instance.database.address} -U myadminuser postgres -c 'GRANT ${self.name} TO myadminuser, ${postgresql_role.app.name};'"
  }
}

resource "postgresql_database" "database" {
  name  = "mydatabase"
  owner = "${postgresql_role.group.name}"
}

which seems to work compared to setting ownership only for the app user. I do wonder if there's a better way I can do this without having to shell out in a local-exec though?

ydaetskcoR ydaetskcoR · Accepted Answer · 2018-04-12T13:14:06

After raising this question I managed to raise a pull request with the fix that was released in in version 0.1.1 of the Postgresql provider so now works fine in the latest release of the provider.

How to destroy Postgres databases owned by other users in RDS using Terraform?

1 Answers