PostgreSQL MCP Server

by henkdz

18 stars
Not rated
GitHub

About

Integrates with PostgreSQL databases to enable schema management, data migration, performance monitoring, and security configuration through direct database operations without requiring separate management tools.

Details

Author
henkdz
Repository
HenkDz/postgresql-mcp-server
GitHub stars
18
License
GNU Affero General Public License v3.0
Categories
Database, Productivity, Developer Tools, Design, Workplace, File Management, AI, Infrastructure, Frontend
Tags
#analytics

Jump to

✅ Complete CRUD operations - INSERT/UPDATE/DELETE/UPSERT with parameterized queries
✅ Flexible querying - SELECT with count/exists support and bounded safety limits
✅ Arbitrary SQL execution - Transaction support for complex operations

Setting up with Highlight

This MCP is not yet compatible with Highlight’s one-click setup. However, you can still use it with Highlight by following these steps:

  1. Download and install Highlight from highlightai.com/download
  2. Navigate to the plugins tab and select "Add Custom Plugin"
  3. Configure the plugin with the settings below
    Plugin Name PostgreSQL MCP Server
    Command (node, npx, python, etc.) npx
    Arguments
    • Argument 1 @henkey/postgres-mcp-server
    • Argument 2 --connection-string
    • Argument 3 postgresql://user:password@host:port/database

    Please refer to the README for specific instructions on how to obtain API keys or other required environment variables.

  4. Enable "Start Automatically" if you want the plugin to start when Highlight launches

From the repository

Option 4 Manual Installation Development

git clone <repository-url> cd postgresql-mcp-server npm install npm run build

{ "mcpServers": { "postgresql-mcp": { "command": "node", "args": [ "/path/to/postgresql-mcp-server/build/index.js", "--connection-string", "postgresql://user:password@host:port/database" ] } } }

The server now starts inreadonlymode by default. Tools may still be listed for MCP discovery, but every call is classified and checked before it reaches the database.

Destructive operations such as drops, resets, and arbitrary SQL also require explicit opt-in:

# Default: readonly, no per-tool connection strings npx @henkey/postgres-mcp-server --connection-string "postgresql://readonly_user:pass@host:5432/db" # Enable DML mutations, but still block DDL/admin/arbitrary SQL npx @henkey/postgres-mcp-server --security-mode write --connection-string "postgresql://app_writer:pass@host:5432/db" # Enable admin tools and destructive operations npx @henkey/postgres-mcp-server --security-mode admin --allow-destructive --connection-string "postgresql://admin_user:pass@host:5432/db" # Enable arbitrary SQL only for trusted local/admin use npx @henkey/postgres-mcp-server --security-mode unsafe --allow-destructive --connection-string "postgresql://admin_user:pass@host:5432/db"

Per-toolconnectionString,sourceConnectionString, andtargetConnectionStringarguments are disabled by default. Prefer a fixed server-level connection string with a least-privilege PostgreSQL role. For development only, enable per-tool connection strings with--allow-tool-connection-stringorPOSTGRES_MCP_ALLOW_TOOL_CONNECTION_STRING=true. Explicit per-tool, CLI, andPOSTGRES_CONNECTION_STRINGvalues must be non-empty strings. Blank higher-priority connection strings fail validation instead of falling back to lower-priority sources.

Optionally restrict all server-level and per-tool connection strings to an allowlist with--allowed-connection-target,allowedConnectionTargets, orPOSTGRES_MCP_ALLOWED_CONNECTION_TARGETS. Target patterns use[user@]host[:port][/database]; omitted fields are unconstrained andis allowed only as a full-field wildcard, for examplereadonly@db.internal:5432/appor@localhost:/dev.

For deployment grants, seePostgreSQL Role Templates. The templates split readonly, writer, schema-admin, and role-admin credentials so the PostgreSQL role remains aligned with the selected MCPsecurityMode.

Security settings can also be placed in the tools config file:

{ "securityMode": "readonly", "allowDestructive": false, "allowToolConnectionString": false, "workspaceDir": "/path/to/mcp-workspace", "auditFile": "/path/to/postgres-mcp-audit.jsonl", "maxConnections": 20, "idleTimeoutMillis": 30000, "connectionTimeoutMillis": 2000, "maxFileBytes": 10485760, "statementTimeoutMs": 30000, "queryTimeoutMs": 45000, "lockTimeoutMs": 10000, "idleInTransactionSessionTimeoutMs": 60000, "allowedConnectionTargets": [ "readonly@db.internal:5432/app" ], "enabledTools": [ "pg_analyze_database", "pg_manage_schema", "pg_execute_query" ] }

