Installation and Configuration

📋 System Requirements

PostgreSQL

System Requirements

🚀 Installation Methods

Method 1: Docker (Recommended)

Pull the Image

docker pull neverinfamous/postgres-mcp:latest

Run with Environment Variable

docker run -i --rm \
 -e DATABASE_URI="postgresql://username:password@localhost:5432/dbname" \
 neverinfamous/postgres-mcp:latest \
 --access-mode=restricted

Using Docker Compose

version: '3.8'
services:
 postgres-mcp:
 image: neverinfamous/postgres-mcp:latest
 stdin_open: true
 environment:
 - DATABASE_URI=postgresql://user:pass@postgres:5432/db
 command: ["--access-mode=restricted"]
 depends_on:
 - postgres
 postgres:
 image: postgres:16
 environment:
 - POSTGRES_PASSWORD=password
 - POSTGRES_DB=mydb
 volumes:
 - pgdata:/var/lib/postgresql/data
volumes:
 pgdata:

docker-compose up -d

Method 2: Python/pip Installation

Install from PyPI

pip install postgres-mcp

Verify Installation

postgres-mcp --version

Run the Server

export DATABASE_URI="postgresql://user:pass@localhost:5432/db"
postgres-mcp --access-mode=restricted

Method 3: From Source

Clone Repository

git clone https://github.com/neverinfamous/postgres-mcp.git
cd postgres-mcp

Install Dependencies (using uv)

# Install uv if not installed
curl -LsSf https://astral.sh/uv/install.sh | sh
# Sync dependencies
uv sync

Run Tests

uv run pytest -v

Run Server

export DATABASE_URI="postgresql://user:pass@localhost:5432/db"
uv run python -m postgres_mcp.server --access-mode=restricted

🔧 Configuration

Environment Variables

DATABASE_URI (Required)

# Basic format
export DATABASE_URI="postgresql://username:password@hostname:port/database"
# With SSL
export DATABASE_URI="postgresql://user:pass@host:5432/db?sslmode=require"
# With connection pooling
export DATABASE_URI="postgresql://user:pass@host:5432/db?pool_size=20"
# Cloud providers
# AWS RDS
export DATABASE_URI="postgresql://admin:pass@mydb.region.rds.amazonaws.com:5432/db"
# Google Cloud SQL
export DATABASE_URI="postgresql://user:pass@/db?host=/cloudsql/project:region:instance"
# Azure Database
export DATABASE_URI="postgresql://user@server:pass@server.postgres.database.azure.com:5432/db"

Access Mode

# Restricted mode (production) - read-only
--access-mode=restricted
# Unrestricted mode (development) - full access
--access-mode=unrestricted

MCP Client Configuration

Claude Desktop (Quick Reference)

{
 "mcpServers": {
 "postgres-mcp": {
 "command": "docker",
 "args": [
 "run", "-i", "--rm",
 "-e", "DATABASE_URI=postgresql://user:pass@host:5432/db",
 "neverinfamous/postgres-mcp:latest",
 "--access-mode=restricted"
 ]
 }
 }
}

Cursor IDE (Quick Reference)

{
 "mcpServers": {
 "postgres-mcp": {
 "command": "postgres-mcp",
 "args": ["--access-mode=restricted"],
 "env": {
 "DATABASE_URI": "postgresql://user:pass@host:5432/db"
 }
 }
 }
}

🔐 Security Configuration

Database User Setup

Create Read-Only User (Restricted Mode)

-- Create user
CREATE USER mcp_readonly WITH PASSWORD 'secure_password';
-- Grant connection
GRANT CONNECT ON DATABASE mydb TO mcp_readonly;
-- Grant schema usage
GRANT USAGE ON SCHEMA public TO mcp_readonly;
-- Grant SELECT on all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
-- Grant SELECT on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
 GRANT SELECT ON TABLES TO mcp_readonly;
-- Grant EXECUTE on functions (for extensions)
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO mcp_readonly;

Create Full Access User (Unrestricted Mode)

-- Create user
CREATE USER mcp_admin WITH PASSWORD 'secure_password';
-- Grant full privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO mcp_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO mcp_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO mcp_admin;

SSL/TLS Configuration

Enable SSL in PostgreSQL

ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'
ssl_ca_file = '/path/to/root.crt'

