Skip to main content
Glama

Postgres MCP Server

by itsalfredakku
npmGitHub
TypeScript
82
1
  • Apple
  • Linux
OverviewInspectNewEndpointsSchemaRelated ServersReviewsScore
Need Help?View Source CodeReport Issue

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

Deploy Server
-
security - not tested
F
license - not found
-
quality - not tested

How are these scores calculated?

hybrid server

The server is able to function both locally and remotely, depending on the configuration or use case.

Tools

View all tools

Enables comprehensive PostgreSQL database management through natural language including queries, schema operations, user management, and administrative tasks. Features enterprise-grade connection pooling, transaction support, and full database administration capabilities.

  1. Quick Setup
    1. 1. Installation
    2. 2. Claude Desktop Configuration
    3. 3. Environment Configuration
  2. Features
    1. Installation
      1. Configuration Options
        1. Database Connection
        2. Connection Pool Settings
        3. Performance & Caching
      2. Usage
        1. Development
        2. Production
        3. Testing
      3. Tools
        1. Database Operations
        2. Data Management
        3. Administration & Security
        4. Schema Management Features ✅
        5. Index Management Features ✅
        6. Database Administration Features ✅
      4. Architecture
        1. Troubleshooting
          1. Common Issues
          2. Debug Mode
        2. Database Permissions Setup
          1. Full Admin Access
          2. Using MCP Permission Tools
        3. License

          Appeared in Searches

          New MCP Servers

          MCP directory API

          We provide all the information about MCP servers via our MCP API.

          curl -X GET 'https://glama.ai/api/mcp/v1/servers/itsalfredakku/postgres-mcp'

          If you have feedback or need assistance with the MCP directory API, please join our Discord server