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

Quick Start

Prerequisites

• Python 3.12+
• PostgreSQL 14+
• pip

Installation

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

# 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=false
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",
      "env": {
        "DATABASE_URL": "postgresql+asyncpg://user:pass@localhost:5432/db"
      }
    }
  }
}

Cursor / VS Code

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

Available Tools

Database Information

Tool	Description
list_databases	List all accessible databases
current_database	Get current database name
list_schemas	List all schemas
list_tables	List tables in a schema
list_views	List views in a schema
list_functions	List functions in a schema
list_indexes	List indexes in a schema
list_sequences	List sequences in a schema
list_extensions	List installed extensions

Schema Inspection

Tool	Description
describe_table	Full table description
describe_column	Column metadata
foreign_keys	Foreign key relationships
primary_keys	Primary key columns
unique_constraints	Unique constraints
check_constraints	Check constraints
table_size	Table size with indexes
database_size	Total database size

Query Execution

Tool	Description
execute_select	Structured SELECT queries
execute_insert	Insert rows
execute_update	Update rows
execute_delete	Delete rows
execute_query	Raw SQL execution

Transactions

Tool	Description
begin_transaction	Start a transaction
commit_transaction	Commit changes
rollback_transaction	Undo changes

Admin Tools

Tool	Description
active_connections	List active connections
running_queries	List running queries
locks	List locks and deadlocks
vacuum_status	Vacuum and dead tuple info
analyze_table	Update table statistics
server_version	Get server version
server_settings	Get server settings

AI Tools

Tool	Description
generate_sql	Generate SQL from description
explain_sql	Human-readable query explanation
optimize_sql	Query optimization suggestions
validate_sql	Validate without executing
detect_sql_injection	Security analysis
estimate_query_cost	Cost estimation

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

Role	Access Level
Viewer	Read metadata only
Analyst	SELECT queries + AI tools
Developer	INSERT/UPDATE/DELETE + transactions
DBA	Admin tools (connections, settings)
Admin	Full 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