PostgreSQL Data Access Module (MCP)

This repository serves as a foundational template for deploying an MCP server specialized for relational database management, specifically PostgreSQL. It emphasizes simplicity and ease of customization, featuring clean code organization alongside comprehensive MCP documentation references for rapid onboarding.

Understanding MCP

In essence: It's the standard mechanism for enabling Large Language Models (LLMs) to utilize external utilities.

Model Context Protocol (MCP) defines a structured approach for LLMs to interface with external resources. Key components include:

• Tools: Callable functions allowing the LLM to perform actions (e.g., running a database query).
• Resources: Attachments or data references made available during conversational context (akin to file uploads).
• Prompts: Predefined instruction sets used to generate reliable, consistent inputs for the LLM.

Capabilities Summary

This PostgreSQL integration module exposes the following MCP constructs:

1. Service Functions (Tools)

   • query_executor: Executes arbitrary SQL statements against the connected database.
   • connection_verifier: Confirms the operational status of the database linkage.

2. Data References (Resources)

   • db://schema/tables: Provides a manifest of all relational sets within the current schema.
   • db://schema/tables/{table_name}: Details the structure (columns, types) of a specified relation.
   • db://schema/full_details: Outputs comprehensive structural metadata for the entire database context.

3. Instruction Templates (Prompts)

   • Templates optimized for generating precise SQL commands.
   • Builders for complex analytical queries.
   • Templates derived from reference implementations within this repository.

System Requirements

• Python version 3.8 or newer.
• The uv package manager (for modern Python dependency handling).
• Node Package Execute (npx), typically bundled with Node.js.
• A reachable PostgreSQL instance.

Initial Configuration Guide

1. Environment Setup & Dependency Installation: bash # Isolate dependencies using uv uv venv

   Activate the isolated environment

   source .venv/bin/activate # Use .venv\Scripts\activate on Windows

   Install required packages

   uv pip install -r requirements.txt

2. Server Initialization via MCP Inspector: bash # Substitute placeholders with actual PostgreSQL connection parameters npx @modelcontextprotocol/inspector uv --directory . run postgres -e DSN=postgresql://user:pass@host:port/dbname -e SCHEMA=public

   Note: If this is the first execution, npx will request permission to install necessary packages; confirm with 'y'.

   Upon successful launch, the MCP Inspector GUI should open in your default browser, typically accessible at http://localhost:5173. Verify the status message in the console.

3. Interacting with the Inspector Interface:

   • If no console errors are shown, click the 'Establish Connection' control.
   • Navigate the 'Service Functions', 'Data References', and 'Instruction Templates' tabs.
   • Experiment by selecting exposed commands or inputting resource paths to test server feedback.

4. Reference Documentation

   Developer guide for MCP server construction: https://modelcontextprotocol.io/quickstart/server

   Inspector tool details: https://modelcontextprotocol.io/docs/tools/inspector

Integrating with Your LLM Agent

To allow an AI assistant to utilize this service, define a server configuration block:

{ "mcpServers": { "postgres_access": { "command": "/path/to/uv", "args": [ "--directory", "/path/to/pg-data-connector-mcp", "run", "postgres" ], "env": { "DSN": "postgresql://user:password@local:5432/my_data", "SCHEMA": "public" } } } }

Alternatively, use the provided setup script to generate this manifest automatically:

bash

Ensure executability

chmod +x generate_mcp_config.sh

Execute the configuration script

./generate_mcp_config.sh

Provide your PostgreSQL Connection String (DSN) and desired schema when prompted.

Once configured, natural language instructions map directly to database actions: - "List all database tables." - "Fetch the top five most recent customer records." - "Aggregate the count of entries grouped by geographical region."

Claude Desktop offers native MCP support, fully enabling all features out-of-the-box.

Optional: Sample Data Environment

If you lack an existing database or face connection difficulties, a test harness is included:

bash

Make setup script executable

chmod +x example-db/create-db.sh

Deploy the self-contained PostgreSQL instance with sample data

./example-db/create-db.sh

Use the following connection details when running the inspector against this temporary container:

bash npx @modelcontextprotocol/inspector uv --directory . run postgres -e DSN=postgresql://postgres:postgres@localhost:5432/user_database -e SCHEMA=public

Future Expansion

To integrate novel MCP capabilities:

1. Establish a new directory under /src (e.g., /src/inventory_mcp).
2. Develop the new server implementation, mirroring the PostgreSQL example structure.

3. Register the new entry in pyproject.toml:

   toml [project.scripts] postgres = "src.postgres:main" inventory = "src.inventory_mcp:main" # New entry

   Then launch with:

   bash npx @modelcontextprotocol/inspector uv --directory . run inventory

Documentation Resources

• Embedded documentation for LLM instruction design.
• Reference architecture: https://modelcontextprotocol.io/tutorials/building-mcp-with-llms

Safety Considerations

This project is intended as an experimental development scaffold. While basic validation exists (e.g., checking if queries begin with 'SELECT'), comprehensive security measures against malicious input (like SQL Injection) are minimal. Deployment in production environments, especially those handling sensitive data or operating for external clients, is strongly discouraged without substantial security hardening.

Licensing

MIT License