postgresdb-mcp

postgresdb-mcp

A Model Context Protocol (MCP) server that gives any MCP-compatible AI assistant direct access to PostgreSQL databases across multiple environments.

Features

• Multi-environment: connect to any number of databases (local, tst, stg, preprod, prod…) from a single .env file
• Write protection (fail-closed): writes disabled by default (ALLOW_WRITES=false), or enabled with mandatory "WRITE" confirmation (ALLOW_WRITES=true) for any statement that is not clearly read-only
• Schema scope control: optionally restrict each environment to one or more schemas via SCHEMA (comma-separated)
• 5 tools: query, list-tables, describe-table, list-schemas, list-environments
• Connection pooling: up to 5 connections per environment, with automatic pool recovery on error
• Parameterized queries: safe execution with $1, $2 … placeholders
• SSL support: configurable per environment with certificate verification control

Installation

git clone https://github.com/yourusername/postgresdb-mcp.git
cd postgresdb-mcp
npm install
npm run build

Configuration

Copy the example env file and fill in your credentials:

cp .env.dist .env

.env is gitignored so your credentials stay local and are never committed.

Edit .env with your database credentials. Environments are auto-discovered: any POSTGRES_{ENV}_HOST variable defines a new environment. The order in the file is preserved.

# Local
POSTGRES_LOCAL_HOST=localhost
POSTGRES_LOCAL_DATABASE=mydb
POSTGRES_LOCAL_USER=postgres
POSTGRES_LOCAL_PASSWORD=postgres
POSTGRES_LOCAL_ALLOW_WRITES=false

# Staging
POSTGRES_STG_HOST=your-env-host
POSTGRES_STG_DATABASE=stg_mydb
POSTGRES_STG_USER=stg_user
POSTGRES_STG_PASSWORD=your-stg-password
POSTGRES_STG_SSL=true
POSTGRES_STG_ALLOW_WRITES=false

# Production
POSTGRES_PROD_HOST=your-env-host
POSTGRES_PROD_DATABASE=prod_mydb
POSTGRES_PROD_USER=prod_user
POSTGRES_PROD_PASSWORD=your-prod-password
POSTGRES_PROD_SSL=true
POSTGRES_PROD_ALLOW_WRITES=false

Available variables per environment (prefix: POSTGRES_{ENV}_):

| Variable | Required | Default | Description | |---|---|---|---| | HOST | yes | - | PostgreSQL host | | PORT | no | 5432 | PostgreSQL port | | DATABASE | yes | - | Database name | | USER | yes | - | Database user | | PASSWORD | yes | - | Database password | | SCHEMA | no | _all schemas_ | Schema allowlist (comma-separated). Example: public or public,users. When set, tools are scoped and queries are restricted to these schemas. | | SSL | no | false | Enable SSL (true/false) | | SSL_REJECT_UNAUTHORIZED | no | true | Verify SSL certificate. Default true — only set to false if your DB uses a self-signed cert and you have no other option. Never disable in production. | | ALLOW_WRITES | no | false | true: writes allowed, confirm_write="WRITE" required to execute. false: writes completely blocked, no confirmation shown. |

MCP client setup

This server works with any MCP-compatible client. Below are examples for common ones.

Claude Desktop

Add to ~/.config/Claude/claude_desktop_config.json (macOS: ~/Library/Application Support/Claude/claude_desktop_config.json):

{
  "mcpServers": {
    "postgresdb": {
      "command": "node",
      "args": [
        "/absolute/path/to/postgresdb-mcp/dist/index.js",
        "--env",
        "/absolute/path/to/.env"
      ]
    }
  }
}

Restart Claude Desktop after editing.

Claude CLI

claude mcp add postgresdb -- node /absolute/path/to/dist/index.js --env /absolute/path/to/.env

Other MCP clients

Start the server manually, it communicates over stdio:

node /absolute/path/to/postgresdb-mcp/dist/index.js --env /absolute/path/to/.env

If --env is omitted, the server looks for a .env file in the current working directory.

Refer to your client's documentation for how to register an MCP server using stdio transport.

Available tools

query

Execute a SQL query on a target environment. Returns environment, database, queryType, duration, rowCount, rows, and fields.

Run: SELECT COUNT(*) FROM users.orders WHERE status = 'pending' on stg

Write operations are subject to the environment's write protection mode (see Write protection). To confirm a write on an environment with ALLOW_WRITES=true, pass confirm_write="WRITE".

list-tables

List all tables in a schema.

List all tables in the public schema on local

describe-table

Get the full structure of a table (columns, types, nullability, defaults).

Describe the users table in the public schema on stg

list-schemas

List all user-defined schemas in a database.

What schemas are available on prod?

list-environments

List all configured environments (no credentials exposed).

What environments are configured?

Write protection

Every environment has one of two write modes, controlled by POSTGRES_{ENV}_ALLOW_WRITES:

| Mode | Config | Behaviour | |---|---|---| | Blocked (default) | ALLOW_WRITES=false or not set | Writes (UPDATE, DELETE, INSERT, DROP…) are immediately rejected. No confirmation prompt is shown. | | Allowed with confirmation | ALLOW_WRITES=true | Writes are allowed, but the AI must explicitly pass confirm_write="WRITE" (exact string, case-sensitive) to execute. |

The guard inspects SQL in a fail-closed way:

• comments and quoted literals are neutralized before analysis (including dollar-quoted blocks)
• multi-statement payloads are checked statement-by-statement
• CTEs and hidden payloads (for example via PREPARE ...; EXECUTE ...) are detected
• statements outside the explicit read-only subset (SELECT, WITH, VALUES, SHOW, TABLE, EXPLAIN) are treated as write-sensitive and require confirm_write="WRITE" when writes are enabled
• defense-in-depth: when confirm_write!="WRITE", queries run inside BEGIN READ ONLY, so accidental writes from side-effect functions are blocked at PostgreSQL level

Schema scope

By default, if POSTGRES_{ENV}_SCHEMA is not set, the MCP can access all schemas the DB user is allowed to access.

Set POSTGRES_{ENV}_SCHEMA to scope access per environment:

• single schema: POSTGRES_STG_SCHEMA=public
• multiple schemas: POSTGRES_PROD_SCHEMA=public,users

When schema scope is configured:

• list-tables / describe-table reject schemas outside the allowlist
• list-schemas only returns allowed schemas
• query blocks explicit references to non-allowed schemas
• query executes with SET LOCAL search_path on allowed schema(s) for additional isolation

For strongest isolation, keep using dedicated PostgreSQL users with least-privilege grants per schema.

Recommendation: set ALLOW_WRITES=true on environments where you need to write from the AI (preprod, prod) — every write will require a deliberate "WRITE" confirmation. Leave it unset on read-only environments (replicas, analytics DBs).

Development

npm run build   # compile TypeScript
npm run watch   # watch mode

Requirements: Node.js >= 18

License

MIT