@opichi/postgres-inspector

A generic PostgreSQL database inspector MCP (Model Context Protocol) server that allows AI assistants to inspect any PostgreSQL database.

Features

• Generic Database Support: Works with any PostgreSQL database via connection URI
• Comprehensive Inspection Tools:
  • List all tables
  • Show table schemas
  • Preview table data
  • Execute read-only SELECT queries
  • Count table rows
  • Show foreign key relationships
• Safe Write Operations:
  • INSERT operations with user approval
  • UPDATE operations with user approval
  • Non-destructive schema changes with user approval
  • Comprehensive impact analysis before execution
  • Transaction-based execution with automatic rollback
• Advanced Safety Features:
  • Strict prohibition of destructive operations (DROP, TRUNCATE, DELETE)
  • Mandatory user approval for all write operations
  • Detailed risk assessment and rollback strategies
  • Audit trail for all approved operations
• Easy Integration: Works with Claude Desktop, Windsurf, Cursor, and other MCP-compatible clients

Installation

npx @opichi/postgres-inspector

Usage

With Claude Desktop

Add this to your claude_desktop_config.json:

{
  "mcpServers": {
    "postgres-inspector": {
      "command": "npx",
      "args": ["@opichi/postgres-inspector"]
    }
  }
}

With Windsurf

Add this to your Windsurf MCP configuration:

{
  "mcpServers": {
    "postgres-inspector": {
      "command": "npx",
      "args": ["@opichi/postgres-inspector"]
    }
  }
}

With Cursor

Add this to your Cursor MCP configuration:

{
  "mcpServers": {
    "postgres-inspector": {
      "command": "npx",
      "args": ["@opichi/postgres-inspector"]
    }
  }
}

Available Tools

list-tables

Lists all tables in the specified PostgreSQL database.

Parameters:

• connectionUri (string): PostgreSQL connection URI

show-schema

Shows the schema (columns, types, etc.) for a specific table.

Parameters:

• connectionUri (string): PostgreSQL connection URI
• tableName (string): Name of the table to inspect

show-data

Shows a preview of data from a table.

Parameters:

• connectionUri (string): PostgreSQL connection URI
• tableName (string): Name of the table to query
• limit (number, optional): Maximum number of rows to return (default: 10)

execute-query

Executes a read-only SELECT SQL query.

Parameters:

• connectionUri (string): PostgreSQL connection URI
• query (string): SQL SELECT query to execute

count-rows

Counts the total number of rows in a table.

Parameters:

• connectionUri (string): PostgreSQL connection URI
• tableName (string): Name of the table to count

show-relationships

Shows the foreign key relationships between tables.

Parameters:

• connectionUri (string): PostgreSQL connection URI

Safe Write Operations

⚠️ IMPORTANT: All write operations require explicit user approval and provide detailed impact analysis before execution.

propose-insert

Proposes an INSERT operation for user approval with detailed impact analysis.

Parameters:

• connectionUri (string): PostgreSQL connection URI
• sql (string): INSERT SQL statement to propose
• description (string): Human-readable description of what this INSERT will accomplish

Example:

INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')

propose-update

Proposes an UPDATE operation for user approval with detailed impact analysis.

Parameters:

• connectionUri (string): PostgreSQL connection URI
• sql (string): UPDATE SQL statement to propose
• description (string): Human-readable description of what this UPDATE will accomplish

Example:

UPDATE users SET last_login = NOW() WHERE id = 123

propose-schema-change

Proposes a non-destructive schema change for user approval with detailed impact analysis.

Parameters:

• connectionUri (string): PostgreSQL connection URI
• sql (string): Schema change SQL statement to propose (ALTER TABLE ADD, CREATE INDEX, etc.)
• description (string): Human-readable description of what this schema change will accomplish

Example:

ALTER TABLE users ADD COLUMN phone_number VARCHAR(20)

execute-approved-write

Executes a previously approved write operation using its approval ID.

Parameters:

• approvalId (string): The approval ID from a previous propose-* operation

list-pending-approvals

Lists all pending write operations awaiting approval.

Parameters: None

cancel-approval

Cancels a pending approval request.

Parameters:

• approvalId (string): The approval ID to cancel

Write Operation Workflow

1. Propose Operation: Use propose-insert, propose-update, or propose-schema-change
2. Review Impact Analysis: The system provides:
   • 🔍 Detailed explanation of what will happen
   • 🎯 Risk assessment (🟢 Low, 🟡 Medium, 🔴 High)
   • 📊 Estimated number of affected rows
   • 🔄 Rollback strategy and recommendations
   • 📋 List of affected tables
3. Approve or Cancel: Use execute-approved-write to proceed or cancel-approval to abort
4. Execution: Operation runs within a transaction with automatic rollback on errors
5. Audit Trail: All operations are logged with timestamps and approval IDs

Safety Guarantees

✅ Allowed Operations

• INSERT statements (adding new data)
• UPDATE statements (modifying existing data)
• CREATE statements (tables, indexes, views)
• ALTER TABLE ADD statements (adding columns, constraints)

❌ Prohibited Operations

• DROP statements (tables, columns, databases)
• TRUNCATE statements
• DELETE statements
• ALTER TABLE DROP statements (removing columns)
• Any destructive schema changes

🛡️ Safety Features

• Transaction Isolation: All writes execute within transactions
• Automatic Rollback: Failed operations are automatically rolled back
• Impact Analysis: Detailed assessment before any write operation
• Approval Required: No write operation can execute without explicit user approval
• Audit Trail: Complete logging of all approved operations

count-rows

Counts the total number of rows in a table.

Parameters:

• connectionUri (string): PostgreSQL connection URI
• tableName (string): Name of the table to count

show-relationships

Shows the foreign key relationships between tables.

Parameters:

• connectionUri (string): PostgreSQL connection URI

Connection URI Format

The connection URI should follow the PostgreSQL standard format:

postgresql://username:password@hostname:port/database_name

Example:

postgresql://myuser:mypassword@localhost:5432/mydatabase

Security

• Only SELECT queries are allowed for safety
• Connection pooling is configured with reasonable limits
• All database connections are properly closed after use

Development

# Clone the repository
git clone https://github.com/opichillc/postgres-inspector-mcp.git
cd postgres-inspector-mcp

# Install dependencies
npm install

# Build the project
npm run build

# Test locally
node build/index.js

License

MIT

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.