postgresql_grant persistent plan drift even without underlying terraform code changed

[bfox1793]

Terraform Version

1.1.3

Affected Resource(s)

postgresql_grant

Terraform Configuration Files

required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "= 3.71.0"
    }
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "1.15.0"
    }
  }

Debug Output

N/A

Panic Output

N/A

Expected Behavior

Having a postgresql_grant object with privileges SELECT, UPDATE, INSERT without any changes reflects as much on subsequent terraform plan runs.

Actual Behavior

terraform plan detects constant drift between INSERT grants being either removed, and so it attempts to add it, or it says it was added and needs to remove the INSERT privilege (though I want the insert to be added).

These subsequent applies don't appear to affect the underlying resources, just the terraform state detection. The terraform configs are putting grants on the DB as-expected.

Steps to Reproduce

1. terraform apply > creates the postgresql_grant with SELECT, UPDATE, INSERT access
2. terraform plan > expect no drift since underlying TF code wasn't touched, but instead it detects that the grant's INSERT either needs to be added or removed from the grant.

Important Factoids

N/A

References

N/A

[cyrilgdn]

Hi @bfox1793 ,

Could you provide example of Terraform resources to reproduce this behavior?
I'm not able to reproduce it with a simple test.

[yaronmahat]

Hi @cyrilgdn ,
This happens to me also.
I think I have pin-pointed the cause of it:

This happens when using default privileges. when a new table is created, privileges on it will be granted according to defaults and will reflect on information_schema.role_table_grants table.
However, they will not reflect in pg_class column relacl which is used by the TF provider. Only when the table will be issued one grant command, even if unrelated to the default privileges, the relacl column in pg_class will be populated.

A small example:

1. create user yaron with password 'yaron';
2. ALTER DEFAULT PRIVILEGES FOR ROLE yaron grant select,insert,update,delete on tables to yaron;
   3)connect as yaron
   4)create table test(x int);

postgres=> select*from information_schema.role_table_grants where table_name='test'; (privileges are shown)

grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
yaron | yaron | postgres | public | test | INSERT | YES | NO
yaron | yaron | postgres | public | test | SELECT | YES | YES
yaron | yaron | postgres | public | test | UPDATE | YES | NO
yaron | yaron | postgres | public | test | DELETE | YES | NO
yaron | yaron | postgres | public | test | TRUNCATE | YES | NO
yaron | yaron | postgres | public | test | REFERENCES | YES | NO
yaron | yaron | postgres | public | test | TRIGGER | YES | NO

postgres=> select relacl from pg_class where relname='test'; (relacl is empty)
relacl

(1 row)

now, grant select to some other user on the new table:

postgres=> grant select on test to yaron2;
GRANT
postgres=> select relacl from pg_class where relname='test';
relacl

{yaron=arwdDxt/yaron,yaron2=r/yaron}

grants are now shown in relacl on yaron user,

This causes the provider to re-grant the privileges on the new tables which updates relacl, so after the apply there is no drift until the next table is created.

Hope this helps.

[bfox1793]

@cyrilgdn - I think @yaronmahat 's details above provides a more concise example of this behavior. Let me know if you need any additional information!

[n1ngu]

This simple config granting a role all privileges on a DB schema

terraform {
  required_providers {
    postgresql = {
      source = "cyrilgdn/postgresql"
    }
  }
  required_version = ">= 1.0"
}

provider "postgresql" {
  host            = var.psql_host
  port            = 5432
  database        = "postgres"
  username        = var.psql_username
  password        = var.psql_password
  sslmode         = "require"
  connect_timeout = 15
  superuser       = false  # It's an AWS RDS
}

resource "postgresql_role" "app_role" {
  name  = "app"
  login = false
}

resource "postgresql_database" "app" {
  name              = "app"
  owner             = postgresql_role.app.name
  template          = "template0"
  lc_collate        = "en_US.UTF-8"
  connection_limit  = -1
  allow_connections = true
}

resource "postgresql_grant" "app_tables" {
  database    = postgresql_database.app.name
  role        = postgresql_role.app.name
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER"]
}

resource "postgresql_grant" "app_sequences" {
  database    = postgresql_database.app.name
  role        = postgresql_role.app.name
  schema      = "public"
  object_type = "sequence"
  privileges  = ["USAGE", "SELECT", "UPDATE"]
}

