@nam088/mcp-postgres

PostgreSQL plugin for MCP (Model Context Protocol) server. Provides tools for interacting with PostgreSQL databases.

Features

• ✅ Connection pooling with configurable pool size
• ✅ Support for connection string or individual config
• ✅ SSL/TLS support
• ✅ Environment variable configuration
• ✅ Read-only and full access modes
• ✅ Parameterized queries for SQL injection prevention
• ✅ Database schema inspection
• ✅ Table and index listing
• ✅ Connection timeout and retry logic

Installation

npm install @nam088/mcp-postgres

Quick Start

Standalone Server

# Using environment variables
export POSTGRES_HOST=localhost
export POSTGRES_PORT=5432
export POSTGRES_USER=postgres
export POSTGRES_PASSWORD=your_password
export POSTGRES_DB=your_database
export POSTGRES_MODE=READONLY

npx mcp-postgres

As a Plugin

import { PostgresPlugin } from '@nam088/mcp-postgres';
import { PluginRegistry } from '@nam088/mcp-core';

const registry = new PluginRegistry(server);
await registry.registerPlugin(PostgresPlugin, {
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'your_password',
  database: 'your_database',
  mode: 'READONLY'
});

Configuration

Connection String (Recommended)

{
  connectionString: 'postgresql://user:password@localhost:5432/database',
  ssl: true
}

Individual Settings

{
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'your_password',
  database: 'your_database',
  ssl: {
    rejectUnauthorized: false, // For self-signed certificates
  },
  max: 10, // Maximum pool size
  min: 0,  // Minimum pool size
  connectionTimeoutMillis: 5000,
  idleTimeoutMillis: 10000,
  statement_timeout: 30000,
  query_timeout: 30000
}

Environment Variables

| Variable | Description | Default | |----------|-------------|---------| | POSTGRES_URL or DATABASE_URL | Connection string | - | | POSTGRES_HOST or PGHOST | PostgreSQL host | localhost | | POSTGRES_PORT or PGPORT | PostgreSQL port | 5432 | | POSTGRES_USER or PGUSER | Database user | postgres | | POSTGRES_PASSWORD or PGPASSWORD | Database password | - | | POSTGRES_DB or PGDATABASE | Database name | postgres | | POSTGRES_MODE | Plugin mode (READONLY, FULL) | READONLY | | POSTGRES_SSL | Enable SSL | false | | POSTGRES_SSL_REJECT_UNAUTHORIZED | Reject unauthorized SSL | true | | POSTGRES_MAX_POOL | Maximum pool size | 10 | | POSTGRES_MIN_POOL | Minimum pool size | 0 | | POSTGRES_TIMEOUT | Connection timeout (ms) | 5000 | | POSTGRES_STATEMENT_TIMEOUT | Statement timeout (ms) | 0 | | POSTGRES_QUERY_TIMEOUT | Query timeout (ms) | 0 | | POSTGRES_APP_NAME | Application name | mcp-postgres |

Available Tools

22 powerful tools for PostgreSQL management!

📊 Schema & Structure Tools (9 tools)

postgres_list_schemas

List all schemas in the database.

{}

postgres_list_tables

List all tables in a schema.

{ schema: "public" }

postgres_describe_table

Get detailed information about table structure.

{ table: "users", schema: "public" }

postgres_list_views

List all views in a schema.

{ schema: "public" }

postgres_list_materialized_views

List all materialized views in a schema.

{ schema: "public" }

postgres_list_functions

List all functions and procedures in a schema.

{ schema: "public" }

postgres_list_sequences

List all sequences in a schema.

{ schema: "public" }

postgres_list_indexes

List all indexes for a table.

{ table: "users", schema: "public" }

postgres_list_triggers

List all triggers for a table (or all tables in schema).

{ table: "users", schema: "public" }  // or just { schema: "public" }

🔍 Query & Analysis Tools (3 tools)

postgres_query

Execute SELECT queries on PostgreSQL database.

{ query: "SELECT * FROM users WHERE id = $1", params: [1] }

postgres_explain_query

Explain a query execution plan (EXPLAIN or EXPLAIN ANALYZE).

{ 
  query: "SELECT * FROM users WHERE age > $1",
  params: [25],
  analyze: true  // false = EXPLAIN only, true = EXPLAIN ANALYZE
}

postgres_database_info

Get PostgreSQL database server information.

{}

📈 Performance & Stats Tools (4 tools)

postgres_table_stats

Get statistics about a table (size, row count, dead rows, vacuum info).

{ table: "users", schema: "public" }

postgres_index_usage

Get index usage statistics for a table or all tables.

{ table: "users", schema: "public" }  // or just { schema: "public" }

postgres_list_constraints

List all constraints for a table (FK, PK, unique, check).

{ table: "users", schema: "public" }

postgres_active_queries

List currently running queries in the database.

{ include_idle: false }  // true to include idle connections

postgres_lock_info

Get information about current locks and blocked queries.

{ blocked_only: true }  // false to show all locks

🔧 Write Tools (Requires FULL mode)

postgres_execute

Execute INSERT, UPDATE, DELETE, or DDL queries.

{ query: "INSERT INTO users (name) VALUES ($1)", params: ["John"] }

postgres_kill_query

Terminate a running query by process ID.

{ pid: 12345, force: false }  // true = terminate, false = cancel

postgres_vacuum_analyze

Run VACUUM ANALYZE on a table for maintenance.

{ table: "users", schema: "public", full: false }

postgres_refresh_materialized_view

Refresh a materialized view.

{ view: "user_stats", schema: "public", concurrently: true }

Security

Plugin Modes

• READONLY: Only SELECT queries and schema inspection
• FULL: All database operations (INSERT, UPDATE, DELETE, DDL)

Best Practices

1. Use parameterized queries to prevent SQL injection:

   // ✅ Good
   { query: "SELECT * FROM users WHERE id = $1", params: [userId] }
      
   // ❌ Bad
   { query: `SELECT * FROM users WHERE id = ${userId}` }

2. Use connection strings for sensitive credentials:

   export DATABASE_URL='postgresql://user:pass@host:5432/db'

3. Enable SSL for production:

   {
     ssl: {
       rejectUnauthorized: true,
       ca: fs.readFileSync('ca-cert.pem').toString()
     }
   }

4. Set statement timeouts to prevent long-running queries:

   {
     statement_timeout: 30000, // 30 seconds
     query_timeout: 30000
   }

Examples

See EXAMPLES.md for detailed usage examples.

License

MIT