[CT-3173] [Bug] Postgres + Grants + Username-With-Dashes does not work

[tomans-spirent]

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 I configure grants in dbt for a postgres database and a db role like 'foo-bar' I get postgres syntax errors because the name is not quoted.

If I try to quote it like "\"foo-bar\"" it works for the grants part but breaks in the incremental grants part. Looks like some places do not strip and some places do strip those extra quotes I added to my db role name.

Expected Behavior

I should not have to put in extra quotes - dbt should be able to create the appropriate database syntax for supported role names.

Steps To Reproduce

Create a postgres role with dashes in the name.

Try to have dbt assign grants to that user - it will break.
Add manual quotes - it will work.

Try to make a incremental model with those same grants - it will break.

Relevant log output

No response

Environment

- OS: macOS ventura 13.6
- Python: 3.10.12
- dbt: 1.6.2

Which database adapter are you using with dbt?

postgres

Additional Context

No response

[tomans-spirent]

As an update and workaround to this issue - I have been able to leverage the post-hook feature of models to apply the grants myself - where I just make sure I properly quote my db role name.

[dbeatty10]

Thanks for reporting this @tomans-spirent !

Could you try this workaround and see if it also works for you?

[tomans-spirent]

Seems like a similar issue - but no such luck if I add that revoke macro to my project.

Some extra context from my dbt run log output:

Database Error in model my_model (models/my_group/my_model.sql)
  syntax error at or near "-"
  LINE 4: ...db"."schema"."my_model" to grantee-with-dash;

With the following in my dbt_project.yml

models:
  my_group:
    +grants:
      select: ["grantee-with-dash"]

[dbeatty10]

Ah, sorry I only gave you the revoke-side of the story @tomans-spirent ! 😅

Related issues

It looks to me like there are two different (but very related) things going on:

1. dbt-postgres/redshift doesn't quote username when granting (#8751)
2. dbt-postgres/redshift doesn't quote username when revoking (#6444)

See below for two different approaches you can try.

Option 1: Add more quotes

Does it work if you add double quotes within your configuration?

For example, like this for models YAML configuration within dbt_project.yml (note both single and double quotes):

models:
  my_group:
    +grants:
      select: ['"grantee-with-dash"']

If this does work for you, I think you'll still need to that macro you just added to handle the revoke side of things.

Option 2: Add a grant-related macro

dbt-labs/dbt-redshift#282 has a proposed solution for both of those issues listed above (which is converted from being redshift-specific to default below):

{%- macro default__get_grant_sql(relation, privilege, grantees) -%}
    grant {{ privilege }} on {{ relation }} to
    {% for grantee in grantees %}
    {{ adapter.quote(grantee) }}
    {% if not loop.last %},{% endif %}
    {% endfor %}
{%- endmacro -%}


{%- macro default__get_revoke_sql(relation, privilege, grantees) -%}
    revoke {{ privilege }} on {{ relation }} from
    {% for grantee in grantees %}
    {{ adapter.quote(grantee) }}
    {% if not loop.last %},{% endif %}
    {% endfor %}
{%- endmacro -%}

Trade-offs

I didn't try these out myself, but I'm assuming it requires all grants to be case-sensitive when they are written in YAML and config for dbt models. So that trade-off would probably work for you, but might break others if we were to just roll these macros out everywhere as-is.

Wanna give one or both of these ideas a try and let us know how it goes and what you think?