resource "postgresql_role" "app_user" {
  name  = "app-user"
  login = true
  roles = [postgresql_role.app_role.name]
}

regularly detects a drift in the granted privileges and requires a new apply

  # postgresql_grant.app_sequence will be updated in-place
  ~ resource "postgresql_grant" "app_sequences" {
        id                = "app_public_sequence"
      ~ privileges        = [
          + "SELECT",
          + "UPDATE",
          + "USAGE",
        ]
        # (5 unchanged attributes hidden)
    }
Plan: 0 to add, 1 to change, 0 to destroy.

despite nobody tampered the privileges and the application works just right.

This happens from time to time and causes some noise in terraform plans.

FWIW: all my DB services are AWS RDS, so I haven't tested this on a bare postgresql.

[nocive]

bump

[shawon-crosen]

Any updates on this? I am seeing the same thingas @n1ngu , also with AWS RDS postgres instances

[steintore]

Seems like the same issue as here #303

We're having the same issue and we do experience 1-2 seconds where the user looses access to tables etc.

Any updates on this?

[a-nldisr]

Similar behavior on AWS Aurora and RDS.

[jalitzinger]

In more recent versions, https://github.com/cyrilgdn/terraform-provider-postgresql/pull/135/files#diff-df65aafa037f8919594f7968b37996a7cb876d3a94de44286f83bd81288fd159L40 will cause recreations, not just updates.

[rmihael]

I also experience the same behavior with version 1.22.0. I have the following resource definition

resource postgresql_grant connect_privilege {
  for_each = toset(var.database_users_roles)

  database = var.database
  object_type = "database"
  privileges = ["CONNECT"]
  role = each.value
}

Every time I run apply it wants to change it:

  # module.database_config.postgresql_grant.connect_privilege["users-qntpm-dev-2/qntpmdev"] must be replaced
-/+ resource "postgresql_grant" "connect_privilege" {
      ~ id                = "users-qntpm-dev-2/qntpmdev_qntpmdev_database" -> (known after apply)
      ~ privileges        = [ # forces replacement
          + "CONNECT",
        ]
        # (4 unchanged attributes hidden)
    }

I run Terraform with TF_LOG=INFO and this part seems to be relevant:

module.database_config.postgresql_grant.connect_privilege["users-qntpm-dev-2/qntpmdev"]: Refreshing state... [id=users-qntpm-dev-2/qntpmdev_qntpmdev_database]
2024-07-23T14:20:19.248+0300 [WARN]  Provider "registry.terraform.io/cyrilgdn/postgresql" produced an unexpected new value for module.database_config.postgresql_grant.connect_privilege["users-qntpm-dev-2/qntpmdev"] during refresh.
      - .privileges: planned set element cty.StringVal("CONNECT") does not correlate with any element in actual

[andersthorbeck]

Is anyone looking into this issue? The Terraform plans have been consistently misleading on postgresql_grant resources for several years now.

[michelefa1988]

Any fix/ workaround on this issue. I am having the same issue on some of my databases? I really think that is it an annoying bug with the provider. I also suspect the issue occurs when default privileges are added although in general the behaviour seems to be different on different databases.

  # module.env-definition.module.webhook_tester_postgresql_db.module.psql_roles.postgresql_grant.grant_access[0] must be replaced
-/+ resource "postgresql_grant" "grant_access" {
      ~ id                = "webhooktester_webhook_tester_dev_database" -> (known after apply)
      ~ privileges        = [ # forces replacement
          + "CREATE",
          + "TEMPORARY",
        ]
        # (5 unchanged attributes hidden)
    }

[mjanschek]

Got the same issue, did anyone test this with different Postgre versions, yet?

[yoonhee-attentive]

Still seeing this issue. Is anyone working to fix this issue?

[jamesmazur]

Hi @cyrilgdn , would you be able to look at this? It appears this issue has existed for almost 3 years. It is causing some confusion at the company I work at.

[mjanschek]

We managed to "solve" this issue by reviewing our permission plans. We figured out that the role used by the provider wouldn't be able to grant these privileges. The grants also didn't show up in the state file. I'm not familiar with GoLang so I didn't check but it seems the provider doesn't provide a clear error message if the used role is not able to assign grants.

This case might also apply to given example as var.psql_username doesn't necessarily possesses the privileges that are supposed to be granted to the new roles