PostgreSQL数据库MCP服务

PostgreSQL MCP Server

Connects AI to PostgreSQL via the Model Context Protocol (MCP).

Features

MCP tools

• 12 tools: test_connection, list_schemas, list_tables, describe_table, query, execute and the performance tools explain_query, top_queries, table_stats, index_health, active_queries, performance_overview
• query runs inside BEGIN READ ONLY, one statement per call — PostgreSQL itself rejects writes, not a keyword filter
• execute runs in a transaction with automatic ROLLBACK on error
• Parameterized queries (params), results capped at 200 rows to keep LLM context small
• Query plans via explain_query (or EXPLAIN through query): ANALYZE actually runs the statement, but inside a read-only transaction that is always rolled back; generic_plan explains statements with unbound $1 placeholders (PostgreSQL 16+)
• Performance tuning: slowest statements from pg_stat_statements, stale statistics and per-column planner statistics, table bloat and seq-scan hot spots, unused/duplicate/invalid indexes, running and blocked sessions with wait events, cache hit ratio and key settings — all read-only, see Performance analysis

Multi-user & access control

• Bearer-token authentication with two levels: admin token (AUTH_TOKEN) and any number of client tokens
• Per-token database connection — one server instance serves several databases / DB users; each token is routed to its own connection pool
• Client-supplied connections (opt-in per token): clients log in with their own database account or choose the database via X-Pg-* headers (details)
• Tokens can be created, renamed, disabled and deleted at runtime — via web Admin UI (/admin, screenshots), REST API (OpenAPI spec included) or admincli.sh
• Admin UI in your corporate design: own logo and colors without rebuilding the image (branding)
• Tokens stored only as SHA-256 hashes, constant-time comparison; per-token DB passwords encrypted at rest (AES-256-GCM, STORE_ENCRYPTION_KEY)

Security & transport

• stdio (Claude Desktop / Claude Code) or Streamable HTTP with session management
• HTTPS with own certificates, auto-generated self-signed certs (renewed before expiry) and optional mTLS for clients
• TLS to PostgreSQL incl. server-cert verification (PG_SSL=verify) and client certificates
• Container runs as non-root (uid 1000), no privilege escalation, all capabilities dropped in Kubernetes

Audit logging

• Every tool call with token name, client IP and parameters; session start/stop with duration
• Failed logins with reason (unknown / disabled / invalid admin token) — presented tokens are never logged
• Admin API actions (example output); LOG_LEVEL (debug/info/warn/error) — SQL text only at debug

Operations

• Docker image on Docker Hub, docker-compose with test database, persistent volumes for tokens and certs
• Helm chart: PVC for the token store, existing Secrets, ingress, HPA, TLS-aware health probes
• Only two runtime dependencies (MCP SDK, pg), no build step, ~100 unit tests, Dependabot updates

Why this server?

Most PostgreSQL MCP servers are built for one developer on one machine: started via stdio with a single connection string, no authentication and no audit trail. This server targets the shared, self-hosted case — a team or several AI clients using one central instance:

Compared to the archived reference server @modelcontextprotocol/server-postgres (stdio only, a single read-only query tool), this server adds schema tools, a separate write tool, remote transport and everything above.

When to choose something else: the performance tools cover plans, workload statistics from pg_stat_statements, index and table health. For index recommendations verified with hypothetical indexes, look at Postgres MCP Pro; for several database engines (MySQL, SQL Server, SQLite, …) behind one server, look at DBHub. pg-mcp-server focuses on secure, audited, multi-user SQL access to PostgreSQL.

Overview

Environment Variables

Docker Hub

The image is available on Docker Hub:

docker pull tommi2day/pg-mcp-server:latest

Quick start from Hub

docker run -d --name pg-mcp-server \
  -p 3000:3000 \
  --add-host=host.docker.internal:host-gateway \
  -e TRANSPORT=http \
  -e AUTH_TOKEN=$(openssl rand -hex 32) \
  -e TOKENS_FILE=/data/tokens.json \
  -e PG_HOST=host.docker.internal \
  -e PG_DATABASE=mydb \
  -e PG_USER=user \
  -e PG_PASSWORD=password \
  -v pg-mcp-data:/data \
  -v pg-mcp-certs:/certs \
  tommi2day/pg-mcp-server:latest

Both volumes keep their contents across container restarts and re-creation:

In docker-compose.yml

Use the Hub image instead of building locally:

