MCP Postgres Full Access Extended

🚀 PostgreSQL Full Access MCP Server

This PostgreSQL Full Access MCP (Machine Learning Platform) Server is a powerful tool that enables full access to PostgreSQL databases, including read and write operations. It offers advanced features such as enhanced schema details, transaction support, index information, foreign key details, estimated row counts, and table descriptions.

✨ Features

Overview

The PostgreSQL Full Access MCP Server is a robust solution that provides comprehensive access to PostgreSQL databases. It supports all database operations, including reading and writing, and offers advanced features for in - depth database management.

Core Features

• Read and Write Access: Supports all database operations such as SELECT, INSERT, UPDATE, and DELETE.
• Enhanced Schema Details: Provides detailed table structure information, including index and foreign key relationships.
• Transaction Support: Allows explicit control of transactions with a timeout mechanism to ensure data consistency.
• Index and Foreign Key Information: Displays index and foreign key details of tables.
• Row Count Estimation: Provides an estimate of the number of records in a table.
• Table Descriptions: Contains detailed description information for each table.

Advantages

Compared to the official PostgreSQL MCP Server, this server offers more comprehensive functionality, supports more advanced operations, and provides more detailed database metadata.

📦 Installation

Running with Docker

# Build the Docker image
docker build -t mcp - postgres - full - access.

# Start the container
docker run -i --rm mcp - postgres - full - access "postgresql://username:password@host:5432/database"

Important Note

• On macOS, use host.docker.internal instead of the hostname to connect to the Docker container's network.

💻 Usage Examples

Creating a New Table and Adding Data

User: "I need to create a new product table with id, name, price, and inventory."

Claude: Analyze the database and generate a query

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    inventory INTEGER DEFAULT 0
);

User: Review and click "Allow Once"

Claude: "I have created the product table. Do you want to add some sample data?"

User: "Yes, please add 5 sample product records."

Claude: Generate an insert statement and request approval User reviews and clicks "Allow Once"

Data Analysis (Read - only Operations)

User: "Please tell me what the top 3 most expensive products are."

Claude: Execute a read - only query and display the results

📚 Documentation

Functional Modules

1. Full PostgreSQL Access:
   • Supports complete SQL queries, including DDL and DML operations.
   • Provides detailed query results and execution plans.
2. Enhanced Schema Information:
   • Displays detailed information such as table structure, indexes, and foreign keys.
   • Supports automatic generation of database documentation.
3. Transaction Control:
   • Allows explicit start and commit of transactions.
   • Sets a timeout mechanism to prevent deadlocks and other concurrency issues.
4. Performance Optimization Tools:
   • Provides query execution plan analysis.
   • Displays table space usage and index statistics.

Installation and Configuration

Quick Installation

# Start the MCP server using Docker
docker run -d --name mcp - server -p 5000:5000 mcr.microsoft.com/mssql - tools:latest

Configuration Options

• Port Mapping: Uses port 5000 by default.
• Environment Variable Configuration:

  docker run -e PGPORT = 5432 -e PGUSER = username -e PGPASSWORD = password ...
  

Usage Tutorial

Creating a New Database

CREATE DATABASE my_database;
\c my_database;  -- Switch to the new database

Managing Users and Permissions

CREATE USER new_user WITH PASSWORD 'securepass';
GRANT ALL PRIVILEGES ON DATABASE my_database TO new_user;

Sample Queries

-- Query all products and their prices
SELECT * FROM products ORDER BY price DESC;

-- Insert a new record
INSERT INTO products (name, price, inventory) VALUES ('New Product', 99.99, 10);

🔧 Technical Details

Database User Permissions

⚠️ Important Note

Use a restricted database user and grant it the minimum necessary permissions.

-- Example: Create a restricted user (adjust as needed)
CREATE USER claude_user WITH PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO claude_user;
GRANT INSERT, UPDATE, DELETE ON TABLE table1, table2 TO claude_user;
-- Grant specific permissions according to requirements

Best Practices

💡 Usage Tip

1. Always use "Allow Once": Carefully review each instruction before performing write operations.

• Never select "Always Allow" unless you have fully verified the code.

2. Connect to a Test Database: When using the server for the first time, connect to a test or backup database.
3. Handle Sensitive Data with Caution: Ensure that sensitive information in the database is properly protected.

Security and Compliance

Data Encryption

• Encryption in Transit: Ensure that all database connections use SSL encryption.
• Encryption at Rest: Encrypt stored data to prevent physical theft.

Audit Logs

• Record all user operations and query history.
• Configure an audit policy to monitor sensitive operations.

Regulatory Compliance

• Ensure compliance with data protection regulations such as GDPR and HIPAA.
• Conduct regular security audits and vulnerability scans.

📚 Frequently Asked Questions

Why is enhanced schema information needed?

• Answer: Enhanced schema information helps in better understanding the database structure, optimizing query performance, and generating documentation.

How to handle large transactions?

• Answer: It is recommended to break long transactions into multiple small transactions and commit them regularly to avoid locking issues.

📄 Summary

The PostgreSQL Full Access MCP Server is a comprehensive tool suitable for database management scenarios that require full control and detailed information. Through Docker containerization, it can be easily integrated into existing environments while ensuring security and performance optimization.