[CT-1901] [Bug] Revoke granted IAM roles fails

[mp24-git]

Is this a new bug in dbt-redshift?

• I believe this is a new bug in dbt-redshift
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

I am using the new grants setting to grant select permissions on all DBT-generated tables to the AWS Administrator Access IAM role, so that anyone who can access our AWS account can also query the Redshift database, e.g. using Redshift Query Editor.

In Redshift SQL, the grant works like this:

grant select on "mydb"."myschema"."mytable to "IAMR:AWSReservedSSO_AWSAdministratorAccess_XXXXXXXXXXX"

To achieve this, I have added the following in my dbt_project.yml:

models:
  myschema:
    +grants:
        +select:
          # variables are not supported in dbt_project.yml (https://github.com/dbt-labs/dbt-core/discussions/6170)
          - "{{ '\"IAMR:AWSReservedSSO_AWSAdministratorAccess_XXXXXXXXXXXXXX\"' if target.name == 'prod' else '\"IAMR:AWSReservedSSO_AWSAdministratorAccess_YYYYYYYYYYYYYYY\"' }}"

Note the additional quotation marks in the grant statement and the role name. Those are required by Redshift for IAM users/roles.

This works for any table model which is built as table, but not for incremental models. For incremental models, DBT generates a revoke statement first. This however does not contain the quotation marks:

revoke select on "mydb"."myschema"."mytable" from IAMR:AWSReservedSSO_AWSAdministratorAccess_XXXXXXXXXXX;
        
grant select on "mydb"."myschema"."mytable" to "IAMR:AWSReservedSSO_AWSAdministratorAccess_XXXXXXXXXXX";

The revoke fails with Redshift error message user "IAMR:awsreservedsso_awsadministratoraccess_xxxxxxxxxxx" does not exist.

Expected Behavior

The revoke statement should be valid for IAM users/roles and contain the required quotation marks.

Steps To Reproduce

1. Create an incremental table model
2. Set a grant to use an IAM role using the "IAMR:xxxx" syntax
3. Run the model
4. Run the model again (incrementally) and see the revoke fail

Relevant log output

No response

Environment

- OS: Windows 10
- Python: 3.9.1
- dbt-core: 1.3.1
- dbt-redshift: 1.3.0

Additional Context

No response

[mp24-git]

As a workaround, I run the following macro in the on-run-end hook:

{% macro create_schema_grants() %}
{% for schema in schemas %}
grant usage on schema {{ schema }} to {{ var("iam_grant_role_admin") }};
grant select on all tables in schema {{ schema }} to {{ var("iam_grant_role_admin") }};
{% endfor %}
{% endmacro %}

where the variable is defined in dbt_project.yml:

vars:
  iam_grant_role_admin: "{{ '\"IAMR:AWSReservedSSO_AWSAdministratorAccess_XXXXXXXXXXXXXX\"' if target.name == 'prod' else '\"IAMR:AWSReservedSSO_AWSAdministratorAccess_YYYYYYYYYYYYYYY\"' }}"

[jtcohen6]

Thanks for opening @mp24-git! I think this is a duplicate of dbt-labs/dbt-adapters#156.

We might want to quote grant recipients by default, except on databases where quoting leads to gnarly case sensitivity (Snowflake).

I'd encourage you to weigh in over there. I'm going to close this issue in the meantime to keep the conversation & tracking centralized.