services:
  pg-mcp-server:
    image: tommi2day/pg-mcp-server:latest
    # build: .   ← remove or comment out

1 · Local (stdio)

npm install
node src/index.js

claude_desktop_config.json:

{
  "mcpServers": {
    "postgresql": {
      "command": "node",
      "args": ["/path/to/src/index.js"],
      "env": {
        "PG_HOST": "localhost",
        "PG_DATABASE": "mydb",
        "PG_USER": "user",
        "PG_PASSWORD": "password"
      }
    }
  }
}

2 · Docker

Quick start with run.sh

scripts/run.sh builds and starts the container in one step:

# Optionally configure the PostgreSQL connection via .env in the project root
cp .env.example .env
# edit .env: set PG_HOST, PG_DATABASE, PG_USER, PG_PASSWORD, ...

./scripts/run.sh              # start as "pg-mcp-server"
./scripts/run.sh my-name      # start with a custom container name

run.sh reads PGHOST / PGPORT / PGDATABASE / PGUSER / PGPASSWORD / PG_SSL from .env and auto-generates AUTH_TOKEN on first run (saved to ./auth_token).

• Stops and removes any existing container with the same name
• Auto-generates AUTH_TOKEN on first run and saves it to ./auth_token
• Reads defaults.env and then .env from the project root if present (.env wins)
• Image: IMAGE (default tommi2day/pg-mcp-server) and IMAGE_TAG (default latest), e.g. to use a mirror in a private registry

defaults.env is meant for site-specific defaults that are committed in a fork (registry, image tag, Helm values files), while .env stays local and untracked. scripts/helm_install.sh reads the same files and additionally accepts IMAGE_REPO, IMAGE_TAG and HELM_VALUES_FILES (space-separated list of extra -f values files).

Quick start (manual)

# Build image
docker build -t pg-mcp-server .

# Run against a local PostgreSQL
docker run -d --name pg-mcp-server \
  -p 3000:3000 \
  --add-host=host.docker.internal:host-gateway \
  -e TRANSPORT=http \
  -e AUTH_TOKEN=$(openssl rand -hex 32) \
  -e TOKENS_FILE=/data/tokens.json \
  -e PG_HOST=host.docker.internal \
  -e PG_DATABASE=mydb \
  -e PG_USER=user \
  -e PG_PASSWORD=password \
  -v pg-mcp-data:/data \
  -v pg-mcp-certs:/certs \
  pg-mcp-server

With docker-compose (including test database)

Copy the example env file, edit it, then start:

cp .env.example .env
# edit .env: set AUTH_TOKEN, PG_PASSWORD, etc.

docker compose up -d
docker compose logs -f pg-mcp-server

docker compose automatically reads .env from the project root. The docker-compose.yml includes a postgres-test container (port 5433) that must be healthy before pg-mcp-server starts (depends_on: condition: service_healthy).

Key variables in .env:

Enable TLS (optional)

environment:
  TLS_ENABLED: "true"
  TLS_SAN: "DNS:my-host.local,IP:192.168.1.10"

docker-compose.yml mounts the named volume mcp-certs at /certs (and mcp-data at /data for the token store), so certificates survive docker compose down / up. To use your own certificates, replace mcp-certs:/certs with a bind mount such as ./certs:/certs.

On startup:

• /certs contains a certificate → it is used. If the container (uid 1000) owns the key, it is set to mode 640; the key is never made world-readable. Mounted certs must be readable by uid 1000 (owner, or group 1000 with key mode 640)
• /certs is empty → a self-signed certificate is generated and stored in the volume
• A previously generated self-signed certificate (CN=pg-mcp-server) that expires within 30 days is regenerated automatically; your own certificates are never replaced

docker compose down -v deletes both volumes, including all tokens.

Once running, open http://localhost:3000/admin to manage tokens via the web UI.

Traefik reverse proxy (optional)

Route traffic through Traefik with automatic HTTP → HTTPS redirect and Let's Encrypt TLS.
TLS terminates at Traefik — keep TLS_ENABLED=false inside the container.

Both variants require the traefik-public external network (created by your Traefik stack).
Add it to the pg-mcp-server service networks list:

networks:
  - mcp-net
  - traefik-public

And declare it in the top-level networks: section:

networks:
  mcp-net:
    driver: bridge
  traefik-public:
    external: true

Host-based routing

Dedicated domain for the MCP server (e.g. pg-mcp.example.com):

