tommi2day/pg-mcp-server repository overview

docker pull tommi2day/pg-mcp-server:latest

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 \
  tommi2day/pg-mcp-server:latest

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

npm install
node index.js

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

# 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

# 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 \
  pg-mcp-server

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

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

environment:
  TLS_ENABLED: "true"
  TLS_SAN: "DNS:my-host.local,IP:192.168.1.10"
volumes:
  - ./certs:/certs   # mount real certs; leave empty → self-signed is generated

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

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)

# 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

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

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

# 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

./scripts/token.sh list                     # list all tokens (with connection info)
./scripts/token.sh add "claude-desktop"     # create new token (plaintext shown once)
./scripts/token.sh delete <id>              # deactivate token
./scripts/token.sh disable <id>             # temporarily block
./scripts/token.sh enable  <id>             # re-enable
./scripts/token.sh rename  <id> <new-name>  # rename

# Per-token database connection
PG_HOST=db.example.com PG_DATABASE=mydb PG_USER=u PG_PASSWORD=p \
  ./scripts/token.sh add "mydb-client"      # create token with custom connection

./scripts/token.sh setconn <id> '{"host":"db.example.com","port":5432,"database":"mydb","user":"u","password":"p"}'
./scripts/token.sh clearconn <id>           # reset to default admin connection

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

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

# Deactivate token
curl -X DELETE http://localhost:3000/admin/tokens/<id> \
  -H "Authorization: Bearer $AUTH_TOKEN"

[2026-03-28T19:32:51.654Z] [MCP]   token="claude-desktop" action="list_tables" ip="192.168.1.10" params={"schema":"public"}
[2026-03-28T19:32:51.859Z] [ADMIN] token="admin"          action="POST /admin/tokens" ip="192.168.1.10"

{
  "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": "secret",
        "ssl": "false"
      }
    }
  ],
  "next_id": 3
}

# 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

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