# 🚀 mcp-server-postgres

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

[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)
[![MCP Compliant](https://img.shields.io/badge/MCP-Compliant-blue.svg)](https://modelcontextprotocol.io)
[![PRs Welcome](https://img.shields.io/badge/PRs-welcome-brightgreen.svg)](http://makeapullrequest.com)

`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

```mermaid
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
```bash
npm install
npm run build
npm run start
```

### Integration with Claude Desktop
Add to yours `claude_desktop_config.json`:
```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.
```json
{
  "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.