PostgreSQL Model Context Protocol (MCP) Server

A secure, robust Model Context Protocol (MCP) server written in Python that allows AI models to safely read, write, update, and delete records from a PostgreSQL database.

Project Overview

Purpose & Problem Solved

AI models lack direct access to databases, which hinders their ability to build reports, analyze database structure, and manage records on demand. Traditional integrations can expose databases to major security risks, such as SQL injection, resource exhaustion, or accidental truncation.

This PostgreSQL MCP Server acts as a secure, sandboxed gateway for AI agents. It implements:

• Strict parameterized query enforcement to eliminate SQL injection risks.
• Multi-tier security verification:
  • Transactional Dry-Runs (Small/Medium operations): Runs write operations inside a PostgreSQL BEGIN transaction, captures a sample preview of affected records, and automatically issues a ROLLBACK so zero disk changes persist until explicitly confirmed.
  • Disposable Environment Testing (High-Risk/Large-Scale operations): Tests high-risk DDL (DROP TABLE, TRUNCATE) or large-scale modifications (> 1,000 rows) inside an isolated temporary schema (_mcp_disposable_...), inspects structural outcomes, and automatically drops the disposable environment.
• Row limits preventing memory exhaustion from accidental massive selects.
• Automated pre-deletion backups (CSV and database dumps) with built-in age and count retention policies.
• Error feedback loops and Explain Plan integrations to allow self-correcting query generation.

Tech Stack

• FastMCP: High-level SDK for creating MCP tools, resources, and prompts.
• asyncpg: High-performance, asynchronous PostgreSQL client library.
• sqlglot: SQL parser and transpiler used to inspect ASTs and validate query safety.
• python-dotenv: Configuration management via .env files.

---

Installation & Configuration

Prerequisites

• Python 3.10+
• PostgreSQL database instance
• pg_dump (Optional, fallback Python backup generator will be used if missing)

Installation

1. Clone the repository and navigate to the project directory:

   cd "Postgres MCP"
   

2. Create and activate a virtual environment:

   python3 -m venv venv
   source venv/bin/activate
   

3. Install the dependencies:

   pip install -r requirements.txt
   

Configuration

Create a .env file in the root of the workspace directory. The server loads parameters using both standard PostgreSQL keys and user-friendly aliases:

# Database Connection (Uses URL or individual variables)
DATABASE_URL=postgresql://user:password@host:5432/dbname

# Fallback individual configuration parameters
DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=your_password
DB_NAME=postgres

# SSL / TLS Settings ('disable', 'prefer', 'require', 'verify-ca', 'verify-full')
PGSSLMODE=prefer

# Row limit guardrail for SELECT queries
ROW_LIMIT=500

# Large Scale Operation Threshold (triggers Disposable Environment testing)
LARGE_SCALE_THRESHOLD=1000

# Backup Retention Configurations
BACKUP_DIR=./backups
MAX_BACKUPS=10
MAX_BACKUP_AGE_DAYS=7.0
DB_BACKUP_THRESHOLD_BYTES=10485760 # 10MB

MCP Client Integration (mcp.json)

To register the server with an MCP client (such as Claude Desktop or other AI interfaces), add the server setup details to your client configuration file.

Use absolute paths for the virtual environment's python interpreter and the mcp_server.py script:

{
  "mcpServers": {
    "postgres-mcp": {
      "command": "/Users/dbearsong/Documents/projects/python/Postgres MCP/venv/bin/python",
      "args": [
        "/Users/dbearsong/Documents/projects/python/Postgres MCP/mcp_server.py"
      ],
      "env": {
        "DB_HOST": "192.168.1.89",
        "DB_PORT": "5432",
        "DB_USER": "postgres",
        "DB_PASSWORD": "G382XM284YZ-1281!Cy7127#",
        "DB_NAME": "job_searcher"
      }
    }
  }
}

Note: Passing environment variables inside the "env" key in the client configuration is a secure way to supply database credentials at runtime.

---

Usage Examples

Running the Server

Run the main script to start the server over stdio transport:

python mcp_server.py

Context Injection (Resources)

The server exposes the database schema to the AI via:

• Resource URI: postgres://schema
  • Returns: A complete Markdown outline of all tables, comments, column types, nullability, defaults, check constraints, indexes, and foreign keys.

Tools Exposed to the AI

1. get_schema_info

Lists all database tables and schema-wide foreign key relationships.

2. get_table_details

Retrieves column schemas, CHECK constraints, indexes, and descriptions for a specific table.

• Parameters:
  • table_name (string)

3. execute_read_query

Executes a read-only query. Limits results to ROW_LIMIT (default 500) and returns results in a structured JSON format.

• Parameters:
  • query (string): e.g., "SELECT name, email FROM users WHERE age > $1"
  • parameters (list, optional): e.g., [25]
  • explain (boolean, optional): Prepend EXPLAIN ANALYZE to the query.

4. execute_write_query

Executes write queries (INSERT, UPDATE, DELETE). Requires explicit confirmation.

• Parameters:
  • query (string): e.g., "INSERT INTO users (name, email) VALUES ($1, $2)"
  • parameters (list, optional): e.g., ["Alice", "alice@example.com"]
  • confirm (boolean): Default False. If False, the query is not run and a pending_approval message is returned.

5. delete_records

Deletes rows from a table. Backs up matching records to a timestamped CSV before performing the delete. Rotates backups based on retention settings, and runs a full database backup if the CSV size exceeds the database backup threshold.

• Parameters:
  • table_name (string)
  • where_clause (string): e.g., "id = $1" (Must not be empty or evaluate to 1=1)
  • parameters (list, optional): e.g., [104]
  • confirm (boolean): Default False.

6. export_to_csv

Executes a SELECT query and exports the structured results directly to a CSV file in the backups directory.

• Parameters:
  • query (string)
  • parameters (list, optional)
  • filename (string, optional)

7. backup_database

Runs a full database schema and data backup immediately.

---

Contribution Guidelines

We welcome contributions to enhance security features, support additional dialects, or improve parsing performance.

Project Layout

• [config.py](file:///Users/dbearsong/Documents/projects/python/Postgres%20MCP/config.py): Configuration parser.
• [safety.py](file:///Users/dbearsong/Documents/projects/python/Postgres%20MCP/safety.py): Query AST checks and literal validation.
• [database.py](file:///Users/dbearsong/Documents/projects/python/Postgres%20MCP/database.py): Connection pool and database queries.
• [backup.py](file:///Users/dbearsong/Documents/projects/python/Postgres%20MCP/backup.py): Pre-deletion backups and retention rotation.
• [mcp_server.py](file:///Users/dbearsong/Documents/projects/python/Postgres%20MCP/mcp_server.py): FastMCP API registration.
• [test_server.py](file:///Users/dbearsong/Documents/projects/python/Postgres%20MCP/test_server.py): Integration test suite.

Testing

To run the integration and safety test suite, ensure your .env connection values are set and run:

python test_server.py

Note: The test suite sets up temporary tables (test_mcp_users, test_mcp_logs), executes query tools, attempts SQL injection/destructive actions, validates backups, and cleans up the tables automatically upon completion.