[CT-506] Apply grants within materializations

[jtcohen6]

How this works today

{{ config(post_hook = 'grant select on {{ this }} to role reporter') }}

select 1 as id

During each materialization, in run_hooks(post_hooks), dbt will run the arbitrary SQL that the user has provided.

What's not great about this?

• The post_hook is just a string. dbt doesn't know any structured information about grants, privileges, recipients. If we want metadata later on, "Who should have access to model X?", we have no idea—we'd need to run a database query (show grants).
• {{ this }} is a special thing, as is post_hook. If you don't get this syntax exactly right, or try granting on a different table instead, you can end up in some weird circumstances: Post hooks that call macros get parsed with execute = False #2370, get_relation returns none in hook context #2938, this.is_view and this.is_table not working in BigQuery inside a hook #3529, custom table schema path of {{ this }} parsed in correctly in post-hook macro #3985, Post-hook doesn't resolve custom schema #4023, [CT-80] [Bug] post-hook macro generates SQL with incorrect source table #4606. The proposal in this issue doesn't solve for that, but it may help newer users from needing to wade into deeper nested-curly waters.

What we want

I can define grants as a resource config on each model/seed/snapshot. As with all resource configs, I can define reasonable defaults in dbt_project.yml, plus the ability to define within each model SQL file or yml file.

# dbt_project.yml
models:
  export:
    +grants:
      select: ['reporter', 'bi']

{{ config(
    grants = {'select': ['other_user']}
) }}
-- this should totally replace the 'reporter' + 'bi' default configs defined above

select ...

When my dbt model runs, all grants are automatically applied:

-- e.g. on Snowflake
create or replace table dbt_jerco.my_model
as (
  select 1 as id
);

grant select on table dbt_jerco.my_model to other_user;

We’re targeting the 95% use case here: The right people can select from your dbt models, as soon as those models are created. There may be super specific grants that users want to put together. For that, there are always hooks, as above.

Required changes

• Add grants as a supported node config. Grants should be merged/clobbered—like meta, not tags. (Opt for less access, not more.)
• In all materializations, add a call to an apply_grants macro, very similar to persist_docs
• dbt-core’s global project implements a dispatched macro, {% macro get_grant_sql(relation, privilege, recipients) %}, with a sane default__get_grant_sql
• Adapter plugins reimplement that macro as adapter__get_grant_sql if the default doesn’t work for them

Considerations

grants will support grants on the current model only. dbt grants access on model X, as soon as model X has finished running. It won’t be possible to grant permissions on model Y as soon as model X has finished running.

On some databases, grants are automatically “inherited” when a table is recreated (e.g. copy grants on Snowflake). Should we strongly advise use of those configs, where available? Should we always revoke + rerun every grant, every time? Or should we first ask which grants are in place (show grants), calculate diffs, and then decide which grants to use? Related: If users have configured column/row-level restrictive access policies, we need to ensure that those restrictions are applied first, before grants (which are permissive). Otherwise we risk a moment in which a user has more access than they should.

The words here are different on different databases. ("Role" on BigQuery means "privilege," whereas on Snowflake it means "recipient group.") How should we factor this config, to avoid bad abstractions later on?

# dbt_project.yml
models:
  export:
    +grants:
      # 'concise' version
      select: ['reporter', 'bi']

      # we really only expect people to be granting 'select' on views/tables,
      # but let's make sure we're not hurting ourselves in a future version where
      # we support grants on other object types (schemas, policies, functions, ...)

      # how to uniquely identify this combo to support merging/clobbering?
      - privileges:
          - select # on Postgres/Redshift/Snowflake
          - 'roles/viewer' # same idea but on BigQuery - should we support 'select' as an alias?
        recipients:
          - reporter
          - bi

We’ll need to update, in our documentation, the places where we strongly recommend running grants inside of hooks:

• https://docs.getdbt.com/reference/project-configs/on-run-start-on-run-end#examples
• https://docs.getdbt.com/reference/dbt-jinja-functions/this#grant-permissions-on-a-model-in-a-post-hook

[jtcohen6]

Closing in favor of the implementation tickets (#5189 + #5263)