# 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

| Tool | Scope | What it answers |
|---|---|---|
| `search_products` | read | "Do we sell a hand grinder? How much, how many left?" |
| `low_stock` | read | "What is about to run out?" |
| `sales_summary` | read | "Revenue by day / product / category between two dates" |
| `find_orders` | read | "Orders from Hana last month that are still pending" |
| `overdue_orders` | read | "Who owes us money, and how late are they?" |
| `update_order_status` | write | "Mark order 412 as shipped" — only allowed transitions, row-locked |

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](docs/demo-session.md).

## Quick start (5 minutes)

Needs Python 3.11+, [uv](https://docs.astral.sh/uv/) and a PostgreSQL you can create a database in.

```bash
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`:

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

```bash
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`](scripts/bench.py), raw numbers in [docs/bench-2026-10-27.jsonl](docs/bench-2026-10-27.jsonl):

| Tool | Median | p90 |
|---|---|---|
| search_products | 3.6 ms | 5.4 ms |
| low_stock | 3.9 ms | 4.7 ms |
| sales_summary | 5.3 ms | 6.4 ms |
| find_orders | 6.9 ms | 7.4 ms |
| overdue_orders | 5.2 ms | 6.3 ms |

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

## Tests

```bash
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](https://www.upwork.com/freelancers/~015dd5df10439c0d91). Hands-on AI tool tests:
[ranthis.com](https://ranthis.com).

## License

MIT