Warehouse privileges¶

dbt charts Cloud

This page describes dbt charts Cloud, the hosted product — these features are not part of the open source dct engine.

dbt charts only ever reads from your warehouse. It runs SELECT queries to draw boards and never writes to your tables. The credential you give a connection should be able to do exactly that much and no more.

One exception is worth knowing before you scope the role: a board may declare setup_sql, which is allowed to create temporary functions, tables, and views for the length of its own query session. Nothing it creates outlives the session or touches your data. If your boards use it, the role needs whatever temporary- object privilege your warehouse requires — on PostgreSQL that is TEMPORARY on the database.

This page covers creating a read-only role and granting it access to the tables your boards read.

Don't reuse your dbt credential¶

The most convenient credential is usually the one already in your project's profiles.yml, and it is the wrong one. That credential builds your models — it holds CREATE, and usually DROP, on the schemas dbt writes to. Handing it to a reporting tool gives that tool the ability to destroy the warehouse it is supposed to be reading.

Cloud pre-fills a new connection's host, database, and other non-secret settings from your committed profiles.yml, because that saves you looking them up. Secrets are never read from your repository. Enter a separate read-only credential in the password field.

1. Create the role¶

dbt does not create roles or users, so this part is a one-time piece of SQL you run yourself. Substitute your own names, schemas, and a strong password.

PostgreSQL¶

CREATE ROLE dbt_charts_ro LOGIN PASSWORD 'replace-me';
GRANT CONNECT ON DATABASE analytics TO dbt_charts_ro;
GRANT USAGE ON SCHEMA analytics TO dbt_charts_ro;

Redshift¶

CREATE USER dbt_charts_ro PASSWORD 'replace-me';
GRANT USAGE ON SCHEMA analytics TO dbt_charts_ro;

Snowflake¶

CREATE ROLE dbt_charts_ro;
CREATE USER dbt_charts_svc PASSWORD = 'replace-me' DEFAULT_ROLE = dbt_charts_ro;
GRANT ROLE dbt_charts_ro TO USER dbt_charts_svc;

GRANT USAGE ON WAREHOUSE reporting_wh TO ROLE dbt_charts_ro;
GRANT USAGE ON DATABASE analytics TO ROLE dbt_charts_ro;
GRANT USAGE ON SCHEMA analytics.marts TO ROLE dbt_charts_ro;

A Snowflake connection needs USAGE on a warehouse as well as on the data — without it the role cannot run a query at all.

BigQuery¶

BigQuery uses IAM rather than SQL. Create a service account, then give it two roles: roles/bigquery.jobUser on the project so it can run queries, and roles/bigquery.dataViewer on each dataset it should read.

gcloud iam service-accounts create dbt-charts-ro --project my-project

gcloud projects add-iam-policy-binding my-project \
  --member "serviceAccount:dbt-charts-ro@my-project.iam.gserviceaccount.com" \
  --role roles/bigquery.jobUser \
  --condition=None

--condition=None states the binding is deliberately unconditional. Without it, gcloud refuses to add a binding on any project whose IAM policy already contains conditional bindings and prompts interactively instead.

Grant roles/bigquery.dataViewer per dataset rather than project-wide, so the service account sees only what it needs.

2. Grant read access — with dbt¶

This is the recommended path for anything dbt builds. dbt has a grants config that is declarative, lives in git alongside the models it describes, and is re-applied on every run. Add one block to dbt_project.yml:

models:
  my_project:
    +grants:
      select:
        - dbt_charts_ro

On your next dbt run, dbt grants SELECT on every model it builds to dbt_charts_ro. New models are covered automatically as they are added, which is the part that hand-written SQL cannot do.

Start at this level — one grant covering everything your project builds. It is the right amount of access to begin with, and dbt charts can help you narrow it later once you have boards to narrow against.

Snowflake and BigQuery normalize identifiers differently, so write the grantee name the way your warehouse expects it. On BigQuery the grantee is an IAM principal, for example serviceAccount:dbt-charts-ro@my-project.iam.gserviceaccount.com.

Narrowing to part of your project¶

The same config works at any level of the models: tree, so a folder can grant differently from the project default:

models:
  my_project:
    +grants:
      select:
        - dbt_charts_ro
    finance:
      +grants:
        +select:
          - finance_team

Note the + on +select:. Inside a grants block, a privilege written as select: replaces whatever was inherited, while +select: adds to it. Written without the + above, models under finance/ would be readable by finance_team and no longer by dbt_charts_ro.

This is easy to get wrong and fails quietly — one model out of hundreds stops being readable. If you take one thing from this page, take the +.

3. Grant read access — by hand¶

Raw SQL is the right tool in two cases: sources dbt does not build (a landing schema loaded by something else), and one-off situations where you would rather apply the privilege yourself.

PostgreSQL and Redshift¶

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

ALTER DEFAULT PRIVILEGES only covers objects created by the role that runs it, so run it as the role that loads that schema — otherwise new tables will not be covered.

Snowflake¶

GRANT SELECT ON ALL TABLES IN SCHEMA analytics.raw TO ROLE dbt_charts_ro;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.raw TO ROLE dbt_charts_ro;
GRANT SELECT ON ALL VIEWS IN SCHEMA analytics.raw TO ROLE dbt_charts_ro;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA analytics.raw TO ROLE dbt_charts_ro;

ALL TABLES does not include views, and FUTURE grants are what cover objects created later — all four statements matter.

Hand-written grants and dbt do not mix¶

If you grant by hand on a relation dbt builds, expect to lose it. There are two separate ways that happens, and both are worth knowing.

Once a model has any grants: config, dbt owns its whole grant set. On the next run dbt lists the relation's current grants, compares them to what your config declares, and revokes anything it finds that you did not configure. A privilege you granted by hand is not merely forgotten — it is explicitly revoked. On Redshift and BigQuery this is the behaviour you will hit.

On PostgreSQL and Snowflake, a full refresh drops grants outright. Both treat a replaced relation as a new object that does not inherit the old one's privileges, so dbt run --full-refresh loses hand-issued grants regardless of config. (Snowflake can be told otherwise per model with copy_grants, which defaults to off.)

There is one case where a hand-issued grant does survive: a project with no grants: config anywhere. dbt skips grant handling entirely, so nothing is revoked — until the relation is next replaced, on the warehouses above.

The practical rule is simple. If dbt builds it, grant it in +grants:. If dbt does not build it, grant it by hand.

Verifying it worked¶

A connection test tells you the credential can reach the warehouse and log in. Being able to log in is not the same as being able to read your tables — a role with no SELECT anywhere still authenticates successfully.

After granting, run dbt run so the grants are applied, then open a board. If a query fails on permissions, the message names the relation the role cannot read, which is the one to add.

Data connections — creating and testing a connection
Access control — which people can see which boards, a separate concern from what the warehouse credential can read

Warehouse privileges¶

Don't reuse your dbt credential¶

1. Create the role¶

PostgreSQL¶

Redshift¶

Snowflake¶

BigQuery¶

2. Grant read access — with dbt¶

Narrowing to part of your project¶

3. Grant read access — by hand¶

PostgreSQL and Redshift¶

Snowflake¶

Hand-written grants and dbt do not mix¶

Verifying it worked¶

Related¶