labels:
  - "traefik.enable=true"
  # HTTP → HTTPS redirect
  - "traefik.http.routers.pg-mcp-http.rule=Host(`pg-mcp.example.com`)"
  - "traefik.http.routers.pg-mcp-http.entrypoints=web"
  - "traefik.http.routers.pg-mcp-http.middlewares=pg-mcp-https-redirect"
  - "traefik.http.middlewares.pg-mcp-https-redirect.redirectscheme.scheme=https"
  - "traefik.http.middlewares.pg-mcp-https-redirect.redirectscheme.permanent=true"
  # HTTPS router
  - "traefik.http.routers.pg-mcp.rule=Host(`pg-mcp.example.com`)"
  - "traefik.http.routers.pg-mcp.entrypoints=websecure"
  - "traefik.http.routers.pg-mcp.tls=true"
  - "traefik.http.routers.pg-mcp.tls.certresolver=letsencrypt"
  - "traefik.http.services.pg-mcp.loadbalancer.server.port=3000"
  - "traefik.docker.network=traefik-public"

MCP URL: https://pg-mcp.example.com/mcp

Path-based routing

Sub-path on a shared domain (e.g. https://proxy.example.com/pg-mcp).
Traefik strips the /pg-mcp prefix before forwarding, so the container still receives requests at /mcp, /admin/tokens, etc. unchanged.

labels:
  - "traefik.enable=true"
  # HTTP → HTTPS redirect
  - "traefik.http.routers.pg-mcp-http.rule=PathPrefix(`/pg-mcp`)"
  - "traefik.http.routers.pg-mcp-http.entrypoints=web"
  - "traefik.http.routers.pg-mcp-http.middlewares=pg-mcp-https-redirect"
  - "traefik.http.middlewares.pg-mcp-https-redirect.redirectscheme.scheme=https"
  - "traefik.http.middlewares.pg-mcp-https-redirect.redirectscheme.permanent=true"
  # HTTPS router — strip prefix, then forward to container
  - "traefik.http.routers.pg-mcp.rule=PathPrefix(`/pg-mcp`)"
  - "traefik.http.routers.pg-mcp.entrypoints=websecure"
  - "traefik.http.routers.pg-mcp.tls=true"
  - "traefik.http.routers.pg-mcp.tls.certresolver=letsencrypt"
  - "traefik.http.routers.pg-mcp.middlewares=pg-mcp-strip"
  - "traefik.http.middlewares.pg-mcp-strip.stripprefix.prefixes=/pg-mcp"
  - "traefik.http.services.pg-mcp.loadbalancer.server.port=3000"
  - "traefik.docker.network=traefik-public"

MCP URL: https://proxy.example.com/pg-mcp/mcp

For run.sh / docker run, translate each label to a --label flag.

Connect Claude Desktop / .mcp.json

{
  "mcpServers": {
    "postgresql": {
      "type": "http",
      "url": "http://<HOST>:3000/mcp",
      "headers": {
        "Authorization": "Bearer <AUTH_TOKEN>"
      }
    }
  }
}

Replace http:// with https:// for HTTPS.

Claude Desktop (claude_desktop_config.json) — HTTP/Remote

[!WARNING] Claude Desktop only supports HTTPS for non-local (remote) MCP servers. If your server runs on plain HTTP and is not on localhost, Claude Desktop will refuse the connection. Use one of the options below:

Option A — Preferred: Put the server behind a reverse proxy (Traefik, nginx, Caddy) with a valid TLS certificate and use https:// in the URL.

Option B — Quick workaround: Use mcp-remote as a local stdio bridge. It runs on your machine and forwards requests to the HTTP server, so Claude Desktop treats it like a stdio tool.

{
  "mcpServers": {
    "postgresql": {
      "command": "npx",
      "args": [
        "-y", "mcp-remote",
        "http://192.168.1.100:3000/mcp",
        "--allow-http",
        "--header", "Authorization:Bearer <AUTH_TOKEN>"
      ]
    }
  }
}

Verify the MCP endpoint

Health check:

curl http://localhost:3000/health

MCP initialize handshake (checks that the server responds to MCP requests):

curl -s -X POST http://localhost:3000/mcp \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer <AUTH_TOKEN>" \
  -d '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"test","version":"1.0"}}}'

Interactive browser UI via MCP Inspector:

# Local server — direct HTTP
npx @modelcontextprotocol/inspector http://localhost:3000/mcp

# Remote HTTP server — via mcp-remote bridge
npx @modelcontextprotocol/inspector \
  npx mcp-remote http://192.168.1.100:3000/mcp \
  --allow-http \
  --header "Authorization:Bearer <AUTH_TOKEN>"

The inspector opens a browser UI where you can list tools, call them individually, and inspect responses.

3 · Kubernetes with Helm

Prerequisites

• kubectl configured
• helm v3 installed
• Image accessible in a registry

Quick start (HTTP, no TLS)

helm install pg-mcp ./helm/pg-mcp-server \
  --namespace mcp --create-namespace \
  --set image.repository=tommi2day/pg-mcp-server \
  --set postgresql.host=my-db-host \
  --set postgresql.database=mydb \
  --set postgresql.user=user \
  --set postgresql.password=secret \
  --set auth.token=$(openssl rand -hex 32)

With HTTPS

# Create TLS secret
kubectl create secret tls pg-mcp-tls \
  --cert=certs/tls.crt --key=certs/tls.key -n mcp

# Create auth secret
kubectl create secret generic my-auth-secret \
  --from-literal=token=$(openssl rand -hex 32) -n mcp

# PostgreSQL CA (only for PG_SSL=verify)
kubectl create secret generic pg-ca-cert \
  --from-file=ca.crt=certs/pg-ca.crt -n mcp

helm install pg-mcp ./helm/pg-mcp-server \
  --namespace mcp --create-namespace \
  --set server.tlsEnabled=true \
  --set tls.existingSecret=pg-mcp-tls \
  --set auth.existingSecret=my-auth-secret \
  --set postgresql.ssl=verify \
  --set tls.pgCaSecret=pg-ca-cert \
  --set image.repository=tommi2day/pg-mcp-server \
  --set postgresql.host=my-db-host \
  --set postgresql.database=mydb \
  --set postgresql.user=user \
  --set postgresql.existingSecret=pg-credentials

Production values.yaml

image:
  repository: tommi2day/pg-mcp-server
  tag: "latest"

replicaCount: 2

persistence:
  enabled: true
  size: 50Mi
  storageClass: "standard"

auth:
  existingSecret: "my-auth-secret"

postgresql:
  host: "rds.example.com"
  database: "prod_db"
  user: "prod_user"
  ssl: "verify"
  existingSecret: "pg-credentials"

server:
  tlsEnabled: true

tls:
  existingSecret: "pg-mcp-tls"
  pgCaSecret: "pg-ca-cert"

service:
  type: LoadBalancer

ingress:
  enabled: true
  className: nginx
  annotations:
    nginx.ingress.kubernetes.io/backend-protocol: "HTTPS"
    cert-manager.io/cluster-issuer: letsencrypt-prod
  hosts:
    - host: pg-mcp.example.com
      paths:
        - path: /
          pathType: Prefix
  tls:
    - secretName: pg-mcp-ingress-tls
      hosts:
        - pg-mcp.example.com

autoscaling:
  enabled: true
  minReplicas: 2
  maxReplicas: 10

Upgrade / Uninstall

helm upgrade pg-mcp ./helm/pg-mcp-server -n mcp -f my-values.yaml
helm uninstall pg-mcp -n mcp

MCP Client Configuration

Use the HTTP variant when the server runs in Docker or on a remote host, and the stdio variant for a local Node.js install.

Claude Code CLI

# HTTP (Docker / remote)
claude mcp add --transport http postgresql http://localhost:3000/mcp \
  --header "Authorization: Bearer <AUTH_TOKEN>"

# stdio (local Node.js)
claude mcp add postgresql node /path/to/src/index.js \
  --env PG_HOST=localhost \
  --env PG_DATABASE=mydb \
  --env PG_USER=user \
  --env PG_PASSWORD=password

Add --scope user to register the server globally instead of per-project.

VS Code (GitHub Copilot)

Create .vscode/mcp.json in your project root (VS Code 1.99+):

HTTP (Docker / remote)

{
  "servers": {
    "postgresql": {
      "type": "http",
      "url": "http://localhost:3000/mcp",
      "headers": {
        "Authorization": "Bearer <AUTH_TOKEN>"
      }
    }
  }
}

stdio (local Node.js)

{
  "servers": {
    "postgresql": {
      "type": "stdio",
      "command": "node",
      "args": ["/path/to/src/index.js"],
      "env": {
        "PG_HOST": "localhost",
        "PG_DATABASE": "mydb",
        "PG_USER": "user",
        "PG_PASSWORD": "password"
      }
    }
  }
}

OpenCode

Use the interactive opencode mcp add command (answers shown for each prompt):

HTTP (Docker / remote)

opencode mcp add
  Name  →  postgresql
  Type  →  remote
  URL   →  http://localhost:3000/mcp
  Header Authorization  →  Bearer <AUTH_TOKEN>

stdio (local Node.js)

opencode mcp add
  Name     →  postgresql
  Type     →  local
  Command  →  node /path/to/src/index.js
  Env PG_HOST      →  localhost
  Env PG_DATABASE  →  mydb
  Env PG_USER      →  user
  Env PG_PASSWORD  →  password

The server is saved to opencode.json in the project root (or ~/.config/opencode/opencode.json for global config). Use opencode mcp list to verify, and opencode mcp remove <name> to delete.

Authentication

AUTH_TOKEN (env var) is the admin token — it grants access to /mcp and the token management API. Additional file tokens can be created via the admin UI or API; they only have access to /mcp. Token values are stored as SHA-256 hashes in a local JSON file (TOKENS_FILE); plaintext is shown only once at creation and never stored.

Each file token can optionally have its own PostgreSQL connection. When a token has no custom connection, it uses the server's default connection (PG_HOST / PG_DATABASE / … env vars).

No AUTH_TOKEN set → auth is completely disabled (local/dev only). The admin UI still works but does not require a token.

Client-supplied connections

By default a client always uses the connection of its token (or the server default). The admin can allow the clients of a token to send their own connection parameters — e.g. so that every user logs in with a personal database account instead of a shared technical user. The permission is the token field client_connection (admin UI: Client-supplied connection, admincli.sh add-token … --client-connection / set-client-conn):

For the AUTH_TOKEN admin token (and anonymous access when auth is disabled) PG_CLIENT_CONNECTION sets the same permission (default none).

Call format

The parameters are sent as HTTP headers on the MCP initialize request — the request that opens the MCP session (no mcp-session-id yet). They apply to the whole session; headers on later requests are ignored:

POST /mcp HTTP/1.1
Authorization: Bearer <token>
Content-Type: application/json
Accept: application/json, text/event-stream
X-Pg-User: jdoe
X-Pg-Password: s3cret
X-Pg-Database: sales

{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2025-06-18","capabilities":{},
 "clientInfo":{"name":"my-client","version":"1.0"}}}

