pg-readonly-mcp
An MCP server for safely querying a PostgreSQL database read-only through Claude.
List readable tables and views as
schema.name.Describe columns of a table or view, flagging masked columns.
Run one read-only SQL statement (
SELECT/WITH/TABLE/VALUES/EXPLAIN).Apply row caps, column masking, statement timeouts, and audit logging.
Enforce read-only access so data cannot be changed.
Provides read-only access to a PostgreSQL database: tools to list available tables/views, describe a table's columns, and run ad-hoc single-statement SELECT/WITH/TABLE/VALUES/EXPLAIN queries, with row capping, column masking, statement timeouts, and a JSON-lines audit log of every call.
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 show me total orders per month for the last six months"
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
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 | Postgres |
2. Read-only transaction | Every call runs in | Postgres |
3. One statement, read verbs only | The server prepares each query as a single statement, so | server |
4. Row cap and masking | Each read is wrapped as | Postgres + 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 server speaks MCP over stdio, so each person runs it locally with the connection string, and the audit user comes from
PGMCP_USER, set once per deployment. That suits one analyst or a small team that trusts each other. For a regulated team, run it behind a gateway that signs people in with your SSO and keeps the audit log in an append-only store.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: postgres-mcp-lab
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 |
| required | Connection string for the read-only role |
|
| Who is asking, written to every audit line |
| none | Comma-separated column names to mask |
|
| Row cap per query |
|
| Postgres |
|
| 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 -qThe integration tests start a throwaway Postgres with initdb and pg_ctl (found on PATH or through pg_config --bindir), and skip when those aren't installed. CI sets PGMCP_REQUIRE_PG=1, so on GitHub Actions they fail instead of skipping. 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, and the row cap runs inside Postgres (a 200-million-row query returns in milliseconds);
rows are capped and masked;
every call, including rejected ones and failed
describe_tablecalls, lands in the audit log with the user.
License
MIT
Available Tools
3 toolsdescribe_tableBRead-onlyIdempotent
Columns of one table or view (schema.name). Masked columns are flagged.
Inputs: name (required).
list_tablesARead-onlyIdempotent
Tables and views this connection can read, as schema.name.
Inputs: no named parameters.
queryARead-onlyIdempotent
Run one read-only SQL statement (SELECT / WITH / TABLE / VALUES / EXPLAIN).
Inputs: sql (required).
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 3 tools
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.
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.
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.
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.
Related MCP Connectors
Query 40 databases from Claude, ChatGPT, or Cursor — on any device. Read-only, encrypted, audited.
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.
Query your Postgres from ChatGPT or Claude without exposing the database or handing over credentials. Run npx boltschema connect next to your database and it dials out over HTTPS — no inbound firewall rule, no open port, works with localhost and VPC-private databases. Read-only is enforced by a SQL guard, a Postgres READ ONLY transaction, and a scoped role generated for you.
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.69 npmISC
- AlicenseAqualityCmaintenanceProvides Claude Code read-only access to PostgreSQL for relational queries, schema inspection, EXPLAIN plans, pgvector similarity searches, and Apache AGE graph traversals.6Apache 2.0
- AlicenseNot gradedqualityCmaintenanceEnables Claude to translate natural-language business questions into safe, read-only SQL queries by exposing tools for listing tables, inspecting schemas, and executing row-capped SELECT statements against PostgreSQL.MIT
- AlicenseNot gradedqualityCmaintenanceEnables Claude to query and safely write to a Postgres database, driven entirely by one policy file with read-only access by default, column-precise PII masking, and an append-only audit log. SQL is parsed and validated before execution, writes use parameterized queries with dry-run/confirm and transactions, and every call is rate-limited and audited so destructive or exfiltrating requests are refused.MIT