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

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:

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:

chmod +x install_service.sh
./install_service.sh

(You’ll be prompted for sudo.) 3) Check status/logs:

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:

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):

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.