terraform destroy fails because it is unable to drop an extension

Terraform Version

v0.12.20

Affected Resource(s)

Please list the resources as a list, for example:

postgresql_schema
postgresql_extension

Terraform Configuration Files

// We can create using the azurerm_postgresql_database resource in the azurerm provider.
resource "azurerm_postgresql_database" "tfePersistData" {
  charset             = "UTF8"
  collation           = "English_United States.1252"
  name                = lower("${var.PREFIX}${var.ENVIRONMENT}_db")
  resource_group_name = azurerm_resource_group.tfePersistData.name
  server_name         = azurerm_postgresql_server.postgres00.name
}

// https://www.terraform.io/docs/providers/postgresql/index.html
// Setup our DB connection.
provider "postgresql" {
  host             = azurerm_postgresql_server.postgres00.fqdn
  port             = 5432
  username         = "${azurerm_postgresql_server.postgres00.login}@${azurerm_postgresql_server.postgres00.name}"
  password         = var.tfvars_postgres_dba_password
  database         = azurerm_postgresql_database.tfePersistData.name
  superuser        = false
  sslmode          = "require"
  connect_timeout  = 15
  expected_version = azurerm_postgresql_server.postgres00.version
}

// create the role for our DB user and DB
resource "postgresql_role" "tfe-role" {
  name               = lower("${var.PREFIX}${var.ENVIRONMENT}_role_admin")
  login              = true
  password           = var.tfvars_postgres_role_admin_password
  encrypted_password = true
}

resource "postgresql_schema" "rails" {
  name = "rails"
  depends_on = [azurerm_postgresql_database.tfePersistData]
}

resource "postgresql_schema" "vault" {
  name = "vault"
  depends_on = [azurerm_postgresql_database.tfePersistData]
}

resource "postgresql_schema" "registry" {
  name = "registry"
  depends_on = [azurerm_postgresql_database.tfePersistData]
}

resource "postgresql_extension" "hstore" {
  name   = "hstore"
  schema = "rails"
  depends_on = [postgresql_schema.rails]
}

resource "postgresql_extension" "uuid-ossp" {
  name   = "uuid-ossp"
  schema = "rails"
  depends_on = [postgresql_schema.rails]
}

resource "postgresql_extension" "citext" {
  name   = "citext"
  schema = "registry"
  depends_on = [postgresql_schema.registry]
}

Debug Output

https://gist.github.com/ausfestivus/6512bc147daf61259cdd5cf75789a8ab

Panic Output

No panic.

Expected Behavior

Database and Database server should have been destroyed.

Actual Behavior

Database and Database server are not destroyed.

Steps to Reproduce

create a postgres DB server using the AzureRM provider.
create a postgres DB using the AzureRM provider.
use the postgres provider to apply extensions and create a schema.
put data into the database.
run a terraform delete

Important Factoids

If you run the delete BEFORE you place any data in the database, the delete operation will succeed.

References

There are several issues in the postgres-provider provider that may be tangentially related. Couldnt find any clear existing issues that seemed similar enough.

👋 HashiCorp support engineer here. I was asked to look into this problem and the following is my take on it. If I have missed anything or can offer any assistance on tracking down a better solution / workaround (than two-stage delete or more resources) just let me know.

I've worked with the configuration some more and have determined that a single destroy operation here can't be handled in Terraform currently. This has to do with Terraform being unaware of information that has happened post-apply which changes the dependencies between the resources. Even if Terraform was aware, it would not be able to resolve the logic behind what could be done to work around it.

To use the rails schema and hstore extension resources as examples:

The hstore extension is dependent on the rails schema. hstore has a schema argument that can be set to postgresql_schema.rails.name to eliminate the need for the depends_on.

Because of this dependency, the rails schema is created first, then the hstore extension is created.

Outside of Terraform, the schema (set schema rails) and the extension (attr hstore) are used to create a table and data. At this point, a dependency has been formed that Terraform is unaware of. Terraform will try to remove the extension first because it is dependent on the schema (delete in reverse order of the create). Ignoring anything pertaining to Terraform, this operation won't succeed because of the table.

So in addition to the graph edge in the configuration from the extension to the schema, we have unaccounted for edges from the table to the schema and the extension. Since Terraform does not control the table, it cannot remove it, and this means the extension and schema cannot be removed.

Also, once these items are created, postgresql allows for the deletion of the schema without any changes required from Terraform. That is, the initial creation contains additional information, but if the schema is deleted later then that is OK. Terraform can't perform this action without modification to the configuration (removal of either the depends_on or the postgresql_schema.rails.name).

Practically speaking, as mentioned, the schema can be destroyed. Either the items can be dropped or a cascade drop can be configured on the resource. In fact, a terraform destroy -target postgresql_schema.rails will succeed, and this is likely the easiest thing to do.

Once the schema and data are gone, a destroy without any additional arguments will succeed. Unfortunately, this is going to be the case concerning items created out-of-band that interfere with the deletion of resources. It is a known problem, and is a theoretical / design problem rather than a bug. Future improvements in Terraform may include the automatic detection of situations where two-stage applies may work, and do them both in one run.

Lastly, one way to "fix" this issue would be to have the drop of an extension delete those dependent items. However this makes the resource operate far outside of its intended scope, and makes it quite dangerous, as you'd be deleting things that would not have been presented in the plan. Another way to address this is to have a feature request filed to support more resources in the provider - specifically tables. If Terraform can become aware of the table and can drop it and can form an appropriate dependency connection between the table, extension, and schema, then it could handle the removal of them.

To conclude, I think going forward the simplest solution is to perform a two-stage destroy, with the first one targeting the schema(s), then follow with a full destroy. This is due to a limitation that cannot be worked around, other than to introduce additional resources to account for those created outside of Terraform.

hashicorp / terraform-provider-postgresql