Grant Schema (external) does not reliably set permissions

[markAcomm]

if you create a resource like so:

resource "redshift_schema" "external" {
    name = var.external_schema_name
    owner = "owner"
    external_schema {
        database_name = var.catalog_database_name
        data_catalog_source {
            iam_role_arns = [
                var.spectrum_iam_role_arn
            ]
            create_external_database_if_not_exists = true
        }
    }
}

resource "redshift_grant" "schema_group_engineering" {
    object_type     = "schema"

    schema          = var.external_schema_name
    privileges      = ["create, "usage"]   # effective equivalent of ALL
    group           = "engineering"
}

This gets into an infinite loop where each cycle to plan/apply is expecting to add the 'create' permission to the resource.

When I run this series under a debug log, I see the following lines that are relevant:

2022-06-22T23:41:08.831+0700 [INFO]  provider.terraform-provider-redshift_v1.0.0: 2022/06/22 23:41:08 [DEBUG] Created REVOKE query: REVOKE ALL PRIVILEGES ON SCHEMA "warehouse_external" FROM GROUP "engineering": timestamp=2022-06-22T23:41:08.830+0700
2022-06-22T23:41:08.878+0700 [INFO]  provider.terraform-provider-redshift_v1.0.0: 2022/06/22 23:41:08 [DEBUG] Created GRANT query: GRANT create,usage ON SCHEMA "warehouse_external" TO GROUP "engineering": timestamp=2022-06-22T23:41:08.878+0700
2022-06-22T23:41:10.174+0700 [INFO]  provider.terraform-provider-redshift_v1.0.0: 2022/06/22 23:41:10 [DEBUG] Collected schema 'warehouse_external' privileges for  engineering: [usage]: timestamp=2022-06-22T23:41:10.174+0700
2022-06-22T23:41:10.174+0700 [INFO]  provider.terraform-provider-redshift_v1.0.0: 2022/06/22 23:41:10 [DEBUG]: sql: transaction has already been committed or rolled back: timestamp=2022-06-22T23:41:10.174+0700

As you can see, the provider built a command: GRANT create,usage ON SCHEMA "warehouse_external" TO GROUP "engineering"

and then shortly after, when the provider read the permissions back out, only 'usage' is seen Collected schema 'warehouse_external' privileges for engineering: [usage]

Thus, we can see that the command is only setting a subset of the permissions.

If you try to run this GRANT command manually against the system, what you can see Is that Redshift is actually reporting an error (or could be a warning) that the provider is not catching and bubbling up to the user. The error from Redshift looks like this:

CREATE privilege on external schema can only be granted to IAM Roles using GRANT on EXTERNAL SCHEMA.  For users or groups, only USAGE privilege can be granted or ownership of external schema can be transfered.

I would ask that you catch this type of error from Redshift and expose it to the engineer running the apply plan. The fix on the resource is pretty simple in this case (remove the 'create' permission). But the terraform user may spend quite a bit of time trying to understand what is going wrong.