concurrency issue when granting privileges on tables with postgresql_grant

[vedata]

Hi there,

We're encountering a persistent issue during the execution of Terraform apply, consistently receiving the error message: "Error: Could not execute revoke query: pq: tuple concurrently updated." Despite upgrading the provider to cyrilgdn/postgresql 1.18.0, the error remains unresolved

Terraform Version

Terraform v1.5.0

Affected Resource(s)

• postgresql_grant

Terraform Configuration Files

resource "postgresql_grant" "rw_role_table_crud" {
  depends_on = [postgresql_schema.public]
  count      = var.create_rw_role ? 1 : 0

  database    = postgresql_database.service_database[0].name
  role        = postgresql_role.rw_role[0].name
  object_type = "table"
  schema      = "public"
  privileges  = ["INSERT", "SELECT", "UPDATE", "DELETE", "TRUNCATE"]
}

resource "postgresql_grant" "ro_role_table_crud" {
  depends_on = [postgresql_schema.public]
  count      = var.create_ro_role ? 1 : 0

  database    = postgresql_database.service_database[0].name
  role        = postgresql_role.ro_role[0].name
  object_type = "table"
  schema      = "public"
  privileges  = ["SELECT"]
}

Expected Behavior

resource "postgresql_grant" "rw_role_table_crud" {
depends_on = [postgresql_schema.public]
count = var.create_rw_role ? 1 : 0

database = postgresql_database.service_database[0].name
role = postgresql_role.rw_role[0].name
object_type = "table"
schema = "public"
privileges = ["INSERT", "SELECT", "UPDATE", "DELETE", "TRUNCATE"]
}

resource "postgresql_grant" "ro_role_table_crud" {
depends_on = [postgresql_schema.public]
count = var.create_ro_role ? 1 : 0

database = postgresql_database.service_database[0].name
role = postgresql_role.ro_role[0].name
object_type = "table"
schema = "public"
privileges = ["SELECT"]
}

Actual Behavior

│ Error: could not execute revoke query: pq: tuple concurrently updated
│
│ with module.cp-database.postgresql_grant.rw_role_table_crud[0],
│ on ../../modules/postgresql-db/main.tf line 155, in resource "postgresql_grant" "rw_role_table_crud":
│ 155: resource "postgresql_grant" "rw_role_table_crud" {
│
╵
╷
│ Error: could not execute revoke query: pq: tuple concurrently updated
│
│ with module.cp-database.postgresql_grant.ro_role_table_crud[0],
│ on ../../modules/postgresql-db/main.tf line 244, in resource "postgresql_grant" "ro_role_table_crud":
│ 244: resource "postgresql_grant" "ro_role_table_crud" {
│
╵

Steps to Reproduce

1. terraform apply

Important Factoids

The DB is running on AWS RDS with Engine version: 13.11

References

• Fix concurrency issue when granting privileges on databases #224

[enricojonas]

Same issue for us, need to apply several times.

[vedata]

Removed table lock in DB

[kkapoorOS]

Why was this issue closed? I'm still seeing this issue even with version 1.22. The issue is also flaky, as it doesn't happen all the time. Applying with -parallelism=1 is a workaround that has worked for us (so far), but it is definitely not ideal! Please re-open and address

[awolfson2]

I've verified -parallelism=1 worked for me. As another test, I also set max_connections=1 in the provider block, along with depends_on on the grant resources and have currently not run into problems.

[ja6a-regnosys]

I finally fixed this issue once I changed the owner of the database and schema from one of the roles defined in the terraform to the DBMS superuser role. I also added a depends_on to the database resource listing the roles defined in the terraform. This means the database is deleted before the roles. Finally I added -parallelism=1 to be safe.