Constant configuration drift on `postgresql_grant` for `ALL TABLES`

[mbrancato]

Terraform Version

Terraform v1.4.6
on darwin_amd64
+ provider registry.terraform.io/cyrilgdn/postgresql v1.19.0

Affected Resource(s)

• postgresql_grant

Terraform Configuration Files

resource "postgresql_grant" "access" {
  provider    = postgresql.mydb
  database    = var.database
  object_type = "table"
  schema      = var.schema
  privileges  = ["SELECT", "INSERT", "UPDATE", "DELETE"]
  role        = var.role
}

And role is in the form my-svc-account-id@project.iam for google service accounts

Expected Behavior

Grants are already applied, it shouldn't try to recreate them.

Actual Behavior

Almost all the time, but not quite always, it wants to replace the grants:

-/+ resource "postgresql_grant" "access" {
      ~ id                = "my-svc-account-id@project.iam_something_something_table" -> (known after apply)
      ~ privileges        = [ # forces replacement
          + "DELETE",
          + "INSERT",
          + "SELECT",
          + "UPDATE",
        ]
        # (5 unchanged attributes hidden)
    }

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform plan

Important Factoids

Cloud SQL on Google with IAM auth.

When I try to inspect the state I get an error, even with the latest version of Terraform:

% terraform state show "postgresql_grant.access"
unsupported attribute "columns"
# postgresql_grant.access:
resource "postgresql_grant" "access" {

References

[mootari]

Does it still happen if you order the privileges in your configuration alphabetically?

[karelorigin]

@mootari, yes, it does not seem to matter. Currently, I'm having the same issue with ["ALL"]

[MaikuMori]

I'm having the same with grant on schema with ["CREATE", "USAGE"].

[antmordel]

Same here. I have constantly problems with this. I need to normally apply the same config 2 or 3 times.

[PierreBeucher]

This hits really hard, it made us lost grants altogether.

We had for some reasons create_before_destroy: true for Grants objects, but as the "new" resource was the same as the "old" resource, we ended-up losing grants altogether along with a constant diff being shown: the provider was "creating" a new resource (applying the same existing grant) and then "deleting" the old one (thus removing already existing grant).

[raul-verdi]

I'm experiencing the same issue. Tried to debug a bit more. I will share the details, maybe it helps someone from the team to find the cause.

To start I have the following grant:

resource "postgresql_grant" "table" {
  database      = postgresql_database.this.name
  role          = postgresql_role.this.name
  schema        = "public"
  object_type   = "table"
  privileges     = ["SELECT"]
}

Running the apply, the state has the privileges on the grant of the table. "privileges": ["SELECT"],

Running a plan right after the apply we get the different in the config, as it wants to add those privileges again.

Running a terraform refresh, refreshes the state, and the privileges on the state now becomes privileges = [], and a terraform plan shows no drift in configuration.

I got the refresh debug log and found this interesting message

2023-07-14T13:53:24.471+0900 [WARN]  Provider "registry.terraform.io/cyrilgdn/postgresql" produced an unexpected new value for module.psql.postgresql_grant.table["<id_resource>"] during refresh.
      - .privileges: actual set element cty.StringVal("SELECT") does not correlate with any element in plan
      - .privileges: length changed from 0 to 1

I still don't know why it is happening, but there seems to be something going on in the refresh, like it cannot get the values of the privileges, and then the plan wants to add them again, even tho originally after the first apply they were added.

Hope it helps.

[mbrancato]

The grant I have the most issues with is configured just like @raul-verdi described above. Only a single SELECT permission on all tables in a database. It is the only grant resource for the role. After a few days, a Terraform plan things it needs to add the SELECT permission back to the role. I think it might be related to failover during maintenance. PG fails over to it's HA follower replica, for maintenance. But that's just a guess on my end since it's not reproducible immediately.

[insider89]

I see such behaviour, when I add new table to DB. As I understand new table not inherit any permissions, and terraform sees the drift. Does someone know how to grant same permission for existing and new tables in DB with terraform provider?

[CarpathianUA]

I can confirm that this behavior happens after a new table / sequence creation. I've reproduced this by manual table creation -> terraform plan -> there is a drift - force recreation of grants. Then I've manually deleted this table -> terraform plan again -> no drift, no recreations.

I've tried also to play a little bit with postgresql_default_privileges and assign the same default privileges as assigned by postgresql_grant, so newly created table(s) will have the same set of privileges as were granted with postgresql_grant - no luck, I see the drift each time I create a new table, and what's most critical here that provider tries to force recreate all the postgresql_grant resources.

[jaorr95]

I am having the same issue with grants after new tables are created. Any updates or workaround?

[karelorigin]

@jaorr95 I'm pretty sure I've used Terraform's ignore_changes lifecycle argument to get around it. It's far from ideal, but it's all we have right now.

[talbx]

@mootari, yes, it does not seem to matter. Currently, I'm having the same issue with ["ALL"]

@karelorigin this issue is known and being fixed with the following PR: https://github.com/cyrilgdn/terraform-provider-postgresql/pull/339