Provides comprehensive PostgreSQL database management capabilities including query execution, schema management, transaction handling, user administration, permissions management, performance monitoring, and database maintenance operations.
Click on "Install Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@Postgres MCP Servershow me the top 10 customers by total purchase amount"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
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 build2. 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=infoFeatures
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 installConfiguration 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=falseConnection 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 queriesUsage
Development
npm run devProduction
npm run build
npm startTesting
npm run test
npm run test:queriesTools
Database Operations
query- Execute SQL queries with transaction support, explain plans, analysistables- List, create, alter, drop tables with detailed metadataschemas- FULLY IMPLEMENTED Create, drop, list schemas and manage permissionsindexes- FULLY IMPLEMENTED Create, drop, analyze, reindex with usage statistics
Data Management
data- Insert, update, delete operations with bulk supporttransactions- Begin, commit, rollback with savepoint support
Administration & Security
admin- FULLY IMPLEMENTED Complete database administration and maintenancepermissions- Complete user/role/privilege managementsecurity- SSL, authentication, encryption, auditingmonitoring- Performance metrics and analysisconnections- 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_databasePermission 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.jsonis absoluteVerify
npm run buildcompleted successfullyCheck that
dist/index.jsexists
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
Resources
Unclaimed servers have limited discoverability.
Looking for Admin?
If you are the server author, to access and configure the admin panel.