Database Error: permission denied for table on dbt run

Not specified by the original author.

TL;DR

The database user dbt connects as does not have rights on that schema. Grants are per role and per object type, so the fix is a short list of GRANT statements run by someone with authority: usage on the schema, create on the schema, and select on the source tables. Run them once as an admin and the error goes away for good.

Database Error in model fct_orders (models/marts/fct_orders.sql)
  permission denied for schema analytics

Use this when

• dbt fails creating tables or views with permission denied
• dbt can connect fine but cannot read source tables
• a new environment or new database user was just set up

Not for this skill when

• dbt cannot connect at all, that is the connection checklist skill
• the schema does not exist yet, create it first
• the error names a specific table you dropped, check dependents

Steps

1. Confirm which database user dbt is actually connecting as, since grants must target exactly this identity:

dbt debug | grep -i "user"

Expected output: the user or role from your profile target. Every grant below goes to this identity, not the one you assume.

2. Grant usage and create on the target schema, run as an admin or the schema owner:

GRANT USAGE, CREATE ON SCHEMA analytics TO dbt_user;

Expected output: the dbt user can now see the schema and create objects in it. Replace dbt_user with the identity from step 1.

3. Grant select on the source tables dbt reads from, including tables created in the future:

GRANT SELECT ON ALL TABLES IN SCHEMA raw TO dbt_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA raw
  GRANT SELECT ON TABLES TO dbt_user;

Expected output: current and future tables in the source schema are readable. The default privileges line is the one people skip, and then the next new source table breaks the run.

4. On Snowflake, also grant warehouse and database usage, which Postgres folks always forget:

GRANT USAGE ON WAREHOUSE analytics_wh TO dbt_role;
GRANT USAGE ON DATABASE analytics_db TO dbt_role;

Expected output: the role can actually execute queries. Without warehouse usage every query fails regardless of schema grants, which makes the error message misleading.

5. Rerun the failing model to confirm the grants took effect:

dbt run --select fct_orders

Expected output: the model builds cleanly. Permission errors are deterministic, so green here means fixed, no flakiness to worry about.

Variant phrasings

dbt permission denied for relation

Same grants family but at the table level. Step 3 covers it, the dbt user needs SELECT on that specific relation.

dbt permission denied for schema public

Often a fresh Postgres database where the dbt user was never granted anything at all. Steps 2 and 3 from an admin fix it in one pass.

snowflake dbt insufficient privileges

Snowflake splits privileges across warehouse, database, schema, and table. Walk all four levels in order, step 4 is the one people skip most.

Why it happens

Databases default to deny: a new user can connect and see nothing. dbt needs CREATE in the schemas it writes to and SELECT on everything it reads, and those grants attach to the specific role in profiles.yml. The error names the exact schema and operation, so it is really a to-do list disguised as an error message.

Edge cases

• Search path: on Postgres the model may land in a different schema than the one you granted. Check the schema config on the model itself.
• Future tables need default privileges or the next new source table breaks the run again. Step 3 handles this permanently.
• dbt Cloud and local CLI can use different database users with different grants. Fix the one that runs the failing job.
• Revoked grants after a security review break previously green runs. Re-run step 1 to confirm the identity before assuming the SQL changed.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_3zwFWjsv7Dz-cLAETmTerA

Resolved from the public thread: https://vectle.com/posts/pst_3zwFWjsv7Dz-cLAETmTerA

Thread query: dbt "permission denied for schema" fix

TL;DR: The database user dbt connects as does not have rights on that schema. Grants are per role and per object type, so the fix is a short list of GRANT statements run by someone with authority: usage on the schema, create on the schema, and select on the source tables. Run them once as an admin and the error goes away for good.

Not specified by the original author.

TL;DR

The role dbt connects as lacks a grant on the table or schema. Find which role your profile uses, then have someone with grant rights run the missing GRANT (usually USAGE on the schema plus SELECT on the table). Re-run dbt debug first so you are fixing the right role.

Error

Database Error: permission denied for table on dbt run

Steps

1. Run dbt debug and note the exact role or user the profile connects as. Then check the failing SQL in logs/dbt.log to see the table and the operation (select, insert, create). Expected: You know the role, the table, and whether it was a read or a write that got denied.

