Skip to main content
Glama
hellorutvik

pg-readonly-mcp

by hellorutvik
OverviewSchemaSourceRelated ServersDiscussionsAdmin
Python
Remote

pg-readonly-mcp

An MCP server that lets Claude answer questions from a PostgreSQL database without being able to change it. Your ops or finance team asks in plain English. Claude writes the SQL, runs it read-only, and shows the query it ran.

Three tools: list_tables, describe_table and query. Each is marked readOnlyHint so MCP clients can treat it as safe.

Security model

The database decides what Claude can see. The server adds four more layers on top, so one mistake doesn't expose data.

Layer

What it does

Where

1. Database role

Claude connects as a role that can only SELECT the views you grant. Tables you don't grant don't exist for it.

Postgres

2. Read-only transaction

Every call runs in BEGIN READ ONLY with a statement_timeout and is rolled back afterwards. A write fails even if the role could write.

Postgres

3. One statement, read verbs only

The server prepares each query as a single statement, so select 1; commit; delete ... is refused. The first keyword must be SELECT, WITH, TABLE, VALUES or EXPLAIN.

server

4. Row cap and masking

Results stop at PGMCP_MAX_ROWS. Columns you list in PGMCP_MASK_COLUMNS come back as **** plus the last four characters.

server

5. Audit log

Every call, including rejected ones, is appended as a JSON line with time, user, SQL, outcome, row count and duration.

server

The tests check each layer against a real Postgres. Some of them connect as a superuser and try to write, to prove that layers 2 and 3 hold on their own.

What this does not do

  • Masking works on result column names. A query can still alias a column (select iban as x), so the database view is the right place to leave out or hash fields Claude should never see. Use masking as a second net, not the first.

  • The audit user comes from PGMCP_USER, set once per deployment. For per-person audit across a team, run one server per user or put it behind your SSO.

  • Data Claude reads goes to the model provider you configure. For EU data, use an EU-hosted model endpoint, for example Claude on AWS Bedrock in Frankfurt.

Related MCP server: PostgreSQL MCP Server for Claude Desktop

Setup

1. Create the read-only role (run as an admin)

create role claude_reader login password 'change-me';
create schema reporting;

-- Expose only what Claude should see, through views.
create view reporting.customer_orders as
  select c.name, o.total, o.ordered_at
  from orders o join customers c on c.id = o.customer_id;

grant usage on schema reporting to claude_reader;
grant select on reporting.customer_orders to claude_reader;

-- Belt and braces: the role's own sessions default to read-only.
alter role claude_reader set default_transaction_read_only = on;

Point it at a read replica if you have one.

2. Install

python3 -m venv .venv
.venv/bin/pip install .

3. Add it to Claude Desktop or Claude Code

{
  "mcpServers": {
    "company-data": {
      "command": "/path/to/pg-readonly-mcp/.venv/bin/pg-readonly-mcp",
      "env": {
        "DATABASE_URL": "postgresql://claude_reader:change-me@db.internal:5432/shop",
        "PGMCP_USER": "jane@yourcompany.com",
        "PGMCP_MASK_COLUMNS": "iban,email,phone",
        "PGMCP_MAX_ROWS": "500",
        "PGMCP_STATEMENT_TIMEOUT_MS": "5000",
        "PGMCP_AUDIT_LOG": "/var/log/pg-readonly-mcp/audit.jsonl"
      }
    }
  }
}

Configuration

Variable

Default

Meaning

DATABASE_URL

required

Connection string for the read-only role

PGMCP_USER

unknown

Who is asking, written to every audit line

PGMCP_MASK_COLUMNS

none

Comma-separated column names to mask

PGMCP_MAX_ROWS

500

Row cap per query

PGMCP_STATEMENT_TIMEOUT_MS

5000

Postgres statement_timeout per query

PGMCP_AUDIT_LOG

audit.jsonl

Path of the JSON-lines audit log

An audit line looks like this:

{"ts": "2026-10-26T14:02:11+00:00", "user": "jane@yourcompany.com", "sql": "select name, sum(total) ...", "ok": true, "rows": 5, "truncated": false, "ms": 41}

Tests

.venv/bin/pip install pytest
PYTHONPATH=. .venv/bin/python -m pytest -q

The integration tests start a throwaway Postgres with initdb and pg_ctl, and skip when those aren't installed. They cover:

  • the role can't read tables it wasn't granted;

  • a write inside a CTE fails even as a superuser;

  • select 1; commit; delete ... is refused;

  • queries time out;

  • rows are capped and masked;

  • every call lands in the audit log with the user.

License

MIT

Available Tools

3 tools
describe_tableB
Read-onlyIdempotent

Columns of one table or view (schema.name). Masked columns are flagged.

NameRequiredDescriptionDefault
nameYes

TDQS

B3.1/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare readOnlyHint, idempotentHint and destructiveHint=false, so safety is covered structurally. The description adds one genuinely new behavioral fact — masked columns are surfaced in the result — which is output-shaping context not present in annotations.

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 short clauses, zero filler, with the resource stated first and the qualifier about masked columns appended. Nothing could be removed without losing information.

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 no output schema and no parameter documentation, the description must carry more — and it at least signals the return subject (columns) and one notable result characteristic (masked-flagging). A brief note on other returned column attributes would close the remaining gap, but the essentials for calling it correctly are present for a one-parameter, single-purpose lookup.

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 parameter has 0% schema description coverage, so the schema alone gives only a bare string named "name". The description compensates partially by specifying the expected format as schema.name, but leaves unresolved whether it accepts a bare table name or how quoting/case are handled.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose3/5