MCP clients send configured headers with every request, so a static header entry is all that is needed:

// .mcp.json (Claude Code) / any client with Streamable HTTP + headers
{
  "mcpServers": {
    "pg-sales": {
      "type": "http",
      "url": "https://mcp.example.com/mcp",
      "headers": {
        "Authorization": "Bearer <token>",
        "X-Pg-User": "jdoe",
        "X-Pg-Password": "${PGPASSWORD}"
      }
    }
  }
}

# Claude Code CLI
claude mcp add --transport http pg-sales https://mcp.example.com/mcp \
  --header "Authorization: Bearer <token>" --header "X-Pg-User: jdoe" --header "X-Pg-Password: s3cret"

# Claude Desktop and other stdio-only clients via mcp-remote
npx mcp-remote https://mcp.example.com/mcp --header "Authorization: Bearer <token>" \
  --header "X-Pg-User: jdoe" --header "X-Pg-Password: s3cret"

Rules and safeguards:

• Client parameters override the token's connection: user/password replace the token's credentials; a client host replaces the token's host, port and database.
• Any client host, port, database or ssl requires client credentials — the password of the token or server default is never sent to a host chosen by the client, and TLS cannot be switched off for it.
• Tokens without an own connection keep the server's TLS settings (PG_SSL, CA and client certificates) for client credentials; server-side certificate files cannot be set by clients.
• Rejected requests get HTTP 403 (not permitted) or 400 (invalid, e.g. user without password) with a JSON-RPC error message, and are logged as [AUTH] result="denied" … reason="client connection: …". A wrong database password shows up as the error of the first tool call.
• The permission is re-checked on every request: switching a token back to none ends the use of client parameters in its open sessions immediately.
• Sessions with client parameters get their own connection pool (application_name stays <MCP_SERVER_NAME>:<token>), which is closed when the last such session ends. The session start log shows connection="client" target="host:port/database" user="…" (never the password).
• Passwords travel in HTTP headers — use HTTPS (TLS_ENABLED=true or a TLS-terminating proxy).

