startino/unrestricted-postgres-mcp

Unrestricted PostgreSQL MCP Server

Fork of mcp-postgres-full-access by Syahiid Nur Kamil - Enhanced with modern tooling, transaction recovery tools, and improved developer experience.

A powerful Model Context Protocol (MCP) server that provides full read-write access to PostgreSQL databases. Unlike read-only MCP servers, this implementation enables Large Language Models to safely query, modify, and manage database content with comprehensive transaction management and recovery capabilities.

✨ Key Features

🔐 Safe Full Database Access

• Read Operations: Execute SELECT queries with automatic read-only transaction protection
• Write Operations: Safely perform INSERT, UPDATE, DELETE with explicit transaction management
• Schema Management: Create, alter, and drop database objects with DDL operations
• Maintenance Commands: Execute VACUUM, ANALYZE, and CREATE DATABASE operations

🛡️ Advanced Transaction Management

• Explicit Commit/Rollback: Two-step process requiring user confirmation for all changes
• Transaction Recovery: Tools to recover from aborted transaction states
• Timeout Protection: Automatic rollback of abandoned transactions
• Session Reset: Complete session reset capabilities for stuck connections
• Connection Status: Real-time monitoring of database connection state

📊 Rich Schema Information

• Comprehensive Metadata: Detailed column information, data types, constraints
• Relationship Mapping: Primary keys, foreign keys, and index information
• Performance Insights: Row count estimates and table statistics
• Documentation Support: Table and column descriptions when available

🔧 Developer Experience

• Modern Build System: Powered by Vite for fast development and building
• TypeScript Support: Full type safety and IntelliSense support
• Hot Reload: Instant development server with vite-node
• Comprehensive Tooling: 10+ specialized tools for database operations

🚀 Quick Start

Prerequisites

• Node.js 18.0.0 or higher
• PostgreSQL 12.0 or higher
• Claude Desktop (for MCP integration)

Installation

# Install globally
npm install -g unrestricted-postgres-mcp

# Or use with npx (recommended)
npx unrestricted-postgres-mcp postgresql://user:password@localhost:5432/database

Claude Desktop Configuration

Add to your claude_desktop_config.json:

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

🛠️ Available Tools

Query & Analysis Tools

Data Modification Tools

Transaction Management & Recovery

🔄 Workflow Examples

Safe Data Modification Workflow

1. Analyze: Use execute_query to understand current data
2. Modify: Use execute_dml_ddl_dcl_tcl to make changes
3. Review: Transaction is created but not committed
4. Decide: Use execute_commit or execute_rollback based on review

Recovery from Stuck Transactions

1. Diagnose: Use get_connection_status to check state
2. List: Use list_transactions to see active transactions
3. Recover: Use force_rollback to clear aborted state
4. Reset: If needed, use reset_session for complete reset

Schema Exploration

1. Discover: Use list_tables to see available tables
2. Examine: Use describe_table for detailed schema information
3. Query: Use execute_query to explore data patterns

⚙️ Configuration

Environment Variables

Security Best Practices

1. Create Dedicated Database User:

   CREATE USER mcp_user WITH PASSWORD 'secure_password';
   GRANT SELECT, INSERT, UPDATE, DELETE ON specific_tables TO mcp_user;
   

2. Use "Allow Once" for All Operations:

   • Never select "Always allow" for database modifications
   • Review all SQL operations before approval

3. Test with Non-Production Data:

   • Use a development database for initial testing
   • Implement regular backups before extensive use

🏗️ Development

Prerequisites

• Node.js 18+
• pnpm (recommended) or npm
• PostgreSQL database

Setup

# Clone the repository
git clone https://github.com/your-username/unrestricted-postgres-mcp.git
cd unrestricted-postgres-mcp

# Install dependencies
pnpm install

# Create environment file
cp .env.example .env
# Edit .env with your database connection details

# Start development server
pnpm run dev

# Build for production
pnpm run build

Available Scripts

Project Structure

src/
├── index.ts                 # Main server entry point
├── lib/
│   ├── config.ts           # Configuration management
│   ├── tool-handlers.ts    # Tool implementation functions
│   ├── transaction-manager.ts # Transaction lifecycle management
│   ├── types.ts            # TypeScript type definitions
│   └── utils.ts            # Utility functions
└── types.ts                # Additional type definitions

🔍 Troubleshooting

Common Issues

"Current transaction is aborted" Error:

1. Use get_connection_status to diagnose
2. Use force_rollback to clear aborted state
3. If still stuck, use reset_session

Connection Timeouts:

1. Check PG_STATEMENT_TIMEOUT_MS setting
2. Increase TRANSACTION_TIMEOUT_MS if needed
3. Verify database connection limits

Permission Errors:

1. Verify database user permissions
2. Check table-specific access rights
3. Ensure user has necessary schema access

📊 Comparison with Official MCP Servers

🤝 Contributing

We welcome contributions! Please see our for details.

Quick Contribution Guide

1. Fork the repository
2. Create a feature branch: git checkout -b feature/amazing-feature
3. Make your changes
4. Run tests: pnpm run type-check && pnpm run build
5. Commit changes: git commit -m 'Add amazing feature'
6. Push to branch: git push origin feature/amazing-feature
7. Open a Pull Request

📄 License

This project is licensed under the Apache License Version 2.0 - see the file for details.

👥 Credits

• Original Creator: Syahiid Nur Kamil - mcp-postgres-full-access
• Current Maintainer: Jonas Lindberg - Enhanced version with modern tooling and recovery capabilities

🙏 Acknowledgments

• Model Context Protocol for the MCP specification
• Anthropic for Claude and MCP integration
• PostgreSQL for the excellent database system
• Vite for the modern build tooling

⚠️ Important: This server provides full database access. Always review operations before committing changes and use appropriate database user permissions for security.