# readonly-postgres-mcp

Readonly PostgreSQL MCP server with SQL guardrails for analytical queries and schema introspection.

**This package never writes to the database.** There is no write API and no migration runner.

## Install from npm

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

Or run the MCP server without a global install:

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

Optional peer for NestJS apps:

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

## Environment

```env
PG_HOST=localhost
PG_PORT=5432
PG_DATABASE=postgres
PG_USERNAME=readonly_user
PG_PASSWORD=...
PG_SSL=false
PG_SEARCH_PATH=public
PG_STATEMENT_TIMEOUT_MS=30000
PG_MAX_ROWS=10000
PG_MCP_ALLOW_ADHOC=true
```

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

Two readonly tools:

| Tool | Purpose |
|------|---------|
| `pg_query` | Named catalog query by `queryId` |
| `pg_query_sql` | Ad-hoc `SELECT` / `WITH` / `EXPLAIN` (enabled by default) |

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

Cursor MCP config:

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

## Adding queries

1. Add `.sql` under `queries/` using `:namedParams`
2. Register in `registry.json`
3. Run `npm run validate:catalog`

## 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 |