postgres_grant privileges always being created at planning

[tcondeixa]

Hi, I'm having problems with postgres_grant always planning changes.

Terraform Version

0.12.26

Postgresql provider version

both 1.6.0. and 1.7.0

Postgres Database

both AWS RDS aurora 10.11 and RDS postgres 10.2

Affected Resource(s)

• postgresql_grant

Terraform Configuration Files

I'm using terraform modules, so it could be difficult to understand. However I included in the Debug Ouput section an example of what is happening to me in the plan phase.

resource postgresql_grant "user" {
  for_each = var.create ? var.db_users : {}

  database    = var.db_name
  role        = each.key
  schema      = var.db_schema
  object_type = "table"
  privileges  = each.value["permissions"]

  depends_on = [postgresql_role.user]

}

Debug Output

  ~ resource "postgresql_grant" "user" {
        database          = "mydb"
        id                = "reader_mydb_public_table"
        object_type       = "table"
      ~ privileges        = [
          + "SELECT",
        ]
        role              = "reader"
        schema            = "public"
        with_grant_option = false
    }

Expected Behavior

No changes should appear to be applied every time I run terraform. I checked the DB and the privileges are there configured.

Actual Behavior

The priviledge list is always presented as new in terraform plan, so the postgresql_grant is always marked as a resource to be changed.

Steps to Reproduce

I just created a readonly user with postgresql_role, postgresql_grant and postgresql_default_privileges for public schema

Thanks

[bendrucker]

We've encountered this at @TakeScoop with RDS Postgres. I created the following isolated test and ran it against a local Postgres server to verify that there's no perpetual diff:

provider "postgresql" {
  sslmode = "disable"
}

resource "postgresql_database" "foo" {
  name = "foo"
}

resource "postgresql_role" "foo" {
  name = "foo"
}

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

Launching a fresh RDS instance now to try to see if its immediately reproducible there.

[bendrucker]

Hmm, definitely not reproducible on an RDS Postgres instance running PG 12. Debug logs, generated with TF_LOG=debug would be helpful here since the refresh behavior here is a little weird:

https://github.com/terraform-providers/terraform-provider-postgresql/blob/master/postgresql/resource_postgresql_grant.go#L313-L316

Namely, if the privileges returned don't match what the configuration has, the state is set to [], rather than the actual returned privileges. Haven't dug deep enough to find out why that might be. But it definitely means the plan is not showing us the true diff, but the logs will include it.

Will go try to generate those now.

[bendrucker]

I found this:

2020-10-07T21:27:43.444Z [DEBUG] plugin.terraform-provider-postgresql_v1.7.1_x4: 2020/10/07 21:27:43 [DEBUG] TABLE spatial_ref_sys has not the expected privileges [] for role foo

Googling reminded me that's a PostGIS thing:

https://postgis.net/docs/using_postgis_dbmanagement.html#spatial_ref_sys

[bendrucker]

Went to the live DB where we observed this. \dt spatial_ref_sys reveals the issue:

              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | spatial_ref_sys | table | rdsadmin
(1 row)

That doesn't seem to be entirely consistent with AWS's own PostGIS recommendations:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferOwnership

That calls for an ownership change to the rds_superuser role. I think that would likely resolve our issue in the RDS master user is a member of the rds_superuser role whereas rdsadmin is the actual PostgreSQL superuser accessible only to Amazon.

[tcondeixa]

Thanks a lot, changing the table owner to rds_superuser solved the problem. The postgres provider using the master user it's now able to set the privileges for all tables in the public schema, so the plan is not showing changes every time anymore 🥇

[bendrucker]

Awesome! Per above:

Namely, if the privileges returned don't match what the configuration has, the state is set to [], rather than the actual returned privileges.

This is because each table might have an entirely different set of permissions, so there's truly no way to represent the diff. I'm guessing there's some way to exclude tables the user can't access in the query I linked, but at least for now this can serve as a reference for anyone hitting this with RDS + PostGIS.