AliQambari/PostgreSQL-MCP-Server

PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides secure, read-only access to PostgreSQL databases for AI assistants like Claude Desktop.

Features

• Database Schema Resources: Access table structures, relationships, and metadata
• Read-only SQL Tools: Execute SELECT queries safely with built-in security controls
• Data Analysis Prompts: Pre-built templates for common database analysis tasks
• Performance Monitoring: Built-in queries for database performance analysis
• Data Quality Reports: Comprehensive data quality assessment tools

Quick Start with UV

1. Install Dependencies

# Install uv if you haven't already
pip install uv

# Install project dependencies
uv sync

2. Environment Setup

Create a .env file in the project root:

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=your_database_name
POSTGRES_USER=your_username
POSTGRES_PASSWORD=your_password
POSTGRES_SSL=prefer

3. Run the Server

# Run as HTTP server
uv run python -m fastmcp.server server:mcp --port 8000

# Or with specific database parameters
uv run python server.py --host localhost --port 5432 --database mydb --user postgres

MCP Client Configuration

Claude Desktop

Add this configuration to your Claude Desktop config file:

Location:

• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
• Windows: %APPDATA%\Claude\claude_desktop_config.json

{
  "mcpServers": {
    "fastapi-mcp": {
      "command": "npx",
      "args": [
        "mcp-remote",
        "http://127.0.0.1:8000/mcp",
        "8080"
      ]
    }
  }
}

Cursor IDE

For Cursor or other MCP clients:

{
  "mcpServers": {
    "postgresql-mcp": {
      "command": "npx",
      "args": ["mcp-remote", "http://127.0.0.1:8000/mcp", "8080"]
    }
  }
}

Remote Server Configuration

For remote servers with HTTPS:

{
  "mcpServers": {
    "postgresql-mcp": {
      "command": "npx",
      "args": ["mcp-remote", "https://your-server.com:8000/mcp", "8080"]
    }
  }
}

Available Resources

• schema://tables - List all database tables
• schema://table/{table_name} - Detailed table schema
• schema://relationships - Foreign key relationships

Available Tools

• execute_read_query(sql, limit) - Execute SELECT queries safely
• describe_table(table_name) - Get table structure and sample data
• get_table_stats(table_name) - Statistical analysis of table data

Available Prompts

• analyze_table_data(table_name) - Comprehensive table analysis template
• performance_analysis() - Database performance monitoring queries
• data_quality_report() - Data quality assessment framework

Security Features

• Read-only access: Only SELECT statements allowed
• Query validation: Blocks dangerous SQL keywords
• Result limits: Configurable row limits (max 1000)
• Connection pooling: Efficient database connections
• Error handling: Graceful error responses

Troubleshooting

Connection Issues

1. Verify database credentials in .env file
2. Check PostgreSQL server is running
3. Ensure network connectivity to database host
4. Verify SSL settings match your database configuration

Permission Issues

Make sure your database user has SELECT permissions on the tables you want to query:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user;

UV Issues

# Update uv
pip install --upgrade uv

# Clear uv cache if needed
uv cache clean

# Reinstall dependencies
rm -rf .venv
uv sync

Development

Running Tests

uv run python test_client.py

Adding New Features

1. Add new tools/resources to server.py
2. Update this README with new functionality
3. Test with your MCP client

Requirements

• Python 3.13+
• PostgreSQL database
• UV package manager
• MCP-compatible client (Claude Desktop, Cursor, etc.)

Dependencies

• asyncpg - PostgreSQL async driver
• fastmcp - MCP server framework
• mcp - Model Context Protocol library
• python-dotenv - Environment variable management

License

This project is open source. See the license file for details.