PostgreSQL Explorer

by gldc

11 stars

Not rated

GitHub

About

Enables natural language interaction with PostgreSQL databases through tools for schema exploration, table inspection, relationship discovery, and SQL query execution

Details

Author: gldc
Repository: gldc/mcp-postgres
GitHub stars: 11
License: MIT License
Categories: AI, Design, Developer Tools, Search, Infrastructure, Database, Frontend

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:

Download and install Highlight from highlightai.com/download
Navigate to the plugins tab and select "Add Custom Plugin"
Configure the plugin with the settings below
Plugin Name PostgreSQL Explorer

Command (node, npx, python, etc.) /path/to/.venv/bin/python
Arguments
- Argument 1 /path/to/postgres_server.py
Environment
- POSTGRES_CONNECTION_STRING postgresql://username:password@host:5432/database?ssl=true
Please refer to the README for specific instructions on how to obtain API keys or other required environment variables.
Enable "Start Automatically" if you want the plugin to start when Highlight launches

Setting up with Cursor

Open Cursor's settings
Navigate to the MCP section
Click "Add new Global MCP server"
Copy and paste the configuration below based on your operating system

JSON

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

JSON

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

JSON

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

Setting up with Claude Desktop

Open Claude Desktop's settings
Go to the Integrations tab
Click "Add MCP Server"
Use the configuration below based on your operating system

Note: Make sure Claude Desktop is running before attempting to connect.

JSON

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

JSON

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

JSON

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

Setting up with Windsurf

Open Windsurf's preferences
Go to the Integrations section
Click "Add MCP Integration"
Use the configuration below based on your operating system

JSON

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

JSON

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

JSON

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

Setting up with Cline

Open Cline's settings
Navigate to the Integrations section
Click "Add MCP Server"
Copy and paste the configuration below based on your operating system

Note: Make sure to restart Cline after adding the configuration.

JSON

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

JSON

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

JSON

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

Setting up with Goose

Open Goose's preferences
Go to the Integrations tab
Select "MCP Servers"
Click "Add New Server"
Use the configuration below based on your operating system

JSON

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

JSON

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

JSON

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

From the repository

- Google OAuth 2.0: Secure user authentication
- Session Management: Token-based sessions with expiration
- User Isolation: Each user's database connections are separate
- Read-only Mode: Optional query restrictions
- Query Timeouts: Prevent runaway queries
- Health Monitoring: Built-in health checks and metrics

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"

To install PostgreSQL MCP Server for Claude Desktop automatically via Smithery:

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

1. Clone this repository:

git clone <repository-url>

cd mcp-postgres

2. Create and activate a virtual environment (recommended):

python -m venv .venv

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

3. Install dependencies:

pip install -r requirements.txt

1. Start the server in OAuth mode:

   python postgres_server.py --oauth-only --transport streamable-http --port 8000

2. Get authentication info (via MCP client like Claude):

   User: "Show me information about the database server"

   Claude: [Calls auth_info tool, provides Google OAuth login URL]

3. Complete OAuth flow:
- Visit the provided login URL
- Authenticate with Google
- Receive session token

4. Configure database connection:
- Browser: visit http://localhost:8000/connection (uses your session cookie)
- API:

     curl -X POST http://localhost:8000/connection/set \

       -H "Authorization: Bearer YOUR_SESSION_TOKEN" \

       -H "Content-Type: application/json" \

       -d '{"connection_string": "postgresql://user:pass@host:port/db"}'

5. Use database tools - all operations now work with your authenticated connection

export POSTGRES_CONNECTION_STRING="postgresql://username:password@host:port/database"
python postgres_server.py

- auth_info: Get authentication status and login instructions
- server_info: Server configuration and capabilities

// 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-railway": {
      "transport": {
        "type": "http",
        "url": "https://your-app-name.railway.app"
      }
    }
  }
}

- POSTGRES_CONNECTION_STRING: Direct database connection (traditional mode)
- MCP_TRANSPORT: stdio|sse|streamable-http (default: stdio)
- MCP_HOST: Host for HTTP transports (default: 127.0.0.1)
- MCP_PORT: Port for HTTP transports (default: 8000)

