Skip to main content
Glama
milad-codes

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: pg-lens-mcp

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.

NameRequiredDescriptionDefault
tableYes"table" or "schema.table"

Output Schema

NameRequiredDescription
kindYes
nameYes
schemaYes
columnsYes
commentYes
indexesYes
totalSizeYes
primaryKeyYes
foreignKeysYes
estimatedRowsYes
checkConstraintsYes
uniqueConstraintsYes

TDQS

B3.2/5.0
Behavior3/5

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.

Conciseness4/5

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.

Completeness3/5

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.

Parameters3/5

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.

Purpose4/5

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.

Usage Guidelines2/5

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

NameRequiredDescriptionDefault
sqlYes
paramsNo
analyzeNoExecute the query to get actual timings (if enabled)

Output Schema

NameRequiredDescription
planYes
summaryYes

TDQS

A3.8/5.0
Behavior4/5

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.

Conciseness5/5

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.

Completeness4/5

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.

Parameters3/5

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.

Purpose4/5

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.

Usage Guidelines3/5

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 tablesA
Read-onlyIdempotent

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

NameRequiredDescriptionDefault
schemaNoOnly this schema (must be one of the allowed schemas)

Output Schema

NameRequiredDescription
tablesYes

TDQS

A3.6/5.0
Behavior3/5

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.

Conciseness5/5

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.

Completeness4/5

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.

Parameters3/5

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.

Purpose4/5

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.

Usage Guidelines3/5

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

NameRequiredDescriptionDefault
sqlYesA single SELECT statement; use $1, $2 … for values
limitNoMaximum rows to return (≤ 200)
paramsNoValues for $1, $2 …

Output Schema

NameRequiredDescription
rowsYes
columnsYes
rowCountYes
truncatedYes
durationMsYes
redactedColumnsYes

TDQS

A4.3/5.0
Behavior5/5

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.

Conciseness5/5

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.

Completeness5/5

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.

Parameters3/5

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.

Purpose4/5

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.

Usage Guidelines4/5

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.

  1. 4 tool updatesv0.1.1
    • First observeddescribe_table
    • First observedexplain_query
    • First observedlist_tables
    • First observedrun_query

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
ResponsivenessUnresponsive

Related MCP Connectors

Related MCP Servers

  • A
    license
    A
    quality
    D
    maintenance
    Enables AI assistants to interact with PostgreSQL databases using natural language queries, providing secure read-only access to database schemas and SQL translation capabilities.
    6
    8 npm
    ISC
  • A
    license
    Not graded
    quality
    C
    maintenance
    Securely connect AI assistants to PostgreSQL databases with read-only access, schema discovery, querying, and performance analysis tools.
    6 npm
    MIT
  • 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