Readonly Postgres

Let AI read your PostgreSQL database - without letting it write to it.

npm version npm downloads CI license

One MCP server. One job. Read PostgreSQL safely.

This package never writes to the database. There is no write API and no migration runner - not a mode that is switched off, but code that does not exist. Three independent layers enforce it: a SQL guard, a read-only transaction, and a database role granted SELECT and nothing else.

Read SECURITY.md for the threat model and the guard's documented limits before pointing this at production.

Install from npm

npm install readonly-postgres-mcp

Or run the MCP server without a global install:

npx readonly-postgres-mcp

Optional peer for NestJS apps:

npm install @nestjs/common

Environment

A connection URL works, if you already have one:

DATABASE_URL=postgresql://readonly_user:[email protected]:5432/analytics?sslmode=require

PG_URL is also accepted and takes precedence. Any discrete PG_* variable overrides the matching part of the URL.

Or set the parts individually:

PG_HOST=localhost
PG_PORT=5432
PG_DATABASE=postgres
PG_USERNAME=readonly_user
PG_PASSWORD=...
PG_SSL_MODE=require
PG_SEARCH_PATH=public
PG_STATEMENT_TIMEOUT_MS=30000
PG_MAX_ROWS=10000
PG_MCP_ALLOW_ADHOC=true
PG_ALLOW_EXPLAIN_ANALYZE=false

Variable	Default	Purpose
PG_HOST PG_DATABASE PG_USERNAME PG_PASSWORD	-	Required unless a connection URL is set
PG_PORT	5432
PG_SSL_MODE	see TLS	libpq sslmode value
PG_SEARCH_PATH	public	Comma-separated schemas
PG_STATEMENT_TIMEOUT_MS	30000	MCP tools use 15000
PG_MAX_ROWS	10000	MCP tools use 1000
PG_MCP_ALLOW_ADHOC	true	false hides pg_query_sql
PG_ALLOW_EXPLAIN_ANALYZE	false	EXPLAIN ANALYZE executes what it explains
PG_QUERY_REGISTRY	-	Path to your own registry.json; enables pg_query

Optional (backward-compatible fallbacks): PG_SSL (boolean) and PG_SSL_REJECT_UNAUTHORIZED (boolean, overrides certificate verification for whatever mode is resolved).

TLS

PG_SSL_MODE is a discrete connection parameter, set the same way as PG_HOST, PG_DATABASE, PG_USERNAME and PG_PASSWORD - no connection string required. It accepts the same values as a libpq sslmode= parameter:

PG_SSL_MODE	Pool ssl value
disable	false
allow	{ rejectUnauthorized: false }
prefer	{ rejectUnauthorized: false }
require	{ rejectUnauthorized: false }
no-verify	{ rejectUnauthorized: false }
verify-ca	{ rejectUnauthorized: true }
verify-full	{ rejectUnauthorized: true }

Unset: TLS is disabled for localhost / 127.0.0.1 and enabled without certificate verification for any other host.

Use a dedicated database role with SELECT only. See docs/db-role.sql.

Usage

import { PgReadonlyClient, QUERY_IDS } from 'readonly-postgres-mcp';

const pg = await PgReadonlyClient.fromEnv();

const result = await pg.readonly().run(QUERY_IDS.EXAMPLE_PING, {
  params: { message: 'hello' },
});

await pg.readonly().query(
  'SELECT table_name FROM information_schema.tables WHERE table_schema = $1 LIMIT 10',
  { values: ['public'] },
);

await pg.close();

NestJS

import { PgReadonlyModule, NESTJS, PgReadonlyClient } from 'readonly-postgres-mcp/nestjs';

@Module({
  imports: [PgReadonlyModule.forRoot()],
})
export class AppModule {}

@Injectable()
export class ReportService {
  constructor(@Inject(NESTJS.PG_READONLY_CLIENT) private readonly pg: PgReadonlyClient) {}
}

MCP server

