Skip to content

All guides

Guide · October 2026

How to give Claude read-only access to your data warehouse, with a spending cap

Create a database role that can only read. Give it compute of its own with a hard monthly limit. Then connect Claude with that role's credentials and no others.

Set both limits in the warehouse itself. An instruction in a prompt is a request, and a read-only switch in a connector is code that can fail. A role that was never granted a write cannot be talked into one.

This guide gives the commands for Snowflake and for Postgres, then the checks that show the setup holds. Each block of commands says whether it was run on this site's demo setup or follows the vendor's documentation. It is the arrangement behind the AI data assistant I built: a read-only role on the few views it needs, with a monthly spending cap.

Why the limits belong in the warehouse

Claude writes the SQL it runs. Most of the time that is the query you wanted. The setup has to hold on the day it is not: a question is misread, or a document Claude was given carries instructions of its own.

Three things can stand between the model and your data: a sentence in the prompt, a read-only mode in the connector, and the privileges of the database role. Only the last is enforced by the database.

The middle one has failed in public. In August 2025, Datadog Security Labs showed (opens in a new tab) that the reference Postgres MCP server, which ran every query inside a read-only transaction, could be handed a query that ended the transaction and ran anything after it. That server is now archived. Datadog's advice included the plain one: connect as a database user with restricted privileges.

1. A role that can only read

Start from nothing and add. The role may connect, see one schema and run select on what you choose. It owns nothing and belongs to no other role.

PostgresRun on this site's demo setup
create role ai_readonly login;
revoke all on database chinook from public;
revoke all on schema public from public;
grant connect on database chinook to ai_readonly;
grant usage on schema public to ai_readonly;
grant select on all tables in schema public to ai_readonly;

The two revoke lines matter. By default every Postgres role may connect to a database and create temporary tables in it (opens in a new tab). The first revoke removes that default for every role, and the second does the same for the public schema. On a database other people use, check what relies on those defaults before you run them.

grant select on all tables covers the tables that exist today. A table added later cannot be read until you grant it. For an AI, I would keep it that way.

Where you can, grant views made for the purpose, not raw tables. A view can leave out the columns an assistant has no reason to read.

SnowflakeFrom the documentation, not run here
CREATE ROLE ai_readonly;
GRANT USAGE ON DATABASE analytics TO ROLE ai_readonly;
GRANT USAGE ON SCHEMA analytics.reporting TO ROLE ai_readonly;
GRANT SELECT ON ALL VIEWS IN SCHEMA analytics.reporting TO ROLE ai_readonly;

Snowflake has no read-only switch for a role. The role is read-only because it holds USAGE and SELECT and nothing that writes: unless a grant allows it, access is denied (opens in a new tab). The database and schema names are examples.

One list to read before you go on. Every Snowflake role also holds whatever has been granted to the built-in PUBLIC role. Run SHOW GRANTS TO ROLE PUBLIC; and read the result as part of the AI's role, because it is.

2. Compute of its own, with a monthly cap

A role that can only read can still spend. Queries are what a warehouse bills for, and one careless join over a large table costs the same whoever wrote it.

On Snowflake the cap has three parts: a small warehouse that nothing else uses, a timeout on that warehouse, and a resource monitor (opens in a new tab) that suspends it when the month's credits are spent.

SnowflakeFrom the documentation, not run here
CREATE WAREHOUSE ai_wh
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  STATEMENT_TIMEOUT_IN_SECONDS = 120;

CREATE RESOURCE MONITOR ai_monthly_cap WITH
  CREDIT_QUOTA = 20
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE ai_wh SET RESOURCE_MONITOR = ai_monthly_cap;
GRANT USAGE ON WAREHOUSE ai_wh TO ROLE ai_readonly;

The numbers are examples. The quota is counted in credits, not dollars. Snowflake lists (opens in a new tab) a first-generation X-Small warehouse at one credit for each hour it runs, so 20 credits is about 20 hours of queries a month. Multiply by what you pay for a credit to see the cap in money.

Three details carry the weight:

  • SUSPEND_IMMEDIATE cancels what is running when the quota is reached. SUSPEND would let running queries finish first.
  • The timeout sits on the warehouse, where a session cannot raise it. When a session and a warehouse both set one, Snowflake applies the lower (opens in a new tab).
  • The role gets USAGE on this warehouse and on no other. A role that can use a second warehouse can spend there instead.

Creating the monitor and attaching it to the warehouse both take the ACCOUNTADMIN role.

Know what the cap does not cover. A resource monitor tracks warehouses only, not serverless features or Snowflake's AI functions, and by default every role may call those functions (opens in a new tab). Decide whether the AI's role should. The monitor is also not exact: Snowflake says a warehouse can take time to suspend, so set the quota a little under your real limit.

Postgres itself has no credits to cap. What a role can carry is two defaults:

PostgresRun on this site's demo setup
alter role ai_readonly set default_transaction_read_only = on;
alter role ai_readonly set statement_timeout = '15s';

Set both, and count on neither. They are defaults for the role's sessions, and Postgres lets a role change its own. Step 4 shows that happening. If the database also runs your product, point Claude at a read replica instead. A hot standby (opens in a new tab) refuses every write whatever the role asks for, and a heavy query there runs on the replica's hardware, not the primary's.

3. Connect Claude with that role and no other

However Claude reaches the warehouse, through an MCP server, a connector or a command-line client, it should hold one set of credentials: the read-only role's. Not yours, and not an admin's that a tool promises to use gently.

In Claude Code, the documentation's own example adds a Postgres database as an MCP server and says the same: use a read-only database user (opens in a new tab) in the connection string. This site's demo uses no server at all. Claude Code runs in a shell whose environment names the read-only role (PGUSER=ai_readonly), and psql is the one command it is allowed to run:

