[Bug] GRANT revoke statements in redshift fails when usernames contain certain characters (eg. .)

[ivansabik]

Is this a new bug in dbt-core?

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

Current Behavior

When grants are setup in an incremental model dbt attempts to run some a REVOKE statement. When the username(s) that currently have access to that table contain a dot character (eg. ivan.sabik), which is completely valid for a Redshift username , building those models fails with:

syntax error at or near "."

That would be solved by adding quotes to those users, that is instead of revoke select on table eventview from ivan.sabik it should be revoke select on table eventview from "ivan.sabik"

Expected Behavior

REVOKE statements don't throw a syntax error

Steps To Reproduce

1. Create a username containing a dot character
2. Run an incremental dbt model
3. Manually add GRANT statements for the user from step 1
4. Add a grant configuration in dbt for this model
5. Re-run the model
6. Confirm you get an error: syntax error at or near "."

Relevant log output

No response

Environment

- OS:
- Python:
- dbt:

Which database adapter are you using with dbt?

redshift

Additional Context

No response

[dbeatty10]

Thanks for reporting this @ivansabik !

The relevant code appears to be in apply_grants.sql, and I didn't notice any specify overrides of get_revoke_sql within dbt-redshift (neither within the current code or the original implementation dbt-labs/dbt-redshift#131).

In the meantime, you might be able to resolve it by adding something like the following to your dbt project in a file named macros/overrides/get_revoke_sql.sql:

{%- macro default__get_revoke_sql(relation, privilege, grantees) -%}
    -- just figure out the right code to put here
{%- endmacro -%}

[dbeatty10]

@ivansabik wanna give this a try?

This worked for me in dbt-postgres, but I didn't try it out in dbt-redshift:

macros/overrides/get_revoke_sql.sql

{%- macro default__get_revoke_sql(relation, privilege, grantees) -%}
    
    {%- set quoted_grantees = [] -%}

    {%- for grantee in grantees -%}
        {%- do quoted_grantees.append(adapter.quote(grantee)) -%}
    {% endfor -%}

    revoke {{ privilege }} on {{ relation }} from {{ quoted_grantees | join(', ') }}
{%- endmacro -%}

See below for the details to reproduce this.

Reprex

Here's the model I used:

models/my_model.sql

{{ config(materialized="incremental") }}

select 1 as id

Here's how I created the users in my local postgres instance:

CREATE USER "dbt_test_user.1";
CREATE USER "dbt_test_user.2";
CREATE USER "dbt_test_user.3";

Here's the YAML file I used to do the initial grants:

models:
  - name: my_model
    config:
      grants:
        select: ['"dbt_test_user.1"', '"dbt_test_user.2"', '"dbt_test_user.3"']

Then I ran this command:

dbt run -s my_model

Then I changed the YAML to this:

        select: []

Then I ran the model again:

dbt run -s my_model

[dbeatty10]

In order handle both grant and revoke statements the same way, we'd probably want something like this using adapter.quote:

{%- macro quote_grantees(grantees) -%}
    {%- set quoted_grantees = [] -%}
    {%- for grantee in grantees -%}
        {%- do quoted_grantees.append(adapter.quote(grantee)) -%}
    {% endfor -%}
    {%- do return(quoted_grantees) -%}
{%- endmacro -%}


{%- macro default__get_revoke_sql(relation, privilege, grantees) -%}
    revoke {{ privilege }} on {{ relation }} from {{ quote_grantees(grantees) | join(', ') }}
{%- endmacro -%}


{%- macro default__get_grant_sql(relation, privilege, grantees) -%}
    grant {{ privilege }} on {{ relation }} to {{ quote_grantees(grantees) | join(', ') }}
{%- endmacro -%}

Something to be aware of: going this route would make all user names / roles case-sensitive when they are written in the YAML.

[gekas93]

I have the same issue, is there any news about the fix of this bugs in future DBT versions?

[ivansabik]

Forgot to post an update here but the suggested solution workaround did the trick for me using Redshift