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

Postgres MCP Server

Postgres MCP Server

Quick Setup

1. Installation

2. Claude Desktop Configuration

3. Environment Configuration

Features

Installation

Configuration Options

Database Connection

Connection Pool Settings

Performance & Caching

Usage

Development

Production

Testing

Tools

Database Operations

Data Management

Administration & Security

Schema Management Features ✅

Index Management Features ✅

Database Administration Features ✅

Architecture

Troubleshooting

Common Issues

Debug Mode

Database Permissions Setup

Full Admin Access

Using MCP Permission Tools

License

Related Resources

Tools

Appeared in Searches

New MCP Servers

MCP directory API