Overview
BI tools are great for dashboards, bad for one-off questions. This build wires Claude Desktop to a read-only Postgres role via the official Postgres MCP server. Ask questions in English, get real SQL back, and see results as tables. The read-only role is the guardrail; the LLM is the interface.
How it works
- Create a read-only role in Postgres with SELECT on the analytics schema only.
- Install and configure the Postgres MCP server pointed at that role.
- Register in claude_desktop_config.json.
- Claude discovers list_tables, describe_table, query tools.
- Every query runs under the read-only user — no chance of accidental writes.
Benefits
- PMs and founders get self-serve analytics without a BI license per seat.
- SQL is generated and shown, so answers are auditable.
- Read-only role means no risk of destructive queries.
Use cases
- Startups with 3-5 analytics questions a day that don't justify a dashboard.
- Data teams offloading ad-hoc requests.
- Product managers exploring hypotheses before opening a Metabase card.
Step-by-step guide
Step 1: Create the read-only role
CREATE ROLE claude_ro LOGIN PASSWORD '...'; GRANT USAGE ON SCHEMA analytics TO claude_ro; GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO claude_ro; ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT SELECT ON TABLES TO claude_ro;
Step 2: Restrict connection source
pg_hba.conf: allow claude_ro only from your machine's IP. Or run behind a Tailscale ACL.
Step 3: Install the MCP server
npm install -g @modelcontextprotocol/server-postgres. Test connection standalone first.
Step 4: Register in Claude
mcpServers.postgres: { command: ..., env: { DATABASE_URL: postgres://claude_ro:...@host/db } }.
Step 5: Prompt with schema context
'The analytics schema has orders, users, events. Use snake_case columns. Prefer window functions over subqueries when possible.'
Step 6: Sanity check + iterate
Ask Claude to run EXPLAIN before large queries. Add row limits by default in your system message.
Example
You: 'What was the DAU trend for the last 30 days, week over week?'
Claude: writes a query using date_trunc, runs it, returns a table + brief interpretation.