Admin UI

Open http://<HOST>:3000/admin in a browser. The web interface lets you manage tokens without using the command line or curl.

• Login: enter the server URL and the AUTH_TOKEN value. Leave the token field empty if auth is disabled.
• Token list: see all tokens with status (active/inactive), connection info, and last-used timestamp.
• Create token: enter a name and optionally configure a custom PostgreSQL connection. The generated token value is shown once — copy it before closing.
• Edit token: rename a token, toggle its active state, or update its database connection.
• Delete token: permanently removes the token record and immediately revokes access.

The session is stored in sessionStorage (cleared when the browser tab is closed).

Token list — status, per-token connection (server default or own database) and last use at a glance:

Admin UI branding

The admin UI can be switched to a corporate design without rebuilding the image:

Both files are read once at startup; an unreadable file is logged as [WARN] [CONFIG] and the built-in design is used. The design is served to everyone who opens /admin (also before sign-in), so do not put anything confidential into it.

All colors of the UI are CSS variables in the :root block at the top of admin.html:

examples/admin-theme/ contains a complete example (fictional "ACME data" design: light header with accent bar, teal primary color, own logo):

:root {
  --primary:      #00857c;
  --primary-dk:   #006b63;
  --header-bg:    #ffffff;
  --header-text:  #12333a;
  --font:         "Segoe UI", Arial, Helvetica, sans-serif;
}
header { border-bottom: 3px solid #f2a900; }   /* additional rules are fine */

docker run -d -p 3000:3000 \
  -v "$PWD/examples/admin-theme:/branding:ro" \
  -e ADMIN_THEME_CSS=/branding/theme.css -e ADMIN_LOGO=/branding/logo.svg \
  -e MCP_SERVER_NAME="ACME PostgreSQL MCP" … tommi2day/pg-mcp-server

With Helm put the files into a ConfigMap and set adminUi.brandingConfigMap, adminUi.themeCss and adminUi.logo (see values.yaml):

kubectl -n mcp create configmap mcp-branding \
  --from-file=examples/admin-theme/theme.css --from-file=examples/admin-theme/logo.svg

Token store

Tokens are persisted in a JSON file (default ./tokens.json, configurable via TOKENS_FILE).
Mount a volume at the file's directory so tokens survive container restarts — see the Docker and Helm sections above.

Password encryption

Set STORE_ENCRYPTION_KEY to a passphrase to enable AES-256-GCM encryption of connection.password values at rest. Passwords are encrypted on save and decrypted transparently on load. On startup the server migrates any plaintext passwords it finds in the file — no manual step required.

export STORE_ENCRYPTION_KEY="my-secret-passphrase"

Without this variable the passwords are stored as plaintext (acceptable for local/dev use). Changing the key requires re-saving every token (plaintext migration runs automatically on startup only when the key is set).

Manage tokens with admincli.sh

admincli.sh reads AUTH_TOKEN and MCP_URL from environment variables or from a scripts/.env file:

# Option A – environment variables
export AUTH_TOKEN=<admin-token>
export MCP_URL=http://localhost:3000   # optional, default

# Option B – scripts/.env file
cat > scripts/.env <<EOF
AUTH_TOKEN=<admin-token>
MCP_URL=http://localhost:3000
EOF

When using run.sh, the generated token is stored in ./auth_token:

复制

export AUTH_TOKEN=$(cat auth_token)

# Server info
./scripts/admincli.sh health                        # server health status + TLS flag (no auth required)

# Token management
./scripts/admincli.sh list-tokens                   # list all tokens (CONN column shows per-token connection)
./scripts/admincli.sh show-token <id>               # full details for one token, including connection config
./scripts/admincli.sh add-token "claude-desktop"    # create new token (plaintext shown once)
./scripts/admincli.sh delete-token <id>             # permanently delete token
./scripts/admincli.sh disable-token <id>            # temporarily block
./scripts/admincli.sh enable-token  <id>            # re-enable
./scripts/admincli.sh rename-token  <id> <new-name> # rename

# Per-token database connection — pass flags or env vars (flags take precedence)
./scripts/admincli.sh add-token "mydb-client" \
  --host db.example.com --database mydb --user u --password p --ssl false

# env-var form still works:
PG_HOST=db.example.com PG_DATABASE=mydb PG_USER=u PG_PASSWORD=p \
  ./scripts/admincli.sh add-token "mydb-client"

./scripts/admincli.sh set-conn <id> '{"host":"db.example.com","port":5432,"database":"mydb","user":"u","password":"p"}'
./scripts/admincli.sh clear-conn <id>               # reset to default admin connection

add-token connection flags: --host, --port, --database, --user, --password, --ssl.
Flags take precedence over the corresponding PG_* env vars. Omit all connection arguments to use the server's default admin connection.

Validate a token with test_token.sh

Connects to the server using the given token and lists tables — useful to confirm a newly created token works:

./scripts/test_token.sh <token>              # schema: public (default)
./scripts/test_token.sh <token> myschema     # specific schema

Reads MCP_URL from environment or scripts/.env. Exits with a clear error message on failure (invalid token, server unreachable, MCP tool error, etc.).

The script sends an X-Real-IP header so the server logs the real client IP. The value is taken from X_REAL_IP env var if set, otherwise auto-detected from the first local interface (hostname -I).

Manage tokens with curl

# List tokens (includes connection info; token_hash is never returned)
curl http://localhost:3000/admin/tokens \
  -H "Authorization: Bearer $AUTH_TOKEN"

# Create token (default connection)
curl -X POST http://localhost:3000/admin/tokens \
  -H "Authorization: Bearer $AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name": "claude-desktop"}'

