itsalfredakku/postgres-mcp

Postgres MCP Server

MCP server for PostgreSQL database management and operations, built with a sophisticated enterprise-grade architecture.

Quick Setup

1. Installation

npm install
npm run build

2. Claude Desktop Configuration

Add this to your Claude Desktop claude_desktop_config.json:

Windows:

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["C:\\path\\to\\postgres-mcp\\dist\\index.js"],
      "env": {
        "DATABASE_URL": "postgresql://username:password@localhost:5432/dbname"
      }
    }
  }
}

macOS/Linux:

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/path/to/postgres-mcp/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgresql://username:password@localhost:5432/dbname"
      }
    }
  }
}

3. Environment Configuration

Option A: Via Claude Desktop config (recommended)

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/Users/itsalfredakku/McpServers/postgres-mcp/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgresql://postgres:password@localhost:5432/mydb",
        "POOL_MAX": "20",
        "LOG_LEVEL": "info"
      }
    }
  }
}

Option B: Using .env file Create .env in the project root:

DATABASE_URL=postgresql://username:password@localhost:5432/dbname
POOL_MAX=10
LOG_LEVEL=info

Features

• Database Operations: Query, insert, update, delete operations
• Schema Management: Create, alter, drop tables and indexes
• Transaction Management: Begin, commit, rollback transactions
• Connection Management: Advanced connection pooling
• Data Management: Import/export, backup/restore operations
• Monitoring: Performance metrics and query analysis
• Admin Operations: User management, permissions, database administration

Installation

npm install

Configuration Options

Database Connection

# Required - Primary connection string
DATABASE_URL=postgresql://username:password@localhost:5432/dbname

# Alternative - Individual connection parameters
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_USER=postgres
POSTGRES_PASSWORD=your_password
POSTGRES_DATABASE=your_database
POSTGRES_SSL=false

Connection Pool Settings

POOL_MIN=2                    # Minimum connections
POOL_MAX=10                   # Maximum connections  
POOL_IDLE_TIMEOUT=30000       # Idle timeout (ms)
POOL_ACQUIRE_TIMEOUT=60000    # Acquire timeout (ms)

Performance & Caching

CACHE_ENABLED=true            # Enable query result caching
CACHE_TTL=300000             # Cache TTL (ms)
LOG_LEVEL=info               # Logging level (error|warn|info|debug)
SQL_LOGGING=false            # Log SQL queries

Usage

Development

npm run dev

Production

npm run build
npm start

Testing

npm run test
npm run test:queries

Tools

Database Operations

• query - Execute SQL queries with transaction support, explain plans, analysis
• tables - List, create, alter, drop tables with detailed metadata
• schemas - FULLY IMPLEMENTED Create, drop, list schemas and manage permissions
• indexes - FULLY IMPLEMENTED Create, drop, analyze, reindex with usage statistics

Data Management

• data - Insert, update, delete operations with bulk support
• transactions - Begin, commit, rollback with savepoint support

Administration & Security

• admin - FULLY IMPLEMENTED Complete database administration and maintenance
• permissions - Complete user/role/privilege management
• security - SSL, authentication, encryption, auditing
• monitoring - Performance metrics and analysis
• connections - Connection pool management

Schema Management Features ✅

• Schema Operations: Create, drop, list all schemas
• Permission Management: View and manage schema-level permissions
• Owner Management: Set schema ownership during creation
• Conditional Operations: IF EXISTS, IF NOT EXISTS support
• System Schema Filtering: Distinguish between user and system schemas

Index Management Features ✅

• Index Operations: Create, drop, list, reindex indexes
• Performance Analysis: Analyze index usage statistics
• Unused Index Detection: Find indexes that are never used
• Multiple Index Types: Support for btree, hash, gist, gin, brin
• Concurrent Operations: Create and reindex with CONCURRENTLY
• Size Monitoring: Index size tracking and reporting

Database Administration Features ✅

• Database Information: Complete database stats and configuration
• User Management: Create, drop, list users with detailed privileges
• Permission Control: Grant/revoke permissions on tables and schemas
• Maintenance Operations: VACUUM, ANALYZE, REINDEX with options
• System Monitoring: Connection counts, database size, uptime tracking
• Configuration Access: View database settings and parameters

Architecture

The server follows a modular architecture with:

• Configuration Management - Environment and file-based configuration
• Connection Pooling - Advanced PostgreSQL connection management
• Domain APIs - Separated concerns for different database operations
• Validation - Comprehensive parameter validation
• Error Handling - Robust error handling with retries
• Caching - Intelligent caching for performance
• Logging - Structured logging with Winston

Troubleshooting

Common Issues

Connection Refused

# Check if PostgreSQL is running
brew services list | grep postgresql
# or
sudo systemctl status postgresql

# Test connection manually
psql -h localhost -p 5432 -U postgres -d your_database

Permission Denied

-- Grant necessary permissions
GRANT CONNECT ON DATABASE your_database TO your_user;
GRANT USAGE ON SCHEMA public TO your_user;
GRANT CREATE ON SCHEMA public TO your_user;

MCP Server Not Found

• Ensure the path in claude_desktop_config.json is absolute
• Verify npm run build completed successfully
• Check that dist/index.js exists

Debug Mode

Set environment variables for detailed logging:

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/path/to/postgres-mcp/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgresql://user:pass@localhost:5432/db",
        "LOG_LEVEL": "debug",
        "SQL_LOGGING": "true"
      }
    }
  }
}

Database Permissions Setup

Full Admin Access

For complete database management capabilities, ensure your PostgreSQL user has appropriate privileges:

-- Connect as superuser (postgres)
psql -U postgres

-- Create a dedicated MCP user with admin privileges
CREATE USER mcp_admin WITH PASSWORD 'secure_password';
ALTER USER mcp_admin SUPERUSER;
ALTER USER mcp_admin CREATEDB;
ALTER USER mcp_admin CREATEROLE;
ALTER USER mcp_admin REPLICATION;

-- Or grant specific privileges without superuser
CREATE USER mcp_user WITH PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE your_database TO mcp_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO mcp_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO mcp_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO mcp_user;

-- Grant schema usage and creation
GRANT USAGE, CREATE ON SCHEMA public TO mcp_user;

-- Allow user management (requires elevated privileges)
ALTER USER mcp_user CREATEROLE;

Using MCP Permission Tools

Once connected, you can use the MCP server to manage permissions:

// List all users and their privileges
await mcpServer.callTool('permissions', { operation: 'list_users' });

// Create a new user
await mcpServer.callTool('permissions', { 
  operation: 'create_user', 
  username: 'newuser', 
  password: 'password123',
  attributes: { createdb: true, login: true }
});

// Grant all privileges to a user
await mcpServer.callTool('permissions', { 
  operation: 'grant_all_privileges', 
  username: 'newuser', 
  database: 'mydatabase' 
});

// Check user permissions
await mcpServer.callTool('permissions', { 
  operation: 'check_permissions', 
  username: 'newuser' 
});

License

MIT