gem-hunter

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.

• 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

• 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

• 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)

• 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

• execute_query

  • Execute read-only SQL queries (SELECT statements)
  • Input: sql (string): The SQL query to execute
  • All queries are executed within a READ ONLY transaction
  • Results include execution time metrics and field information

• execute_dml_ddl_dcl_tcl

  • Execute data modification operations (INSERT, UPDATE, DELETE) or schema changes (CREATE, ALTER, DROP)
  • Input: sql (string): The SQL statement to execute
  • Automatically wrapped in a transaction with configurable timeout
  • Returns a transaction ID for explicit commit
  • Important safety feature: The conversation will end after execution, allowing the user to review the results before deciding to commit or rollback

• execute_maintenance

  • Execute maintenance commands like VACUUM, ANALYZE, or CREATE DATABASE outside of transactions
  • Input: sql (string): The SQL statement to execute - must be VACUUM, ANALYZE, or CREATE DATABASE
  • Returns a result object with execution time metrics

• execute_commit

  • Explicitly commit a transaction by its ID
  • Input: transaction_id (string): ID of the transaction to commit
  • Safely handles cleanup after commit or rollback
  • Permanently applies changes to the database

• execute_rollback

  • Explicitly rollback a transaction by its ID
  • Input: transaction_id (string): ID of the transaction to rollback
  • Safely discards all changes and cleans up resources
  • Useful when reviewing changes and deciding not to apply them

• list_tables

  • Get a comprehensive list of all tables in the database
  • Includes column count and table descriptions
  • No input parameters required

• 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

The server provides enhanced schema information for database tables:

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

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

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.

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)

  • Increase this if your transactions need more time
  • Transactions exceeding this time will be automatically rolled back for safety

• MAX_CONCURRENT_TRANSACTIONS: Maximum concurrent transactions (default: 10)

  • Lower this number for more conservative operation
  • Higher values allow more simultaneous write operations

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

  • Monitors and automatically rolls back abandoned transactions
  • Rarely needs to be disabled

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

  • Limits how long any single SQL statement can run
  • Important safety feature to prevent runaway queries

• PG_MAX_CONNECTIONS: Maximum PostgreSQL connections (default: 20)

  • Important to stay within your database's connection limits

• MONITOR_INTERVAL_MS: How often to check for stuck transactions (default: 5000)

  • Usually doesn't need adjustment

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

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"

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

Claude: Executes a read-only query automatically Shows you the results

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.):
   • Claude executes the SQL in a transaction and ends the conversation
   • You review the results
   • In a new conversation, you respond with "Yes" to commit or "No" to rollback
   • Claude Desktop shows you exactly what will be changed and asks for permission
   • You click "Allow once" to permit the specific operation
   • Claude executes the operation and returns results

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

When connecting Claude to your database with write access:

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

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

   • Never select "Always allow" for database modifications
   • Take time to review the SQL carefully

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

   • Consider using a database copy/backup for initial testing

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

   • Avoid using a superuser or admin account
   • Grant table-specific permissions when possible

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

   • Check table names
   • Verify column names and data
   • Confirm WHERE clauses are appropriate
   • Look for proper transaction handling

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"

This MCP server is licensed under the MIT License.

Created by Syahiid Nur Kamil (@syahiidkamil)

Copyright © 2024 Syahiid Nur Kamil. All rights reserved.