Claude Desktop Postgres read-only setup: the secure walkthrough

Connecting Claude Desktop to your Postgres database is one of the highest-leverage MCP setups you can do — Claude answers "what's our weekly signup trend?" in seconds. It's also the one where an insecure setup can cause the most damage. Here's the exact five-minute recipe we run.

The wrong way

Almost every blog post suggests this:

"postgres": {
  "command": "npx",
  "args": ["-y", "@modelcontextprotocol/server-postgres",
           "postgresql://admin:password@prod.example.com/app"]
}

That's your app's admin connection string, with write access to every table in production. One prompt injection from a scraped web page and you have a bad story to explain to your team.

Step 1: create a dedicated read-only role

Connect as superuser:

CREATE ROLE ai_readonly WITH LOGIN PASSWORD 'generate_a_strong_one';
GRANT CONNECT ON DATABASE prod TO ai_readonly;
GRANT USAGE ON SCHEMA public TO ai_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO ai_readonly;

The ALTER DEFAULT PRIVILEGES line is important — without it, new tables created later are invisible to the agent. Makes for very confusing debugging sessions three months from now.

Step 2: hard-cap runaway queries

An over-eager model can trivially DoS your database with a bad query. Cap statements at 10 seconds:

ALTER ROLE ai_readonly SET statement_timeout = '10s';
ALTER ROLE ai_readonly SET idle_in_transaction_session_timeout = '1min';

Step 3: verify the role is actually read-only

psql -U ai_readonly -d prod -c "SELECT COUNT(*) FROM users;"     # should work
psql -U ai_readonly -d prod -c "DELETE FROM users;"              # should error
psql -U ai_readonly -d prod -c "DROP TABLE users;"               # should error

Three commands, two errors. If any destructive one succeeds, your role has more privileges than you intended — revisit Step 1.

Step 4: pick a data subset

Even read-only access shouldn't include every table (user passwords, payment details, PII). Revoke selectively:

REVOKE SELECT ON users_private, payment_methods FROM ai_readonly;

Or, better, create a dedicated schema with only safe views:

CREATE SCHEMA ai_readonly_schema;
CREATE VIEW ai_readonly_schema.users AS
  SELECT id, email, country, created_at FROM users;
ALTER ROLE ai_readonly SET search_path = ai_readonly_schema;

Step 5: connection string hygiene

"args": [
  "-y", "@modelcontextprotocol/server-postgres",
  "postgresql://ai_readonly:your_strong_password@host:5432/prod?sslmode=require"
]

Three must-haves:

sslmode=require — never connect in plain text over the internet.
Never commit to git. Personal machine only, not shared repo.
Rotate the password every 90 days.

Step 6: test from Claude Desktop

Fully quit and relaunch. Ask: "What tables are in the database and roughly how many rows does each have?" Claude uses schema introspection, then runs reltuples queries. Nice summary, no writes possible, no PII exposed.

Common follow-up questions

Can I point at a production replica?

Yes — better, actually. A read-replica has literally no write path. Use its connection string instead of the primary.

What if I actually need writes?

Separate MCP server instance with a ai_writer role that has INSERT/UPDATE only on specific tables. Never combine read-only and write access in one server.

Does this work with Supabase?

Use Supabase MCP instead — it handles service role vs anon role distinction automatically, including RLS.