Connect with SSL

export DATABASE_URI="postgresql://user:pass@host:5432/db?sslmode=require"
# With certificate verification
export DATABASE_URI="postgresql://user:pass@host:5432/db?sslmode=verify-full&sslrootcert=/path/to/root.crt"

🧩 Extension Installation

Required Extensions

-- Query tracking (required for performance tools)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Text similarity (required for text tools)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Fuzzy matching (required for text tools)
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

shared_preload_libraries = 'pg_stat_statements'

Optional Extensions

-- Hypothetical indexes (for performance testing)
CREATE EXTENSION IF NOT EXISTS hypopg;
-- Vector similarity (for AI/ML applications)
CREATE EXTENSION IF NOT EXISTS vector;
-- Geospatial operations
CREATE EXTENSION IF NOT EXISTS postgis;

✅ Verification

Test Connection

# Using MCP client
list_schemas()
# Expected: List of database schemas

Check Database Health

analyze_db_health(health_type="all")
# Expected: Comprehensive health report

Verify Extensions

get_top_queries(sort_by="total_time", limit=5)
# Expected: Top 5 queries (requires pg_stat_statements)

Test Security Mode

# In restricted mode, this should fail:
execute_sql(sql="DROP TABLE test")
# Expected: "Operation not allowed in restricted mode"

🐳 Advanced Docker Configuration

Custom Network

# Create network
docker network create postgres-mcp-net
# Run PostgreSQL
docker run -d \
 --name postgres \
 --network postgres-mcp-net \
 -e POSTGRES_PASSWORD=password \
 postgres:16
# Run MCP server
docker run -i --rm \
 --network postgres-mcp-net \
 -e DATABASE_URI="postgresql://postgres:password@postgres:5432/postgres" \
 neverinfamous/postgres-mcp:latest

Volume Mounting

# Mount configuration files
docker run -i --rm \
 -v $(pwd)/config:/app/config \
 -e DATABASE_URI="postgresql://user:pass@host:5432/db" \
 neverinfamous/postgres-mcp:latest

Health Checks

version: '3.8'
services:
 postgres-mcp:
 image: neverinfamous/postgres-mcp:latest
 healthcheck:
 test: ["CMD", "pg_isready", "-h", "postgres", "-U", "user"]
 interval: 30s
 timeout: 10s
 retries: 3

🔍 Troubleshooting Installation

Docker Issues

Container Exits Immediately

# Check logs
docker logs <container_id>
# Ensure -i flag is used
docker run -i --rm ...
# Verify DATABASE_URI is set
docker exec <container_id> env | grep DATABASE_URI

Can't Connect to Database

# From host
docker run -i --rm \
 -e DATABASE_URI="postgresql://user:pass@host.docker.internal:5432/db" \
 neverinfamous/postgres-mcp:latest
# Or use host networking
docker run -i --rm --network=host \
 -e DATABASE_URI="postgresql://user:pass@localhost:5432/db" \
 neverinfamous/postgres-mcp:latest

Python Installation Issues

Module Not Found

# Ensure correct Python version
python --version # Should be 3.10+
# Install in virtual environment
python -m venv venv
source venv/bin/activate # Linux/Mac
# or
venv\Scripts\activate # Windows
pip install postgres-mcp

Permission Errors

# Use --user flag
pip install --user postgres-mcp
# Or use virtual environment (recommended)

Connection Issues

Authentication Failed

-- Check pg_hba.conf
# Allow password authentication
host all all 0.0.0.0/0 scram-sha-256

Database Not Found

# List databases
psql -h localhost -U postgres -l
# Create database if needed
createdb -h localhost -U postgres mydb

📊 Performance Tuning

PostgreSQL Configuration

# postgresql.conf
# Memory
shared_buffers = 4GB # 25% of RAM
work_mem = 64MB # Per query operation
maintenance_work_mem = 1GB # For maintenance operations
# Connections
max_connections = 100 # Adjust based on workload
# Query Performance
effective_cache_size = 12GB # 75% of RAM
random_page_cost = 1.1 # For SSDs
# Logging (for pg_stat_statements)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Connection Pooling

export DATABASE_URI="postgresql://user:pass@host:5432/db?pool_size=20&max_overflow=10"

📚 Next Steps

🔗 Additional Resources