Skip to main content
Glama

PostgreSQL MCP Server

by abdou-ghonim

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

  1. Clone the repository:

git clone <your-repo-url> cd postgresql-mcp-server
  1. Install dependencies:

pip install -r requirements.txt
  1. 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

  1. Verify your database credentials in .env

  2. Ensure PostgreSQL is running and accessible

  3. Check firewall and network connectivity

  4. Test connection: python main.py --test

Permission Issues

  1. Ensure the database user has appropriate SELECT permissions

  2. Check schema access permissions

  3. Verify SSL configuration if required

Performance Issues

  1. Adjust connection pool settings

  2. Implement query optimization

  3. Consider adding row limits to queries

  4. Monitor query execution times

Contributing

  1. Fork the repository

  2. Create a feature branch

  3. Make your changes

  4. Add tests for new functionality

  5. Submit a pull request

License

MIT License - see LICENSE file for details.

Related MCP Servers

  • A
    security
    A
    license
    A
    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 -
    18
    1,751
    124
    AGPL 3.0
    • Linux
    • Apple
  • -
    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 -
    15
    MIT License
    • Linux
    • Apple
  • -
    security
    A
    license
    -
    quality
    A Model Context Protocol server that provides AI assistants with comprehensive access to SQL databases, enabling schema inspection, query execution, and database operations with enterprise-grade security.
    Last updated -
    1
    2
    MIT License
  • -
    security
    F
    license
    -
    quality
    A Model Context Protocol server providing dual transport (HTTP and Stdio) access to PostgreSQL databases, allowing AI assistants to query databases and fetch schema information through natural language.
    Last updated -
    47
    20

View all related MCP servers

MCP directory API

We provide all the information about MCP servers via our MCP API.

curl -X GET 'https://glama.ai/api/mcp/v1/servers/abdou-ghonim/mcp-postgres'

If you have feedback or need assistance with the MCP directory API, please join our Discord server