Using DEFAULT in the postgresql_database resource doesn't behave intuitively

[Magnitus-]

Terraform Version

hashicorp/terraform:0.14.11

Affected Resource(s)

• postgresql_database

Terraform Configuration Files

terraform {
  required_providers {
    postgresql = {
      source = "cyrilgdn/postgresql"
      version = "= 1.13.0"
    }
  }
}

...

resource "postgresql_role" "eric" {
  name                      = "eric"
  password                  = "eric"
  login                     = true
  encrypted_password        = true
  superuser                 = false
  create_database           = false
  create_role               = false
  replication               = false
  bypass_row_level_security = false 
}

resource "postgresql_database" "eric" {
  name              = "eric"
  owner             = postgresql_role.eric.name
  connection_limit  = 100
  allow_connections = true
  tablespace_name   = "DEFAULT"
  is_template       = false
  template          = "DEFAULT"
  encoding          = "DEFAULT"
  lc_collate        = "DEFAULT"
  lc_ctype          = "DEFAULT"
}

Expected Behavior

When I run terraform apply after the initial one, it should not try to destroy and re-create the postgres database.

Additionally, the value of "template" should be "template0".

Actual Behavior

When I run terraform apply after the initial one, it tries to destroy and re-create the postgres database.

This is because the value "DEFAULT" is substituted by the actual default value in most fields and during future iterations, it doesn't safeguard against that change, thus leading Terraform to believe that those fields have changed and that the database should be re-created.

It doesn't do that for the "template" field, because it leaves it as "DEFAULT" instead of substituting for "template0".

It is probably better to explicitly input the defaults anyways to safeguard against the future, but the documentation says you can use "DEFAULT" and I'm pretty sure someone who uses it will be in for a shock.

Steps to Reproduce

Use the configuration above, run terraform apply and then terraform plan (or just terraform apply twice)

[cyrilgdn]

Hi @Magnitus- ,

Thanks for opening this issue. You're right, DEFAULT values don't behave correctly for some fields, here is the unexpected plan:

  # postgresql_database.eric must be replaced
-/+ resource "postgresql_database" "eric" {
      ~ encoding          = "UTF8" -> "DEFAULT" # forces replacement
      ~ id                = "eric" -> (known after apply)
      ~ lc_collate        = "en_US.utf8" -> "DEFAULT" # forces replacement
      ~ lc_ctype          = "en_US.utf8" -> "DEFAULT" # forces replacement
        name              = "eric"
      ~ tablespace_name   = "pg_default" -> "DEFAULT"
        # (5 unchanged attributes hidden)
    }

Plan: 1 to add, 0 to change, 1 to destroy.

(don't hesitate to add the terraform plan output when you create an issue BTW)

This is because the value "DEFAULT" is substituted by the actual default value in most fields and during future iterations, it doesn't safeguard against that change, thus leading Terraform to believe that those fields have changed and that the database should be re-created.

It doesn't do that for the "template" field, because it leaves it as "DEFAULT" instead of substituting for "template0".

It's not really true though, we don't substitute with the default value. I mean not the provider, it's Postgres which stores the real value of DEFAULT at the database creation. So when the provider reads the current state, it should check, if we ask for DEFAULT, if the current value is the default one.

It "works" for template as the provider doesn't refresh the Terraform state with the real database template value (it's historic and I don't know why)

It is probably better to explicitly input the defaults anyways to safeguard against the future

You're right, maybe we should deprecate the DEFAULT value and remove it from the documentation. DEFAULT is useful when you manually create a database but setting it in Terraform definition is a bit weird as if an admistrator changes defaults, Terraform will recreate every existing databases.

[Magnitus-]

Hi Cyril,

Thanks for opening this issue.

Thanks for maintaining this great tool.

(don't hesitate to add the terraform plan output when you create an issue BTW)

Will do in the future. Thanks.

It's not really true though, we don't substitute with the default value. I mean not the provider, it's Postgres which stores the real value of DEFAULT at the database creation. So when the provider reads the current state, it should check, if we ask for DEFAULT, if the current value is the default one.

Indeed. That would probably be the best solution in the event that DEFAULT continues to be supported by the provider.

I guess there is a maintenance burden should these defaults change in a future version of Postgres, though I would hazard a guess that they haven't changed for some time.

It "works" for template as the provider doesn't refresh the Terraform state with the real database template value (it's historic and I don't know why)

Interesting.

It is probably better to explicitly input the defaults anyways to safeguard against the future

You're right, maybe we should deprecate the DEFAULT value and remove it from the documentation. DEFAULT is useful when you manually create a database but setting it in Terraform definition is a bit weird as if an admistrator changes defaults, Terraform will recreate every existing databases.

I believe that would be the best course of action.

In theory, I guess it could break someone's existing Terraform orchestration. In practice, I don't believe someone would have used the DEFAULT value for anything of significance given the behavior.

[cyrilgdn]

I guess there is a maintenance burden should these defaults change in a future version of Postgres, though I would hazard a guess that they haven't changed for some time.

For template field indeed, but DEFAULT for fields like lc_collate, lc_type, tablespace_name can be changed by the user (it's actually the values of the chosen template database)

In theory, I guess it could break someone's existing Terraform orchestration. In practice, I don't believe someone would have used the DEFAULT value for anything of significance given the behavior.

I agree, I'll probably deprecate these values in the next version and remove them later.