Runtime configuration precedence is CLI options, then the tools config file, then environment variables. Explicitfalsevalues in the tools config override enabling environment variables such asPOSTGRES_MCP_ALLOW_DESTRUCTIVE=true.

If a tools config path is provided, the server treats it as required: unreadable, malformed, non-object, incorrectly typed, unknown-key, invalidsecurityMode, or unknownenabledToolsentries stop startup instead of falling back to all available tools.

- --version
- --connection-string
- --tools-config
- --security-mode
- --allow-destructive
- --allow-tool-connection-string
- --workspace-dir
- --audit-file
- --max-connections
- --idle-timeout-ms
- --connection-timeout-ms
- --max-file-bytes
- --statement-timeout-ms
- --query-timeout-ms
- --lock-timeout-ms
- --idle-in-transaction-session-timeout-ms
- --allowed-connection-target

- POSTGRES_TOOLS_CONFIG=/path/to/tools.json
- POSTGRES_MCP_SECURITY_MODE=readonly|write|admin|unsafe
- POSTGRES_MCP_ALLOW_DESTRUCTIVE=true
- POSTGRES_MCP_ALLOW_TOOL_CONNECTION_STRING=true
- POSTGRES_MCP_WORKSPACE_DIR=/path/to/mcp-workspace
- POSTGRES_MCP_AUDIT_FILE=/path/to/postgres-mcp-audit.jsonl
- POSTGRES_MCP_MAX_CONNECTIONS=20
- POSTGRES_MCP_IDLE_TIMEOUT_MS=30000
- POSTGRES_MCP_CONNECTION_TIMEOUT_MS=2000
- POSTGRES_MCP_MAX_FILE_BYTES=10485760
- POSTGRES_MCP_STATEMENT_TIMEOUT_MS=60000
- POSTGRES_MCP_QUERY_TIMEOUT_MS=65000
- POSTGRES_MCP_LOCK_TIMEOUT_MS=10000
- POSTGRES_MCP_IDLE_IN_TRANSACTION_SESSION_TIMEOUT_MS=60000
- POSTGRES_MCP_ALLOWED_CONNECTION_TARGETS=readonly@db.internal:5432/app,@localhost:/dev
- POSTGRES_MCP_DEBUG_SQL=trueto opt into verbosepg-monitorSQL tracing. This may log raw SQL and bind values, so leave it disabled unless you are debugging a trusted local database.

Boolean environment flags must be exactlytrueorfalsewhen set. Numeric resource settings from CLI, tools config, or environment variables must be positive integers. Runtime defaults use a 20-connection pool, a 30000 ms pool idle timeout, a 2000 ms connection timeout, a 60000 ms PostgreSQLstatement_timeout, a 65000 ms node-postgres query timeout, a 10000 ms PostgreSQLlock_timeout, and a 60000 ms PostgreSQLidle_in_transaction_session_timeout. Pool and timeout settings can be raised or lowered with--max-connections,--idle-timeout-ms,--connection-timeout-ms,--statement-timeout-ms,--query-timeout-ms,--lock-timeout-ms,--idle-in-transaction-session-timeout-ms,maxConnections,idleTimeoutMillis,connectionTimeoutMillis,statementTimeoutMs,queryTimeoutMs,lockTimeoutMs,idleInTransactionSessionTimeoutMs,POSTGRES_MCP_MAX_CONNECTIONS,POSTGRES_MCP_IDLE_TIMEOUT_MS,POSTGRES_MCP_CONNECTION_TIMEOUT_MS,POSTGRES_MCP_STATEMENT_TIMEOUT_MS,POSTGRES_MCP_QUERY_TIMEOUT_MS,POSTGRES_MCP_LOCK_TIMEOUT_MS, orPOSTGRES_MCP_IDLE_IN_TRANSACTION_SESSION_TIMEOUT_MS. Explicit connection string,workspaceDir,auditFile,--workspace-dir, and--audit-filevalues must be non-empty strings. Connection target allowlists are enforced before tool execution for per-tool connection strings and during connection resolution for server-level sources. When an allowlist is configured, connection strings must be PostgreSQL URL or keyword-style strings with an explicithostorhostaddr.

Mutation, index, export, and copy filters should use structuredwherepredicates. Legacy stringwhereclauses are rejected; the explicitrawWhereescape hatch is treated as arbitrary SQL and requires--security-mode unsafe --allow-destructive.

EXPLAIN tools only accept one read-only statement and run inside a read-only transaction.analyze: truestill requires unsafe mode because PostgreSQL executes the supplied query to collect runtime statistics.

Multi-statementpg_execute_sqlcalls must usetransactional: true,expectRows: false, and no bindparameters. Use a single parameterized statement or CTE when bind parameters are needed.

Error messages, diagnostics, and catalog metadata are sanitized by default. SQL text frompg_stat_statements, function definitions, RLS predicates, check constraints, index definitions, and column defaults are redacted unless they are intentionally returned as user data. Data execution, query/performance, schema, index, constraint, user/permission, trigger, comment, function, RLS, migration, and diagnostic tools reject unknown input fields so misspelled or unintended parameters fail before connection resolution.

Denied security-boundary requests emit one structured stderr line prefixed with[MCP Audit]. Audit events include sanitized fields such astoolName,reason,securityMode,risk, and whether per-tool connection strings were present; they do not log raw SQL, full request payloads, or connection-string passwords. SetPOSTGRES_MCP_AUDIT_FILE,--audit-file, orauditFileto append the same sanitized audit events to a JSONL file.

Filesystem tools such as table export/import require a workspace directory and only read or write.jsonand.csvfiles inside it:

npx @henkey/postgres-mcp-server \ --security-mode admin \ --allow-destructive \ --workspace-dir /path/to/mcp-workspace \ --connection-string "postgresql://admin_user:pass@host:5432/db"

18 powerful toolsorganized into three categories:

- 🔄 Consolidation: 34 original tools consolidated into 8 intelligent meta-tools
- 🔧 Specialized: 6 tools kept separate for complex operations
- 🆕 Enhancement: 4 brand new tools (not in original 46)

- Schema Management- Tables, columns, ENUMs, constraints
- User & Permissions- Create users, grant/revoke permissions
- Query Performance- EXPLAIN plans, slow queries, statistics
- Index Management- Create, analyze, optimize indexes
- Functions- Create, modify, manage stored functions
- Triggers- Database trigger management
- Constraints- Foreign keys, checks, unique constraints
- Row-Level Security- RLS policies and management

Brand new capabilities not available in the original 46 tools

- Execute Query- SELECT operations with count/exists support
- Execute Mutation- INSERT/UPDATE/DELETE/UPSERT operations
- Execute SQL- Arbitrary SQL execution with transaction support
- Comments Management- Comprehensive comment management for all database objects

- Database Analysis- Performance and configuration analysis
- Debug Database- Troubleshoot connection, performance, locks
- Data Export- JSON/CSV data export
- Data Import- JSON/CSV data import
- Copy Between Databases- Cross-database data transfer
- Real-time Monitoring- Live database metrics and alerts

// Analyze database performance { "analysisType": "performance", "schema": "public" } // Create a table with constraints { "operation": "create_table", "tableName": "users", "columns": [ { "name": "id", "type": "SERIAL PRIMARY KEY" }, { "name": "email", "type": "VARCHAR(255) UNIQUE NOT NULL" } ] } // Query data with parameters { "operation": "select", "query": "SELECT  FROM users WHERE created_at > $1", "parameters": ["2024-01-01"], "limit": 100 } // Select results are always bounded: default limit 100, max 1000. // Insert new data { "operation": "insert", "table": "users", "data": {"name": "John Doe", "email": "john@example.com"}, "returning": "", "maxReturningRows": 100 } // Mutation RETURNING output is capped in the response: default 100, max 1000. // Find slow queries { "operation": "get_slow_queries", "limit": 5, "minDuration": 100 } // Execute a parameterized SELECT query { "operation": "select", "query": "SELECT  FROM users WHERE id = $1", "parameters": [1] } // Perform an INSERT mutation { "operation": "insert", "table": "products", "data": {"name": "New Product", "price": 99.99}, "returning": "id", "maxReturningRows": 100 } // Perform an UPDATE mutation with a structured WHERE predicate { "operation": "update", "table": "products", "data": {"price": 89.99}, "where": {"id": 123}, "returning": ["id", "price"] } // Manage database object comments { "operation": "set", "objectType": "table", "objectName": "users", "comment": "Main user account information table" }

📋Complete Tool Schema Reference- All 18 tool parameters & examples in one place

For additional information, see thedocs/folder:

- 🔐 Security Posture- Sandboxing, approvals, audit events, and deployment posture
- PostgreSQL Role Templates- Least-privilege database roles for each deployment profile
- 📖 Usage Guide- Hardened usage patterns and examples
- 🛠️ Development Guide- Setup and release checklist
- ⚙️ Technical Details- Security architecture and implementation constraints
- 👨‍💻 Developer Reference- Contribution rules for tool and policy changes
- 📋 Documentation Index- Complete documentation overview

✅34→8 meta-tools- Intelligent consolidation for better AI discovery
✅Multiple operations per tool- Unified schemas with operation parameters
✅Smart parameter validation- Clear error messages and type safety

✅Complete CRUD operations- INSERT/UPDATE/DELETE/UPSERT with parameterized queries
✅Flexible querying- SELECT with count/exists support and bounded safety limits ✅Arbitrary SQL execution- Transaction support for complex operations

✅Controlled connection- CLI args or env vars by default; per-tool connection strings require opt-in ✅Security focused- Read-only default mode, centralized policy checks, structured mutation predicates ✅Robust architecture- Connection pooling, comprehensive error handling

The PostgreSQL MCP Server is fully Docker-compatible and can be used in production environments. The image uses a multi-stage build, installs only production dependencies in the runtime stage, and runs as the non-rootnodeuser.

# Build locally docker build -t postgres-mcp-server . # Or pull from Docker Hub docker pull henkey/postgres-mcp:latest

# Basic usage (using Docker Hub image) docker run -i --rm \ -e POSTGRES_CONNECTION_STRING="postgresql://user:password@host:port/database" \ henkey/postgres-mcp:latest # Or with locally built image docker run -i --rm \ -e POSTGRES_CONNECTION_STRING="postgresql://user:password@host:port/database" \ postgres-mcp-server # With tools configuration docker run -i --rm \ -e POSTGRES_CONNECTION_STRING="postgresql://user:password@host:port/database" \ -e POSTGRES_TOOLS_CONFIG="/app/config/tools.json" \ -v /path/to/config:/app/config \ postgres-mcp-server

version: '3.8' services: postgres-mcp: build: . environment: - POSTGRES_CONNECTION_STRING=postgresql://user:password@postgres:5432/database depends_on: - postgres stdin_open: true tty: true postgres: image: postgres:15 environment: - POSTGRES_DB=database - POSTGRES_USER=user - POSTGRES_PASSWORD=password ports: - "5432:5432"

For use with MCP clients like Cursor or Claude Desktop:

{ "mcpServers": { "postgresql-mcp": { "command": "docker", "args": [ "run", "-i", "--rm", "-e", "POSTGRES_CONNECTION_STRING", "henkey/postgres-mcp:latest" ], "env": { "POSTGRES_CONNECTION_STRING": "postgresql://user:password@host:port/database" } } } }

- Node.js ≥ 18.0.0 (for local development)
- Docker (for containerized deployment)
- PostgreSQL server access
- Valid connection credentials
- Fork the repository
- Create a feature branch
- Commit your changes
- Create a Pull Request

SeeDevelopment Guidefor detailed setup instructions.

AGPLv3 License - seeLICENSEfile for details.

Official Airtable MCP server and skills for working with bases, records, workflows, and business operations from AI agents.

MCP Server For Apache Doris, an MPP-based real-time data warehouse.

Official MCP Server from Atlan which enables you to bring the power of metadata to your AI tools

Query Onchain data, like ERC20 tokens, transaction history, smart contract state.

Read and write access to your Baserow tables.

Introspect and query your apps deployed to Convex.

Interact with the data stored in Couchbase clusters using natural language.

Maritime intelligence for tracking vessels, analysing ports, and exploring ship data.

Execute Query

Perform SELECT operations with count/exists support.

Execute Mutation

Perform INSERT, UPDATE, DELETE, or UPSERT operations.

Execute SQL

Execute arbitrary SQL with transaction support.

Comments Management

Manage comments for all database objects.

Database Analysis

Analyze database performance and configuration.

Debug Database

Troubleshoot database connection, performance, and locks.

Data Export

Export data in JSON or CSV format.

Data Import

Import data from JSON or CSV files.

Copy Between Databases

Transfer data across different databases.

Real-time Monitoring

Monitor live database metrics and receive alerts.

Claude Desktop / Cursor

Paste into your MCP client config file to install this server.

{
    "mcpServers": {
        "postgresql mcp server": {
            "env": {},
            "args": [
                "@henkey/postgres-mcp-server",
                "--connection-string",
                "postgresql://user:password@host:port/database"
            ],
            "command": "npx"
        }
    }
}

Linux

{
    "env": [],
    "args": [
        "@henkey/postgres-mcp-server",
        "--connection-string",
        "postgresql://user:password@host:port/database"
    ],
    "command": "npx"
}

Macos

{
    "env": [],
    "args": [
        "@henkey/postgres-mcp-server",
        "--connection-string",
        "postgresql://user:password@host:port/database"
    ],
    "command": "npx"
}

Windows

{
    "env": [],
    "args": [
        "/c",
        "npx",
        "@henkey/postgres-mcp-server",
        "--connection-string",
        "postgresql://user:password@host:port/database"
    ],
    "command": "cmd"
}

PostgreSQL MCP Server

smithery badge

<a href="https://glama.ai/mcp/servers/@HenkDz/postgresql-mcp-server">
PostgreSQL Server MCP server
</a>

A Model Context Protocol (MCP) server that provides comprehensive PostgreSQL database management capabilities for AI assistants.

🚀 What's New: This server has been completely redesigned from 46 individual tools to 18 intelligent tools through consolidation (34→8 meta-tools) and enhancement (+4 new tools), providing better AI discovery while adding powerful data manipulation and comment management capabilities.

Breaking Changes in 2.0.0

Version 2.0.0 introduces security boundaries that intentionally change default behavior from the 1.x line:

- The server starts in readonly mode. Mutations, DDL, role administration, filesystem import/export, and arbitrary SQL require --security-mode write, --security-mode admin, or --security-mode unsafe as appropriate.
- Destructive operations such as drops, resets, broad role grants, and arbitrary SQL require --allow-destructive.
- Per-tool connectionString, sourceConnectionString, and targetConnectionString arguments are disabled by default. Use server-level --connection-string or POSTGRES_CONNECTION_STRING, or explicitly opt in with --allow-tool-connection-string.
- Legacy string where clauses are rejected for mutation, index, export, and copy filters. Use structured where predicates, or rawWhere only with --security-mode unsafe --allow-destructive.
- Multi-statement pg_execute_sql calls must use transactional: true, expectRows: false, and no bind parameters.
- Tool schemas reject unknown fields, so misspelled or unintended inputs fail before connection resolution.
- User and target identifiers are restricted to safe simple PostgreSQL identifiers.

For the non-breaking security patch line, use @henkey/postgres-mcp-server@1.0.7.

Quick Start

Prerequisites

- Node.js ≥18.0.0 - Access to a PostgreSQL server - (Optional) An MCP client like Cursor or Claude for AI integration

Install MCP Server

Option 1: npm (Recommended)

```bash
No reviews yet — be the first

Sign in to leave a review

Use Google, GitHub, or an email account so ratings stay tied to real people.

Email sign in

No reviews posted yet.

Install

Add to Claude Desktop or Cursor

{
    "mcpServers": {
        "postgresql mcp server": {
            "env": {},
            "args": [
                "@henkey/postgres-mcp-server",
                "--connection-string",
                "postgresql://user:password@host:port/database"
            ],
            "command": "npx"
        }
    }
}

More like this

Similar MCP servers you might find useful

Browse category

Time

A Model Context Protocol server that provides time and timezone conversion capabilities. It enables LLMs to get current time information and perform timezone…

88.2k
Productivity

Git

A Model Context Protocol (MCP) server that provides tools for LLMs to read, search, and manipulate Git repositories. It enables automated repository…

88.1k
Developer Tools