PostgreSQL MCP Server

Overview

The PostgreSQL MCP server creates a bridge between AI assistants and your PostgreSQL databases, allowing for safe, read-only access to your data. This enables AI models to help with data analysis, query optimization, and database exploration without risking data modifications.

Installation

You can run the PostgreSQL MCP server using either Docker or NPX. Choose the method that best fits your environment.

Prerequisites

• A running PostgreSQL database
• Docker installed (for Docker method) or Node.js installed (for NPX method)

Installation with Claude Desktop

To use this server with Claude Desktop, add the following configuration to the "mcpServers" section of your claude_desktop_config.json:

Using Docker:

{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": [
        "run",
        "-i",
        "--rm",
        "mcp/postgres",
        "postgresql://host.docker.internal:5432/mydb"
      ]
    }
  }
}

Note for macOS users: When running Docker on macOS, use host.docker.internal instead of localhost if the PostgreSQL server is running on your host machine.

Using NPX:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://localhost:5432/mydb"
      ]
    }
  }
}

Installation with VS Code

For VS Code integration, you can add the configuration to your User Settings (JSON) file or to a .vscode/mcp.json file in your workspace.

Using Docker:

{
  "mcp": {
    "inputs": [
      {
        "type": "promptString",
        "id": "pg_url",
        "description": "PostgreSQL URL (e.g. postgresql://user:pass@host.docker.internal:5432/mydb)"
      }
    ],
    "servers": {
      "postgres": {
        "command": "docker",
        "args": [
          "run",
          "-i",
          "--rm",
          "mcp/postgres",
          "${input:pg_url}"
        ]
      }
    }
  }
}

Using NPX:

{
  "mcp": {
    "inputs": [
      {
        "type": "promptString",
        "id": "pg_url",
        "description": "PostgreSQL URL (e.g. postgresql://user:pass@localhost:5432/mydb)"
      }
    ],
    "servers": {
      "postgres": {
        "command": "npx",
        "args": [
          "-y",
          "@modelcontextprotocol/server-postgres",
          "${input:pg_url}"
        ]
      }
    }
  }
}

Connection URL Format

The PostgreSQL connection URL follows this format:

postgresql://[user[:password]@][host][:port][/database]

Examples:

• postgresql://localhost:5432/mydb (default user, no password)
• postgresql://user:password@localhost:5432/mydb (with credentials)
• postgresql://host.docker.internal:5432/mydb (for Docker on macOS)

Usage

Once configured, the PostgreSQL MCP server provides:

1. Table Schema Information: The server automatically discovers and exposes schema information for all tables in your database, including column names and data types.

2. Read-Only Query Execution: You can execute SQL queries against your database. All queries are executed within a READ ONLY transaction to ensure data safety.

Building from Source

If you prefer to build the Docker image yourself:

docker build -t mcp/postgres -f src/postgres/Dockerfile .

Security Considerations

• The server only allows read-only access to your database
• All queries are executed within READ ONLY transactions
• Consider using a read-only database user for additional security
• Be cautious about exposing sensitive data through this interface