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

npm install readonly-postgres-mcp

Or run the MCP server without a global install:

npx readonly-postgres-mcp pg-readonly-mcp

Optional peer for NestJS apps:

npm install @nestjs/common

Environment

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

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

Two readonly tools:

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

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.

Cursor MCP config:

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

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