# Create token with a custom DB connection
curl -X POST http://localhost:3000/admin/tokens \
  -H "Authorization: Bearer $AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "mydb-client",
    "connection": {
      "host": "db.example.com",
      "port": 5432,
      "database": "mydb",
      "user": "myuser",
      "password": "secret",
      "ssl": "false"
    }
  }'

# Set or update the connection on an existing token
curl -X PATCH http://localhost:3000/admin/tokens/<id> \
  -H "Authorization: Bearer $AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"connection": {"host": "db.example.com", "database": "mydb", "user": "u", "password": "p"}}'

# Clear per-token connection (fall back to default admin connection)
curl -X PATCH http://localhost:3000/admin/tokens/<id> \
  -H "Authorization: Bearer $AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"connection": null}'

# Rename / re-enable token
curl -X PATCH http://localhost:3000/admin/tokens/<id> \
  -H "Authorization: Bearer $AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name": "new-name", "active": true}'

# Delete token (permanently removed)
curl -X DELETE http://localhost:3000/admin/tokens/<id> \
  -H "Authorization: Bearer $AUTH_TOKEN"

Logging

All log lines go to stderr and are visible in docker logs <name>. Each line carries an ISO 8601 timestamp, a level and a category:

[2026-03-28T19:32:50.100Z] [INFO] [SESSION] token="claude-desktop" action="start" session="3f2c…" ip="192.168.1.10"
[2026-03-28T19:32:51.654Z] [INFO] [MCP] token="claude-desktop" action="list_tables" ip="192.168.1.10" params={"schema":"public"}
[2026-03-28T19:32:51.700Z] [INFO] [MCP] token="claude-desktop" action="query" ip="192.168.1.10" params={"sql":"<42 chars, LOG_LEVEL=debug to show>"}
[2026-03-28T19:32:51.859Z] [ERROR] [MCP] token="claude-desktop" action="query" ip="192.168.1.10" error="column \"x\" does not exist"
[2026-03-28T19:32:51.900Z] [INFO] [SESSION] token="claude-desktop" action="stop" session="3f2c…" ip="192.168.1.10" duration=312s
[2026-03-28T19:32:51.950Z] [WARN] [AUTH] result="denied" action="POST /mcp" ip="10.1.2.3" reason="unknown token"
[2026-03-28T19:32:51.960Z] [WARN] [AUTH] result="denied" token="old-client" action="POST /mcp" ip="10.1.2.3" reason="token disabled"
[2026-03-28T19:32:51.970Z] [INFO] [ADMIN] token="admin" action="POST /admin/tokens" ip="192.168.1.10"
[2026-03-28T19:32:52.001Z] [ERROR] [ADMIN] token="admin" action="GET /admin/tokens" ip="192.168.1.10" error="ENOENT: ..."
[2026-03-28T19:32:52.100Z] [ERROR] [DB] Pool error: Connection terminated unexpectedly
[2026-03-28T19:32:52.200Z] [ERROR] [HTTP] Unhandled error for POST /mcp: socket hang up
[2026-03-28T19:33:00.000Z] [ERROR] [FATAL] Unhandled rejection: getaddrinfo ENOTFOUND db.example.com

