Skip to main content
Glama
pg-schema-scout

pg-schema-scout

by akshayyswami

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 blob

10,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.


Related MCP server: AgenticMCP

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.

Tool

Signature

Annotations

search_schema

(question: str, limit: int = 8) -> SchemaSlice

readOnlyHint=true, idempotentHint=true, openWorldHint=false, destructiveHint=false

describe_table

(name: str) -> TableDetail

same

explain_query

(sql: str) -> QueryPlan

same

run_query

(sql: str, max_rows: int = 200) -> QueryResult

same

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.

Layer

What it does

Why it is not enough alone

readOnlyHint=true

Tells the client the tool is safe to auto-approve

An untrusted hint. Decorative

AST policy (core/guard.py)

Parses with sqlglot and rejects anything that is not a single read-only SELECT

A parser bug is a bypass

scout_ro database role

Holds SELECT and nothing else, anywhere

This 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 weight

recall@8

complete recall

mean slice tokens

0.00 (off)

0.840

17/26

1,684

0.25

0.955

24/26

1,800

0.35 (shipped)

0.955

24/26

1,816

0.50

0.965

24/26

1,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.

Backend

FK propagation

recall@8

precision@8

complete recall

BM25 (default)

0.35

0.955

0.260

24/26

Titan V2

0.35

0.926

0.255

22/26

BM25

off

0.840

0.226

17/26

Titan V2

off

0.833

0.226

17/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.

Available Tools

4 tools
describe_tableA
Read-onlyIdempotent

Return full detail for one table by name.

Use this when you already know the table name: either search_schema returned it and you need the complete column list, or search_schema did not return it and you believe it exists anyway. This does not use the ranking, so it is the reliable way to reach a table that retrieval scored poorly.

Accepts either a qualified name ("sales.salesorderheader") or a bare one ("salesorderheader"). Returns columns with types and comments, the primary key, foreign keys out, and the foreign keys pointing in, which is how you find the tables that join to this one.

On failure:

  • "No table named ..." means it does not exist under that name. The error lists close matches; try one of those. Do not retry the same name, and do not guess a third spelling. Fall back to search_schema with a description of what the table should contain.

Inputs: name (required). Returns: Everything known about one table.

explain_queryA
Read-onlyIdempotent

Plan a SELECT without executing it.

Use this before run_query when the query touches a large table, has no WHERE clause, or joins several tables, so you can see the cost before committing to it. The query is planned only; no rows are read and nothing is executed. EXPLAIN ANALYZE is deliberately not offered, because it would run the statement.

Read total_cost as a relative number, useful for comparing two phrasings of the same query rather than as a time.

On failure:

  • "Blocked: ..." means the same read-only policy as run_query rejected it. Rewrite as a plain SELECT.

  • A "column does not exist" error means the schema assumption was wrong. Call describe_table and fix the column names.

Inputs: sql (required). Returns: An EXPLAIN result. The query is planned but never executed.

run_queryA
Read-onlyIdempotent

Execute a read-only SELECT and return the rows.

Use this once you know which tables and columns you need. If you do not yet know, call search_schema first rather than guessing table names; a query against a table that does not exist wastes a round trip.

Only a single SELECT is accepted. WITH is fine as long as the whole statement is read-only. Anything else, including INSERT/UPDATE/DELETE, DDL, statement stacking, and DML hidden inside a CTE, is rejected before it reaches the database.

On failure:

  • "Blocked: ..." means the statement violated the read-only policy. The message names the offending construct. Rewrite as a plain SELECT; do not retry the same statement.

  • A syntax or "column does not exist" error means the schema assumption was wrong. Call describe_table on the table in question and correct the column names rather than guessing again.

  • A statement timeout means the query was too expensive. Call explain_query to see the plan, then add a WHERE clause or aggregate.

Results are capped. When truncated is true the rows shown are a prefix, not the answer; narrow the query instead of treating them as complete.

Inputs: sql (required), max_rows. Returns: Rows from a read-only query.

search_schemaA
Read-onlyIdempotent

Find the tables relevant to a question and return their DDL.

Prefer this over trying to read the whole schema. The schema is far larger than the part of it any one question needs, and sending all of it costs context you will want for the answer. Pass the user's question in natural language, not a table name guess: this ranks on column names, types and the schema's own comments.

Returns the highest-scoring tables with their columns and keys, plus the foreign-key edges between the returned tables, which is what you need to write the joins.

On failure or a disappointing result:

  • If a table you know you need is missing from the results, call describe_table with its name. Retrieval ranks, and a needed table can fall below the cutoff; describe_table is the escape hatch and does not depend on the ranking.

  • If nothing looks relevant, re-ask with the domain words the user used (for example "reseller", "purchase order", "pay history") before widening limit. Raising limit costs tokens and rarely fixes a vocabulary mismatch.

  • Do not invent table names. If you cannot find it here, say so.

Inputs: question (required), limit. Returns: The tables relevant to a question, instead of the whole schema.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    TDQS

    A5/5.0

    Scored across 4 tools

    Disambiguation5/5

    Each tool has a clear, distinct role: search_schema for discovery, describe_table for known tables, explain_query for planning, and run_query for execution. There is no overlap between them.

    Naming Consistency5/5

    All four tools follow a consistent verb_noun pattern (search_schema, describe_table, explain_query, run_query), making the set predictable and easy to navigate.

    Tool Count5/5

    Four tools is well within the ideal range for a focused schema exploration and read-only query server. Each tool earns its place and covers a distinct step in the workflow.

    Completeness5/5

    The tool surface fully covers the stated purpose: discover relevant tables, inspect a specific table, plan a query, and execute it. The descriptions even include escape hatches (describe_table when search_schema misses, explain_query for costly queries), leaving no obvious gaps.

    Maintenance

    ActivityMaintained
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers