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-mcp Which 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: sql-copilot
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.
Inputs: table (required). Returns: kind, name, schema, columns, +8 more
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.
Inputs: sql (required), params, analyze. Returns: plan, summary
list_tablesList tablesARead-onlyIdempotent
Lists tables and views with their kind, approximate row count, total size and comment.
Inputs: schema. Returns: tables
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.
Inputs: sql (required), limit, params. Returns: rows, columns, rowCount, truncated, +2 more
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 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
- 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
- AlicenseNot gradedqualityBmaintenanceGives AI agents read-only access to Postgres with parser-checked queries, hidden tables and columns, row limits and timeouts, caller-bound Row-Level Security, and a tamper-evident audit log.MIT
- AlicenseAqualityBmaintenanceEnables AI assistants to explore PostgreSQL schemas and run safe, read-only queries in plain English. It applies SQL validation, row limits, and read-only sessions to prevent writes and unsafe operations.6MIT