Fix ALL privileges for postgresql_grant

[icterine]

Hi

It will be good if postgresql_grant resource will allow ALL in addition to others like

privileges - (Required) The list of privileges to grant. There are different kinds of privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

as per

GRANT { { CREATE | USAGE } [,...] | **ALL** [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

edit by @cyrilgdn : Recreation of https://github.com/hashicorp/terraform-provider-postgresql/issues/72 and https://github.com/hashicorp/terraform-provider-postgresql/issues/166

[cyrilgdn]

Hi @icterine ,

It's already possible but actually does not work correctly. If you set privileges = ["ALL"], all privileges will be correctly granted but then the next terraform plan will show a diff.

We need to find a way to fix it (I think will need a new all_privileges settings which will conflict with privileges but I need to check if it works)

[zswanson]

Is there any way to work around this in the meantime? Its not blocking anything but it tends to confuse people and leads to a lot of uncertainty on whether the plan is valid.

[cyrilgdn]

@zswanson You can simply pass all the possible privileges, and to avoid passing in multiple resources you can define local variables for that.

e.g.:

locals {
  all_privileges_database = ["CREATE", "CONNECT", "TEMPORARY", "TEMP"]
  all_privileges_table = ["SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER"]
}


resource "postgresql_grant" "test" {
  database    = "test_db"
  role        = "test_role"
  schema      = "public"
  object_type = "table"
  privileges  = local.all_privileges_table
}

Allowed privileges per type are defined here: https://github.com/cyrilgdn/terraform-provider-postgresql/blob/master/postgresql/helpers.go#L237-L244

See also: https://www.postgresql.org/docs/current/sql-grant.html

[nitrocode]

For some reason TEMP always needs to be added with every apply. Perhaps all_privileges_database should be

locals {
  all_privileges_database = ["CREATE", "CONNECT", "TEMPORARY"]
}

[sarahkadar]

I see the same issue but with SELECT only.