PostgreSQL MCP Server

A comprehensive Model Context Protocol (MCP) server for PostgreSQL database access. Provides 36 tools for querying, managing, and interacting with PostgreSQL databases through the MCP interface.

Features

• 36 Database Tools: Complete set of read-only and write operations

• PostgreSQL-Specific Features: Schema support, JSONB operations, extensions, functions, triggers, views, sequences

• Full SSL/TLS Support: CA certificates, client certificates, configurable TLS versions

• Security First: Query validation, rate limiting, blocked dangerous operations

• Connection Pooling: Efficient connection management with configurable limits

• Audit Logging: Track all database operations

Installation

# Clone or copy to your tools directory
cd /path/to/tools/mav-postgresql-mcp-server

# Install dependencies
npm install

# Build the server
npm run build

Configuration

Copy .env.example to .env and configure your PostgreSQL connection:

cp .env.example .env

Required Settings

SSL Configuration

Security Settings

Rate Limiting

Usage

With Claude Desktop

Add to your Claude Desktop configuration (~/Library/Application Support/Claude/claude_desktop_config.json on macOS):

{
  "mcpServers": {
    "postgresql": {
      "command": "node",
      "args": ["/path/to/mav-postgresql-mcp-server/build/index.js"],
      "env": {
        "PG_HOST": "localhost",
        "PG_PORT": "5432",
        "PG_USER": "your_user",
        "PG_PASSWORD": "your_password",
        "PG_DATABASE": "your_database",
        "PG_SCHEMA": "public",
        "ALLOW_WRITE_OPERATIONS": "false"
      }
    }
  }
}

With MCP Inspector

npx @anthropic/mcp-inspector node build/index.js

Available Tools

Core Read-Only Tools (7)

PostgreSQL-Specific Read-Only Tools (14)

Write Operation Tools (15)

Requires ALLOW_WRITE_OPERATIONS=true

MCP Resources

The server exposes database schema as MCP resources:

• pg://database/schema - List all tables and columns

• pg://database/info - Database information

• pg://table/{schema}.{table} - Individual table schema

Security Features

Blocked Operations

The server blocks dangerous operations by default:

• File system operations (COPY FROM/TO, pg_read_file, etc.)

• Permission modifications (GRANT, REVOKE, ALTER ROLE)

• Administrative commands (CREATE ROLE, DROP DATABASE, etc.)

• System catalog modifications

Protected Tables

Access to sensitive system tables is blocked:

• pg_catalog.pg_authid

• pg_catalog.pg_shadow

• pg_catalog.pg_auth_members

Query Validation

• All identifiers are validated (max 63 characters, safe characters only)

• Query timeouts prevent long-running operations

• Rate limiting prevents abuse

Setting Up a Read-Only User

For production use, create a dedicated read-only PostgreSQL user:

# Run as PostgreSQL superuser
psql -U postgres -f setup-readonly-user.sql

Or manually:

-- Create user
CREATE USER mcp_readonly WITH PASSWORD 'secure_password';

-- Grant connect
GRANT CONNECT ON DATABASE your_database TO mcp_readonly;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO mcp_readonly;

-- Grant read access to all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO mcp_readonly;

Development

# Run in development mode
npm run dev

# Build for production
npm run build

# Type checking
npm run typecheck

Troubleshooting

Connection Issues

1. Verify PostgreSQL is running: pg_isready -h localhost -p 5432

2. Check credentials: psql -h localhost -U your_user -d your_database

3. Enable debug mode: MCP_DEBUG=true

SSL Issues

1. Verify certificate paths are correct

2. Check certificate permissions (readable by the user running the server)

3. Try PG_SSL_MODE=require first, then upgrade to verify-ca or verify-full

Rate Limiting

If you're hitting rate limits:

1. Increase RATE_LIMIT_PER_MINUTE and RATE_LIMIT_PER_HOUR

2. Batch operations where possible

3. Use more specific queries to reduce call volume

License

MIT