- GOOGLE_CLIENT_ID: Google OAuth client ID (required for OAuth mode)
- GOOGLE_CLIENT_SECRET: Google OAuth client secret (required for OAuth mode)
- SECRET_KEY: Session encryption key (32+ characters, required for OAuth mode)
- REDIRECT_URI: OAuth redirect URI (auto-configured for Railway)

- DATABASE_URL: Managed PostgreSQL connection string
- PORT: Server port (Railway-provided)
- RAILWAY_PUBLIC_DOMAIN: Public domain for OAuth redirects
- RAILWAY_ENVIRONMENT: Deployment environment

Build the image:

docker build -t mcp-postgres .

Traditional mode:

docker run \

  -e POSTGRES_CONNECTION_STRING="postgresql://username:password@host:5432/database" \

  -p 8000:8000 \

  mcp-postgres

OAuth mode:

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

- OAuth Secrets: Keep Google OAuth credentials secure, never commit to git
- Session Keys: Use strong SECRET_KEY (32+ characters minimum)
- Token Rotation: Regularly rotate OAuth credentials
- User Isolation: Each authenticated user has separate database connections

- Environment Variables: Never expose secrets in code or logs
- HTTPS: Use HTTPS for all OAuth flows (Railway provides this automatically)
- Access Controls: Implement proper database user permissions
- Connection Pooling: Use connection pooling for better resource management

- Any cloud provider supporting Python applications
- Container-based deployment with Docker
- Manual OAuth configuration
- Custom domain and SSL setup

1. Create a .venv and install runtime deps: pip install -r requirements.txt
2. (Optional) Install test deps: pip install -r dev-requirements.txt
3. Set up OAuth credentials for testing (see OAUTH_SETUP.md)
4. Run tests: pytest -q

`query`

Execute SQL queries against the database.

`list_schemas`

List all available schemas.

`list_tables`

List all tables in a specific schema.

`describe_table`

Get detailed information about a table's structure.

`get_foreign_keys`

Get foreign key relationships for a table.

`find_relationships`

Discover both explicit and implied relationships for a table.

`db_identity`

Show current db/user/host/port, search_path, and version.

`auth_info`

Get authentication status and login instructions.

`server_info`

Server configuration and capabilities.

`run_query`

Execute with typed input (`sql`, `parameters`, `row_limit`, `format: 'markdown'|'json'`).

`run_query_json`

Execute and return JSON-serializable rows.

`list_schemas_json`

List schemas with filters (`include_system`, `include_temp`, `require_usage`, `row_limit`).

`list_schemas_json_page`

Paginated listing with filters and `name_like` pattern.

`list_tables_json`

List tables within a schema with filters (name pattern, case sensitivity, table_types, row_limit).

`list_tables_json_page`

Paginated tables listing with filters.

Claude Desktop / Cursor

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

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

Linux

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

Macos

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

Windows

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

PostgreSQL MCP Server with OAuth

A PostgreSQL MCP server implementation with OAuth authentication support using the Model Context Protocol (MCP) Python SDK. This server enables AI agents to interact with PostgreSQL databases through a standardized interface, with secure multi-user authentication and cloud deployment capabilities.

✨ New Features

Features

Core Database Operations

- List database schemas with advanced filtering and pagination - List tables within schemas with pattern matching - Describe table structures and constraints - Discover table relationships (explicit foreign keys + implied relationships) - Execute SQL queries with safety controls - Typed tools with JSON/markdown output - Optional table resources and guidance prompts

Authentication & Security

- Google OAuth 2.0: Secure user authentication - Session Management: Token-based sessions with expiration - User Isolation: Each user's database connections are separate - Read-only Mode: Optional query restrictions - Query Timeouts: Prevent runaway queries - Health Monitoring: Built-in health checks and metrics

Deployment Options

- Local Development: Direct database connections - OAuth Mode: Multi-user authentication with personal database connections - Railway Cloud: One-click cloud deployment with managed PostgreSQL - Docker: Containerized deployment

🚀 Quick Start

Option 1: Traditional Mode (Direct Connection)

# 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"

Option 2: OAuth Mode (Multi-user)

# 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

Option 3: Railway Cloud Deployment

# 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)

📚 Documentation

