mcp-postgresql

README

MCP PostgreSQL Server

A lightweight, compiled MCP (Model Context Protocol) server that exposes PostgreSQL database operations as tools over stdio transport. Built in Go with zero CGO dependencies for easy deployment.

Features

• 5 MCP Tools: query, execute, list_tables, describe_table, list_schemas
• Read-Only / Read-Write Modes: Defense-in-depth with tool registration + transaction-level enforcement
• Flexible Configuration: Environment variables, CLI flags, or both (with clear precedence)
• Connection Pooling: Built on pgx with configurable pool size
• Static Binary: Single file, no runtime dependencies, ~10 MB

Installation

Go Install

go install github.com/andresfrei/mcp-postgresql@latest

Build from Source

git clone https://github.com/andresfrei/mcp-postgresql.git
cd mcp-postgresql
go build -o mcp-postgresql .

Optimized Build

CGO_ENABLED=0 go build -ldflags="-s -w" -o mcp-postgresql .

Configuration

Configuration follows three-tier precedence: defaults → environment variables → CLI flags.

Before configuration is resolved, the server optionally bootstraps environment values from dotenv files and mapping rules:

1. Load dotenv files (auto .env and .env.local, or custom files)
2. Apply MCP_ENV_MAP mappings (TARGET=SOURCE)
3. Resolve config from env aliases + CLI flags

Environment Variables

The server resolves env vars in this order:

1. Primary PostgreSQL-style vars (PG*, MCP_*)
2. Compatible aliases (POSTGRES_*, DB_*, MCP_SERVER_*)

Within each setting, the first non-empty variable in the list wins.

Variable	Required	Default	Description
PGHOST (POSTGRES_HOST, DB_HOST)	No	localhost	PostgreSQL server hostname
PGPORT (POSTGRES_PORT, DB_PORT)	No	5432	PostgreSQL server port
PGUSER (POSTGRES_USER, DB_USER)	No	postgres	Database user
PGPASSWORD (POSTGRES_PASSWORD, DB_PASSWORD)	Yes	—	Database password
PGDATABASE (POSTGRES_DB, POSTGRES_DATABASE, DB_NAME)	No	postgres	Target database name
PGSSLMODE (POSTGRES_SSLMODE, DB_SSLMODE)	No	prefer	SSL mode (disable, allow, prefer, require, verify-ca, verify-full)
MCP_READ_ONLY (MCP_SERVER_READ_ONLY)	No	true	Access mode: true for read-only, false for read-write
MCP_POOL_MAX_CONNS (MCP_SERVER_POOL_MAX_CONNS)	No	10	Maximum number of connections in the pool
MCP_ENV_FILES (MCP_ENV_FILE)	No	auto (.env,.env.local)	Optional dotenv file override (comma-separated paths)
MCP_ENV_MAP	No	—	Optional variable map TARGET=SOURCE,TARGET2=SOURCE2

Dotenv Loading

• Auto mode: if present in the current working directory, .env and .env.local are loaded.
• .env.local is loaded after .env, so it can override values from .env.
• Existing OS environment variables are never overwritten by dotenv files.
• Set MCP_ENV_FILES (or MCP_ENV_FILE) to replace auto mode with one or more custom files.

Example (.env + .env.local):

# .env
PGHOST=localhost
PGUSER=app_user
PGPASSWORD=dev_password
PGDATABASE=app_db

# .env.local
MCP_READ_ONLY=false
MCP_POOL_MAX_CONNS=20

Example override:

export MCP_ENV_FILES=".env,./config/db.env"

Environment Variable Mapping (MCP_ENV_MAP)

Use MCP_ENV_MAP when your app uses different variable names. Format:

MCP_ENV_MAP="TARGET=SOURCE,TARGET2=SOURCE2"

Rules:

• Mapping runs before config parsing.
• Mapping does not overwrite an already populated TARGET variable.
• If SOURCE is empty/unset, that mapping entry is skipped.

Example (app vars to PostgreSQL vars):

export DATABASE_HOST=db.internal
export DATABASE_USER=service_user
export DATABASE_PASSWORD=secret
export DATABASE_NAME=service_db
export MCP_ENV_MAP="PGHOST=DATABASE_HOST,PGUSER=DATABASE_USER,PGPASSWORD=DATABASE_PASSWORD,PGDATABASE=DATABASE_NAME"

CLI Flags

