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

Related MCP server: Postgres MCP Server

🚀 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

Typical Usage Pattern

1. Query data to understand current state

2. Execute modifications (automatically committed)

3. Query again to verify changes

Recovery from Stuck Transactions

1. Diagnose: Use list_transactions 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 list_transactions 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 Contributing Guidelines 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 LICENSE 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.