Can not destroy postgresql_role after create

[boskiv]

Hi there,

I have a problem with destroying postgres role with database, created before.

Terraform Version

Terraform v0.11.7
+ provider.aws v1.17.0
+ provider.postgresql v0.1.1
+ provider.template v1.0.0

Affected Resource(s)

• postgresql_role

Terraform Configuration Files

provider "postgresql" {
  alias    = "rds"
  host     = "${var.rds_endpoint}"
  username = "${var.rds_master_username}"
  password = "${var.rds_master_password}"
}

resource "postgresql_role" "tenant" {
  provider        = "postgresql.rds"
  name            = "${local.tenant_name}"
  login           = true
  password        = "${local.tenant_name}"
  create_database = true
}

Debug Output

2018/05/09 10:32:39 [DEBUG] plugin: waiting for all plugin processes to complete...
Error: Error applying plan:

1 error(s) occurred:

* postgresql_role.tenant (destroy): 1 error(s) occurred:

* postgresql_role.tenant: Error deleting role: pq: permission denied to reassign objects

Expected Behavior

Role deleted, database deleted

Actual Behavior

Role and database still existing.

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply
2. terraform destroy

References

Same error noticed here but no answer

• provider/postgres grant role when creating database terraform#11452

[boskiv]

Here is a some notice: https://www.postgresql.org/message-id/1270219014.5640.7116.camel%40ebony

2. REASSIGN OWNED BY cannot be executed by the role that is being
reassigned. It throws
ERROR: permission denied to reassign objects

[boskiv]

Solve it temporary with:

resource "postgresql_role" "tenant" {
  provider            = "postgresql.rds"
  name                = "${local.tenant_name}"
  login               = true
  password            = "${local.tenant_name}"
  skip_reassign_owned = true
}

[galindro]

The proposed workaround didn't solved my problem

[gmattar]

Got the same problem today. The workaround didn't work for me too.

[tthayer]

I had to perform an apply to update the role and then the destroy worked.

[slocke716]

I've created a pull request to fix this. Please take a look when you get a chance.

[minhajuddin]

I had to manually remove the role from RDS and then clean up the state using terraform state rm postgresql_role.my_role

[viesti]

I had initially created the role without skip_reassign_owned = true and had to run apply to set the attribute, like tthayer mentioned, and then the destroy worked.