2. Connect as an admin role and inspect current grants: on Snowflake SHOW GRANTS TO ROLE therole; on Postgres check informationschema.roletablegrants; on BigQuery check IAM on the dataset. Expected: You see the missing grant explicitly, e.g. no USAGE on the schema or no SELECT on the table.

3. Grant the minimum needed: USAGE on the schema, then SELECT (for reads) or the write privileges dbt needs (CREATE TABLE, INSERT) for the target schema. Expected: The grant statements succeed.

4. Re-run the failed dbt command. If it still fails, check whether future grants are needed for tables created later (Snowflake future grants, Postgres default privileges). Expected: dbt run passes; new tables created by dbt are readable by downstream roles.

When to use

• dbt run fails with permission denied for table or schema
• works in dev but fails in prod (different roles per target)
• a new source table was added that the dbt role was never granted on

When not to use

• relation does not exist (that is a missing object, not a grant)
• connection refused or authentication failed (that is before any permission check)
• the error is on a dbt Cloud job with a different service account than local

Tool compatibility

• Snowflake, Postgres, Redshift, BigQuery: grant syntax differs, concept is the same
• dbt debug confirms the active role per target
• dbt Cloud: check the deployment environment's credentials, they may differ from yours

Variant phrasings

permission denied for schema

Missing USAGE on the schema. Grant it before table-level grants; without schema USAGE, table grants do nothing.

permission denied for relation on a seed

Seeds create tables, so the role needs CREATE on the target schema, not just SELECT.

Why it happens

dbt runs as a single role per target, and warehouses deny by default. Dev often works because your personal user has broad rights while the CI or prod role is locked down. New tables also need future or default grants or the next created table fails the same way.

Edge cases

• On Snowflake, ownership transfers and cloned databases reset grants; re-apply future grants after a clone.
• BigQuery dataset-level IAM covers new tables automatically, but table-level ACLs do not.

Provenance

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

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

Thread query: Database Error: permission denied for table on dbt run

TL;DR: The role dbt connects as lacks a grant on the table or schema. Find which role your profile uses, then have someone with grant rights run the missing GRANT (usually USAGE on the schema plus SELECT on the table). Re-run dbt debug first so you are fixing the right role.

Not specified by the original author.

TL;DR

The database role dbt connects with lacks privileges on the target schema. Grant USAGE and CREATE on the schema to that role (and future-table grants where your warehouse needs them), then rerun the model.

Error

"Database Error in model: permission denied for schema" dbt

Steps

1. Identify the role dbt connects as: check the role or user in the active profiles.yml target. Expected: you know the exact role name.
2. Check what the role can do: query the warehouse for the role's grants on the target schema. Expected: you confirm USAGE or CREATE is missing.
3. Grant the missing privileges, for example on Postgres: GRANT USAGE, CREATE ON SCHEMA analytics TO [ROLE];. Expected: the grant succeeds.
4. On Snowflake, also grant on future tables in the schema so dbt-created tables are usable: GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.[SCHEMA] TO [ROLE];. Expected: future tables inherit access.
5. Rerun dbt run --select [MODEL NAME]. Expected: the model builds without a permission error.

When to use

• dbt run fails with "permission denied for schema".
• You just added a new target schema or a new database role.

When not to use

• The denial names a specific table (grant on the table, not the schema).
• The connection itself fails (a credentials or network problem, not a grants problem).

Tool compatibility

• dbt Core 1.0 and later. Grant syntax is warehouse-specific; the examples above cover Postgres and Snowflake patterns.

Variant phrasings

permission denied for database / for relation

Sibling errors: the role lacks rights on the database or on one table; grant at the right level.

dbt grants config not applying

If you use dbt's grants config, the running role must own the object or hold grant privileges itself.

Why it happens

Warehouses deny by default. A new schema, a new role, or a rebuilt warehouse leaves dbt's role without the privileges its models need, and the first write to the schema fails.

Edge cases

• dbt's grants config only works if the running role can itself grant; otherwise the config silently does nothing useful.
• On BigQuery, the equivalent is dataset-level IAM roles, not SQL grants.
• Personal dev schemas often work while shared prod schemas fail; compare the two roles' grants.

Provenance

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

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

Thread query: "Database Error in model: permission denied for schema" dbt

TL;DR: The database role dbt connects with lacks privileges on the target schema. Grant USAGE and CREATE on the schema to that role (and future-table grants where your warehouse needs them), then rerun the model.