mcp-postgres-starter

A small, production-minded remote MCP server that lets Claude (or any MCP client) answer questions from a PostgreSQL database — safely. Bearer tokens with scopes, read-only transactions, row caps, an audit log of every call, and tests that run over real HTTP.

It ships with a fake coffee-shop database (customers, products, orders) so you can try it in five minutes, then swap in your own tables.

Demo data is fictional: generated by seed.py from fixed word lists, emails on example.com.

What Claude can do with it

A real session (Claude Code, Sonnet, fake data) answering "who owes us the most, what is running out, revenue by category" in one prompt: 5 tool round-trips, 19 s, $0.10 at API prices, and every number matched direct SQL. Full transcript: docs/demo-session.md.

Related MCP server: mcp-postgres

Quick start (5 minutes)

Needs Python 3.11+, uv and a PostgreSQL you can create a database in.

git clone https://github.com/ranthishq/mcp-postgres-starter && cd mcp-postgres-starter
createdb mcp_demo
export DATABASE_URL=postgresql://postgres@localhost:5432/mcp_demo
uv run mcp-postgres-starter-seed                         # fake data: 60 customers, 22 products, 600 orders
uv run mcp-postgres-starter-token my-laptop read         # prints a token ONCE; tokens.json keeps only its hash
uv run mcp-postgres-starter                              # serves http://127.0.0.1:8000/mcp

Use read,write instead of read to issue a token that may call update_order_status. The server refuses to start without a tokens file: an HTTP endpoint with write tools should never be open.

Connect Claude

Claude Code — mcp.json:

{"mcpServers": {"store": {"type": "http", "url": "http://127.0.0.1:8000/mcp",
  "headers": {"Authorization": "Bearer <token>"}}}}

claude --mcp-config mcp.json --allowedTools "mcp__store__*"

Claude Desktop / claude.ai (custom connector) need a public HTTPS URL: put the server behind a reverse proxy (Caddy, nginx, Cloudflare Tunnel) and set MCP_PUBLIC_URL=https://your-host. Per Claude's custom connector docs a connector can send a fixed request header (use Authorization: Bearer <token>), or sign users in with OAuth — for OAuth, swap the static verifier for one that checks your identity provider's tokens (see "How it is built"). Only the Claude Code setup above was tested for this README.

How it is built

Claude ──HTTP + Bearer──> server.py (tool) ──scope check──> queries.py ──> PostgreSQL
                                   └── every call (allowed or refused) ──> audit_log table

• auth.py — StaticTokenVerifier: tokens are random, stored as SHA-256 hashes with a client id and scopes. To use your identity provider (OAuth 2.1 + PKCE, JWTs), replace this class; tools only read scopes.

• queries.py — every SQL statement. Reads run in READ ONLY transactions with a 5 s statement timeout, so a read tool cannot modify data even if its SQL were wrong (there is a test for that).

• server.py — tool definitions on the official MCP Python SDK (mcp 2.x, MCPServer, Streamable HTTP). One wrapper does scope check, row cap (MCP_MAX_ROWS, default 200) and audit for every tool.

• schema.sql / seed.py — the demo store. Replace both with your schema; keep audit_log.

Settings (environment): DATABASE_URL, MCP_TOKENS_FILE (default tokens.json), MCP_MAX_ROWS, MCP_HOST, MCP_PORT (default 8000), MCP_PUBLIC_URL.

Measured (2026-10-27)

Client-side latency over HTTP on one laptop (server, Postgres 18 and client on the same machine), 30 calls per tool, scripts/bench.py, raw numbers in docs/bench-2026-10-27.jsonl:

The database is never the slow part: in the demo session nearly all of the 19 s is the model thinking and writing.

Tests

createdb mcp_demo_test
TEST_DATABASE_URL=postgresql://postgres@localhost:5432/mcp_demo_test uv run pytest

Eight tests against a real database: revenue matches a direct SQL sum, row caps, overdue logic, reads cannot write, allowed/refused status transitions, and over real HTTP with the official client: no token → 401, read token can read, read token is refused on the write tool, and both the refusal and the success land in audit_log.

Need this for your own data?

I build custom MCP servers that connect Claude to company databases and apps (PostgreSQL, MySQL, Supabase, REST APIs), with scoped auth, audit logs and tests — and I run one in production for a commerce company. Hire me on Upwork. Hands-on AI tool tests: ranthis.com.

License

MIT