readonly-postgres-mcp

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

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.