Managing destruction of Postgres DBs in RDS

[tomelliff]

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}"
}

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?

[eranreshef]

@tomelliff I've been trying to apply same solution that you suggested as a temp workaround and from some unknown reason the role is granted only to the regular user and not to the admin user:

(local-exec): Executing: /bin/sh -c "PGPASSWORD=******* psql -h localhost -p 54236 -U postgres postgres -c 'grant eran2_group TO eran2, postgres ;'" (local-exec): GRANT ROLE

I than use pgAdmin to review db properties and see that my admin user (postgres) didn't get the "eran2_group" membership.
So I executed same command manually from shell and got:
PGPASSWORD=******** psql -h localhost -p 54236 -U postgres postgres -c 'grant eran2_group TO eran2, postgres ;' NOTICE: role "eran2" is already a member of role "eran2_group" GRANT ROLE
and after that postgres user did get the eran2_group membership.

any insights?

[tomelliff]

Yeah I noticed the same thing after posting it. I'm not sure what's wrong with using the local exec but the grant doesn't seem to get added at all when ran from it but running the same command locally does seem to work.

It doesn't seem to be a timing thing either because when I added another local exec provisioner running the same command I got a warning that the role already had that grant (can't remember the exact wording) but it didn't.

I'm not that experienced with Postgres though so I might be missing something stupid but it stumped me too.

For now I'm leaving my Terraform unable to destroy stuff and where necessary I'm locally running Terraform with the provisioner built from my branch to tear things down (or just removing the state for the database and role resources first as I'm going to tear down the RDS instance anyway).

[eranreshef]

I assume that the reason for the admin user not receiving the group membership is that this user is being used by terraform in an active connection so perhaps it can't be modified while the connection is active. makes sense?

[tomelliff]

Doesn't look that way because the connected user is added to the owner's role when creating the database. My pull request simply copies that block (including the deferred undo of that grant) for the database destroy too.

[honzasterba]

I have spent some time on this and discovered why this does not work.

GRANT app_role TO group_role;
NOTICE: role "group_role" is already a member of role "app_role"

It seems the postgresql provider adds the system user into the newly created role so he seems like a superuser, but removes it after the setup is finishied. Unfortunatelly the same thing does not happen when destroying.

[tomelliff]

@honzasterba I raised a pull request for that here: #18

[sean-]

Please try master and let us know if that has fixed your problem, @honzasterba / @tomelliff.

[tomelliff]

Just tried this now after building master and looks like it works as expected, thanks for the merge.