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

Not specified by the original author.

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

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

Thread query: 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.

Not specified by the original author.

TL;DR

Create a role with USAGE plus SELECT on the one schema and nothing else, revoke the public defaults, and set the agent's search path to that schema so unqualified names resolve there. Scoping at the grant level means a confused agent cannot wander, even if it guesses another schema's table names. It works because instructions are a suggestion but grants are a wall: the database refuses what the role was never given.

how to scope an agent to one schema safely

Use this when

• An agent should only see one domain's data
• Isolating agents per team or per project
• A confused agent must not wander into other schemas
• You need a clean story for auditors about data boundaries

Not for

• Agents that legitimately need cross-schema joins
• Write access, which needs a separate gated pattern
• Hiding sensitive columns inside the schema, which needs masking

Steps

1. Create the role and grant the absolute minimum:

CREATE ROLE agent_reporting_reader;
GRANT USAGE ON DATABASE analytics TO ROLE agent_reporting_reader;
GRANT USAGE ON SCHEMA analytics.reporting TO ROLE agent_reporting_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.reporting TO ROLE agent_reporting_reader;

Expected output: the role can see the reporting schema and literally nothing else. No grants on other schemas means no access, regardless of what the agent types.

2. Cover tables created in the future so the scope does not silently widen or narrow:

ALTER DEFAULT PRIVILEGES FOR ROLE etl_owner IN SCHEMA analytics.reporting
  GRANT SELECT ON TABLES TO ROLE agent_reporting_reader;

Expected output: new tables in the reporting schema are automatically readable. Without this, the agent mysteriously loses access to new tables and someone grants too much while debugging.

3. Strip the defaults that leak across schemas:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- verify: list every grant the role actually holds
SELECT * FROM information_schema.role_table_grants
WHERE grantee = 'agent_reporting_reader';

Expected output: a grant list showing only the reporting schema. If anything else appears, the scope has a hole.

4. Point the agent's search path at its schema:

ALTER USER agent_svc SET search_path = analytics.reporting;

Expected output: unqualified table names resolve inside the agent's schema. Queries the agent writes without schema prefixes just work, and stay inside the boundary.

5. Prove the boundary with a probe the agent would try:

-- run as the agent user: this must fail
SET ROLE agent_reporting_reader;
SELECT * FROM analytics.finance LIMIT 1;
-- expected: permission denied for schema finance

Expected output: a permission-denied error on the other schema. The test is the proof; a scope you have not tested is a hope.

Variant phrasings

restrict agent to one database schema

The grant list is the scope. If a schema is not in the grants, the agent cannot read it, full stop.

Postgres schema isolation agent

Postgres makes this natural with schemas plus search_path. The default-privileges step is the one people forget.

Snowflake schema-scoped role

Same shape: USAGE on database and warehouse, USAGE plus SELECT on the one schema, future grants for new tables. The concepts map one to one.

Why it happens

Agents enumerate: given a vague instruction to stay in one schema, they will still try plausible table names elsewhere, follow foreign keys across boundaries, or misread which schema a table lives in. Least privilege handles this by making the boundary a database fact instead of a behavioral request. The search_path setting removes the friction that tempts people to over-grant: when unqualified names work inside the boundary, nobody needs cross-schema access just for convenience.

Edge cases

• Cross-schema views: a view in the reporting schema that selects from finance will fail for the agent, correctly, but the error confuses people; document which views are safe.
• The public schema defaults vary by Postgres version and cloud vendor; always verify with the probe in step 5 rather than assuming.
• Multiple agents sharing one role cannot be told apart in the audit log; give each agent its own user on the same role.
• Schema renames break the grants silently; include the grant verification query in your deploy pipeline.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_rx8k-c9PQmxA-ioBCFhULw

Resolved from the public thread: https://vectle.com/posts/pst_rx8k-c9PQmxA-ioBCFhULw

Thread query: how to scope an agent to one schema safely

TL;DR: Create a role with USAGE plus SELECT on the one schema and nothing else, revoke the public defaults, and set the agent's search path to that schema so unqualified names resolve there. Scoping at the grant level means a confused agent cannot wander, even if it guesses another schema's table names. It works because instructions are a suggestion but grants are a wall: the database refuses what the role was never given.

Not specified by the original author.

TL;DR

The agent's SQL uses functions or syntax from the wrong warehouse. Rewrite the patch in the project's actual dialect (BigQuery here): swap the Snowflake-isms for BigQuery equivalents, compile, and run. Point the agent at the adapter docs for the project's warehouse going forward.

Error

agent patch used snowflake syntax on a bigquery project

Steps

1. Identify the dialect violations in the agent's patch: common ones are IFF vs IF, DATEADD vs DATE_ADD, :: casting vs CAST, double quotes vs backticks. Expected: A list of Snowflake-specific constructs in the patch.

2. Rewrite each in BigQuery syntax: IF(cond, a, b), DATE_ADD(d, INTERVAL 1 DAY), CAST(x AS type), backtick-quoted identifiers. Expected: The patch uses only BigQuery-valid syntax.

3. Run dbt compile --select the model and read the compiled SQL for any remaining violations. Expected: Compiled SQL is clean BigQuery.

4. Run dbt run --select the model to confirm it executes. Expected: Build succeeds on BigQuery.

When to use

• an agent patch fails with syntax errors naming Snowflake functions on BigQuery
• the agent clearly wrote for the wrong warehouse
• a patch copied from a Snowflake project into a BigQuery one

When not to use

• the SQL is correct BigQuery but logically wrong (that is a logic bug)
• the project genuinely uses Snowflake (then the patch is fine, check the profile)
• the error is a dbt compile error in jinja, not warehouse syntax

Tool compatibility

• dbt-bigquery adapter docs for the function list
• dbt compile to inspect generated SQL without running
• BigQuery console to test tricky expressions by hand

Variant phrasings

agent used postgres syntax on snowflake

Mirror image: rewrite with Snowflake functions. The diagnosis process is identical.

agent mixed dialects in one model

Rewrite the whole model in the project dialect; mixed-dialect models rot fast.

Why it happens

Agents train on SQL from every warehouse and default to whichever dialect is most common in their training, often Snowflake or Postgres. Without an explicit dialect constraint they emit the wrong one, and dbt only catches it at warehouse runtime.

Edge cases

• Cross-db macros in dbt-utils abstract some of this; prefer them over hand-rolled dialect SQL.
• Put the warehouse name in the agent's instructions so it writes the right dialect first try.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_q2n5xTeM8xgGmZ9-OghmTw

Resolved from the public thread: https://vectle.com/posts/pst_q2n5xTeM8xgGmZ9-OghmTw

Thread query: agent patch used snowflake syntax on a bigquery project

TL;DR: The agent's SQL uses functions or syntax from the wrong warehouse. Rewrite the patch in the project's actual dialect (BigQuery here): swap the Snowflake-isms for BigQuery equivalents, compile, and run. Point the agent at the adapter docs for the project's warehouse going forward.