postgresql_extension does not use schema in state id

[trombonax]

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version

$ terraform -v Terraform v1.4.6 on linux_amd64

• provider registry.terraform.io/cyrilgdn/postgresql v1.19.0

Affected Resource(s)

• postgresql_schema

Terraform Configuration Files

variable "postgres_database" {
  type        = map(any)
  description = "PostgreSQL Databases"
  default = {
    "test1" = {
      name             = "test1"
      owner            = "test1"
      connection_limit = "50"
      is_template      = false
      encoding         = "UTF8"
      lc_collate       = "C"
      lc_ctype         = "C"
      schema = {
        "test11" = {
          name  = "test11"
          owner = "test1"
          policy = {
            create            = true
            create_with_grant = false
            usage             = true
            usage_with_grant  = false
          }
        },
      }
      extension = {
        "pgstattuple-public" = {
          name           = "pgstattuple"
          version        = ""
          schema         = "public"
          create_cascade = true
          drop_cascade   = false
        },
        "pgstattuple-test11" = {
          name           = "pgstattuple"
          version        = ""
          schema         = "test11"
          create_cascade = true
          drop_cascade   = false
        },
      }
    },
  }
}

locals {
  postgresql_extension = flatten([
    for db, db_extension in var.postgres_database : [
      for extension, extension_data in db_extension.extension : {
        database       = db_extension.name
        name           = extension_data.name
        schema         = extension_data.schema
        version        = extension_data.version
        drop_cascade   = extension_data.drop_cascade
        create_cascade = extension_data.create_cascade
      }
    ]
  ])
}

resource "postgresql_extension" "this" {
  depends_on = [postgresql_schema.this]

  for_each = { for entry in local.postgresql_extension : "${entry.database}-${entry.name}-${entry.schema}" => entry }

  database       = each.value.database
  schema         = each.value.schema
  name           = each.value.name
  version        = each.value.version
  drop_cascade   = each.value.drop_cascade
  create_cascade = each.value.create_cascade
}

Debug Output

[Please provider a link to a GitHub Gist containing the complete debug output: https://www.terraform.io/docs/internals/debugging.html. Please do NOT paste the debug output in the issue; just paste a link to the Gist.](https://gist.github.com/trombonax/e9dd62c54b6936a77e80aece9c132429)

Panic Output

Expected Behavior

resource and state should not be changed

Actual Behavior

postgresql extension moving from schema to schema after each apply

Steps to Reproduce

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

1. do terraform apply many times
2. after each apply look for postgresql database extensions with "\dx"

[timtatt]

Hi @trombonax, my team has experienced the same issue. I believe it is more to do with the understanding of the schema attribute more than it needing to be in the state.

According to Postgres docs, the schema is only the location to install the extension's objects. There should only be one extension defined per db. I believe this is a problem in that you shouldn't be allowed to create a second extension in the same db with the same name (regardless of having a different schema). Please see my PR (#426) to address this by adding a check as part of the creation.