Postgres MCP Server

# Postgres MCP server & client This folder contains an MCP server exposing Postgres tools and a tiny CLI client for quick testing. The server reads connection details from `.env`. ## Setup - Ensure `.env` contains `DB_ADDRESS`, `DB_PORT`, `DB_NAME`, `DB_USER`, `DB_PASSWORD`. - Install deps (already installed in this workspace): `pip install -r requirements.txt`. ## Run the server ```bash python mcp_postgres_server.py ``` It starts an MCP stdio server named `postgres-mcp` with tools: `describe_database`, `run_read_query`, `run_write_query`, and `health_check`. Writes are confined to the `mcp` schema and multi-statement input is rejected. ## CLI testing Use the CLI to spawn the server on demand and call tools: ```bash python cli.py list-tools python cli.py describe --schema ehr python cli.py read "select * from mcp.example_table" --limit 25 python cli.py write "insert into example_table (col) values ('hello')" # Or call any tool with raw JSON args python cli.py call run_read_query --args '{"sql": "select now()"}' ``` ## Run as a systemd service 1) Update `systemd/postgres-mcp.service` if your python path, user/group, or working directory differ. It expects `/home/remote/miniconda3/bin/python`, user `remote`, and `.env` at `/home/data/NUHS/mcp/.env`. 2) Install and start: ```bash chmod +x install_service.sh ./install_service.sh ``` (You’ll be prompted for sudo.) 3) Check status/logs: ```bash sudo systemctl status postgres-mcp journalctl -u postgres-mcp -f ``` ## Run over HTTP (streamable) For MCP clients that need HTTP/SSE, start the server in streamable HTTP mode: ```bash python mcp_postgres_server.py --transport streamable-http --host 0.0.0.0 --port 8000 ``` Expose the port via Tailscale/VPN or your network. Keep the `.env` in place so the server can reach Postgres. If you switch the systemd service to HTTP, update `ExecStart` accordingly (same flags as above). ### Authentication (Bearer) If you set `MCP_API_KEY` in `.env`, the HTTP mode will require `Authorization: Bearer <MCP_API_KEY>` on every request. Stdio mode does not use the token. ### Debugging HTTP connectivity Use the helper script to test the HTTP endpoint (works against OpenWebUI-style connections): ```bash python debug_http_client.py --url http://127.0.0.1:8000/mcp --token "$MCP_API_KEY" # For Tailscale: python debug_http_client.py --url http://100.x.x.x:8000/mcp --token "$MCP_API_KEY" ``` The script initializes a session and lists tools. If it fails, it will print the exception so you can check networking, port, or auth issues. ## Using with different MCP clients - **OpenWebUI (agent/MCP integration)**: In Settings → MCP, add a server entry with command `python /home/data/NUHS/mcp/mcp_postgres_server.py` (adjust to your python path). OpenWebUI will spawn the stdio server locally. If OpenWebUI runs on another machine, configure it to SSH/Tailscale into this host and run the same command so it can reach Postgres. For network-only mode, point it at the HTTP endpoint you start with `--transport streamable-http`. - **Other stdio-capable clients (Cursor/Claude Desktop/etc.)**: Configure the MCP server command to `/home/remote/miniconda3/bin/python /home/data/NUHS/mcp/mcp_postgres_server.py` with working directory `/home/data/NUHS/mcp`. Ensure `DB_*` vars are available via `.env` or exported into the client’s environment before launch. - **Network-only MCP clients**: Start with the HTTP mode above (`--transport streamable-http --host ... --port ...`), open that port via Tailscale/VPN, and point the client at the resulting endpoint. If `MCP_API_KEY` is set, send `Authorization: Bearer <MCP_API_KEY>` in requests. Preserve the write-safety rules if you expose it. ## Connecting a chat client (via Tailscale) This MCP server speaks stdio by default. Most chat clients that support MCP spawn a command. To use it on a Tailscale node: - Install and log in to Tailscale on the server host; note its Tailscale IP (e.g., `100.x.x.x`) from `tailscale ip -4`. - Enable and start the service on that host (`./install_service.sh` or `sudo systemctl start postgres-mcp`). - From your dev machine, use Tailscale SSH to run the client or CLI directly on the host: `ssh remote@100.x.x.x 'cd /home/data/NUHS/mcp && python cli.py list-tools'`. - If your chat app supports running an MCP server command remotely, configure it to SSH into the Tailscale IP and execute `/home/remote/miniconda3/bin/python /home/data/NUHS/mcp/mcp_postgres_server.py`. - For HTTP mode, run the server with `--transport streamable-http --host 0.0.0.0 --port 8000` and point clients to `http://100.x.x.x:8000/mcp` (adjust port/host). If `MCP_API_KEY` is set, include `Authorization: Bearer <MCP_API_KEY>`. ## Suggested system prompt for LLMs If your chat client lets you provide a system prompt for MCP, start with: ``` You are an assistant that uses the "postgres-mcp" server to inspect and modify data in Postgres. - Call "describe_database" first to understand schemas and tables. - Use "run_read_query" for SELECTs; it auto-applies a LIMIT if missing. - Use "run_write_query" only for writes inside the mcp schema; other schemas are blocked. - Do not send multiple statements in one call. - Prefer parameterized SQL and avoid destructive changes outside the mcp schema. ``` If connecting over HTTP and `MCP_API_KEY` is set, include `Authorization: Bearer <MCP_API_KEY>`; stdio mode does not need it.