Granting / revoking privileges on tables through "objects" is not atomic

[jan-wilhelm]

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version

Terraform v1.1.9
on darwin_amd64

• provider registry.terraform.io/cyrilgdn/postgresql v1.16.0

Affected Resource(s)

• postgresql_grant

Terraform Configuration Files

resource "postgresql_grant" "test-grant" {
  database    = "db"
  role        = "test-role"
  schema      = "public"

  object_type = "table"
  objects     = ["table-one"]
  privileges  = ["SELECT"]
}

Expected Behavior

When changing the objects from ["table-one"] to ["table-one", "table-two"], the permissions should be revoked / granted atomatically, so that existing systems querying the database don't temporarily see errors.

Actual Behavior

Because of the way objects was implemented in #105 (specifically, the ForceNew schema flag), Terraform forces the plan to destroy and recreate the grant which does not happen within the same postgres transaction. Therefore, postgres roles using the "SELECT" permission on "table-one" temporarily see errors for a little while when Terraform has deleted but not yet recreated the permission grants.

Steps to Reproduce

1. create a postgres db with a test schema
2. create a testing role and apply the tf code from above
3. Connect to postgres using the test role
4. Continously run statements such as select count(*) from "table-one";
5. change the objects = ["table-one"] line to objects = ["table-one", "table-two"] and apply the changes through terraform
6. Keep running your count(*) statement, you will see some errors for ~1 second while the system revokes / issues new permissions.

Question

Are there any smart workarounds for this? For testing and dev purposes, this behaviour is OK, but for production systems that are running hundreds of queries per second through a role managed with this provider, seeing errors for about a second is something I would definitely like to avoid. Thank you!

[BastianSperrhacke-Otto]

Hello. Are there any updates on this topic? We also ran into this issue...

[mattthaber]

This seems like a rather big issue to be occuring. i have a couple ideas on work arounds , but this is the type of logic id expect the plugin to handle

[mattthaber]

locals {
  grant_tables = [
    "table",
  ]
}

resource "postgresql_grant" "foobar_grants" {
  for_each    = toset(local.grant_tables)
  role        = postgresql_role.foobar[0].name
  database    = local.database
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]

  objects = [
    each.key
  ]

  lifecycle {
    create_before_destroy = true
  }
}

is one work around, in which you isolate all grants to their own resource.

[mattthaber]

@cyrilgdn is there any reason why objects needs to ForceNew ? Looking at the source code, https://github.com/cyrilgdn/terraform-provider-postgresql/blob/master/postgresql/resource_postgresql_grant.go#L163

we already revoke and then grant but in the same transactions, so no downtime.

So deleting the resource first seems redundant?

I can throw up a PR to remove this so it can be updated in place

[dkravetz]

Has there been any update on this?

[nicks]

i think this is a bit more complex than just removing the ForceNew -- the revoke/grant queries need to take into account that objects may have been removed from the set.

[nicks]

posted a fix at #614