Tool	Purpose	Shown when
pg_query_sql	Ad-hoc SELECT / WITH / EXPLAIN	Unless PG_MCP_ALLOW_ADHOC=false
pg_describe	List tables/views, or describe one relation's columns	Always
pg_query	Named catalog query by queryId	When PG_QUERY_REGISTRY is set

All three are annotated readOnlyHint: true, so MCP clients that surface the distinction show them as non-destructive.

pg_describe reads pg_catalog directly - faster than information_schema, and it reports row estimates, comments and partitioned tables correctly. Let the model call it rather than guessing at table names:

{}                        // list every relation in the search path
{ "table": "users" }      // columns, types, nullability, defaults, primary keys

pg-readonly-mcp

Ad-hoc example (pg_query_sql):

{
  "sql": "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY 1 LIMIT 20"
}

With positional params:

{
  "sql": "SELECT table_name FROM information_schema.tables WHERE table_schema = $1 LIMIT 10",
  "values": ["public"]
}

Set PG_MCP_ALLOW_ADHOC=false to hide/disable pg_query_sql.

Limits applied to every query

Limit	MCP default	Setting
Statement timeout	15s	PG_STATEMENT_TIMEOUT_MS
Rows returned	1,000	PG_MAX_ROWS

The row cap is enforced by PostgreSQL, not after the fact: statements are wrapped as SELECT * FROM (<your query>) LIMIT <cap>+1, so a SELECT * against a large table cannot exhaust the server's memory. Because the cap is pushed down, rowCount reports rows returned, not rows matched, and truncated tells you whether more exist.

Supported SQL

SELECT, WITH (non-data-modifying CTEs) and EXPLAIN. One statement per call - no trailing second statement, and no semicolon needed.

EXPLAIN ANALYZE is rejected by default because it executes the statement it explains. Set PG_ALLOW_EXPLAIN_ANALYZE=true if you need it. Plain EXPLAIN always works.

Everything else is rejected before it reaches the database: INSERT, UPDATE, DELETE, MERGE, COPY, CREATE, DROP, ALTER, TRUNCATE, GRANT, REVOKE, VACUUM, REINDEX, CLUSTER, CALL, DO, SELECT INTO, data-modifying CTEs, and multiple statements in one call.

Cursor MCP config:

{
  "mcpServers": {
    "readonly-postgres-mcp": {
      "command": "npx",
      "args": ["-y", "readonly-postgres-mcp"],
      "env": {
        "PG_HOST": "localhost",
        "PG_DATABASE": "postgres",
        "PG_USERNAME": "readonly_user",
        "PG_PASSWORD": "...",
        "PG_SSL_MODE": "require"
      }
    }
  }
}

Named query catalog (optional)

Instead of ad-hoc SQL, you can expose a fixed set of pre-approved queries. Point PG_QUERY_REGISTRY at your own registry file; query paths resolve relative to it, so a catalog is a self-contained folder:

my-catalog/
  registry.json
  queries/
    reports/active-users.sql

1. Write the .sql file using :namedParams
2. Register it in registry.json with its param types
3. Set PG_QUERY_REGISTRY=/path/to/my-catalog/registry.json
4. Validate with npx pg-validate-catalog

Every catalog query is checked by the same SQL guard at startup, so a write statement in a catalog file stops the server rather than running.

Scripts

npm run validate:catalog
npm test
npm run build
npm run pack:check

Publishing to npm

npm login
npm run pack:check
npm publish --access public

Defense in depth

Layer	Mechanism
SDK	SqlGuard allowlist + DML scan + param limits + no write API
Connection	default_transaction_read_only=on
Database	Readonly role with SELECT only

The database role is the security boundary; the other two layers are defense in depth. The guard does not understand function calls, and a few functions (dblink, nextval) escape a read-only transaction - see SECURITY.md. Set the role up with docs/db-role.sql, which includes a checklist for verifying that writes actually fail.

Questions, ideas or feedback?

Email: [email protected]

Or open a GitHub issue.

I read every email.