agent's grant fix used the wrong adapter syntax: grants silently ignored

TL;DR

dbt grants config is translated per adapter, and syntax borrowed from another warehouse (or a post-hook with foreign SQL) can no-op or apply to the wrong object type. Define grants in the model YAML using dbt grants structure for your adapter, run the model, then verify the effective grants directly in the warehouse (information schema or show grants) rather than trusting the green run.

agent's grant fix used the wrong adapter syntax: grants silently ignored

Use this when

• models build successfully but consumers still get access denied on the new tables
• grant config or post-hooks were copied from a project on a different warehouse
• warehouse grant listings show the expected privileges missing or on the wrong object

Not for this skill when

• the role itself does not exist in the warehouse, create it first through your admin process
• grants are applied but on a previous table version that was replaced, check your materialization swap behavior
• access works via a group role the consumer lacks, that is membership, not object grants

Steps

1. Inspect how grants are declared: find grants blocks in YAML or config and any grant post-hooks, and note the exact privileges and grantee names. Verify: the declared grants and their mechanism (config vs hook) are known.

2. Translate to your adapter documented form: use the dbt grants config structure (select, insert and similar keys mapping to role lists) for your warehouse, and remove hand-written grant SQL written for another dialect. Verify: grants are declared in the adapter-correct dbt structure.

3. Run dbt run --select for one affected model so grants apply on creation or via the grants step, watching for warnings that are easy to miss in logs. Verify: the run completes and the log shows the grants application for the model.

4. Verify in the warehouse directly: query the adapter grant views (for example information_schema privileges or show grants on the table) and test a select as the consumer role. Verify: the warehouse shows the intended privileges and the consumer role can read the table.

Variant phrasings

works for tables, not for views (or the reverse)

Some adapters apply grants differently per materialization, and copy grants settings matter when tables are replaced. Check the adapter docs for your materialization mix.

future grants versus current grants

On Snowflake-style systems, existing objects need current grants while future grants cover new objects in a schema. An agent fixing only one leaves the other broken; verify both.

Compatibility: dbt-core grants configuration (1.3 and later) with adapter-specific translation: Snowflake, BigQuery (dataset and table ACL style differences), Postgres, and Redshift each differ. Verify with warehouse-native grant inspection.

Why it happens

Grants sit at the seam between dbt config and warehouse security models, which do not map one to one across vendors. dbt translates what it can and adapters vary in strictness: some ignore unknown privilege names or apply grants only at creation time, so a wrong-dialect declaration can produce a green run and zero effective change. Only reading grants back from the warehouse closes the loop.

Edge cases / pitfalls

• Table replacement materializations can drop grants applied to the old table; prefer config-driven grants that dbt reapplies each run.
• Case sensitivity of role names differs by warehouse; a grant to a differently cased role can target nothing real.
• Keep a consumer-role smoke test in CI for critical models; grant regressions are otherwise discovered by end users.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_QmGd4ibCJ3917kvcegVDaA