Postgres MCP Server

A Model Context Protocol (MCP) server for PostgreSQL databases. Connects AI assistants (Claude Desktop, Cursor, Antigravity, etc.) to a PostgreSQL database with schema discovery, catalog introspection, read-only analytical queries, and opt-in safe write operations.

Features

• Schema Discovery: Inspect schemas, tables, views, column data types, primary keys, and uniqueness constraints (list_tables, describe_table).
• Catalog & Governance Discovery: Discover visible databases, roles with attributes and memberships, and permissions across schemas, tables, and columns (list_databases, list_roles, list_permissions).
• Bounded Read Queries: Run parameterized SQL queries with pagination (limit, offset), strict maximum row limits, and automatic truncation detection (run_query).
• Gated Safe Writes: Write operations (run_write_query for INSERT, UPDATE, DELETE, DDL) are disabled by default and require explicit ALLOW_WRITE=1 configuration.
• Security & Privacy First: Zero credential leakage. Connection strings, passwords, and internal stack traces are redacted from logs and tool responses. Parameterized SQL prevents SQL injection.
• Stdio Transport: Seamlessly runs over stdio conforming to standard MCP protocol clients.

Quick Start

Running via NPX

Pass the connection string directly as a command-line argument or via environment variable:

# Read-only mode (default)
npx @allensandiego/postgres-mcp-server postgres://user:password@localhost:5432/mydb

# Enable write mode via CLI flag
npx @allensandiego/postgres-mcp-server postgres://user:password@localhost:5432/mydb --allow-write

# Or configure via environment variables
export DATABASE_URL="postgres://user:password@localhost:5432/mydb"
export ALLOW_WRITE=1   # Optional: enable write queries
npx @allensandiego/postgres-mcp-server

Global Installation

npm install -g @allensandiego/postgres-mcp-server

# Run read-only
postgres-mcp-server postgres://user:password@localhost:5432/mydb

# Run with write operations enabled
postgres-mcp-server postgres://user:password@localhost:5432/mydb --allow-write

Local Development

# Clone and install dependencies
git clone https://github.com/allensandiego/postgres-mcp-server.git
cd postgres-mcp-server
npm install

# Build
npm run build

# Run with tsx in development
npm run dev -- postgres://user:password@localhost:5432/mydb --allow-write

Configuration

The server can be configured via CLI flags or environment variables:

Connection String

You can provide the connection string in any of the following ways (in order of precedence):

1. CLI Positional Argument: postgres-mcp-server postgres://user:password@host:port/db
2. CLI Option: postgres-mcp-server --url=postgres://... or --connection-string=...
3. Environment Variables: DATABASE_URL, POSTGRES_URL, POSTGRES_CONNECTION_STRING, PG_CONNECTION_STRING, DATABASE_URI, POSTGRES_URI, PGURL, or PG_URL

Enabling Write Operations (ALLOW_WRITE)

By default, the server runs in read-only mode (run_write_query will reject any destructive or mutating SQL). To enable write queries (INSERT, UPDATE, DELETE, CREATE, DROP, ALTER):

• Via CLI flag: Pass --allow-write, --write, or -w
• Via Environment Variable: Set ALLOW_WRITE=1 (or ALLOW_WRITE=true)

Environment Variables Reference

