[ADAP-853] [Feature] Support Dynamic Data Masking

[soksamnanglim]

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-redshift functionality, rather than a Big Idea better suited to a discussion

Describe the feature

I am opening this issue to track the development of Dynamic Data Masking (DDM) support for dbt-redshift. DDM allows customers to dynamically mask columns for users and roles.

The following are features of DDM:

• GRANT PERMISSION, USER CREATION, ROLE CREATION/GRANTING
• MASKING POLICY CREATION
• MASKING POLICY ATTACHING
• MASKING POLICY ALTERATION
• MASKING POLICY DETACHMENT AND DROPPING

We want dbt-redshift to support masking policy creation, attachment, alteration, and detachment and dropping. Role granting is currently unsupported by dbt-redshift and would require a separate issue opened for it. At minimum, dbt-redshift should allow users to create and attach masking policies to tables. Ideally, a mechanism to alter, detach, and drop existing masking policies should also exist.

Describe alternatives you've considered

This discourse was opened in Jan 2021 before dynamic data masking was a feature in Redshift. As of Nov 2022, Redshift supports DDM, however, users leveraging dbt are unable to manage DDM in their dbt projects.

The alternative solution is to continue using Redshift database drivers or the Redshift Query Editor to manage DDM.

Who will this benefit?

This feature will be helpful for users (with DDM permissions) who want to manage data masking policies using dbt.

Are you interested in contributing this feature?

No response

Anything else?

No response

[dataders]

@soksamnanglim thanks for writing this up! We discussed this internally and also believe it would be valuable. I'd check out the write up in dbt-labs/dbt-adapters#85. I'd love to hear which approach makes more sense to you.

Role granting is currently unsupported by dbt-redshift and would require a separate issue opened for it.
Agreed! dbt-labs/dbt-snowflake#415 is where we're tracking that work. IMO, we should ship that feature, before starting this one because:

1. it's a lower lift,
2. makes sense to come first
3. gives us lead time to think through DDM implementation in concert with Core's idea

Looking at the docs you've linked above, two things stand out

1. my understanding is that Redshift's DDM does not support ALTERing existing tables to add masking. This leads me to believe that the implementation will be via model contracts
2. this line reinforces my view that dbt should not support data sharing

DDM policies can't be used with data sharing

[soksamnanglim]

@dataders thanks for getting back so quick!
wrt whether to prioritize role granting or DDM, we are still internally discussing, however personally I am leaning towards shipping dbt-labs/dbt-snowflake#415 first as not supporting end-to-end DDM on dbt would mean users without established RBAC cannot take advantage of DDM. Likewise, DDM seems to be a big feature so time to sort through DDM implementation would be nice 😅 .

my understanding is that Redshift's DDM does not support ALTERing existing tables to add masking. This leads me to believe that the implementation will be via model contracts

wrt above, I may be misunderstanding so please correct me! Redshift's DDM can be applied to existing tables. Are you instead referring to this dbt-labs/dbt-adapters#85? afaik, dbt-redshift would also leave a window where data is unmasked between table creation and DDM application. Forgive my ignorance—how big of a problem is this?

I think we can implement DDM without model contracts, unless there is case I'm overlooking?

this line reinforces my view that dbt should not support data sharing

We understand your view in the context of DDM. While we are still having an internal discussion, we might also want to consider that DDM is used by users who don't use data sharing and vice versa.

[soksamnanglim]

Hi, chiming in again to provide an update that we will be working on allowing dbt users to configure role grants first (#415).
I think the solution will involve overriding macros in apply_grants.sql to support this method of configuring grants:

models:
  - name: specific_model
    config:
      grants:
        select: ['analytics_user', 'bi_user']
        grant_role:
           analytics_role: ['analytics_user']
           bi_role: ['bi_user']

Please let me know your thoughts. An alternative configuration could be:

models:
  - name: specific_model
    config:
      grants:
        select: ['analytics_user', 'bi_user']
        analytics_role: ['analytics_user']
        bi_role: ['bi_user']

Specifically, the macros get_dcl_statement_listand apply_grants should overriden to add role grant templates.

I also think we can extend support for granting privileges to groups by updating dbt documentation with this information: prefacing the group-name with group privilege: "group <group-name>" allows dbt users to configure privilege grants for groups. I am hoping this mitigates some of the issues raised in #8563. Although this does not address the confusion from the error output :/.

We considered segmenting grants into grant_roles, grant_groups, and grant_users configurations to reduce user error from manually prefixing "group"—which works because of how the sql is generated from the templates, however, I am not inclined towards this approach as 1. potentially breaks existing dbt projects 2. deviates drastically from standardized grant configuration in dbt 3. sounds complicated.

As for DDM, we will keep you updated 😄 .