Repository avatar
AI Tools
v0.1.0
active

postgres-schema-mcp

io.github.akshayyswami/postgres-schema-mcp

Schema access for an agent is a retrieval problem, not a dump.

Documentation

postgres-schema-mcp

CI License: MIT MCP 2026-07-28

Schema access for an agent is a retrieval problem, not a dump.

An MCP server that gives an AI agent the relevant slice of a wide Postgres schema instead of all of it, and enforces read-only in three layers rather than annotating it.

Terminal demo: one question retrieves a small slice of the schema, and the read-only guard blocks a DELETE hidden inside a CTE.


The number

Measured on the seeded AdventureWorks database in this repo: 68 tables across 5 schemas, over the 26 questions in evals/questions.yaml.

Tokens
Baseline: the full schema DDL, dumped in one blob10,927
Retrieved slice, mean over the questions (k=8)1,816
6.0× less schema context (83% fewer tokens)

Median slice 1,858, range 1,071–2,368. Tokenizer: tiktoken/cl100k_base. Ranking backend: bm25.

Reproduce: make evals prints the numbers as one JSON object. It needs no database: it reads the frozen schema in evals/fixtures/catalog.json, which is names, comments and keys only, no rows. CI re-measures on every push and fails if any figure in this README differs from the measurement, and checks the frozen schema against a freshly seeded database.

What the number leaves out: it counts schema context only, the DDL a model is given before it writes SQL. It excludes tool descriptions and the rows a query returns.

The ratio is the boring half of the argument. The interesting half is a schema too wide to dump at all. A dump-everything server truncates, and if the table you need falls outside the cutoff, the model has no way to ask for it. describe_table, below, is this server's answer.


Quickstart

git clone https://github.com/akshayyswami/postgres-schema-mcp && cd postgres-schema-mcp
docker compose up -d --wait
uv sync

You need Docker and uv. The first docker compose up builds Postgres, downloads the AdventureWorks OLTP sample, seeds 68 tables and creates both database roles. It takes a few minutes; afterwards it is instant.

Then point your MCP client at it:

{
  "mcpServers": {
    "postgres-schema-mcp": {
      "command": "uv",
      "args": ["run", "--directory", "/path/to/postgres-schema-mcp", "postgres-schema-mcp"],
      "env": {
        "PGSS_DSN": "postgresql://scout_ro:scout_ro_pw@localhost:55432/adventureworks"
      }
    }
  }
}

The package is also on PyPI, so a client can run it without a clone. Set "command": "uvx" and "args": ["postgres-schema-mcp"], with the same env, and point PGSS_DSN at your own database through a role that can only SELECT.

stdio by default. postgres-schema-mcp --http serves Streamable HTTP on port 3000 instead. The credentials above belong to a local throwaway container holding public sample data. There are no real credentials in this repo.


How it works

flowchart LR
    Q[question] --> S[search_schema]
    S -->|"BM25 over one document per table"| R[ranked tables]
    R -->|"plus a share of the best foreign-key neighbour"| T["slice: top tables, DDL, joins"]
    T --> M[model writes SQL]
    M --> G["run_query: AST guard"]
    G --> D[("scout_ro role: SELECT only")]

At start-up the server reads the schema once and builds one document per table: its name, comments, column names and types, and the tables it joins to. search_schema ranks those documents against the question with BM25, which needs no credentials and no per-query cost.

Ranking alone returns slices you cannot join, so it adds one damped round of score propagation across foreign keys: a table next to a strong match is likely to be needed, because you cannot join to what you have not been shown. The share is 0.35. Retrieval can still rank a needed table below the cutoff, so describe_table fetches any table by name without using the ranking.

The four tools

Four, not fourteen. Every tool description is paid for on every request, so mirroring a REST surface into tools taxes each call. These four match the loop an agent actually runs.

ToolSignatureAnnotations
search_schema(question: str, limit: int = 8) -> SchemaSlicereadOnlyHint=true, idempotentHint=true, openWorldHint=false, destructiveHint=false
describe_table(name: str) -> TableDetailsame
explain_query(sql: str) -> QueryPlansame
run_query(sql: str, max_rows: int = 200) -> QueryResultsame

All four return Pydantic models, so outputSchema and structuredContent are generated, not hand-written.

The spec's annotation defaults are pessimistic: omitting them declares a tool destructive, open-world and non-idempotent. So all four set them explicitly, in a fixed order, because the spec cites prompt-cache hit rate as the reason to keep the tool list stable. tools/list carries ttlMs: 300000 and cacheScope: "public". The list comes from this server's own code, so it is the same for every caller, but a redeploy can change it, so it is not cached longer.

Safety model

Three layers, weakest first.

LayerWhat it doesWhy it is not enough alone
readOnlyHint=trueTells the client the tool is safe to auto-approveAn untrusted hint. Decorative
AST policy (core/guard.py)Parses with sqlglot and rejects anything that is not a single read-only SELECTA parser bug is a bypass
scout_ro database roleHolds SELECT and nothing else, anywhereThis is the layer that actually holds

