# PostgreSQL MCP Full Access

A pure, full-access PostgreSQL MCP (Model Context Protocol) server for AI agents. Enables unrestricted SQL execution (DDL/DML) with zero database bundling.

This project embraces a "pure middleware" philosophy, stripping away restrictive database environment setups and hardcoded query filtering. It does exactly one thing: **unconditionally accepts and executes any SQL command from your Agent (including `CREATE`, `INSERT`, `UPDATE`, `DELETE`, and `DROP`), and returns the result.**

All connection methods, network architectures, and security constraints are entirely left to the user and the native PostgreSQL Role-Level Security (RLS) to manage.

## ✨ Key Features

* **Universal Compatibility (Dual-Mode)**: Natively supports both standard MCP SSE (Server-Sent Events) and stateless HTTP POST fallback. This custom-engineered transport layer ensures flawless integration with strict MCP clients as well as agents with non-standard protocol implementations (like Hermes Agent).
* **Network-First Microservice**: Runs securely as an HTTP/SSE service within a closed Docker network, acting as a bridge between your AI Agent and Database without requiring risky host-level Docker socket mounts.
* **Robust Execution Protection**: Built-in connection and statement timeouts prevent rogue or stalled queries (such as AI-induced deadlocks) from freezing the server.
* **Detailed Audit Logging**: Outputs UTC-timestamped API traffic and flattened SQL execution logs directly to the console for transparent debugging.
* **Always Latest**: No locked-in versions. Every build automatically pulls the latest Node.js, MCP SDK, and `pg` drivers, eliminating maintenance overhead.
* **Unrestricted Access**: Breaking free from the typical read-only sandbox. Your AI can autonomously create databases, define tables, and perform vector searches (e.g., `pgvector`).
* **Dynamic Database Switching**: Built-in support for switching databases mid-session—allowing the AI to create a new database and immediately connect to it to execute further commands safely via URL parsing.
* **Clean Deployment**: Easily configurable via `docker-compose` to run continuously in the background alongside your Agent.

---

## 🚀 Quick Start (Docker Network Mode)

**1. Clone the repository**

```bash
git clone https://github.com/BrassaiKao/postgresql-mcp-full-access.git
cd postgresql-mcp-full-access

```

**2. Configure your Database URL**
Edit the `docker-compose.yml` file to include your target database connection string in the environment variables. We highly recommend using a dedicated user and database:

```yaml
services:
  postgres-mcp:
    build:
      context: .
      no_cache: true
    image: postgres-mcp:latest
    container_name: postgres_mcp
    restart: unless-stopped
    environment:
      # Format: postgresql://[Dedicated_User]:[Password]@[Database_Host]:[Port]/[Dedicated_DB]
      - DATABASE_URL=postgresql://agent_user:YourStrongPasswordHere@postgres_vector:5432/agent_db
      - PORT=7432
    networks:
      - private-net

networks:
  private-net:
    external: true

```

**3. Build and Start the Service**
No local Node.js installation is required. Just run:

```bash
docker compose up -d --build

```

(This automatically pulls the latest dependencies and starts the MCP server in the background as a continuous service, listening on port `7432` within the Docker network.)

> 💡 **Note on Docker Networking:**
> By default, `docker-compose.yml` isolates traffic within a shared Docker network (e.g., `private-net`) for security. Docker Desktop will **not** show a published port mapping on your host OS, and `expose` directives are inherently unnecessary for cross-container communication.
> If you need to access the SSE endpoint from your host machine (e.g., for local Postman testing or Desktop apps), explicitly map the ports using `ports:` in `docker-compose.yml`:
> ```yaml
> ports:
>   - "7432:7432"
> 
> ```
> 
> 

## ⚙️ Usage with AI Agents

To use PostgreSQL MCP Full Access with an MCP-compatible agent (like Hermes Agent), you no longer need complex Docker subprocess commands (STDIO). Just point your agent to the SSE endpoint in your agent's configuration.

For Hermes Agent, configure it via UI or in `~/.hermes/config.yaml`:

```yaml
mcp_servers:
  postgres_middleware:
    url: "http://postgres_mcp:7432/sse"

```

*(Ensure your Agent's container is attached to the same shared Docker network as this MCP service).*

> ⚠️ **CRITICAL: Transaction Management for AI Agents**
> AI Agents often try to split SQL transactions into multiple tool calls (e.g., Call 1: `BEGIN;`, Call 2: `INSERT...`, Call 3: `COMMIT;`). This will cause **Database Deadlocks and Timeouts** because the connection pool will hold the lock and wait indefinitely.
> *The tool description is specifically engineered to instruct the AI to send entire transaction blocks in a **single** query string.* If your agent still times out, check the logs to ensure it isn't splitting transactions.

## 🛡️ Security Best Practices (Crucial)

**1. Database Layer Isolation**
Because PostgreSQL MCP Full Access allows all SQL syntax, **DO NOT** give the AI your postgres (Superuser) credentials.
Instead, create a dedicated Role and Database (e.g., `agent_user` and `agent_db`). Use `GRANT` and `REVOKE` to precisely control which tables the Agent can read or write. By delegating security to the database layer, even if a Prompt Injection occurs, malicious commands will be natively blocked by PostgreSQL with a `Permission denied` error!

**2. Network Layer Isolation**
Never expose the MCP server's port (`7432`) directly to the public internet or the host machine using `-p 7432:7432` unless absolutely necessary. Rely on internal Docker user-defined bridge networks to ensure only your AI Agent container can communicate with the MCP server.

## 🔍 Troubleshooting

Check container logs to verify the server started correctly and to monitor real-time SQL execution:

```bash
docker logs -f postgres_mcp

```

**Expected output (with UTC timestamps and execution tracking):**

```text
[2026-08-02T13:35:10.045Z] 🚀 Universal MCP Server running on port 7432 (0.0.0.0)!
[2026-08-02T13:35:25.102Z] 🌍 [GET] /sse
[2026-08-02T13:35:25.105Z] 📡 Client connecting via standard SSE...
[2026-08-02T13:35:28.401Z] 🌍 [POST] /message?sessionId=8f556970-...
[2026-08-02T13:35:28.405Z] 🛠️ [SQL EXEC] SELECT * FROM information_schema.tables WHERE table_schema = 'public';

```