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

Variable	Description	Default
PG_HOST	PostgreSQL server hostname	localhost
PG_PORT	PostgreSQL server port	5432
PG_USER	Database username	postgres
PG_PASSWORD	Database password	-
PG_DATABASE	Target database name	-
PG_SCHEMA	Default schema	public

SSL Configuration

Variable	Description	Options
PG_SSL_MODE	SSL connection mode	disable, require, verify-ca, verify-full
PG_SSL_REJECT_UNAUTHORIZED	Reject self-signed certs	true, false
PG_SSL_CA_PATH	Path to CA certificate	-
PG_SSL_CERT_PATH	Path to client certificate	-
PG_SSL_KEY_PATH	Path to client key	-
PG_SSL_MIN_VERSION	Minimum TLS version	TLSv1.2, TLSv1.3

Security Settings

Variable	Description	Default
ALLOW_WRITE_OPERATIONS	Enable INSERT/UPDATE/DELETE	false
CONNECTION_LIMIT	Max pool connections	10
QUERY_TIMEOUT	Query timeout (ms)	30000
MAX_RESULTS	Maximum rows returned	1000

Rate Limiting

Variable	Description	Default
RATE_LIMIT_PER_MINUTE	Queries per minute	60
RATE_LIMIT_PER_HOUR	Queries per hour	1000
RATE_LIMIT_CONCURRENT	Concurrent queries	10

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)

Tool	Description
query	Execute SELECT queries
list_tables	List all tables in schema
describe_table	Get table structure and columns
database_info	Get database version and settings
show_indexes	List indexes on a table
explain_query	Get query execution plan
show_constraints	List table constraints

PostgreSQL-Specific Read-Only Tools (14)

Tool	Description
list_schemas	List all schemas in database
get_current_schema	Get current search path
list_extensions	List installed extensions
extension_info	Get detailed extension information
list_functions	List user-defined functions
list_triggers	List triggers on a table
list_views	List views in schema
list_sequences	List sequences in schema
table_stats	Get table statistics
connection_info	Get current connection details
database_size	Get database/table sizes
jsonb_query	Query JSONB columns
jsonb_path_query	Execute JSON path queries

Write Operation Tools (15)

Requires ALLOW_WRITE_OPERATIONS=true

Tool	Description
insert	Insert a single row
update	Update rows with conditions
delete	Delete rows with conditions
create_table	Create a new table
alter_table	Modify table structure
drop_table	Drop a table
bulk_insert	Insert multiple rows
execute_procedure	Call stored procedures
add_index	Create an index
drop_index	Remove an index
rename_table	Rename a table
set_search_path	Change schema search path
create_schema	Create a new schema
drop_schema	Drop a schema
jsonb_update	Update JSONB fields
vacuum_analyze	Optimize table statistics

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