Does the description clearly state what the tool does and how it differs from similar tools?

The fragment "Columns of one table or view (schema.name)" names the resource (column metadata) and the target granularity (single table/view), which distinguishes it from list_tables and query in practice. But it is a noun phrase with no verb, so it never explicitly says it returns/describes column metadata.

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 statement of when to call this instead of list_tables or query, and no preconditions or exclusions. The reader must infer that this is the follow-up to list_tables once a table name is known.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

list_tablesA
Read-onlyIdempotent

Tables and views this connection can read, as schema.name.

NameRequiredDescriptionDefault

No parameters

TDQS

A3.7/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare readOnlyHint, idempotentHint, and destructiveHint=false, so the safety profile is covered. The description adds the useful detail that results are scoped to what the connection can read and formatted as schema.name, but says nothing about result size, ordering, or whether views and tables are distinguishable in the output.

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 compact sentence with no filler, and the key scoping constraint ('this connection can read') is front-loaded.

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?

For a zero-parameter listing tool with no output schema, the description covers scope and the naming convention of returned items. It stops short of describing whether results are ordered, paginated, or include object types, which leaves a small gap an agent might want.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The tool takes zero parameters, so the baseline is 4. The description correctly implies no filtering input is required and adds the return-shape hint 'schema.name'.

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 description names the resource (tables and views) and the scope ('this connection can read'), making the listing operation clear even without an explicit verb. It does not explicitly contrast with describe_table or query, though the distinction is fairly intuitive.

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 only implied: an agent can infer this is the discovery step before describe_table or query, but the description never states when to use this versus those siblings. No exclusions or prerequisites are given.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

queryA
Read-onlyIdempotent

Run one read-only SQL statement (SELECT / WITH / TABLE / VALUES / EXPLAIN).

NameRequiredDescriptionDefault
sqlYes

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, and destructiveHint=false, so safety is covered. The description adds context annotations do not carry: only a single statement is accepted, and the statement must be one of SELECT/WITH/TABLE/VALUES/EXPLAIN. Row limits, timeouts, and failure behavior remain undisclosed.

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?

One sentence, front-loaded with the verb and the key constraint, with no filler or redundancy.

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?

There is no output schema, and the description never indicates the shape of results (rows, column names, error reporting), which matters for a general SQL execution tool. The allowed-statement constraint is covered, but an agent still cannot anticipate the return payload.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema coverage is 0% (the only parameter is a bare 'Sql' string with no description), so the description must compensate. It does so by defining what belongs in that parameter: a single read-only statement of one of the listed forms.

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 description names a specific action and resource: execute a single read-only SQL statement, with the permitted statement forms enumerated. It is clearly distinguishable in practice from list_tables and describe_table, though it never names those siblings explicitly.

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?

'Run one read-only SQL statement' implies when to use it, and the enumerated statement types set a boundary. However, there is no explicit guidance on when to prefer list_tables or describe_table for schema exploration versus issuing ad-hoc SQL.

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. 3 tool updatesv0.1.0
    • First observeddescribe_table
    • First observedlist_tables
    • First observedquery
Deploy Server
One endpoint for every MCP toolRayrun runs every MCP server behind one URL and holds the credentials. Each call is decided per client, tool, and argument, then recorded.ray.runAd

TDQS

A3.6/5.0

Scored across 3 tools

Disambiguation4/5

Each tool has a distinct role: catalog listing (list_tables), schema introspection (describe_table), and statement execution (query). The only overlap is that query could technically be used to list tables, but the descriptions steer an agent cleanly toward the dedicated tools.

Naming Consistency4/5

list_tables and describe_table follow a consistent verb_noun snake_case pattern, and query is a single clear verb in the same style. Minor deviation only in that query lacks a noun target, but it reads naturally alongside the others.

Tool Count5/5

Three tools is a tight, well-scoped surface for a read-only database accessor: discover tables, inspect columns, run SELECTs. Nothing extraneous and nothing obviously missing at this scope.

Completeness4/5

The discover-inspect-query lifecycle fully covers read-only database work, including EXPLAIN via query. Minor gaps exist around schema/database-level listing and connection metadata, but agents can work around these easily.

Maintenance

ActivityMaintained
ResponsivenessNo issues

Resources

Related MCP Connectors

Related MCP Servers

  • PostgreSQL MCP Server

    melihbirim
    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

  • PostgreSQL MCP Server for

    User-Gold
    F
    license
    Not graded
    quality
    D
    maintenance
    Enables Claude Desktop to interact with PostgreSQL databases through natural language for schema exploration, data analysis, and query execution. Users can search schemas, describe tables, and perform read or write operations without needing to write manual SQL.
    -

  • SafeSQL

    AbuBaker91-hub
    A
    license
    A
    quality
    B
    maintenance
    Enables Claude Desktop and Claude Code to inspect a Postgres schema and run read-only SQL queries against a demo database through the tools describe_schema and query_readonly. Every proposed query passes the same deterministic guardrails — SELECT-only, allow-listed tables, enforced row limits, statement timeouts and a read-only role — before execution.
    2
    MIT

  • postgres-mcp-lab

    PlbKin190
    A
    license
    A
    quality
    C
    maintenance
    Provides Claude Code read-only access to PostgreSQL for relational queries, schema inspection, EXPLAIN plans, pgvector similarity searches, and Apache AGE graph traversals.
    6
    Apache 2.0