LOG_LEVEL (debug / info / warn / error, default info) sets the minimum level that is written.

Real output of a short session at the default level info — two clients working in parallel, a write attempt rejected by the read-only DB user of reporting-team, and four rejected logins (disabled token, unknown token, wrong admin token, missing token). SQL text is replaced by its length:

With LOG_LEVEL=debug the full SQL text is logged:

Shipping logs to Elasticsearch: the exact line format, tested Filebeat configurations (Docker and Kubernetes) and a Logstash pipeline that turns each line into structured fields are in docs/logging.md.

The client IP is resolved in order: x-real-ip header → first entry of x-forwarded-for → TCP socket address. When running Docker without a reverse proxy, the socket address is the Docker bridge IP — deploy behind nginx or Traefik to log the real client IP.

Token file format

The token store is a plain JSON file. The server reads and writes it automatically — do not edit it while the server is running.

{
  "tokens": [
    {
      "id": 1,
      "name": "claude-desktop",
      "token_hash": "<sha256-hex>",
      "created_at": "2026-04-03T10:00:00.000Z",
      "last_used_at": "2026-04-03T12:34:56.789Z",
      "active": true,
      "connection": null
    },
    {
      "id": 2,
      "name": "mydb-client",
      "token_hash": "<sha256-hex>",
      "created_at": "2026-04-03T10:05:00.000Z",
      "last_used_at": null,
      "active": true,
      "connection": {
        "host": "db.example.com",
        "port": 5432,
        "database": "mydb",
        "user": "myuser",
        "password": "enc:v1:<iv-hex>:<tag-hex>:<ciphertext-hex>",
        "ssl": "false"
      }
    }
  ],
  "next_id": 3
}