Flag	Overrides	Description
--host	PGHOST	PostgreSQL server hostname
--port	PGPORT	PostgreSQL server port
--user	PGUSER	Database user
--password	PGPASSWORD	Database password
--database	PGDATABASE	Target database name
--sslmode	PGSSLMODE	SSL mode
--read-only	MCP_READ_ONLY	Read-only mode (true or false)
--pool-max-conns	MCP_POOL_MAX_CONNS	Maximum pool connections
--env-file	MCP_ENV_FILES	Dotenv file(s) to load first (repeat or comma-separate)

CLI flags take the highest precedence — they override both defaults and environment variables.

For dotenv files specifically, precedence is: --env-file > MCP_ENV_FILES / MCP_ENV_FILE > auto .env + .env.local.

Usage with MCP Clients

Claude Desktop — CLI Flags

Add the following to your Claude Desktop configuration (claude_desktop_config.json):

{
  "mcpServers": {
    "postgresql": {
      "command": "/path/to/mcp-postgresql",
      "args": [
        "--host", "localhost",
        "--port", "5432",
        "--user", "myuser",
        "--password", "mypass",
        "--database", "mydb"
      ]
    }
  }
}

Claude Desktop — Environment Variables

{
  "mcpServers": {
    "postgresql": {
      "command": "/path/to/mcp-postgresql",
      "env": {
        "PGHOST": "localhost",
        "PGPORT": "5432",
        "PGUSER": "myuser",
        "PGPASSWORD": "mypass",
        "PGDATABASE": "mydb",
        "MCP_READ_ONLY": "true"
      }
    }
  }
}

Compatible Alias Example

If your environment already uses POSTGRES_* / DB_* naming, you can use those directly:

export POSTGRES_HOST=localhost
export POSTGRES_PORT=5432
export POSTGRES_USER=myuser
export POSTGRES_PASSWORD=mypass
export POSTGRES_DB=mydb
export MCP_SERVER_READ_ONLY=true

Other MCP Clients

Any MCP client that supports stdio transport can use this server. Start the binary with the appropriate flags or environment variables — the server communicates over stdin/stdout using the MCP JSON-RPC protocol.

Tools Reference

query

Execute a read SQL query and return results as JSON.

Input:

{ "sql": "SELECT id, name FROM users WHERE active = true" }

Output:

{
  "columns": ["id", "name"],
  "rows": [
    {"id": 1, "name": "Alice"},
    {"id": 2, "name": "Bob"}
  ],
  "row_count": 2
}

In read-only mode, all queries run inside a READ ONLY transaction — write attempts are rejected by PostgreSQL.

execute

Execute a write SQL statement (INSERT, UPDATE, DELETE, DDL) and return the number of affected rows. Only available in read-write mode.

Input:

{ "sql": "UPDATE users SET active = true WHERE last_login > '2025-01-01'" }

Output:

{ "affected_rows": 42 }

list_schemas

List all user-defined schemas in the database (excludes pg_catalog, pg_toast, information_schema).

Input:

{}

Output:

{
  "schemas": [
    {"schema_name": "public", "owner": "postgres"},
    {"schema_name": "reporting", "owner": "app_user"}
  ],
  "count": 2
}

list_tables

List all tables in a schema. Defaults to public if no schema is specified.

Input:

{ "schema": "public" }

Output:

{
  "schema": "public",
  "tables": [
    {"table_name": "users", "table_type": "BASE TABLE"},
    {"table_name": "orders", "table_type": "BASE TABLE"}
  ],
  "count": 2
}

describe_table

Describe a table's columns, types, nullability, defaults, and primary key status.

Input:

{ "table_name": "users", "schema": "public" }

Output:

{
  "schema": "public",
  "table_name": "users",
  "columns": [
    {
      "column_name": "id",
      "data_type": "integer",
      "is_nullable": false,
      "column_default": "nextval('users_id_seq'::regclass)",
      "is_primary_key": true
    },
    {
      "column_name": "name",
      "data_type": "character varying",
      "is_nullable": false,
      "column_default": null,
      "is_primary_key": false
    }
  ],
  "column_count": 2
}

Security

Read-Only Mode (Default)

The server defaults to read-only mode, enforced at two independent layers:

1. Tool registration layer: The execute tool is not registered with the MCP server — clients cannot invoke it.
2. Transaction layer: The query tool wraps all SQL in a READ ONLY transaction — PostgreSQL rejects any write operations at the database level.

Both layers are active simultaneously, providing defense in depth.

Recommendations

• Use read-only mode (the default) unless your use case specifically requires write access.
• Create a dedicated PostgreSQL user with minimal privileges for the MCP server.
• Avoid passing passwords via CLI flags in production — prefer environment variables or secrets managers.
• Use --sslmode=require or stricter when connecting over a network.
• Set --pool-max-conns appropriately for your workload to avoid exhausting database connections.

License

MIT License. See LICENSE for details.