The annotation is the weakest layer, on purpose. The specification says clients must treat tool annotations from an untrusted server as untrusted. A malicious server can set readOnlyHint=true and delete your data. It is documentation, not a control. Anyone who says their database MCP server is read-only because of the annotation has misread the spec.

The query a regex guard misses

WITH gone AS (DELETE FROM customer RETURNING *) SELECT * FROM gone;

It begins with WITH. It contains SELECT. It empties the table. A regex allowlist that checks the statement's prefix passes it straight through. sqlglot parses it to a top-level Select whose tree contains a Delete node, so walking the AST rejects it.

The same argument runs the other way. A text denylist wrongly rejects

SELECT 'DROP TABLE customer' AS note FROM person.person;

which is an ordinary read-only query. Both directions are in the fault matrix.

The guard fails closed: unparseable input, empty input, statement stacking, and anything sqlglot can only represent as an opaque Command are all refused. It also blocks SELECT ... FOR UPDATE (row locks are not read-only), SELECT ... INTO (DDL wearing a SELECT costume), and a denylist of filesystem, network, administrative and denial-of-service functions, checked against every function node in the tree rather than the raw string.

Blocked is a tool error, not a protocol error

A rejected query returns HTTP 200 with a JSON-RPC result carrying isError: true and a message the model can act on. Only real protocol faults become JSON-RPC errors. Verified on the wire:

{"jsonrpc":"2.0","id":1,"result":{
  "content":[{"type":"text","text":"Error executing tool run_query: Blocked: the statement contains a DELETE nested inside the query (in a CTE or subquery), which a prefix check would miss. This server is read-only. Rewrite the request as a plain SELECT."}],
  "isError":true,"resultType":"complete"}}

Below the parser

The server authenticates as scout_ro, which is granted SELECT and nothing else, with TEMPORARY revoked on the database and no CREATE on any schema. Every transaction starts as BEGIN READ ONLY, so a query that flips default_transaction_read_only for the session changes nothing. A connection is reset with DISCARD ALL when it goes back to the pool, so a lock or setting one request leaves behind is gone before the next. Sessions run with statement_timeout and idle_in_transaction_session_timeout. Rows are read from a server-side cursor in batches, with a row cap, a per-cell cap and a total size cap, and each cut is reported to the model.

The seeded database goes one step further. init/20-grants.sql revokes EXECUTE from PUBLIC on the functions the guard refuses (pg_sleep, set_config, advisory locks, nextval, large objects, crosstab), and gives scout_ro a connection limit and a temp-file limit. A name the guard missed is then refused by the database. Point PGSS_DSN at your own database and you need to give that role the same limits yourself.

tests/integration/test_readonly_role.py sends DELETE, UPDATE, INSERT, TRUNCATE, DROP, ALTER, CREATE TABLE, CREATE TEMP TABLE, CREATE INDEX and GRANT straight to Postgres, bypassing the guard, and asserts the database refuses each one. That test is the proof the bottom layer holds without the parser.


Evals

Everything below comes from make evals (offline) and make evals-db (against the seeded database), and both run in CI.

Fault matrix: 21/21 blocked, 12/12 allowed.

Two-sided on purpose. The must_allow half is the half people skip, and it proves the guard is a guard and not a policy that refuses everything. make evals-db also executes all 12 allowed queries against the database, so "allowed" means it actually runs.

Retrieval: recall@8 0.955, precision@8 0.260, 24/26 questions with complete recall.

Precision is structurally capped: a question needs 1 to 4 tables but 8 slots come back. Recall is the number that matters, because a missing table breaks the query and a spare one costs a few tokens.

Foreign-key propagation, and what it bought

The first run failed. Purely lexical ranking scored recall@8 0.840, and 9 questions were missing a required table. The misses had a shape: production.product was missed 4 times and person.person 2 times. These are join hubs. A slice containing salesorderdetail without product names a productid the model cannot resolve, so it is not a usable slice.

Ranking now adds one damped round of score propagation across foreign keys. Measured over the 26 questions:

neighbour weightrecall@8complete recallmean slice tokens
0.00 (off)0.84017/261,684
0.250.95524/261,800
0.35 (shipped)0.95524/261,816
0.500.96524/261,864

0.35 takes nearly all the gain. Past 0.5 the graph term starts to swamp the lexical signal, which is how you end up returning the whole schema again.

Two honest caveats. The weight was chosen on the same 26 questions that recall is measured on, so 0.955 is optimistic: it is not a held-out score. And better recall costs tokens. The mean slice grew from 1,684 to 1,816, which moved the headline ratio from 6.5× to 6.0×. Recall was judged worth more than the ratio.

BM25 vs Titan embeddings: the lexical backend wins

Both backends were measured on the same 26 questions. The Titan figures are recorded, not re-measured here: they come from a run on 2026-08-07 against the real Bedrock service (amazon.titan-embed-text-v2:0, 94 invocations), and are kept with that date in evals/baseline/titan-bedrock.json. CI has no AWS credentials, so it does not re-run them. The BM25 rows are measured on every push.

