PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides AI assistants with secure access to PostgreSQL databases.

Features

🔒 Secure database access with read-only queries by default
🛠️ Comprehensive database tools for schema exploration and querying
🧠 Intelligent query validation with security and performance analysis
⚡ Real-time optimization suggestions for better query performance
🎯 SQL injection detection and dangerous operation blocking
⚙️ Configurable connection pooling and query limits
🔍 Schema filtering for multi-tenant environments
📝 Detailed logging and query monitoring
🚀 Easy setup with environment variables or config files

Installation

Clone the repository:

git clone <your-repo-url>
cd postgresql-mcp-server

Install dependencies:

pip install -r requirements.txt

Configure your database connection (see Configuration)

Configuration

Environment Variables (Recommended)

Copy .env.example to .env and configure your database:

cp .env.example .env

Edit .env with your database credentials:

# PostgreSQL Database Configuration
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DATABASE=your_database_name
POSTGRES_USERNAME=your_username
POSTGRES_PASSWORD=your_password
POSTGRES_SSL_MODE=prefer
POSTGRES_MIN_CONNECTIONS=1
POSTGRES_MAX_CONNECTIONS=10

# MCP Server Configuration
MCP_NAME=postgresql-mcp-server
MCP_VERSION=1.0.0
MCP_MAX_QUERY_TIME=30
MCP_MAX_ROWS=1000
MCP_ALLOWED_SCHEMAS=
MCP_LOG_LEVEL=INFO
MCP_LOG_QUERIES=true

JSON Configuration (Alternative)

Copy config.example.json to config.json and modify as needed.

Usage

Start the MCP Server

python main.py

Test Database Connection

python main.py --test

Enable Verbose Logging

python main.py --verbose

Demo Query Validation (No Database Required)

python demo_validation.py

This demo script showcases the query validation features without requiring a database connection.

Available Tools

The MCP server provides the following tools for AI assistants with built-in query validation and optimization:

1. `query`

Execute SELECT queries on the database.

Parameters:

sql (required): SQL SELECT query to execute
params (optional): Array of parameters for the query

Example:

{
  "name": "query",
  "arguments": {
    "sql": "SELECT id, name FROM users WHERE active = $1 LIMIT 10",
    "params": ["true"]
  }
}

2. `list_tables`

List all tables in a database schema.

Parameters:

schema (optional): Schema name (default: "public")

Example:

{
  "name": "list_tables",
  "arguments": {
    "schema": "public"
  }
}

3. `describe_table`

Get detailed information about a table's columns and structure.

Parameters:

table_name (required): Name of the table to describe
schema (optional): Schema name (default: "public")

Example:

{
  "name": "describe_table",
  "arguments": {
    "table_name": "users",
    "schema": "public"
  }
}

4. `list_schemas`

List all available database schemas.

Example:

{
  "name": "list_schemas",
  "arguments": {}
}

5. `test_connection`

Test the database connection and get server information.

Example:

{
  "name": "test_connection",
  "arguments": {}
}

6. `validate_query`

Validate and analyze a SQL query for security issues, performance problems, and optimization opportunities.

Parameters:

sql (required): SQL query to validate and analyze
schema (optional): Database schema name for validation context (default: "public")

Example:

{
  "name": "validate_query",
  "arguments": {
    "sql": "SELECT * FROM users WHERE email LIKE '%@gmail.com' ORDER BY created_at",
    "schema": "public"
  }
}

Features:

Security Analysis: Detects SQL injection patterns and dangerous operations
Performance Warnings: Identifies inefficient query patterns
Optimization Suggestions: Recommends improvements for better performance
Complexity Scoring: Rates query complexity on a 1-10 scale
Index Recommendations: Suggests indexes for better performance

Example Response:

Query Analysis Report
==================================================
Valid: ✅ Yes
Complexity: 4/10

⚡ Performance Warnings:
  WARNING: SELECT * can be inefficient
    💡 Specify only needed columns instead of using SELECT *
  WARNING: LIKE with leading wildcard prevents index usage
    💡 Avoid leading wildcards in LIKE patterns or consider full-text search

💡 Optimization Suggestions:
  1. Run EXPLAIN ANALYZE to see the actual execution plan
  2. Consider adding an index on users.email if queries are slow
  3. For ORDER BY with LIMIT, ensure there's an index on the ORDER BY columns

Query Validation & Optimization

The MCP server includes intelligent query analysis that automatically validates every query for security and performance issues.

Real-Time Query Analysis

Automatic validation of all queries before execution
Security threat detection including SQL injection patterns
Performance issue identification for slow query patterns
Optimization suggestions with specific recommendations
Complexity scoring to help understand query resource usage

