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.

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.

1. 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.

1. 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.

1. 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.

1. 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