[Bug] terraform refresh gets stuck forever on postgresql_grant schema under certain conditions

[giner]

Terraform Version

Terraform v1.5.3
on linux_amd64
+ provider registry.terraform.io/cyrilgdn/postgresql v1.20.0
+ provider registry.terraform.io/hashicorp/aws v4.66.1
+ provider registry.terraform.io/hashicorp/random v3.5.1

Affected Resource(s)

• resource_postgresql_grant (only object_type schema is affected)

Terraform Configuration Files

provider "postgresql" {
  host      = "RDS_HOST_URI"
  port      = 5432
  database  = "postgres"
  username  = "postgres"
  password  = "SECRET"
  sslmode   = "require"
  superuser = false
  max_connections = 1
}

resource "postgresql_grant" "postgres_public_schema_revoke_all_from_public" { 
  database    = "postgres"
  schema      = "public"
  role        = "public"
  object_type = "schema"
  privileges  = []
}

Expected Behavior

• terraform refresh should not get stuck
• irrelevant to this particular issue, but I'd expect the provider to timeout not only when establishing a connection but also during a transaction

Actual Behavior

terraform refresh gets stuck at postgresql_grant schema in ~80% of runs

Steps to Reproduce

1. Create an RDS instance (PostgreSQL 14.7) (Note: I was not able to reproduce the issue on local machine)
2. Expose RDS instance to internet by means of AWS Load Balancer
3. Apply the configs

   terraform apply

4. And then run several times (--lock=false is to avoid need in unlocking every time it gets stuck, --parallelism=1 is to eliminate possible side effects of parallelism)

   terraform refresh --lock=false --parallelism=1

Important Factoids

1. I'm able to reproduce this issue quite reliably on RDS and I'm not able to reproduce this on local
2. The issue happens only when database for initial connection (configured in provider) is the same as the one postgresql_grant is applied to (postgres and postgres in this case, but this issue happens to any other database as long as both are the same), in this case connection to the database is reused
3. The issue happens only when the object_type is schema
4. The provider gets stuck at startTransaction() and more specifically at db.Begin() when a new transaction starts without previous transaction finished
5. With all preconditions mentioned above the provider gets stuck in around 80% of runs.

Workarounds

• Without modifying code of the provider one possible workaround is creating a dummy database to be used for initial connection (specified in provider configuration)

• Another one (with code modification) is to finish the previous transaction before starting a new one

  diff --git a/postgresql/resource_postgresql_grant.go b/postgresql/resource_postgresql_grant.go
  index e6cc531..4807ee3 100644
  --- a/postgresql/resource_postgresql_grant.go
  +++ b/postgresql/resource_postgresql_grant.go
  @@ -730,6 +730,8 @@ func checkRoleDBSchemaExists(client *Client, d *schema.ResourceData) (bool, erro
                  return false, nil
          }
  
  +       deferredRollback(txn)
  +
          pgSchema := d.Get("schema").(string)
  
          if !sliceContainsStr([]string{"database", "foreign_data_wrapper", "foreign_server"}, d.Get("object_type").(string)) && pgSchema != "" {

[pagalba-com]

+1, impossible to apply changes, refresh is always stuck, as there are many objects, thus at least one fails. aws rds Postgresql 14.7. I see MR already on the way, but blocked state, please speed up blocking workflow tests.

Terraform v1.3.10 hashicorp/aws 4.67.0 for linux_amd64 cyrilgdn/postgresql 1.21.0 for linux_amd64

https://github.com/cyrilgdn/terraform-provider-postgresql/pull/351

[johnlister]

from memory, there are issues using parallism and connection limits with some operations - the driver spins up a second connection to check the status of the actual request(s). As this second connection is blocked the driver is never able to determine the change is complete and it hangs. I run 2 provider configs, one allows multiple connections and the other doesn't - there are also issues with concurrent updates as well, hence the need to restrict the driver to 1 connection

[giner]

This issue manifests itself regardless of max_connections and parallelism settings. This is mentioned in the description.