grants config: declarative permissions на models/seeds/snapshots

В production консумеры (BI tools, ML services, analysts) подключаются к warehouse и читают данные. Им нужны permissions: SELECT на marts, иногда USAGE на schema. Без grants — SELECT permission denied for table.

Manual grants (через GRANT SELECT ON ... TO role в SQL) — fragile: someone forgets to grant after creating new model, или revoke remained after model dropped. dbt 1.4+ предоставляет grants config — declarative permissions в YAML, applied automatically при dbt run.

Этот урок — про syntax grants, cumulative behavior (+grants), per-environment grants, и production patterns.

Basic grants syntax

В model YAML:

- name: customer_metrics
  config:
    materialized: table
    grants:
      select: ['analyst_role', 'bi_tool_role']

После dbt run:

GRANT SELECT ON customer_metrics TO analyst_role;
GRANT SELECT ON customer_metrics TO bi_tool_role;

Permissions applied automatically. Idempotent — running again не fails.

Grants on dbt_project.yml level

Per-folder:

# dbt_project.yml
models:
  my_project:
    marts:
      +grants:
        select: ['analyst_role']

    marts:
      finance:
        +grants:
          select: ['finance_analyst_role', 'cfo_role']

All marts models: analyst_role can SELECT. Marts/finance models: ALSO finance_analyst_role and cfo_role.

This is cumulative when using +grants — applies grants from parent + this level.

Cumulative +grants vs override grants:

Cumulative (default with +):

models:
  my_project:
    +grants:
      select: ['all_analyst']

    marts:
      +grants:
        select: ['marts_analyst']    # adds to inherited

    marts:
      finance:
        +grants:
          select: ['finance_role']    # adds further

customer_metrics in marts/finance gets grants: all_analyst + marts_analyst + finance_role.

Override (without + или in config block):

- name: customer_metrics
  config:
    grants:
      select: ['only_this_role']   # overrides any inherited

customer_metrics gets only only_this_role. Inherited grants ignored.

When to use:

• +grants: build up permissions layer by layer (project default + per-folder additions)
• grants: in config: override for specific model (e.g., one model needs different permissions)

Multiple privilege types

- name: customer_metrics
  config:
    grants:
      select: ['analyst_role']      # SELECT only
      insert: ['etl_role']          # also INSERT
      update: ['etl_role']          # also UPDATE

Most common: select. Others (insert, update, delete, references) — rare для dbt-managed models (dbt owns the table).

Sometimes need them для shared tables (e.g., dim_dates table updated by ETL outside dbt).

Per-environment grants

# dbt_project.yml
models:
  my_project:
    marts:
      +grants:
        select: "{{ ['analyst_role'] if target.name == 'prod' else [] }}"

In prod: analyst_role can SELECT. In dev / ci: no grants (no external consumers).

This avoids granting analyst_role on developer’s personal schemas (dbt_alice_dev_marts).

Полный пример:

models:
  my_project:
    +grants:
      select: "{{ ['public_role'] if target.name == 'prod' else [] }}"

    marts:
      +grants:
        select: "{{ ['analyst_role'] if target.name == 'prod' else [] }}"

    marts:
      finance:
        +grants:
          select: "{{ ['finance_role', 'cfo_role'] if target.name == 'prod' else [] }}"

Per-target, per-folder, cumulative.

Warehouse support

For DuckDB course — grants documented но не actively applied. Focus на pattern, не implementation.

Common patterns

Pattern 1: Public marts, private staging

models:
  my_project:
    staging:
      # no grants — internal only

    intermediate:
      # no grants — internal only

    marts:
      +grants:
        select: ['analyst_role', 'bi_tool_role']

Consumer-facing marts: public. Internal staging: private.

Pattern 2: Granular permissions per business unit

models:
  my_project:
    marts:
      finance:
        +grants:
          select: ['finance_analyst', 'cfo', 'controller']

      marketing:
        +grants:
          select: ['marketing_analyst', 'cmo']

      product:
        +grants:
          select: ['product_analyst', 'cpo']

Each business unit sees only their data.

Pattern 3: PII protection

- name: customer_pii
  config:
    grants:
      select: ['pii_authorized_role']   # restricted

- name: customer_metrics_anonymized
  config:
    grants:
      select: ['analyst_role', 'bi_tool_role']   # public

PII separately. Analysts get anonymized version. PII authorized role gets full data.

Pattern 4: Tiered access

models:
  my_project:
    marts:
      +grants:
        select: ['analyst_role']

      executive:
        +grants:
          select: ['executive_role']   # additional, executive level

      sensitive:
        +grants:
          select: ['compliance_role']   # only compliance team

Different tiers, cumulative gains.

Полный пример

# dbt_project.yml
name: my_project

models:
  my_project:
    +materialized: table

    staging:
      +schema: staging
      # no grants — internal

    intermediate:
      +schema: intermediate
      # no grants

    marts:
      +schema: marts
      +grants:
        select: "{{ ['analyst_role'] if target.name == 'prod' else [] }}"

      finance:
        +schema: marts_finance
        +grants:
          select: "{{ ['finance_role'] if target.name == 'prod' else [] }}"

      marketing:
        +schema: marts_marketing
        +grants:
          select: "{{ ['marketing_role'] if target.name == 'prod' else [] }}"

Result в prod:

• staging schemas: no grants
• intermediate: no grants
• marts (default): analyst_role SELECT
• marts/finance: analyst_role + finance_role SELECT
• marts/marketing: analyst_role + marketing_role SELECT

In dev: no grants anywhere.

What +grants does behind scenes

При dbt run для модели:

1. dbt materializes table
2. dbt applies grants — runs GRANT SELECT ON {table} TO {role} для каждого role
3. dbt ALSO revokes grants not in current config (cumulative revoke)

Last step — cleanup: if previously granted role X is removed from config, dbt revokes на next run.

Result: YAML is source of truth for permissions. Out-of-band manual grants get overwritten.

Это feature, не bug — consistent permissions, no drift.

Trade-off: if you have non-dbt-managed grants on same table — they get wiped. Solution — manage all grants через dbt config.

Schema-level vs table-level grants

Grants in dbt are table-level. Some warehouses also support schema-level:

-- Schema-level (Snowflake)
GRANT USAGE ON SCHEMA marts TO analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA marts TO analyst_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA marts TO analyst_role;

dbt doesn’t directly manage schema-level grants. Patterns:

1. On-run-start hook:

on-run-start:
  - "{% if target.name == 'prod' %} GRANT USAGE ON SCHEMA marts TO analyst_role; {% endif %}"

Applies once при каждом dbt run. Idempotent.

2. Future grants (Snowflake):

-- One-time setup
GRANT SELECT ON FUTURE TABLES IN SCHEMA marts TO analyst_role;

All new tables automatically granted. dbt creates new table -> grant applies. But dbt grants config could still revoke!

Solution: don’t use FUTURE grants with dbt grants config — choose one mechanism.

Anti-patterns

1. Hardcoded grants in environment-specific way

+grants:
  select: ['alice_role', 'bob_role']   # specific developers!

Brittle — if Alice leaves, manual cleanup needed. Use group roles (analyst_role includes Alice and Bob).

2. Grants in dev

+grants:
  select: ['analyst_role']

Without target.name guard — applies в dev. analyst_role grants on developer’s personal schemas. Confusing. Use {{ [...] if target.name == 'prod' else [] }}.

3. Too many roles, hard to audit

+grants:
  select: ['role_a', 'role_b', 'role_c', 'role_d', 'role_e', 'role_f']

Hard to audit. Use role hierarchies (Snowflake): analyst_role includes finance + marketing + product roles. One role at table level, role membership manages who.

4. Grants only некоторых моделей в marts

Inconsistent. Bot stunned: ‘Why does this mart have grants but next one doesn’t?’. Use +grants at marts level — all inherit.

5. Forgot grants on new model

Without +grants inheritance — new mart needs explicit grants. Solution — set defaults at folder level (marts/+grants), new models inherit automatically.

6. Stale grants после rename

If model fct_orders renamed to fct_order_lines, old grants on fct_orders (now dropped) — gone. New grants on fct_order_lines need re-apply. dbt handles это automatically at run.

7. Manual SQL grants conflict with dbt

-- Manually run
GRANT SELECT ON marts.fct_orders TO new_role;

Next dbt run — dbt overwrites grants from config. new_role лost.

Solution: add new_role to YAML config. Persist via dbt.

CI gate для grants

# scripts/check_grants.py
import json

with open('target/manifest.json') as f:
    manifest = json.load(f)

errors = []
for node in manifest['nodes'].values():
    if node['resource_type'] != 'model':
        continue

    is_mart = any(p == 'marts' for p in node['fqn'])
    if not is_mart:
        continue

    grants = node.get('config', {}).get('grants', {})
    if not grants.get('select'):
        errors.append(f'Mart {node[\"name\"]} has no SELECT grants — consumers will fail')

if errors:
    print('\n'.join(errors))
    exit(1)

Catches missing grants на consumer-facing models.

Попробуй сам

1. Add grants to one mart model:

   - name: customer_metrics
     config:
       grants:
         select: ['analyst_role']

2. Run on Postgres/Snowflake:

   dbt run --select customer_metrics

3. Verify:

   -- Snowflake
   SHOW GRANTS ON TABLE customer_metrics;

4. Add per-target conditional:

   grants:
     select: "`{{ ['analyst_role'] if target.name == 'prod' else [] }}`"

5. Test cumulative:

   # dbt_project.yml
   models:
     my_project:
       +grants:
         select: ['public']
       marts:
         +grants:
           select: ['analyst_role']

6. Remove role from config — verify dbt revokes на next run.

Ключевые выводы

1. grants config (1.4+) — declarative permissions в YAML, applied automatically при dbt run. select, insert, update privilege types.
2. Cumulative +grants: combines roles from project / folder / model level. Total permissions = union.
3. Override grants: in model config — replaces inherited grants. Useful для exceptions.
4. Per-environment через target.name: {{ ['role'] if target.name == 'prod' else [] }}. Avoid grants в dev на personal schemas.
5. Warehouse support: Snowflake full, Postgres full, BigQuery (через IAM, slightly different), Redshift full, DuckDB limited.
6. Patterns: public marts / private staging, granular per-business-unit, PII protection, tiered access.
7. dbt revokes out-of-band grants — YAML is source of truth. Manage all grants via dbt.
8. Anti-patterns: hardcoded developer names, grants в dev без guard, too many roles (use hierarchies), inconsistent грантеs across marts, manual SQL grants (conflicts).