ShellRun on this site's demo setup
claude -p "How much revenue did we make in 2025, and from how many customers?" --allowedTools "Bash(psql *)"

On Snowflake, the Snowflake-managed MCP server (opens in a new tab) is the shortest path, and Claude connects to it as a connector. Two things in its documentation matter here. Its SQL tool is read-only unless you switch that off, and it takes a warehouse and a timeout: name the capped warehouse. And when a person connects by signing in, the session runs as that person's default role, with everything that role can do. Snowflake's advice is to limit the connection to one least-privileged role. Make it the role from step 1.

If Claude connects with a token instead, issue the token to a service user that holds only this role. A Snowflake programmatic access token can be restricted to a single role (opens in a new tab).

4. Try to break it

Do not take a setup like this on trust, this one included. Connect as the role and try to do damage.

These checks ran on 10 October 2026 against this site's demo database, a public sample dataset on Postgres 17, as the ai_readonly role. The output is as Postgres printed it, with some lines cut for length. Every write sits inside a transaction that is rolled back, so the test cannot change anything. The first line is a psql setting that keeps a transaction open after a statement is refused.

psql, as ai_readonlyRun on this site's demo setup
\set ON_ERROR_ROLLBACK on
begin;
BEGIN
delete from invoice;
ERROR:  cannot execute DELETE in a read-only transaction
rollback;
ROLLBACK
begin read write;
BEGIN
delete from invoice;
ERROR:  permission denied for table invoice
drop table invoice;
ERROR:  must be owner of table invoice
create table scratch (id int);
ERROR:  permission denied for schema public
create temp table scratch (id int);
ERROR:  permission denied to create temporary tables in database "chinook"
grant insert on invoice to ai_readonly;
WARNING:  no privileges were granted for "invoice"
GRANT
rollback;
ROLLBACK

Read it in two halves. The first delete is refused because the role's transactions are read-only (opens in a new tab) by default. Then the role opens a read-write transaction, which any role may do, and that default is gone. What refuses the next four statements is the grants: the role may not delete from the table, does not own it, and may not create anything, not even a temporary table. In the last one the role tries to give itself more. Postgres answers with a warning and grants nothing.

So the read-only default is a seat belt and the grants are the wall. The rest of the session tests the two defaults themselves:

psql, as ai_readonlyRun on this site's demo setup
begin read write;
BEGIN
alter role ai_readonly set default_transaction_read_only = off;
ALTER ROLE
alter role ai_readonly set statement_timeout = 0;
ALTER ROLE
rollback;
ROLLBACK
select pg_sleep(20);
ERROR:  canceling statement due to statement timeout
Time: 15002.929 ms (00:15.003)
set statement_timeout = 0;
SET
select pg_sleep(16);
(1 row)
Time: 16005.411 ms (00:16.005)

The role changes its own defaults with alter role, and Postgres accepts both statements. Only the rollback kept them from lasting. Then the timeout does its job and stops a 20 second statement at 15 seconds, until the session sets its own timeout to zero and a 16 second statement runs to the end.

None of this lets the role write, and all of it is documented behavior (opens in a new tab). It tells you which part of the setup to rely on. On Postgres, rely on the grants. On Snowflake, the timeout and the cap sit on the warehouse and the monitor, which the role has no privilege to change.

On Snowflake, try the same writes as the role, then read three lists. SHOW GRANTS TO ROLE ai_readonly; should show USAGE and SELECT and nothing else. SHOW GRANTS TO ROLE PUBLIC; shows what the role inherits. In SHOW RESOURCE MONITORS; the monitor's level should read WAREHOUSE: a monitor attached to nothing limits nothing (opens in a new tab).

5. Write it down

Keep one page for each connection: the role, what it can read, the warehouse and its cap, who holds the credentials and how to turn it off. When someone asks what the AI can touch, that page is the answer.

Turning it off should be one statement. On Postgres, alter role ai_readonly nologin; refuses new connections (opens in a new tab) for the role, and leaves open the sessions it already has, so end those too. On Snowflake, REVOKE USAGE ON DATABASE analytics FROM ROLE ai_readonly; takes the data out of the role's reach, and the matching GRANT puts it back. Neither statement was run for this page.

Questions

Is a read-only mode in the connector enough?

No. Keep it on, and still connect with a role that cannot write. A connector's read-only mode is code in front of the database, and code can fail. The grants are checked by the database itself, on every statement.

Can a read-only role still run up a bill?

Yes. Queries are what a warehouse charges for, and a read-only role can run as many as it likes. That is why the role in step 1 comes with the cap in step 2.

Does the cap cover what Claude itself costs?

No. It limits what the warehouse can bill. What the model costs is limited where you buy it, and that is a separate setting.

What about BigQuery?

The same two parts under other names. Read access is the BigQuery Data Viewer role (opens in a new tab) on a dataset, plus BigQuery Job User on a project so that Claude can run queries. For cost, BigQuery has custom quotas (opens in a new tab) on query usage, for a project and for each user in it. They differ from a Snowflake monitor in three ways: they reset every day, not every month, they apply to on-demand pricing only, and Google calls them approximate.

Do you set this up for teams?

Yes. It is part of the Two-Week AI Setup: every connection is read-only by default, with a scoped role and a monthly spending cap, and a one-page record of what it can access and how to turn it off. See what is included.

Sources

Set your team up properly.

A 20-minute call to see if it's a fit. If it isn't, I'll tell you what I'd do instead.

Book a 20-minute fit call

Know a team that needs this?

Book a 20-minute fit call