`redshift_grants` resource occasionally shows incorrect diffs or permanent diffs

[viktorradnai]

This issue also affects redshift_permissions but we have migrated to redshift_grants so haven't created a test case for that.

The code used to check grants consists of SQL functions like these:

decode(charindex('r',split_part(split_part(array_to_string(relacl, '|'),gr.groname,2 ) ,'/',1)), 0,0,1) as select,

This is imperfect and gets confused in a number of cases. It's meant to parse ACLs like these:

{"group my_reader_group=r/my_user",my_user=arwdRxtD/myuser}

These have the format of grantee=perms/grantor and grantee may be an user in the form of username or group in the form of group groupname and matches against each table in a target schema

If a group exists with the same name as an user, the code above can break the following ways:

1. The code may match the grantor portion of an ACL entry and fail to pick up any permissions
2. It may pick up grants to an user rather than the group, depending on the order in which the ACL entries occur.
3. If a schema has 10 tables and the user has SELECT granted to only one of these, that may be interpreted by the code as having SELECT granted on all tables in the schema (I haven't verified this)
4. Related to the the above, the code may pick up grants created by redshift_default_privileges if a table was added to the schema, even if no redshift_grants resources were applied before.
5. I don't understand how schema grants are verified (if at all) when there are no tables in a schema and the above query returns zero rows.

Depending on the nature of the error this may result in an one-off diff or even a permanent diff, usually when tables are created outside of Terraform, especially if the creating user had some default permissions that would apply grants to a newly created table.

[viktorradnai]

I've created a test case that will show a permanent diff even if running terraform apply multiple times: https://gist.github.com/viktorradnai/3873bf5ccc9e6cb08fe3eed9e231a2df

This needs a provider config to run, eg:

locals {
  db_host = "your-cluster.redshift.amazonaws.com"
  db_port = "5439"
  db_name = "your-db"

  db_superuser_name = "your-username"
  db_superuser_password = "your-password"
}

terraform {
  required_version = ">= 1.0.4"
  required_providers {
    redshift = {
      source  = "brainly/redshift"
      version = ">= 0.3.0"
    }
  }
}

provider "redshift" {
  database = local.db_name
  host     = local.db_host
  port     = local.db_port

  username = local.db_superuser_name
  password = local.db_superuser_password
}

[winglot]

Thanks for the report. I have run the provided example and can confirm that the problem is present.

Your PR #25 fixed some of the problems only for table grants. I have prepared a new #26 which also fixes the same problem for schema and database with an additional simple test that replicated the bug.

[viktorradnai]

Thanks for the fix. I can confirm that the permanent diff is gone when I run the test case above.

I still get an one-off diff when I run apply the second time (it goes away the third time). But I think that is just because the default permissions for the testwriter group weren't set.

I don't think this is even a bug... but if the redshift_default_privileges and schema-level redshift_grants permissions do not match then upon creating a new table there will be a diff for redshift_grants where terraform will apply the table prvileges to the new table, eg:

Terraform detected the following changes made outside of Terraform since the last "terraform apply":

  # redshift_grant.grant2 has been changed
  ~ resource "redshift_grant" "grant2" {
        id          = "testwriter_table_test_schema"
      ~ privileges  = [
          - "delete",
          - "insert",
          - "references",
          - "select",
          - "update",
        ]
        # (3 unchanged attributes hidden)
    }

Unless you have made equivalent changes to your configuration, or ignored the relevant attributes using ignore_changes, the following plan may include actions to undo or respond to these changes.

─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  ~ update in-place

Terraform will perform the following actions:

  # redshift_grant.grant2 will be updated in-place
  ~ resource "redshift_grant" "grant2" {
        id          = "testwriter_table_test_schema"
      ~ privileges  = [
          + "delete",
          + "insert",
          + "references",
          + "select",
          + "update",
        ]
        # (3 unchanged attributes hidden)
    }

To "fix" this I'd need to add default permissions for each user that has privileges to create tables, automatically granting access when a new table is added, eg:

  resource "redshift_default_privileges" "default" {
      group       = "testwriter"  # this refers to the group
      id          = (known after apply)
      object_type = "table"
      owner       = "testwriter"  # this refers to the user of the same name
      privileges  = [
          "delete",
          "insert",
          "references",
          "select",
          "update",
        ]
      + schema      = "test_schema"
    }

Anyway, I am happy to close this bug as fixed. Perhaps the quirk above could be documented within redshift-_schema or redshift_default_privileges?