Security Validation

SQL injection detection using pattern matching
Dangerous function blocking (e.g., pg_read_file, COPY)
Statement type validation (only SELECT allowed)
Comment pattern analysis for potential bypass attempts

Performance Analysis

SELECT * detection with column-specific recommendations
Missing index suggestions based on WHERE/JOIN clauses
Cartesian product warnings for JOINs without conditions
Leading wildcard detection in LIKE patterns
Query complexity scoring (1-10 scale)

Optimization Suggestions

Index recommendations for frequently filtered columns
Query restructuring suggestions for better performance
LIMIT clause recommendations for large result sets
JOIN order optimization for complex queries
EXISTS vs IN recommendations for subqueries

Security Features

Read-Only Queries

By default, only SELECT statements are allowed. This prevents accidental data modification through the MCP server.

Row Limits

All queries are automatically limited to prevent excessive memory usage and long-running queries.

Schema Filtering

You can restrict access to specific database schemas using the MCP_ALLOWED_SCHEMAS configuration.

Connection Pooling

Database connections are managed through a connection pool to ensure efficient resource usage.

Development

Running Tests

pip install pytest pytest-asyncio
pytest tests/

Code Formatting

pip install black
black .

Type Checking

pip install mypy
mypy src/

Configuration Options

Database Configuration

Variable	Description	Default
`POSTGRES_HOST`	PostgreSQL server host	`localhost`
`POSTGRES_PORT`	PostgreSQL server port	`5432`
`POSTGRES_DATABASE`	Database name	Required
`POSTGRES_USERNAME`	Database username	Required
`POSTGRES_PASSWORD`	Database password	Required
`POSTGRES_SSL_MODE`	SSL connection mode	`prefer`
`POSTGRES_MIN_CONNECTIONS`	Minimum pool connections	`1`
`POSTGRES_MAX_CONNECTIONS`	Maximum pool connections	`10`

Server Configuration

Variable	Description	Default
`MCP_NAME`	Server name	`postgresql-mcp-server`
`MCP_VERSION`	Server version	`1.0.0`
`MCP_MAX_QUERY_TIME`	Max query execution time (seconds)	`30`
`MCP_MAX_ROWS`	Maximum rows returned per query	`1000`
`MCP_ALLOWED_SCHEMAS`	Comma-separated list of allowed schemas	All schemas
`MCP_LOG_LEVEL`	Logging level	`INFO`
`MCP_LOG_QUERIES`	Whether to log executed queries	`true`

Troubleshooting

Connection Issues

Verify your database credentials in .env
Ensure PostgreSQL is running and accessible
Check firewall and network connectivity
Test connection: python main.py --test

Permission Issues

Ensure the database user has appropriate SELECT permissions
Check schema access permissions
Verify SSL configuration if required

Performance Issues

Adjust connection pool settings
Implement query optimization
Consider adding row limits to queries
Monitor query execution times

Contributing

Fork the repository
Create a feature branch
Make your changes
Add tests for new functionality
Submit a pull request

License

MIT License - see LICENSE file for details.

This server cannot be installed

security - not tested

license - not found

quality - not tested

How are these scores calculated?

remote-capable server

The server can be hosted and run remotely because it primarily relies on remote services or has no dependency on the local environment.

A Model Context Protocol server that provides AI assistants with secure, read-only access to PostgreSQL databases while offering comprehensive tools for schema exploration, query validation, and performance optimization.

Related MCP Servers

PostgreSQL MCP Server
HenkDz
-
security
A
license
-
quality
A Model Context Protocol server that enables powerful PostgreSQL database management capabilities including analysis, schema management, data migration, and monitoring through natural language interactions.
Last updated -
355
46
TypeScript
AGPL 3.0
PostgreSQL MCP Server
hthuong09
-
security
F
license
-
quality
A Model Context Protocol server that enables interaction with PostgreSQL databases to list tables, retrieve schemas, and execute read-only SQL queries.
Last updated -
16,948
JavaScript
PostgreSQL MCP Server
gldc
-
security
A
license
-
quality
Enables AI agents to interact with PostgreSQL databases through the Model Context Protocol, providing database schema exploration, table structure inspection, and SQL query execution capabilities.
Last updated -
11
Python
MIT License
PostgreSQL MCP Server
1Levick3
-
security
A
license
-
quality
A Model Context Protocol server that enables interaction with PostgreSQL databases for analyzing setups, debugging issues, managing schemas, migrating data, and monitoring performance.
Last updated -
6
TypeScript
MIT License

View all related MCP servers