🚀 mcp-server-postgres

Production-Grade, Schema-Aware PostgreSQL Agent via Model Context Protocol (MCP)

mcp-server-postgres is an intelligent database isolation layer designed to bridge the gap between Large Language Models (LLMs) and production PostgreSQL clusters. Developed with a security-first, schema-aware architecture, it translates natural language requests into validated, optimized, and ACID-compliant SQL.

---

🏗️ Technical Architecture

graph TD
    Client[MCP Client/LLM] -->|JSON-RPC| Server[MCP Server Hub]
    Server -->|Validation| Guard[Security Guardrails]
    Guard -->|AST/Pattern Scan| SQL[Validated SQL]
    SQL -->|Execution| Pool[Connection Pool Manager]
    Pool -->|Write Ops| Primary[Primary DB]
    Pool -->|Read Ops| Replica[Read Replicas]
    Pool -->|Auditing| Audit[Pino Structured Logs]

Key Architectural Pillars

• Zero-Trust Safety Pipeline: Multi-stage validation including "SELECT *" detection, mandatory schema qualification, and SQL injection prevention.
• Intelligent Pagination: Transparent query wrapping to ensure result boundedness (Default: 100 rows) without performance degradation.
• Dynamic Schema Introspection: Enables LLMs to resolve exact table relationships and indexes in real-time.
• Isolation Layers: Implements strict Permission Tiers (READ_ONLY to ADMIN) to maintain least-privilege principles.

---

🔒 Security Guardrails (Non-Negotiable)

1. Rule-01: No Unbounded Writes: Rejects any UPDATE or DELETE missing an explicit WHERE clause.
2. Rule-04: DDL Confirmation: High-risk schema mutations require a two-step tokenized confirmation flow.
3. Masking: Native support for PII redaction on sensitive columns (e.g., password_hash, ssn).
4. Transaction Wrapping: All multi-statement operations are executed inside durable BEGIN/COMMIT blocks.

---

🛠️ Configuration

Configure your environment variables in .env:

Variable	Description	Default
DATABASE_URL	Primary PostgreSQL DSN	Required
READ_REPLICA_URL	Optional replica DSN for read routing	null
PERMISSION_TIER	READ_ONLY | READ_WRITE | DDL_ALLOWED | ADMIN	READ_ONLY
MASKED_COLUMNS	Comma-separated list of columns to redact	null
MAX_ROWS	Hard upper limit for result pages	100
SLOW_QUERY_THRESHOLD_MS	Warning threshold for latency	500

---

📋 Toolset API

• query(sql, params, page, page_size): Executes validated SQL. Includes auto-pagination.
• nl_query(request, context): High-level translation interface (requires LLM bridge).
• schema_inspect(schema, table): Deep introspection of schema metadata.
• list_tables(schema): Optimized enumeration of current database schema.
• describe_table(schema, table): Column definitions, indexes, and FK relationships.
• explain(sql, params): Transparent query analyzer with cost estimation.
• transaction(operations): Atomic batch execution for complex mutations.
• confirm_ddl(token): Final execution of verified schema changes.

---

🚦 Getting Started

Development

npm install
npm run build
npm run start

Integration with Claude Desktop

Add to yours claude_desktop_config.json:

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-server-postgres/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgresql://user:pass@localhost:5432/db",
        "PERMISSION_TIER": "READ_WRITE"
      }
    }
  }
}

---

📈 Observability & Logging

All operations are logged via pino for enterprise observability.

{
  "level": "INFO",
  "module": "audit",
  "operation_type": "QUERY",
  "affected_tables": ["public.users"],
  "execution_ms": 12.4,
  "row_count": 8,
  "agent_identity": "mcp-server-postgres"
}

---

⚖️ License

MIT License. Created with precision by Ismail-2001.