BackendFK propagationrecall@8precision@8complete recall
BM25 (default)0.350.9550.26024/26
Titan V20.350.9260.25522/26
BM25off0.8400.22617/26
Titan V2off0.8330.22617/26

The embeddings lose. Before propagation the two are indistinguishable, so the embeddings were not finding anything the lexical path missed. After propagation BM25 pulls ahead. That is a reasonable outcome, not a surprising one: a table's document here is mostly identifiers and a one-line comment, not prose, which is the regime BM25 is built for. Foreign-key structure mattered far more than semantic similarity.

Two caveats cut against the winner. Titan was handed the same identifier-heavy text as BM25, and a prose rendering of each table might suit it better. And the lexicon and stemmer were written with BM25 in mind, so the comparison is not perfectly even.

BM25 stays the default, which also means the repo runs offline with no credentials and no per-query cost. To try Titan, install the bedrock extra and set PGSS_RETRIEVAL_BACKEND=titan.


Limits

  • Retrieval has a recall ceiling, and this is the design's real failure mode. At k=8 it misses a required table on 2 of 26 questions: revenue-by-category (a four-table join through the category hierarchy) and reseller-locations (the store-to-address path runs through a cross-reference table that matches nothing in the question's wording). When it misses, the table is absent and the slice alone cannot recover.
  • describe_table is the escape hatch for exactly that. It does not use the ranking, so a model that knows or guesses a table name can always reach it. Every SchemaSlice carries a note telling the model to do this.
  • The lexical backend cannot do irregular morphology. Stemming reduces employees to employee, but paid does not reduce to pay. A test asserts the limit instead of hiding it. Embeddings were the obvious fix, they were measured, and they did not help overall.
  • The identifier lexicon is specific to this schema. AdventureWorks names are lowercase concatenations (salesorderheader), and splitting them needs a word list. The one in core/retrieval.py covers this schema's vocabulary. Another database would want its own.
  • The Titan backend is measured but not the default, because it measured worse. It has only ever been exercised over 68 documents and 26 questions, with no retry, batching or rate-limit handling.
  • Not implemented, on purpose: no prompts, no resources, no write path of any kind, no EXPLAIN ANALYZE (it would execute the query), no cross-database or multi-tenant support, and no auth. The server is expected to run locally over stdio, and the security boundary is the database role, not the transport.
  • The headline number is for one schema. It is measured on AdventureWorks, a sample with 68 tables. A different schema will give different figures, and the lexicon above is part of why.

Conformance: does not pass, and here is why

npx @modelcontextprotocol/conformance@0.1.16 server --url http://localhost:3000/mcp reported 11 passed, 21 failed on 2026-08-07. That is not a green check, and it is not a defect in this server:

  1. The tool has no knowledge of protocol revision 2026-07-28. --spec-version accepts only 2025-03-26, 2025-06-18, 2025-11-25, draft and extension. So it exercised the legacy handshake path the SDK still supports for older clients, which is why server-initialize and ping appear in the passing column although both were removed in the revision this server targets.
  2. Its scenarios assert against a reference fixture server. The failures are things like Unknown resource: test://static-text, Unknown prompt: test_simple_prompt and No image content found. A server with four tools and no prompts or resources cannot satisfy them. logging-set-level fails with Method not found, which is correct behaviour for this revision.

Every scenario that tests protocol behaviour rather than fixture presence passes: tools-list, tools-call-simple-text, tools-call-error, resources-list, prompts-list, ping, server-initialize, server-sse-multiple-streams and dns-rebinding-protection.

This result is recorded in evals/baseline/conformance.json, not re-run in CI. Wiring it in would mean checking in a baseline of 21 expected failures, and a baseline that large hides regressions instead of catching them.

Spec version

Built against MCP protocol revision 2026-07-28 on the official Python SDK mcp==2.0.0, pinned exactly because the v1 line continues in parallel and the two APIs are not compatible. That revision made the protocol stateless, so there is no session handling anywhere in this codebase. Details, and what changed in the SDK, are in docs/architecture.md.


Development

make setup      # uv sync, install the git hooks
make db         # start the seeded Postgres (first run takes a few minutes)
make check      # exactly what CI runs: hooks, mypy, tests with coverage, evals
make test-fast  # no database: unit tests and the offline evals
make evals      # re-measure and rewrite evals/results/latest.json
make fixtures   # re-freeze the schema from the running database

Inspect the server with mcp dev src/postgres_schema_mcp/server.py. Design decisions, with dates and the alternatives considered, are in docs/DECISIONS.md. Contributions: CONTRIBUTING.md.

License

MIT. The AdventureWorks sample data is Microsoft's, downloaded at build time and not redistributed here. The frozen schema in evals/fixtures/ holds table and column metadata, not rows.

Not affiliated with the PostgreSQL project, Microsoft or the Model Context Protocol maintainers.

PYPI
postgres-schema-mcp
Install Command
pip install postgres-schema-mcp