Text-to-SQL with Claude: A Safe Read-Only Postgres Setup

Text-to-SQL is safe when the connection cannot write, not when the prompt says not to. This guide sets up Claude against Postgres in the order that matters: a read-only role first, the MCP server in its read-only mode second, schema and metric context third, and only then a question. The last two steps, reading the SQL and reconciling the number, are what turn a plausible answer into one you can put in front of someone.

It assumes you already have Claude in the terminal; if not, Claude Code for data analysts covers the repo setup and Claude for data analysis is the wider pillar. The concept itself is defined in the glossary under text-to-SQL.

Step 1: Create a read-only role

Do this before you install anything. Every other guard in this guide sits above the database; this one sits inside it.

CREATE ROLE claude_ro LOGIN PASSWORD 'use-a-generated-secret';
 
GRANT CONNECT ON DATABASE analytics TO claude_ro;
GRANT USAGE ON SCHEMA public TO claude_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO claude_ro;
 
-- Cover tables created after today
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO claude_ro;
 
-- Defaults for every session this role opens
ALTER ROLE claude_ro SET default_transaction_read_only = on;
ALTER ROLE claude_ro SET statement_timeout = '30s';

On Postgres 14 and later, GRANT pg_read_all_data TO claude_ro; is a shortcut: the predefined role reads all tables, views, and sequences as if it held SELECT on them and USAGE on every schema. It does not bypass row-level security, so if you use RLS the policies still apply, which is usually what you want here.

Two honest notes about default_transaction_read_only. It is a default, not a privilege: a session can set it back off. And its scope is the transaction, not the connection. It is worth setting because it turns an accidental write into an immediate error, but the GRANT list above is the layer that holds when everything else fails. If your warehouse is Snowflake, BigQuery, or Databricks rather than Postgres, the shape is identical: a role with read privileges on named objects, a statement timeout, and nothing else.

Step 2: Add the MCP server in read-only mode

For self-hosted Postgres, Postgres MCP Pro is the maintained server:

claude mcp add --scope project \
  --env DATABASE_URI="postgresql://claude_ro:...@host:5432/analytics" \
  --transport stdio postgres -- uvx postgres-mcp --access-mode=restricted

--access-mode=restricted limits the server to read-only transactions with an execution-time cap. It also parses each statement before running it and rejects SQL containing COMMIT or ROLLBACK, which closes the obvious escape (ROLLBACK; DROP TABLE users;) from a read-only transaction. Its own documentation is candid that Postgres has no connection-level read-only switch, which is exactly why the role in step 1 is not optional.

For Supabase-hosted Postgres, the official Supabase server is the better fit and its safety posture lives in the URL:

claude mcp add --scope project --transport http supabase \
  "https://mcp.supabase.com/mcp?project_ref=<your-project>&read_only=true&features=database,docs"

read_only=true runs queries as a read-only Postgres user, project_ref pins the server to one project and drops the account-management tools, and features= limits it to the tool groups you name. --scope project writes the server to .mcp.json at the repo root so the team inherits the same configuration; each person approves it once. Alternatives to both servers are compared in the best text-to-SQL tools.

Before you ask a real question, prove the setup. Ask Claude to run CREATE TABLE _probe (id int); and confirm it comes back as a permission error, not a success. A read-only setup that has never been tested is a belief, not a control, and the failure mode is discovering it during an incident. General MCP mechanics, including scopes and troubleshooting, are in the Claude Code MCP setup guide.

Step 3: Give Claude the schema and the definitions

This is the step that decides whether the output is any good, and it is the one most people skip. A model writing SQL is doing schema archaeology: it reads table and column names, types, and whatever comments exist, and guesses the rest. Ambiguity in the schema becomes ambiguity in the answer.

Three things to supply, in order of payoff:

1. Column comments, in the database. COMMENT ON COLUMN orders.status IS 'paid | refunded | pending; refunds keep the original row' travels with the schema, so every tool sees it, not just this session.
2. A metric definitions file. One markdown file naming the canonical table for each subject area, the grain of each table (one row per what?), the join keys, the filters every query must apply (is_internal = false, is_test = false), and the exact definition of each metric including its timezone. Reference it from CLAUDE.md.
3. Three worked examples. A correct query for a simple lookup, one for a cohort, and one for a period comparison. Examples teach conventions faster than prose.

If your organization already maintains a semantic layer, point Claude at it and skip most of the above: that is the same job, done centrally and versioned.

Step 4: Ask, then read the SQL before it runs

Ask in business terms, not in SQL: "Monthly paid revenue by product line for the trailing 12 months, calendar months in America/New_York, using the definitions in docs/metrics.md. State your assumptions before you write the query."

Then read what comes back. The five things that go wrong most often, in the order they are easiest to spot:

• Wrong table. A staging or shadow copy that looks canonical.
• Wrong join type. An inner join where a left join was meant silently drops the rows you were counting.
• Filter after aggregation. A HAVING where a WHERE belonged, or the reverse, moves the number without moving the shape.
• Date boundaries. UTC timestamps bucketed into local months without conversion shift revenue across the month end.
• Grain. A GROUP BY one level off produces double counting that looks like growth.

Make this a keystroke rather than a habit: put the query tool in the ask list in .claude/settings.json, exactly as mcp__postgres__execute_sql with no parentheses, and Claude Code prompts you before every statement.

Step 5: Validate the number two ways, then save it

One number from one query is a hypothesis. Reconcile it against something you already trust (last month's close, the existing dashboard, a total you know by heart), then recompute it by a different route: a daily breakdown that sums to the monthly figure, or a count of distinct IDs against a count of rows. Gaps are informative in both directions. The full pass, including the failure modes that survive both checks, is How to check an AI data analysis.

When it reconciles, save the SQL in your repo next to the definition it implements, with a comment naming the question and the date you validated it. That file, not the chat transcript, is what makes next month's answer match this month's.