terraform-provider-postgresql: "tuple concurrently updated" error on concurrent GRANT statements

Terraform Version

1.1.1

Affected Resource(s)

• postgresql_grant

Terraform Configuration Files

provider "postgresql" {
  host             = var.postgres_host
  port             = var.postgres_port
  username         = var.root_user_name
  password         = var.root_user_password
  expected_version = "12.3"
  superuser        = false
}

resource "postgresql_grant" "connect_db" {
  database    = postgresql_database.db.name
  object_type = "database"
  privileges  = ["CREATE", "CONNECT"]
  role        = postgresql_role.svc_admin.name
}

resource "postgresql_grant" "use_schema" {
  database    = postgresql_database.db.name
  object_type = "schema"
  privileges  = ["CREATE", "USAGE"]
  role        = postgresql_role.svc_admin.name
  schema      = "public"
}

Panic Output

╷
│ Error: could not execute revoke query: pq: tuple concurrently updated
│ 
│   with module.svc.postgresql_grant.use_schema,
│   on .terraform/modules/svc/main.tf line 118, in resource "postgresql_grant" "use_schema":
│  118: resource "postgresql_grant" "use_schema" {
│ 
╵

Expected Behavior

Multiple GRANT statements should get executed correctly.

Actual Behavior

terraform apply fails intermittently when multiple GRANT statements are involved.

Steps to Reproduce

1. terraform apply with multiple grant statements. You can also try a large number of statements with a for_each to make it more likely that the error will happen.

Important Factoids

Found this threads on postgres/terraform mailing lists:

1. https://www.postgresql.org/message-id/967138.1634322291@sss.pgh.pa.us
2. https://www.postgresql.org/message-id/20171228063004.GB6181@paquier.xyz
3. https://discuss.hashicorp.com/t/for-each-support-sequential-operation/34680

The “solution” seems to be to run things sequentially. However, ideally, we should be able to handle this at the provider level. For ex. by either locking the table appropriately, or by retrying after a backoff period perhaps before failing.

One interesting thing that happened was that with my terraform apply, when TF exited, it didn’t save the state. So, it created some resources, but they weren’t tracked in the state. That could be a Terraform bug, but I thought I should at least mention it here.