PostgreSQL Full Access MCP Server

A powerful Model Context Protocol server providing full read-write access to PostgreSQL databases. Unlike the read-only official MCP PostgreSQL server, this enhanced implementation allows Large Language Models (LLMs) to both query and modify database content with proper transaction management and safety controls.

Table of Contents

• Features
  • Full Read-Write Access
  • Rich Schema Information
  • Advanced Safety Controls
• Tools
  • execute_query
  • execute_dml_ddl_dcl_tcl
  • execute_maintenance
  • execute_commit
  • execute_rollback
  • list_tables
  • describe_table
• Resources
• Using with Claude Desktop
  • Claude Desktop Integration
  • Important: Using "Allow Once" for Safety
• Environment Variables
• Using Full Database Access with Claude
• Security Considerations
  • Database User Permissions
  • Best Practices for Safe Usage
• Docker
• License
• Comparison with Official PostgreSQL MCP Server

🌟 Features

Full Read-Write Access

• Safely execute DML operations (INSERT, UPDATE, DELETE)
• Create, alter, and manage database objects with DDL
• Transaction management with explicit commit
• Safety timeouts and automatic rollback protection

Rich Schema Information

• Detailed column metadata (data types, descriptions, max length, nullability)
• Primary key identification
• Foreign key relationships
• Index information with type and uniqueness flags
• Table row count estimates
• Table and column descriptions (when available)

Advanced Safety Controls

• SQL query classification (DQL, DML, DDL, DCL, TCL)
• Enforced read-only execution for safe queries
• All operations run in isolated transactions
• Automatic transaction timeout monitoring
• Configurable safety limits
• Two-step transaction commit process with explicit user confirmation

🔧 Tools

• execute_query

• execute_dml_ddl_dcl_tcl

• execute_maintenance

• execute_commit

• execute_rollback

• list_tables

• describe_table
  • Get detailed information about a specific table structure
  • Input: `table_name` (string): Name of the table to describe
  • Returns complete schema information including primary keys, foreign keys, indexes, and column details

📊 Resources

The server provides enhanced schema information for database tables:

• Table Schemas (`postgres:////schema`)
  • Detailed JSON schema information for each table
  • Includes complete column metadata, primary keys, and constraints
  • Automatically discovered from database metadata

🚀 Using with Claude Desktop

Claude Desktop Integration

To use this server with Claude Desktop, follow these steps:

1. First, ensure you have Node.js installed on your system

2. Install the package using npx or add it to your project

3. Configure Claude Desktop by editing `claude_desktop_config.json` (typically found at `~/Library/Application Support/Claude/` on macOS):

{
  "mcpServers": {
    "postgres-full": {
      "command": "npx",
      "args": [
        "-y",
        "mcp-postgres-full-access",
        "postgresql://username:password@localhost:5432/database"
      ],
      "env": {
        "TRANSACTION_TIMEOUT_MS": "60000",
        "MAX_CONCURRENT_TRANSACTIONS": "5",
        "PG_STATEMENT_TIMEOUT_MS": "30000"
      }
    }
  }
}

4. Replace the database connection string with your actual PostgreSQL connection details

5. Restart Claude Desktop completely

Important: Using "Allow Once" for Safety

When Claude attempts to commit changes to your database, Claude Desktop will prompt you for approval:

Always review the SQL changes carefully before approving them!

Best practices for safety:

• Always click "Allow once" (not "Always allow") for commit operations
• Review the transaction SQL carefully before approving
• Consider using a database user with limited permissions
• Use a testing database if possible when first trying this server

This "Allow once" approach gives you full control to prevent unwanted changes to your database while still enabling Claude to help with data management tasks when needed.

⚙️ Environment Variables

You can customize the server behavior with environment variables in your Claude Desktop config:

"env": {
  "TRANSACTION_TIMEOUT_MS": "60000",
  "MAX_CONCURRENT_TRANSACTIONS": "5"
}

Key environment variables:

• `TRANSACTION_TIMEOUT_MS`: Transaction timeout in milliseconds (default: 15000)

• `MAX_CONCURRENT_TRANSACTIONS`: Maximum concurrent transactions (default: 10)

• `ENABLE_TRANSACTION_MONITOR`: Enable/disable transaction monitor ("true" or "false", default: "true")

• `PG_STATEMENT_TIMEOUT_MS`: SQL query execution timeout in ms (default: 30000)

• `PG_MAX_CONNECTIONS`: Maximum PostgreSQL connections (default: 20)

• `MONITOR_INTERVAL_MS`: How often to check for stuck transactions (default: 5000)
  • Usually doesn't need adjustment

🔄 Using Full Database Access with Claude

This server enables Claude to both read from and write to your PostgreSQL database with your approval. Here are some example conversation flows:

Example: Creating a New Table and Adding Data

You: "I need a new products table with columns for id, name, price, and inventory"

Claude: _Analyzes your database and creates a query_

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

_Claude Desktop will prompt you to approve this operation_

You: _Review and click "Allow once"_

Claude: "I've created the products table. Would you like me to add some sample data?"

You: "Yes, please add 5 sample products"

Claude: _Creates INSERT statements and prompts for approval_

_You review and approve with "Allow once"_

Example: Data Analysis with Safe Queries

You: "What are my top 3 products by price?"

Claude: _Executes a read-only query automatically_

_Shows you the results_

Safety Workflow

The key safety feature is the two-step approach for any operation that modifies your database:

1. Claude analyzes your request and prepares SQL

2. For read-only operations (SELECT), Claude executes automatically

3. For write operations (INSERT, UPDATE, DELETE, CREATE, etc.):

This gives you multiple opportunities to verify changes before they're permanently applied to the database.

⚠️ Security Considerations

When connecting Claude to your database with write access:

Database User Permissions

IMPORTANT: Create a dedicated database user with appropriate permissions:

-- Example of creating 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 specific permissions as needed

Best Practices for Safe Usage

1. Always use "Allow once" to review each write operation

2. Connect to a testing database when first exploring this tool

3. Limit database user permissions to only what's necessary

4. Implement database backups before extensive use

5. Never share sensitive data that shouldn't be exposed to LLMs

6. Verify all SQL operations before approving them

Docker

The server can be easily run in a Docker 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"

For Docker on 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 licensed under the MIT License.

💡 Comparison with Official PostgreSQL MCP Server

Author

Created by Syahiid Nur Kamil (@syahiidkamil)

Copyright © 2024 Syahiid Nur Kamil. All rights reserved.