pg-schema-scout
One-line summary: pg-schema-scout (postgres-schema-mcp) is a read-only MCP server that retrieves the relevant slice of a wide Postgres schema for an agent and executes guarded read-only SELECTs against it.
search_schema(question, limit=8)— ranks tables against a natural-language question (BM25, plus one damped FK-propagation round) and returns their DDL, columns, keys, row estimates, the FK edges between the returned tables (so joins can be written), and token counts vs. the full schema.describe_table(name)— fetches any table by qualified (sales.salesorderheader) or bare name, bypassing the ranking: columns with types/comments/defaults, primary key, foreign keys out and foreign keys pointing in, row estimate, DDL. The escape hatch when retrieval misses a needed table; errors list close matches.explain_query(sql)— runs EXPLAIN (plan only, never executes) and returns the plan, estimated output rows and total cost, so cost can be judged before running.EXPLAIN ANALYZEis deliberately not offered.run_query(sql, max_rows=200)— executes a single read-only SELECT (WITH allowed if the whole statement is read-only) and returns rows with columns, atruncatedflag and a note on how to narrow; results are row-, cell- and size-capped with a statement timeout.Enforced read-only, three layers —
readOnlyHint/idempotentHint/openWorldHint=false/destructiveHint=falseannotations, an sqlglot AST guard that fails closed (rejects DML/DDL, CTE-hidden DELETE, statement stacking,SELECT ... FOR UPDATE,SELECT ... INTO, dangerous functions), and ascout_roDB role withSELECTonly plusBEGIN READ ONLYtransactions andDISCARD ALLon pool return.Error handling that guides the model — blocked queries come back as tool errors (
isError: true, HTTP 200) naming the offending construct, not protocol errors.Transport — stdio by default, or Streamable HTTP via
--httpon port 3000.Out of scope by design — no writes, no prompts/resources, no auth, no multi-database or multi-tenant support; the security boundary is the database role.
Provides read-only access to a PostgreSQL database, enabling schema search, table description, query plan analysis, and execution of SELECT queries.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@pg-schema-scout Which tables should I query to find total sales by territory?"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
postgres-schema-mcp
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.

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 syncYou 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 |
|
|
|
|
| same |
|
| same |
|
| 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 |
| Tells the client the tool is safe to auto-approve | An untrusted hint. Decorative |
AST policy ( | Parses with sqlglot and rejects anything that is not a single read-only SELECT | A parser bug is a bypass |
| Holds | 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) andreseller-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_tableis 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. EverySchemaSlicecarries a note telling the model to do this.The lexical backend cannot do irregular morphology. Stemming reduces
employeestoemployee, butpaiddoes not reduce topay. 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 incore/retrieval.pycovers 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:
The tool has no knowledge of protocol revision 2026-07-28.
--spec-versionaccepts only2025-03-26,2025-06-18,2025-11-25,draftandextension. So it exercised the legacy handshake path the SDK still supports for older clients, which is whyserver-initializeandpingappear in the passing column although both were removed in the revision this server targets.Its scenarios assert against a reference fixture server. The failures are things like
Unknown resource: test://static-text,Unknown prompt: test_simple_promptandNo image content found. A server with four tools and no prompts or resources cannot satisfy them.logging-set-levelfails withMethod 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 databaseInspect 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 toolsdescribe_tableARead-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_queryARead-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_queryARead-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_schemaARead-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
Scored across 4 tools
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.
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.
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.
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
Related MCP Connectors
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Guard AI agents' PostgreSQL/MySQL access via MCP: SQL audit, auth, masking, write approval
Text-to-SQL MCP server: read-only queries on PostgreSQL, MySQL and SQL Server with your real schema
Hosted PostgreSQL MCP server for Claude, ChatGPT and Cursor, with each action switched per account.
Related MCP Servers
- FlicenseNot gradedqualityDmaintenanceAn MCP server that gives an AI agent scoped, safe access to your Postgres databases with per-connection access control, row caps, timeouts, and defense-in-depth read-only enforcement.-
- AlicenseNot gradedqualityDmaintenanceA Model Context Protocol (MCP) server that provides secure, role-based access to PostgreSQL databases for AI agents.MIT
- AlicenseAqualityDmaintenanceA production-grade MCP server that gives AI agents safe, authenticated access to a PostgreSQL database.3MIT
- FlicenseNot gradedqualityDmaintenanceA read-only MCP server that enables AI agents to explore database schemas and execute safe queries on PostgreSQL and MySQL.-