PostgreSQL MCP 服务器

# Run with direct database connection (original behavior)
export POSTGRES_CONNECTION_STRING="postgresql://user:pass@host:5432/db"
python postgres_server.py

# Or pass connection string as argument
python postgres_server.py --conn "postgresql://user:pass@host:5432/db"

# Set up OAuth credentials (see OAUTH_SETUP.md for details)
export GOOGLE_CLIENT_ID="your_client_id.apps.googleusercontent.com"
export GOOGLE_CLIENT_SECRET="your_client_secret"
export SECRET_KEY="your_32_character_secret_key"

# Run in OAuth mode
python postgres_server.py --oauth-only --transport streamable-http --port 8000

# One-click deployment to Railway (see RAILWAY_DEPLOYMENT.md)
# 1. Push code to GitHub
# 2. Connect Railway to your repository
# 3. Create TWO services from this repo (same project):
#    - Service A (MCP server): default settings (SERVICE_ROLE defaults to mcp)
#    - Service B (OAuth companion): set SERVICE_ROLE=oauth; optionally set Environment=oauth
# 4. Add PostgreSQL to the project (Railway sets DATABASE_URL)
# 5. Set GOOGLE_CLIENT_ID / GOOGLE_CLIENT_SECRET / SECRET_KEY at project level
# 6. Deploy (railway.toml uses unified launcher: `python start.py`)

npx -y @smithery/cli install @gldc/mcp-postgres --client claude

git clone <repository-url>
cd mcp-postgres

python -m venv .venv
source .venv/bin/activate  # On Windows, use: .venv\Scripts\activate

pip install -r requirements.txt

# Without a connection string (server starts, DB‑backed tools will return a friendly error)
python postgres_server.py

# Or set the connection string via environment variable:
export POSTGRES_CONNECTION_STRING="postgresql://username:password@host:port/database"
python postgres_server.py

# Or pass it using the --conn flag:
python postgres_server.py --conn "postgresql://username:password@host:port/database"

# Optional: Run over HTTP transports
# Streamable HTTP (recommended for streaming tool outputs)
python postgres_server.py --transport streamable-http --host 0.0.0.0 --port 8000

# SSE transport (server-sent events) mounted at /sse and /messages/
python postgres_server.py --transport sse --host 0.0.0.0 --port 8000 --mount /mcp

// run_query (markdown)
{
  "sql": "SELECT * FROM information_schema.tables WHERE table_schema = %s",
  "parameters": ["public"],
  "row_limit": 50,
  "format": "markdown"
}

// run_query_json
{
  "sql": "SELECT now() as ts",
  "row_limit": 1
}

// List schemas with filters
{
  "include_system": false,
  "include_temp": false,
  "require_usage": true,
  "row_limit": 10000
}

// Paginated list with pattern filter
{
  "include_system": false,
  "include_temp": false,
  "require_usage": true,
  "page_size": 200,
  "cursor": null,
  "name_like": "sales_*",
  "case_sensitive": false
}

{
  "mcpServers": {
    "postgres": {
      "command": "/path/to/.venv/bin/python",
      "args": ["/path/to/postgres_server.py"],
      "env": {
        "POSTGRES_CONNECTION_STRING": "postgresql://username:password@host:5432/database?ssl=true"
      }
    }
  }
}

{
  "mcpServers": {
    "postgres-oauth": {
      "command": "/path/to/.venv/bin/python",
      "args": [
        "/path/to/postgres_server.py",
        "--oauth-only",
        "--transport", "streamable-http",
        "--port", "8000"
      ],
      "env": {
        "GOOGLE_CLIENT_ID": "your_client_id.apps.googleusercontent.com",
        "GOOGLE_CLIENT_SECRET": "your_client_secret",
        "SECRET_KEY": "your_32_character_secret_key"
      }
    }
  }
}

{
  "mcpServers": {
    "postgres-railway": {
      "transport": {
        "type": "http",
        "url": "https://your-app-name.railway.app"
      }
    }
  }
}

docker build -t mcp-postgres .

docker run \
  -e POSTGRES_CONNECTION_STRING="postgresql://username:password@host:5432/database" \
  -p 8000:8000 \
  mcp-postgres

docker run \
  -e GOOGLE_CLIENT_ID="your_client_id" \
  -e GOOGLE_CLIENT_SECRET="your_client_secret" \
  -e SECRET_KEY="your_32_char_secret" \
  -p 8000:8000 \
  mcp-postgres

Unified launcher (optional, same image):
```bash
# MCP
docker run -e SERVICE_ROLE=mcp -p 8000:8000 mcp-postgres python start.py

# OAuth companion
docker run -e SERVICE_ROLE=oauth -e GOOGLE_CLIENT_ID=... -e GOOGLE_CLIENT_SECRET=... -e SECRET_KEY=... -p 8000:8000 mcp-postgres python start.py

python postgres_server.py --transport streamable-http --host 0.0.0.0 --port 8000

curl http://localhost:8000/health
# Expected response:
{
  "status": "healthy",
  "service": "PostgreSQL MCP OAuth Companion",
  "oauth_enabled": true,
  "environment": "development"
}

import asyncio
from mcp.client import streamable_http
from mcp.client.session import ClientSession

async def main():
    url = "http://localhost:8000/mcp"
    async with streamable_http.streamablehttp_client(url) as (read, write, _get_session_id):
        session = ClientSession(read, write)
        init = await session.initialize()
        print("protocol:", init.protocolVersion)

        # List tools
        tools = await session.list_tools()
        print("tools:", [t.name for t in tools.tools])

        # Call typed tool: run_query_json
        result = await session.call_tool(
            "run_query_json",
            {"input": {"sql": "SELECT 1 AS n", "row_limit": 1}},
        )
        print("result:", result.content[0].text if result.content else result.structuredContent)

if __name__ == "__main__":
    asyncio.run(main())