| Variable | Description | Default | |---|---|---| | DATABASE_URL / POSTGRES_URL / POSTGRES_CONNECTION_STRING | Full PostgreSQL connection URI (postgres://user:pass@host:port/db) | None | | ALLOW_WRITE | Enables write queries (1, true, yes, on) | false (Read-only) | | PGHOST / POSTGRES_HOST | Database host name | localhost | | PGPORT | Database port number | 5432 | | PGDATABASE / POSTGRES_DB | Database name | postgres | | PGUSER / POSTGRES_USER | Database user name | postgres | | PGPASSWORD / POSTGRES_PASSWORD | Database password | None | | PGSSLMODE / PGSSL | SSL configuration mode (require, verify-full, etc.) | Disabled | | MAX_ROW_LIMIT / ROW_LIMIT | Maximum rows returned per query | 1000 | | QUERY_TIMEOUT_MS | Per-query timeout in milliseconds | 30000 (30s) | | MAX_CONNECTIONS / POOL_MAX | Maximum active database connections in pool | 10 |

MCP Tools Reference

1. list_tables

Discover all user schemas and their tables/views and columns without writing SQL.

• Arguments:
  • schema (optional string): Filter tables by schema name (e.g. "public").
• Output: Array of { schema, name, type, columns: [{ name, dataType, nullable, isPrimaryKey, isUnique }] }.

2. describe_table

Retrieve detailed column specifications and primary key definitions for a table.

• Arguments:
  • schema (required string): Schema name (e.g. "public").
  • table (required string): Table name (e.g. "users").
• Output: { schema, table, columns: [...], primaryKey?: string }.

3. list_databases

Discover databases visible and connectable to the connected user.

• Arguments: None.
• Output: Array of { name, owner, encoding, isTemplate, connectable }.

4. list_roles

Discover roles/users, their administrative attributes, and group memberships.

• Arguments: None.
• Output: Array of { name, superuser, canLogin, canCreateDb, canCreateRole, canBypassRls, memberOf, members }.

5. list_permissions

Discover granted privileges across schemas, tables, and columns.

• Arguments:
  • objectType (optional string): "schema", "table", or "column".
  • schema (optional string): Schema name filter.
  • table (optional string): Table name filter.
• Output: Array of { grantor, grantee, objectType, objectName, privilege, grantable }.

6. run_query

Execute a read-only parameterized SELECT query.

• Arguments:
  • sql (required string): Parameterized SQL statement (e.g. "SELECT * FROM orders WHERE status = $1").
  • params (optional array): Parameter substitution values.
  • limit (optional integer): Page limit (capped at MAX_ROW_LIMIT).
  • offset (optional integer): Page offset for pagination.
  • role (optional string): Role/user to assume (SET ROLE) for this specific query only.
• Output: { columns, rows, rowCount, truncated }.

7. run_write_query

Execute modifying SQL statements (INSERT, UPDATE, DELETE, DDL). Only active when ALLOW_WRITE=1 or --allow-write is provided.

• Arguments:
  • sql (required string): SQL write statement.
  • params (optional array): Parameter values.
  • role (optional string): Role/user to assume (SET ROLE) for this specific write query only.
• Output: { rowCount }.

8. set_role

Set the active PostgreSQL role/user for the session (SET ROLE) or restore the default session user (RESET ROLE).

• Arguments:
  • role (required string): Role/username to set (e.g. "analyst", "app_readonly", or "NONE" / "RESET" to return to the original session user).
• Output: { activeRole, sessionUser, isReset, message }.

MCP Client Setup Examples

Gemini CLI Configuration (mcp_config.json or settings.json)

Read-only mode (Default):

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@allensandiego/postgres-mcp-server@latest",
        "postgres://username:password@localhost:5432/mydb"
      ]
    }
  }
}

Write-enabled mode:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@allensandiego/postgres-mcp-server@latest",
        "postgres://username:password@localhost:5432/mydb",
        "--allow-write"
      ]
    }
  }
}

Or via environment variables:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@allensandiego/postgres-mcp-server@latest"],
      "env": {
        "DATABASE_URL": "postgres://username:password@localhost:5432/mydb",
        "ALLOW_WRITE": "1"
      }
    }
  }
}

Claude Desktop Configuration (claude_desktop_config.json)

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@allensandiego/postgres-mcp-server@latest",
        "postgres://username:password@localhost:5432/mydb"
      ],
      "env": {
        "ALLOW_WRITE": "0"
      }
    }
  }
}

Antigravity / Cursor Configuration

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@allensandiego/postgres-mcp-server@latest",
        "postgres://username:password@localhost:5432/mydb"
      ],
      "env": {
        "ALLOW_WRITE": "1"
      }
    }
  }
}

Testing & Quality Gates

Run the automated test suite (unit + contract + integration tests):

npm test

Type checking:

npm run typecheck

Linting:

npm run lint

License

This project is licensed under the PolyForm Noncommercial License 1.0.0 - free for personal, educational, research, and non-commercial open-source use. Commercial use requires a commercial license.