@nam088/mcp-postgres

@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

• 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}` }
  

• Use connection strings for sensitive credentials:

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

• Enable SSL for production:

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

• 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