pg-readonly-mcp
Provides read-only access to PostgreSQL databases for schema discovery and SQL querying, including listing tables and views, describing columns, keys, constraints, and indexes, running parameterized SELECT queries with limits and masking, and explaining query plans with index hints.
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-readonly-mcpWhich customers had the most refunded orders last quarter?"
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.
pg-readonly-mcp
A Model Context Protocol server that lets an AI assistant explore a PostgreSQL database and answer questions with SQL. It is read-only, and it keeps the assistant inside the schemas you choose.
You: Which customers had the most refunded orders last quarter?
Assistant: list_tables → describe_table(sales.orders) → run_query(SELECT … GROUP BY …) → answer with numbersThe problem
Handing an assistant a connection string is risky in both directions. It might run a DELETE or a query that scans a large table for minutes. It might also wander into tables it shouldn't see. Prompt instructions ("only run SELECT") don't enforce anything.
This server puts checks in front of the database that don't depend on the model behaving:
PostgreSQL's own parser decides whether a query is a single, plain
SELECT.Execution happens in a read-only transaction with time and row limits.
A database role you control has the final say.
In scope: schema discovery, read-only queries with parameters, query plans with simple index hints. All of it runs over stdio for local MCP clients. Out of scope: writes of any kind, other databases, HTTP transport, multiple users with different permissions.
Related MCP server: pg-lens-mcp
Tools
Tool | Returns |
| tables and views in the allowed schemas, with kind, estimated rows, size and comment |
| columns, primary key, foreign keys, unique and check constraints, indexes |
| rows of one |
| the JSON plan and a summary: node types, sequential scans with table size and filter, index hints; |
The tools carry MCP readOnlyHint annotations and return structured content that is checked against output schemas. When a query is refused, the assistant gets an explanation it can act on, for example "Only SELECT queries are allowed; got DELETE" or "Allowed schemas: public, sales".
Layers and their trade-offs
Layer | Stops | Trade-off |
1. PostgreSQL's parser ( | anything but one | Functions are denied by name, and a deny list can't be complete. It doesn't see inside your own functions or views. |
2. | any write that slips past layer 1 (tested by sending | none worth noting |
3. | runaway queries and lock waits | long legitimate queries need a higher limit |
4. | unqualified names resolving to other schemas | none worth noting |
5. Server-side cursor fetching | huge result sets, without rewriting the SQL | sorting large sets still costs time on the server |
6. Column masking by name pattern | casual exposure of | not a security control: an alias like |
7. Your database role | everything else | none: this is the real boundary, so set it up as below |
Recommended database role
CREATE ROLE mcp_reader LOGIN PASSWORD '…';
ALTER ROLE mcp_reader SET default_transaction_read_only = on;
GRANT CONNECT ON DATABASE app TO mcp_reader;
GRANT USAGE ON SCHEMA public TO mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_reader;
-- tables with secrets: grant only the safe columns
REVOKE SELECT ON public.users FROM mcp_reader;
GRANT SELECT (id, email, created_at) ON public.users TO mcp_reader;Don't grant EXECUTE on functions that write through dblink or similar: the parser can't see inside them.
Try it with the demo database
You need Node 22.12 or newer and Docker for the demo database.
npm ci
docker compose -f examples/docker-compose.yml up -d # PostgreSQL 16 on :5433 with synthetic data
npm run demo # builds, then talks MCP over stdio as a client wouldexamples/shop.sql creates 200 invented customers and 20,000 generated orders, plus a secret schema. It also creates the mcp_reader role from above, and the demo connects as that role. The demo:
lists the tables and describes
sales.ordersanswers a question with a parameterised query
shows masked password hashes and an index hint from the plan
is refused a
DELETE, thesecretschema, SQL hidden in a function argument, and a query that runs past the 1-second limit
CI runs the same demo against PostgreSQL 16 on every change.
Use it with an MCP client
npm ci && npm run buildClaude Desktop (claude_desktop_config.json), Cursor (.cursor/mcp.json) and other clients that start stdio servers take:
{
"mcpServers": {
"postgres": {
"command": "node",
"args": ["/absolute/path/to/pg-readonly-mcp/dist/cli.js"],
"env": {
"DATABASE_URL": "postgresql://mcp_reader:…@localhost:5432/app",
"PG_MCP_SCHEMAS": "public,sales"
}
}
}
}You can also run it as a container. Build it with docker build -t pg-readonly-mcp ., then use "command": "docker", "args": ["run", "-i", "--rm", "-e", "DATABASE_URL", "pg-readonly-mcp"].
Configuration
Variable | Default | |
| — | required |
|
| comma-separated schemas the assistant may read |
|
| hard cap per query |
|
| per statement |
|
|
|
|
| regular expression for masked result columns |
Logs go to stderr, because stdout carries the protocol.
Development
npm test # unit tests; integration tests run when DATABASE_URL is set
DATABASE_URL=postgresql://…/scratch npm test # ⚠ drops and recreates schemas in that databaseThe guard has allow and deny cases for each rule.
Integration tests run real queries, timeouts and plans against PostgreSQL. They include an attack through
ts_statand aDELETEsent past the parser.MCP tests use the SDK client over an in-memory transport.
CI runs lint, the format check, typecheck, all tests against PostgreSQL 16 and the demo. It also builds the image and talks MCP to it over stdio (initialize, tools/list, a query and a refused DROP).
src/
sql/guard.ts parser-based statement checks
db/transaction.ts read-only transaction envelope
db/executor.ts run_query / explain_query
db/catalog.ts list_tables / describe_table
server.ts, cli.ts MCP tools and stdio entry point
examples/ synthetic demo database
scripts/demo.mjs MCP client walkthroughTroubleshooting
Symptom | Cause and fix |
The client shows no tools | The server didn't start. Run the configured command in a terminal and read stderr: usually |
| Set a |
| Wrong credentials, or the database isn't reachable from where the client starts the server. |
| The role lacks |
| Add the schema to |
| Add filters or a |
The demo can't connect | Start the demo database first: |
Limitations
The function deny list stops the cases that are known and tested. Your own functions, views and future PostgreSQL functions are outside its view. Rely on the role.
Masking matches result column names only. Use column privileges for real protection.
One query per call: no scripts and no temporary tables.
Row estimates and index hints come from planner statistics, so they are only as fresh as the last
ANALYZE.The server has no authentication of its own. Anyone who can start it with a connection string has that role's access.
Changes are listed in CHANGELOG.md, and releases are on the releases page.
Security and licence
See SECURITY.md for private reporting and deployment advice. Released under the MIT licence.
Available Tools
4 toolsdescribe_tableDescribe tableBRead-onlyIdempotent
Columns (type, nullability, default, comment), primary key, foreign keys, unique and check constraints, and indexes of one table.
| Name | Required | Description | Default |
|---|---|---|---|
| table | Yes | "table" or "schema.table" |
Output Schema
| Name | Required | Description |
|---|---|---|
| kind | Yes | |
| name | Yes | |
| schema | Yes | |
| columns | Yes | |
| comment | Yes | |
| indexes | Yes | |
| totalSize | Yes | |
| primaryKey | Yes | |
| foreignKeys | Yes | |
| estimatedRows | Yes | |
| checkConstraints | Yes | |
| uniqueConstraints | Yes |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations already declare readOnlyHint, idempotentHint, non-destructive and closed-world, so the safety profile is fully covered. The description adds nothing beyond that: it does not say what happens if the table does not exist or what the output structure looks like, though an output schema exists to cover the latter.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
A single compact clause with zero filler, and the scope qualifier ('of one table') is placed at the end where it can't be missed. Slight deduction for being a sentence fragment that spends its words listing return fields already covered by the output schema.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
With an output schema and full annotation coverage, the description needn't explain return values, yet it does so at the expense of usage context. For a trivial one-parameter introspection tool this is just about adequate, but it omits error behavior (unknown table) and its role in the query workflow.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema coverage is 100% and the schema itself documents the 'table' or 'schema.table' format, so the description's only contribution is 'of one table', reinforcing that a single table is accepted rather than a list. That matches baseline 3 for schema-driven parameters.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The noun phrase enumerates exactly what the tool returns for a single table (columns with type/nullability/default/comment, keys, constraints, indexes), which distinguishes it from list_tables and run_query by scope and content. It lacks an explicit verb, but combined with the name the intent is unambiguous.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
There is no when-to-use guidance, no mention of prerequisites, and no reference to the sibling tools. An agent must infer that this is the schema-inspection step before run_query or explain_query.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
explain_queryExplain a queryARead-onlyIdempotent
Shows the PostgreSQL execution plan of a SELECT with a summary (node types, sequential scans, index suggestions). analyze=true executes the query and is only available if the server enables it.
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | ||
| params | No | ||
| analyze | No | Execute the query to get actual timings (if enabled) |
Output Schema
| Name | Required | Description |
|---|---|---|
| plan | Yes | |
| summary | Yes |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations already declare readOnlyHint/idempotentHint/destructiveHint=false, so the safety profile is covered. The description adds context annotations cannot: that analyze=true actually executes the query and that this mode is gated by server configuration. Minor gap: no statement about cost/runtime risk of executing the query or side effects beyond the SELECT restriction.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
Two sentences, zero filler, with the core behavior front-loaded and the risky analyze mode called out afterward. Every clause earns its place.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
An output schema exists, so return values needn't be detailed, and the description still sketches what the summary contains. The remaining shortfall is the undocumented params array and the absence of any statement about what the explain output looks like beyond node types.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema coverage is only 33% - sql and params carry no schema descriptions at all. The description compensates partially by constraining sql to a SELECT and explaining analyze=true. But the params array (bind parameters for the query) is never mentioned, leaving a meaningful gap.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
Specific verb and resource: shows the PostgreSQL execution plan of a SELECT, and even enumerates what the summary contains (node types, sequential scans, index suggestions). It is distinguishable from run_query by being an explain operation rather than an execution, but it never names the sibling to make that contrast explicit.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
Usage is implied by the purpose (use it to inspect a plan) and there is a useful conditional on analyze=true ('only available if the server enables it'). However, there is no explicit guidance on when to pick this over run_query, or on prerequisites such as whether the statement must be a single SELECT.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
list_tablesList tablesARead-onlyIdempotent
Lists tables and views with their kind, approximate row count, total size and comment.
| Name | Required | Description | Default |
|---|---|---|---|
| schema | No | Only this schema (must be one of the allowed schemas) |
Output Schema
| Name | Required | Description |
|---|---|---|
| tables | Yes |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations already declare readOnlyHint, idempotentHint, destructiveHint=false and openWorldHint=false, so the safety profile is covered. The description adds that row counts are approximate and that views are included, which is useful beyond the annotations, but says nothing about default schema scope or result size limits.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
A single front-loaded sentence with zero filler, and the returned fields are enumerated compactly. Nothing to trim and nothing buried.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
With an output schema present and rich annotations, the description only needs to establish scope and purpose, which it does. It stops short of noting the default behavior when no schema is supplied, a small gap for a list tool.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The single `schema` parameter has 100% schema description coverage, so the schema does the heavy lifting per baseline. The description does not mention the filter at all, notably omitting that omitting it presumably lists all allowed schemas, so it adds nothing over the structured field.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
Specific verb 'Lists' with the resource ('tables and views') plus the exact fields returned (kind, approximate row count, size, comment), so an agent knows this is the overview/inventory tool. It does not explicitly name describe_table or run_query as the more detailed alternatives, which keeps it just short of a 5.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
Usage is implied rather than stated: returning lightweight metadata for all tables suggests a discovery step before describe_table or run_query, but the description never says when to prefer it over those siblings. No prerequisites or exclusions are mentioned.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
run_queryRun a read-only queryARead-onlyIdempotent
Runs one SELECT (WITH, VALUES, UNION allowed) in a read-only transaction with a time limit and returns at most 200 rows. Writes, DDL, locking clauses, system catalogs and side-effecting functions are refused with an explanation.
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | A single SELECT statement; use $1, $2 … for values | |
| limit | No | Maximum rows to return (≤ 200) | |
| params | No | Values for $1, $2 … |
Output Schema
| Name | Required | Description |
|---|---|---|
| rows | Yes | |
| columns | Yes | |
| rowCount | Yes | |
| truncated | Yes | |
| durationMs | Yes | |
| redactedColumns | Yes |
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Adds substantial behavior beyond the annotations: a time limit, a hard 200-row ceiling, the allowed clause forms (WITH, VALUES, UNION), and that rejected statements come back with an explanation rather than a bare error. That tells the agent both the guardrails and how failures surface.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
Two tight sentences, no filler, and the core capability and row cap are front-loaded ahead of the refusal list. Every clause carries information an agent needs.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
The tool is simple, all parameters are schema-described, an output schema exists, and the description still covers the important constraints (single statement, row cap, refused constructs). Nothing needed to call it correctly is missing.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema coverage is 100%, so sql, limit and params are already documented in the schema, giving a baseline of 3. The description's 'at most 200 rows' corroborates the limit maximum but adds no syntax or usage detail the schema lacks.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
States a specific verb and resource ('Runs one SELECT ... returns at most 200 rows') and pins the accepted statement forms, so the agent knows exactly what it does. It does not name any sibling, so the boundary with explain_query (for plans rather than results) is left for the agent to infer.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
Explicitly enumerates what is refused (writes, DDL, locking clauses, system catalogs, side-effecting functions), which is effectively a when-not-to-use list. It never points at an alternative such as explain_query or describe_table for schema inspection, so routing between siblings is inferred.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
4 tool updates
v0.1.1- First observed
describe_table - First observed
explain_query - First observed
list_tables - First observed
run_query
TDQS
Scored across 4 tools
Each tool has a clearly distinct purpose: listing relations, describing one relation, executing a read-only SELECT, and explaining a SELECT plan. run_query and explain_query could superficially overlap, but the descriptions make the execution-vs-plan boundary explicit.
All tool names follow a consistent snake_case verb_noun pattern: list_tables, describe_table, run_query, explain_query. The convention is predictable and readable.
Four tools is well-scoped for a read-only PostgreSQL introspection/query server. Each tool earns its place and the surface avoids unnecessary breadth.
The core read-only workflow is covered: discover tables, inspect table structure, run ad-hoc SELECTs, and inspect query plans. Minor gaps exist around listing schemas, functions, or server-level metadata, but agents can work around these with run_query.
Maintenance
Related MCP Connectors
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Query PostgreSQL databases in plain English — LLM-generated, safety-validated SQL.
Deterministic safety, correctness & cost gate that vets Postgres SQL before your AI agent runs it.
Query 40 databases from Claude, ChatGPT, or Cursor — on any device. Read-only, encrypted, audited.
Related MCP Servers
- AlicenseAqualityDmaintenanceEnables AI assistants to interact with PostgreSQL databases using natural language queries, providing secure read-only access to database schemas and SQL translation capabilities.68 npmISC
- AlicenseNot gradedqualityCmaintenanceSecurely connect AI assistants to PostgreSQL databases with read-only access, schema discovery, querying, and performance analysis tools.6 npmMIT
- AlicenseNot gradedqualityCmaintenanceEnables AI assistants to safely explore PostgreSQL schemas, inspect sample rows, draft queries, and read EXPLAIN plans through a read-only enforced interface for MCP clients.MIT
- AlicenseAqualityBmaintenanceEnables AI clients to safely query PostgreSQL databases using plain English, with read-only access and built-in SQL safety checks.4MIT