# readonly-postgres-mcp

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

[![npm version](https://img.shields.io/npm/v/readonly-postgres-mcp.svg)](https://www.npmjs.com/package/readonly-postgres-mcp)
[![npm downloads](https://img.shields.io/npm/dm/readonly-postgres-mcp.svg)](https://www.npmjs.com/package/readonly-postgres-mcp)
[![CI](https://github.com/amar141989-dev/readonly-postgres-mcp/actions/workflows/ci.yml/badge.svg)](https://github.com/amar141989-dev/readonly-postgres-mcp/actions/workflows/ci.yml)
[![license](https://img.shields.io/npm/l/readonly-postgres-mcp.svg)](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](SECURITY.md) for the threat model and the guard's documented
limits before pointing this at production.

## Install from npm

```bash
npm install readonly-postgres-mcp
```

Or run the MCP server without a global install:

```bash
npx readonly-postgres-mcp
```

Optional peer for NestJS apps:

```bash
npm install @nestjs/common
```

## Environment

A connection URL works, if you already have one:

```env
DATABASE_URL=postgresql://readonly_user:pw@db.example.com: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:

```env
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](#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

```typescript
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

```typescript
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:

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

```bash
pg-readonly-mcp
```

**Ad-hoc example** (`pg_query_sql`):

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

With positional params:

```json
{
  "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:

```json
{
  "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

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

## Publishing to npm

```bash
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](SECURITY.md). Set the role up with
[`docs/db-role.sql`](docs/db-role.sql), which includes a checklist for verifying
that writes actually fail.

## Questions, ideas or feedback?

Email: `amar141989@gmail.com`

Or open a [GitHub issue](https://github.com/amar141989-dev/readonly-postgres-mcp/issues).

I read every email.