1.19.0 replaces postgresql_grant all the time

[alhroub]

Terraform Version

Terraform v1.5.2.

Affected Resource(s)

• postgresql_grant

Terraform Configuration Files

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

provider "postgresql" {
  host            = "localhost"
  port            = 5432
  database        = "postgres"
  username        = "postgres"
  sslmode         = "disable"
  connect_timeout = 15
}

resource "postgresql_grant" "grant_public" {
  database    = "db1"
  role        = "public"
  schema      = "public"
  object_type = "schema"
  privileges  = ["USAGE"]
}

Expected Behavior

The resource should be updated in-place.

Actual Behavior

The resource is being replaced (destroyed then created).

Steps to Reproduce

Tested on PostgreSQL version 12.

1. Create a brand new database called db1.
2. Create a new main.tf file containing the snippet above. Modify the provider block to point to your local/test PostgreSQL instance.
3. Run init/plan/apply
4. connect to the database with psql and change the privileges on the public schema. For example, run

GRANT ALL ON SCHEMA public TO public;

5. Run plan/apply again. The apply will look like this (notice that the resource is being re-created):

postgresql_grant.grant_public: Refreshing state... [id=public_db1_public_schema]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
-/+ destroy and then create replacement

Terraform will perform the following actions:

  # postgresql_grant.grant_public must be replaced
-/+ resource "postgresql_grant" "grant_public" {
      ~ id                = "public_db1_public_schema" -> (known after apply)
      ~ privileges        = [ # forces replacement
          - "CREATE",
            # (1 unchanged element hidden)
        ]
        # (5 unchanged attributes hidden)
    }

Plan: 1 to add, 0 to change, 1 to destroy.
postgresql_grant.grant_public: Destroying... [id=public_db1_public_schema]
postgresql_grant.grant_public: Destruction complete after 0s
postgresql_grant.grant_public: Creating...
postgresql_grant.grant_public: Creation complete after 0s [id=public_db1_public_schema]

Apply complete! Resources: 1 added, 0 changed, 1 destroyed.

Description of the issue

It looks like with version 1.19.0 (and more specifically this PR https://github.com/cyrilgdn/terraform-provider-postgresql/pull/135), the postgresql_grant resource gets re-created when there is a change. Replacing the resource is not a good idea because the "destroy/create" operations are completely separate. i.e. they are not atomic which means (given the example in the "Steps to Reproduce" section above) for a short moment between the 2 operations the public role loses access to the public schema. If for any reason Terraform fails midway or it gets interrupted, users will end up not being able to access the objects in the public schema. This is what happens in the PostgreSQL log:

2023-07-11 14:50:05.989 UTC [1673] LOG:  statement: BEGIN READ WRITE
2023-07-11 14:50:06.000 UTC [1673] LOG:  statement: REVOKE ALL PRIVILEGES ON SCHEMA "public" FROM "public"
2023-07-11 14:50:06.001 UTC [1673] LOG:  statement: COMMIT
2023-07-11 14:50:06.033 UTC [1675] LOG:  statement: BEGIN READ WRITE
2023-07-11 14:50:06.043 UTC [1675] LOG:  statement: REVOKE ALL PRIVILEGES ON SCHEMA "public" FROM "public"
2023-07-11 14:50:06.044 UTC [1675] LOG:  statement: GRANT USAGE ON SCHEMA "public" TO "public"
2023-07-11 14:50:06.045 UTC [1675] LOG:  statement: COMMIT

As you can see they are done in 2 different transactions.

This gets even worse if there is a create_before_destroy lifecycle defined, as the resource will be replaced in reverse order! It gets created (privileges are granted) and then destroyed (revoking everything on the public schema from the public role). The apply looks like this:

postgresql_grant.grant_public: Refreshing state... [id=public_db1_public_schema]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
+/- create replacement and then destroy

Terraform will perform the following actions:

  # postgresql_grant.grant_public must be replaced
+/- resource "postgresql_grant" "grant_public" {
      ~ id                = "public_db1_public_schema" -> (known after apply)
      ~ privileges        = [ # forces replacement
          - "CREATE",
            # (1 unchanged element hidden)
        ]
        # (5 unchanged attributes hidden)
    }

Plan: 1 to add, 0 to change, 1 to destroy.
postgresql_grant.grant_public: Creating...
postgresql_grant.grant_public: Creation complete after 0s [id=public_db1_public_schema]
postgresql_grant.grant_public (deposed object 0a58f931): Destroying... [id=public_db1_public_schema]
postgresql_grant.grant_public: Destruction complete after 0s

Apply complete! Resources: 1 added, 0 changed, 1 destroyed.

And here is what you see in the PostgreSQL log (notice REVOKE ALL... is being executed last):

2023-07-11 14:52:14.044 UTC [1700] LOG:  statement: REVOKE ALL PRIVILEGES ON SCHEMA "public" FROM "public"
2023-07-11 14:52:14.045 UTC [1700] LOG:  statement: GRANT USAGE ON SCHEMA "public" TO "public"
2023-07-11 14:52:14.046 UTC [1700] LOG:  statement: COMMIT
2023-07-11 14:52:14.081 UTC [1702] LOG:  statement: BEGIN READ WRITE
2023-07-11 14:52:14.091 UTC [1702] LOG:  statement: REVOKE ALL PRIVILEGES ON SCHEMA "public" FROM "public"
2023-07-11 14:52:14.091 UTC [1702] LOG:  statement: COMMIT

This caused an outage for us recently! The behaviour was different with version 1.18.0. The resource was updated in-place. If we pin the 1.18.0 version we get this apply output:

postgresql_grant.grant_public: Refreshing state... [id=public_db1_public_schema]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  ~ update in-place

Terraform will perform the following actions:

  # postgresql_grant.grant_public will be updated in-place
  ~ resource "postgresql_grant" "grant_public" {
        id                = "public_db1_public_schema"
      ~ privileges        = [
          - "CREATE",
            # (1 unchanged element hidden)
        ]
        # (5 unchanged attributes hidden)
    }

Plan: 0 to add, 1 to change, 0 to destroy.
postgresql_grant.grant_public: Modifying... [id=public_db1_public_schema]
postgresql_grant.grant_public: Modifications complete after 0s [id=public_db1_public_schema]

Apply complete! Resources: 0 added, 1 changed, 0 destroyed.

and you see this in the PostgreSQL log:

2023-07-11 14:53:52.635 UTC [1734] LOG:  statement: BEGIN READ WRITE
2023-07-11 14:53:52.644 UTC [1734] LOG:  statement: REVOKE ALL PRIVILEGES ON SCHEMA "public" FROM "public"
2023-07-11 14:53:52.645 UTC [1734] LOG:  statement: GRANT USAGE ON SCHEMA "public" TO "public"
2023-07-11 14:53:52.646 UTC [1734] LOG:  statement: COMMIT

The old behaviour should be restored where the resource gets updated in-place as the revoke and grant are done in the same transaction as shown in the log above and as noted here: https://github.com/cyrilgdn/terraform-provider-postgresql/blob/v1.19.0/postgresql/resource_postgresql_grant.go#L188-L189