PostgreSQL MCP Server

A production-ready Model Context Protocol (MCP) server for PostgreSQL, enabling AI assistants to securely interact with PostgreSQL databases.

Features

• 30+ MCP tools for database operations

• Security guardrails with SQL injection detection

• Role-based access control (Viewer, Analyst, Developer, DBA, Admin)

• Read-only mode for safe query execution

• Transaction management with begin/commit/rollback

• AI-powered tools for SQL generation, optimization, and analysis

• Structured audit logging for compliance

• Async execution with connection pooling

Related MCP server: PostgreSQL MCP Server

Quick Start

Prerequisites

• Python 3.12+

• PostgreSQL 14+

• pip

Installation

# Clone the repository
git clone https://github.com/SaadRiaz99/postgres-mcp-server.git
cd postgres-mcp-server

# Create virtual environment
python -m venv .venv
.venv\Scripts\activate   # Windows
# source .venv/bin/activate  # Linux/Mac

# Install dependencies
pip install -r requirements.txt

# Copy and configure environment
copy .env.example .env
# Edit .env with your database credentials

Configure .env

DATABASE_URL=postgresql+asyncpg://user:password@localhost:5432/your_db
DATABASE_HOST=localhost
DATABASE_PORT=5432
DATABASE_NAME=your_db
DATABASE_USER=your_user
DATABASE_PASSWORD=your_password
READ_ONLY_MODE=true
ENABLE_AI_SQL=true
ENABLE_AUDIT_LOG=true

Run the Server

python -m app.server

Run Tests

pytest -v

MCP Client Configuration

Claude Desktop

Add to claude_desktop_config.json:

{
  "mcpServers": {
    "postgres": {
      "command": "python",
      "args": ["-m", "app.server"],
      "cwd": "/path/to/postgres-mcp-server",
      "env": {
        "DATABASE_HOST": "localhost",
        "DATABASE_PORT": "5432",
        "DATABASE_NAME": "your_db",
        "DATABASE_USER": "your_user",
        "DATABASE_PASSWORD": "your_password",
        "READ_ONLY_MODE": "true"
      }
    }
  }
}

Cursor / VS Code

{
  "mcpServers": {
    "postgres": {
      "command": "python",
      "args": ["-m", "app.server"],
      "cwd": "/path/to/postgres-mcp-server"
    }
  }
}

Fork and use it

1. Click Fork on this repository.

2. Clone your fork (git clone https://github.com/YOUR_USERNAME/postgres-mcp-server.git).

3. Follow Installation above. On Linux/macOS use cp .env.example .env instead of copy.

4. Set DATABASE_HOST, DATABASE_PORT, DATABASE_NAME, DATABASE_USER, and DATABASE_PASSWORD in .env. The asyncpg pool uses these separate fields; changing DATABASE_URL alone does not configure that pool.

5. Use a dedicated PostgreSQL role with only the privileges you need. Start with read-only database grants and READ_ONLY_MODE=true. Existing write/admin tools remain exposed; the application flag and role metadata are not a substitute for database-level permissions or authenticated authorization.

6. Configure your MCP client to launch the virtual environment's Python with -m app.server and this repository as its working directory. If the client does not support cwd, launch through a script that changes directory first.

7. Try: “Find columns containing email in public”, “Show index usage”, or “Which sessions are blocking queries?”

These six new inspection tools do not require a paid model API key. An AI client may have its own model costs. Keep .env private and use a test database first.

New inspection and diagnostic tools

schema defaults to public; limit defaults to 100 and accepts 1–1000. Results include success, data, count, limit, and duration_ms. Invalid input and database errors return structured error objects. No new tool modifies data or cancels sessions; schema/search/table values are bound parameters. Results are capped (not paginated); narrow the schema or table to reduce results.

Catalog visibility depends on database grants. Empty results can mean no matching objects or insufficient visibility. Multi-event triggers appear once per event. Table grants are not a complete effective-permission audit. Statistics are estimates/counters since collection/reset; zero index scans alone do not mean an index should be removed. Session details can be null for restricted roles. query_age_seconds is query age, not exact lock-wait duration.

References: PostgreSQL statistics and blocking PID functions.

Run the focused tests with:

python -m pytest tests/test_inspection_tools.py --no-cov

Available Tools

Database Information

Schema Inspection

Query Execution

Transactions

Admin Tools

AI Tools

Security

Guardrails

The server automatically blocks dangerous operations:

• DROP DATABASE / DROP ROLE

• ALTER SYSTEM

• COPY ... PROGRAM

• TRUNCATE (requires confirmation)

• pg_sleep(), pg_read_file(), pg_write_file()

• Multi-statement queries

• SQL injection patterns

Read-Only Mode

When READ_ONLY_MODE=true, only SELECT and EXPLAIN are allowed.

Role-Based Access

Docker

# Build
docker build -t postgres-mcp .

# Run
docker run -p 8000:8000 \
  -e DATABASE_URL=postgresql+asyncpg://user:pass@host:5432/db \
  postgres-mcp

Architecture

app/
├── server.py          # FastMCP server with all tool registrations
├── config.py          # Pydantic Settings configuration
├── database.py        # Async database connection manager
├── guards/
│   ├── sql_guard.py   # SQL validation and risk classification
│   └── permissions.py # Role-based access control
├── tools/
│   ├── schema_tools.py     # Database/schema inspection
│   ├── query_tools.py      # CRUD query execution
│   ├── transaction_tools.py # Transaction management
│   ├── admin_tools.py      # Monitoring and admin
│   └── ai_tools.py         # AI-powered SQL analysis
├── services/
│   └── audit.py       # Audit logging
├── models/
│   └── schemas.py     # Pydantic input/output models
└── utils/
    ├── logging.py     # Structured logging
    └── helpers.py     # Utilities (pagination, injection detection)

Development

# Install dev dependencies
pip install -r requirements.txt

# Run linter
ruff check app/ tests/

# Run formatter
black app/ tests/

# Run type checker
mypy app/

# Run tests with coverage
pytest --cov=app --cov-report=term-missing

Future Enhancements

• pgvector integration for vector similarity search

• Semantic search across database schemas

• RAG workflows for database documentation

• Multi-database support (MySQL, SQLite, SQL Server)

• Streaming for large result sets

• Prepared statement caching

• Query result caching with TTL

• Webhook notifications for schema changes

• GraphQL endpoint alongside MCP

• Dashboard UI for monitoring

License

MIT