MCP Postgres Full Access

🚀 PostgreSQL Full Translation Instructions

This MCP (Machine Learning Platform) server is a custom tool designed to connect to a PostgreSQL database. It supports both read and write operations and offers enhanced database interaction capabilities.

✨ Features

1. Enhanced Database Access

• Read and Write Support: Allows users to execute SELECT, INSERT, UPDATE, and DELETE operations.
• Permission Control: Ensures a secure connection to the PostgreSQL database by creating dedicated database users and granting appropriate permissions. For 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;
-- Only grant necessary permissions

2. Enhanced Database Interaction

• Transaction Support: Users can manage transactions using the commit and rollback commands.

# Commit a transaction
commit

# Roll back a transaction
rollback

• Index Information: Displays the indexes and their types for a specified table.

\di+ table_name

• Foreign Key Information: Shows the foreign key associations for a specific table.

\d+ table_name

• Row Estimation: Provides statistics on the size and number of rows in a table.

# Table size and row count statistics
\dt+

• Table Description: Displays detailed information about a table and its columns, including data types and constraints.

\d table_name

3. Security Precautions

• Always use the "Allow Once" option and carefully review SQL statements before each write operation.
  • Check table names, confirm column names, and ensure data accuracy.
  • Ensure that the WHERE clause is appropriate to avoid accidental deletions or updates.

💻 Usage Examples

Example 1: Create a New Table and Add Data

User: "I need a new product table with id, name, price, and inventory." Claude: Analyzing your database requirements, the following SQL statement is generated:

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 products table. Do you want to add some sample data?" User: "Yes, please add five sample products." Claude: Generate an INSERT statement and prompt for approval.

Example 2: Secure Workflow

For any write operation, adopt a two-step verification process:

1. Analyze the request: Claude prepares the SQL based on your requirements.
2. Automatically execute read operations: Only SELECT queries are run directly.
3. Handle write operations:
   • Claude executes the SQL within a transaction and ends the conversation.
   • The user reviews the results.
   • In a new conversation, the user replies "Yes" to commit or "No" to roll back.

📚 Documentation

Security Precautions

• Create a dedicated database user: Avoid using the superadmin or administrator account.
• Connect to a test environment: When using it for the first time, connect to a test or backup database.
• Regularly backup: Ensure that a database backup strategy is configured before extensive use.
• Limit user permissions: Only grant necessary operation permissions.

Docker Usage Instructions

Build and Run the Container

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

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

Note for macOS: Use host.docker.internal to connect to the host network:

docker run -i --rm mcp-postgres-full-access "postgresql://username:password@host.docker.internal:5432/database"

📄 License

This MCP server is distributed under the MIT License.

👨‍💻 Creator

Created by Syahiid Nur Kamil ((@syahiidkamil).

Copyright © 2024 Syahiid Nur Kamil. All rights reserved.