[ADAP-944] [Feature] Support secondary roles

[seub]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-snowflake functionality, rather than a Big Idea better suited to a discussion

Describe the feature

This feature request is basically a re-opening of dbt-labs/dbt-core#449, but I thought I'd rephrase it slightly differently. It is also related to dbt-core dbt-labs/dbt-adapters#212.

It would be nice if dbt supported secondary roles for the Snowflake adapter. I'm imagining something like, in profiles.yml:

<profile_name>:
  outputs:
    <target_name>:
      type: snowflake
      ...
      role: <primary role>
      secondary_roles: <value>
    
  ...

where <value> is something that can directly be pasted into the Snowflake query

use secondary roles <value>;

such as none, all, role_name, or a comma-separated list of role names.

Describe alternatives you've considered

As a workaround, I've tried to use a pre-hook:

# dbt_project.yml
...
models:
  +pre-hook:
    - use secondary role <role_name>;
    ...

This almost works, but not quite for (at least) the following reasons:

• As emphasized by the other issue, pre-hooks are ignored by dbt test
• The pre-hooks are only executed during the "execute" phase of the model's execution, and not during the "compile" phase. This means that macros relying on secondary roles will fail at compilation.

Who will this benefit?

Here is a use case that I think many people will be interested in.

dbt is great for easily switching environments with--target, and providing powerful tools like --defer and state selection. This enables many projects to implement a CI/CD for their org's dbt projects, which include building the dbt project (probably with --defer --select state:modified+) in an isolated "CI environment", say a dedicated database DBT_CI_TESTS.

In order for dbt to operate in DBT_CI_TESTS, a natural solution is to grant whichever role dbt is using RW privileges in that database. However this is not ideal because:

1. It's not super scalable: as more and more dbt projects use more and more roles, they will all need to be manually granted access.
2. It doesn't fully leverage the isolation of environments: ideally, you'd want dbt to only have write access to the database DBT_CI_TESTS during the CI tests, so that you have a strong guarantee that your prod environment won't be messed up.

Secondary roles elegantly solve both these issues! Just put the usual role as the secondary role, and put as the primary role a dedicated role DBT_CI_TESTS_RW that only has RW access in DBT_CI_TESTS.

Are you interested in contributing this feature?

Yes

Anything else?

No response

[rumbin]

Amazing to see that the feature I am looking for just now has been suggested merely 3 days ago...

My use case is to be able to use dynamic data masking using the is_role_in_session() function. This way our domain-specific analysts can all use the same primary role for wirking with dbt but the accessibility of PII data would be controlled by secondary roles that we'd grant to them based on their data domains.

[luke-siebert]

I would also like to see this feature added, as we have several teams using DBT and are implementing a Snowflake security model that will require the use of secondary roles.

[jharris-coh]

We'd love to see this feature as well. It's not ideal having a robust security model that takes advantage of secondary roles and pivoting to a less scalable workaround for adding new users to work in dbt in our transformation layer.

[benjaminifaught]

My organization would also like this feature.

[jtcohen6]

IMO the way this should work is very similar to how dbt already supports snowflake_warehouse: by configuring a secondary role on the model/test/etc, and dbt would call use secondary role before materializing that model.

I see I said this a while back:

• [CT-2044] Secondary role in tests #449 (comment)

This requires us to fix two things in dbt-core:

• [CT-1123] Adapter pre_model_hook + post_model_hook for tests and compilation, too dbt-adapters#212
• [CT-908] Allow generic tests to accept arbitrary configs dbt-core#5532 (to support this for generic tests in particular)