connection: null means the token uses the server's default PostgreSQL connection. An optional "client_connection": "credentials" or "full" allows its clients to send their own connection parameters (client-supplied connections). The token_hash field is a SHA-256 hex digest — the plaintext token is never stored. When STORE_ENCRYPTION_KEY is set, connection.password is stored as enc:v1:<iv>:<tag>:<ciphertext> (AES-256-GCM); otherwise it is stored in plaintext.

Automated Updates

Dependabot is configured to check for updates weekly for:

• npm dependencies (grouped into production and dev)
• Docker base images
• GitHub Actions

The Dependabot Automerge workflow automatically enables auto-merge for Dependabot PRs.

Development

# Install dependencies
npm install

# Run tests
./scripts/test.sh                        # all tests
./scripts/test.sh tests/lib.test.js      # single file

# Coverage report
./scripts/coverage.sh                    # report written to ./coverage/
./scripts/coverage.sh --open             # open HTML report in browser

# Linting
./scripts/lint.sh                        # check all files
./scripts/lint.sh --fix                  # auto-fix issues

All scripts require only Docker — no local Node.js needed.

Available MCP Tools

The performance tools work best with GRANT pg_monitor TO <user> and the pg_stat_statements extension — details, workflows and troubleshooting in Performance analysis.

Endpoints

A full OpenAPI 3.1 specification is available in openapi.json.

Release

The release workflow (.github/workflows/release.yml) runs lint, tests, builds and pushes the Docker image, and creates a GitHub Release with auto-generated notes.

Option 1 — Push a git tag

Use npm version to bump all version files together, then push the tag:

npm version 1.2.3   # bumps package.json, openapi.json and Chart.yaml, commits, creates git tag
git push origin main 1.2.3

The version lifecycle script keeps openapi.json and helm/pg-mcp-server/Chart.yaml in sync automatically. The tag must match [0-9]+.[0-9]+.[0-9]+ (e.g. 1.2.3, no v prefix).

Option 2 — Manual dispatch (no local git required)

Go to Actions → Release → Run workflow, enter a version number (e.g. 1.2.3), and click Run workflow.

The workflow will:

1. Bump package.json, openapi.json and helm/pg-mcp-server/Chart.yaml to the entered version, commit and push to main
2. Run lint and tests
3. Build and push the Docker image (tommi2day/pg-mcp-server:1.2.3, :1.2, :1, :latest, :sha-<short>)
4. Create and push the git tag automatically
5. Publish a GitHub Release with auto-generated notes

Docker image tags per release

License

MIT