- OAUTH_SETUP.md - Complete OAuth setup guide
- RAILWAY_DEPLOYMENT.md - Railway cloud deployment guide

Installation

Installing via Smithery

To install PostgreSQL MCP Server for Claude Desktop automatically via Smithery:

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

Manual Installation

1. Clone this repository:

git clone <repository-url>
cd mcp-postgres

2. Create and activate a virtual environment (recommended):

python -m venv .venv

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

3. Install dependencies:

pip install -r requirements.txt

Usage

Authentication Flow (OAuth Mode)

1. Start the server in OAuth mode:

   python postgres_server.py --oauth-only --transport streamable-http --port 8000

2. Get authentication info (via MCP client like Claude):

   User: "Show me information about the database server"

   Claude: [Calls auth_info tool, provides Google OAuth login URL]

3. Complete OAuth flow:
- Visit the provided login URL
- Authenticate with Google
- Receive session token

4. Configure database connection:
- Browser: visit http://localhost:8000/connection (uses your session cookie)
- API:

     curl -X POST http://localhost:8000/connection/set \

       -H "Authorization: Bearer YOUR_SESSION_TOKEN" \

       -H "Content-Type: application/json" \

       -d '{"connection_string": "postgresql://user:pass@host:port/db"}'

5. Use database tools - all operations now work with your authenticated connection

Direct Mode (Traditional)

# 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

Available Tools

Core Database Tools

- query: Execute SQL queries against the database - list_schemas: List all available schemas - list_tables: List all tables in a specific schema - describe_table: Get detailed information about a table's structure - get_foreign_keys: Get foreign key relationships for a table - find_relationships: Discover both explicit and implied relationships for a table - db_identity: Show current db/user/host/port, search_path, and version

Authentication Tools (OAuth Mode)

- auth_info: Get authentication status and login instructions - server_info: Server configuration and capabilities

Typed Tools (Preferred)

- run_query(input): Execute with typed input (sql, parameters, row_limit, format: 'markdown'|'json') - run_query_json(input): Execute and return JSON-serializable rows - list_schemas_json(input): List schemas with filters (include_system, include_temp, require_usage, row_limit) - list_schemas_json_page(input): Paginated listing with filters and name_like pattern - list_tables_json(input): List tables within a schema with filters (name pattern, case sensitivity, table_types, row_limit) - list_tables_json_page(input): Paginated tables listing with filters

Example Tool Usage

// 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

}

Resources & Prompts

…

No reviews yet — be the first

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

Email sign in

No reviews posted yet.

About

Details

Jump to

Setting up with Highlight

From the repository

query

list_schemas

list_tables

describe_table

get_foreign_keys

find_relationships

db_identity

auth_info

server_info

run_query

run_query_json

list_schemas_json

list_schemas_json_page

list_tables_json

list_tables_json_page

Claude Desktop / Cursor

Linux

Macos

Windows

PostgreSQL MCP Server with OAuth

✨ New Features

Features

Core Database Operations

Authentication & Security

Deployment Options

🚀 Quick Start

Option 1: Traditional Mode (Direct Connection)

Or pass connection string as argument

Option 2: OAuth Mode (Multi-user)

Run in OAuth mode

Option 3: Railway Cloud Deployment

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)

📚 Documentation

Installation

Installing via Smithery

Manual Installation

Usage

Authentication Flow (OAuth Mode)

Direct Mode (Traditional)

Or set the connection string via environment variable:

Or pass it using the --conn flag:

Optional: Run over HTTP transports

Streamable HTTP (recommended for streaming tool outputs)

SSE transport (server-sent events) mounted at /sse and /messages/

Available Tools

Core Database Tools

Authentication Tools (OAuth Mode)

Typed Tools (Preferred)

Example Tool Usage

Resources & Prompts

Install

More like this

Sentry

Github

Git

GitLab

Context 7

1Panel

`query`

`list_schemas`

`list_tables`

`describe_table`

`get_foreign_keys`

`find_relationships`

`db_identity`

`auth_info`

`server_info`

`run_query`

`run_query_json`

`list_schemas_json`

`list_schemas_json_page`

`list_tables_json`

`list_tables_json_page`

6. Deploy (railway.toml uses unified launcher: `python start.py`)