Skip to main content
Glama
pg-readonly-mcp

pg-readonly-mcp

by milad-codes

pg-readonly-mcp

CI Release License: MIT

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 numbers

The 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

list_tables

tables and views in the allowed schemas, with kind, estimated rows, size and comment

describe_table

columns, primary key, foreign keys, unique and check constraints, indexes

run_query

rows of one SELECT ($1 … parameters), column types, a truncation flag, masked columns, duration

explain_query

the JSON plan and a summary: node types, sequential scans with table size and filter, index hints; analyze only if enabled

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 (libpg-query, WebAssembly)

anything but one SELECT; data-modifying CTEs; SELECT INTO; FOR UPDATE/FOR SHARE; schemas outside the allowlist; side-effecting functions (pg_* except size helpers, dblink, lo_*, set_config) and functions that run SQL passed as text (query_to_xml family, ts_stat, ts_rewrite, crosstab, connectby)

Functions are denied by name, and a deny list can't be complete. It doesn't see inside your own functions or views.

2. BEGIN … READ ONLY, always rolled back

any write that slips past layer 1 (tested by sending DELETE straight to this layer)

none worth noting

3. SET LOCAL statement_timeout and lock_timeout

runaway queries and lock waits

long legitimate queries need a higher limit

4. SET LOCAL search_path to the allowed schemas

unqualified names resolving to other schemas

none worth noting

5. Server-side cursor fetching max + 1 rows

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 password_hash, tokens and similar

not a security control: an alias like SELECT password_hash AS x avoids it

7. Your database role

everything else

none: this is the real boundary, so set it up as below

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 would

examples/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.orders

  • answers a question with a parameterised query

  • shows masked password hashes and an index hint from the plan

  • is refused a DELETE, the secret schema, 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 build

Claude 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

DATABASE_URL

—

required

PG_MCP_SCHEMAS

public

comma-separated schemas the assistant may read

PG_MCP_MAX_ROWS

200

hard cap per query

PG_MCP_STATEMENT_TIMEOUT_MS

5000

per statement

PG_MCP_ALLOW_EXPLAIN_ANALYZE

false

EXPLAIN ANALYZE executes the query

PG_MCP_REDACT_COLUMNS

password|passwd|secret|token|api_?key|ssn|iban|credit_?card

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 database
  • The 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_stat and a DELETE sent 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 walkthrough

Troubleshooting

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 DATABASE_URL is missing or the build step was skipped.

Invalid configuration: DATABASE_URL …

Set a postgres:// or postgresql:// URL in the client's env block.

password authentication failed / ECONNREFUSED

Wrong credentials, or the database isn't reachable from where the client starts the server.

permission denied for table …

The role lacks SELECT on that table. That is the role working as intended; grant it if the assistant should see it.

Schema "x" is not readable here

Add the schema to PG_MCP_SCHEMAS, and grant the role USAGE on it.

The query exceeded the … ms time limit

Add filters or a LIMIT, check the plan with explain_query, or raise PG_MCP_STATEMENT_TIMEOUT_MS.

The demo can't connect

Start the demo database first: docker compose -f examples/docker-compose.yml up -d. It listens on port 5433.

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 tools
describe_tableDescribe tableB
Read-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 queryA
Read-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 tablesA
Read-onlyIdempotent

Lists tables and views with their kind, approximate row count, total size and comment.

Inputs: schema. Returns: tables

run_queryRun a read-only queryA
Read-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

    A3.9/5.0

    Scored across 4 tools

    Disambiguation5/5

    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.

    Naming Consistency5/5

    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.

    Tool Count5/5

    Four tools is well-scoped for a read-only PostgreSQL introspection/query server. Each tool earns its place and the surface avoids unnecessary breadth.

    Completeness4/5

    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

    ActivityMaintained
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables 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
    • A
      license
      A
      quality
      B
      maintenance
      Enables AI clients to safely query PostgreSQL databases using plain English, with read-only access and built-in SQL safety checks.
      4
      MIT
    • A
      license
      Not graded
      quality
      B
      maintenance
      Gives 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
    • A
      license
      A
      quality
      B
      maintenance
      Enables 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.
      6
      MIT