Metadata-Version: 2.4
Name: fluid-postgres-mcp
Version: 0.1.3
Summary: Fluid PostgreSQL MCP Server — reliable database interface for AI agents
Project-URL: Homepage, https://github.com/povesma/fluid-postgres-mcp
Project-URL: Repository, https://github.com/povesma/fluid-postgres-mcp
Author-email: D Povesma <povesma@users.noreply.github.com>
License-Expression: MIT
License-File: LICENSE
Requires-Python: >=3.10
Requires-Dist: attrs>=25.4.0
Requires-Dist: humanize>=4.15.0
Requires-Dist: instructor>=1.14.4
Requires-Dist: mcp[cli]>=1.25.0
Requires-Dist: pglast==7.11
Requires-Dist: psycopg-pool>=3.3.0
Requires-Dist: psycopg[binary]>=3.3.2
Provides-Extra: dev
Requires-Dist: docker>=7.1.0; extra == 'dev'
Requires-Dist: pyright==1.1.408; extra == 'dev'
Requires-Dist: pytest-asyncio>=1.3.0; extra == 'dev'
Requires-Dist: pytest>=9.0.2; extra == 'dev'
Requires-Dist: ruff==0.14.13; extra == 'dev'
Description-Content-Type: text/markdown

# fluid-postgres-mcp

[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](LICENSE)
[![Python 3.10+](https://img.shields.io/badge/python-3.10%2B-blue.svg)](https://www.python.org/downloads/)

A PostgreSQL [MCP](https://modelcontextprotocol.io/) server for AI
agents. Streams large result sets to CSV, enforces per-query timeouts,
auto-reconnects with backoff, and supports long-running tunnel scripts
(e.g. AWS SSM port-forwarding) with credential rotation.

Fork of [crystaldba/postgres-mcp](https://github.com/crystaldba/postgres-mcp).

## Install

> Jump to: [Other AI agents](#other-ai-agents) ·
> [Alternative install methods](#alternative-install-methods) ·
> [Develop](#develop)

Python 3.10+. Published on PyPI as
[`fluid-postgres-mcp`](https://pypi.org/project/fluid-postgres-mcp/);
console entry point of the same name.

### Verify install

Before wiring the MCP into your agent, confirm the install actually
resolved its dependencies:

```bash
uvx fluid-postgres-mcp --version    # prints "fluid-postgres-mcp X.Y.Z", exit 0
uvx fluid-postgres-mcp --help       # prints usage, exit 0
```

Exit 0 from either command means the package downloaded and every
runtime dependency imported successfully. A Python traceback or
non-zero exit means at least one import failed — typically a missing
system library (e.g. `libpq` on minimal Linux images) or a broken
`uvx` cache. Fix that before continuing; an agent registration
against a broken install fails silently at first tool call.

### With Claude Code (primary)

```bash
claude mcp add fluid-postgres-mcp -- \
    uvx fluid-postgres-mcp \
        postgresql://reader:pw@host:5432/db
```

With a long-running tunnel script (see
[Pre-connect scripts](#pre-connect-scripts) for the protocol the
script must speak):

```bash
claude mcp add fluid-postgres-mcp -- \
    uvx fluid-postgres-mcp \
        --pre-connect-script /path/to/your-tunnel.sh
```

### Other AI agents

Brief one-shot snippets — copy-paste, or read your agent's own MCP
docs for the full story. All entries use `uvx fluid-postgres-mcp`
so no global install is needed.

**Codex CLI** —
[docs](https://github.com/openai/codex/blob/main/docs/config.md):

```bash
codex mcp add fluid-postgres-mcp \
    --transport stdio \
    --command "uvx fluid-postgres-mcp postgresql://reader:pw@host:5432/db"
```

**Cursor CLI** — [docs](https://cursor.com/docs/cli):

```bash
agent mcp add fluid-postgres-mcp -- \
    uvx fluid-postgres-mcp postgresql://reader:pw@host:5432/db
```

**Gemini CLI** — add to `~/.gemini/settings.json`
([docs](https://github.com/google-gemini/gemini-cli/blob/main/docs/tools/mcp-server.md)):

```json
{
  "mcpServers": {
    "fluid-postgres-mcp": {
      "command": "uvx",
      "args": ["fluid-postgres-mcp", "postgresql://reader:pw@host:5432/db"]
    }
  }
}
```

**opencode** — add to `opencode.json`
([docs](https://opencode.ai/docs/mcp-servers)):

```jsonc
{
  "mcp": {
    "fluid-postgres-mcp": {
      "type": "local",
      "command": ["uvx", "fluid-postgres-mcp", "postgresql://reader:pw@host:5432/db"]
    }
  }
}
```

**Kiro CLI** — add to `mcp.json`
([docs](https://kiro.dev/docs/cli/mcp)):

```json
{
  "mcpServers": {
    "fluid-postgres-mcp": {
      "command": "uvx",
      "args": ["fluid-postgres-mcp", "postgresql://reader:pw@host:5432/db"]
    }
  }
}
```

**Cursor (IDE)** — add to `~/.cursor/mcp.json`
([docs](https://cursor.com/docs/mcp)):

```json
{
  "mcpServers": {
    "fluid-postgres-mcp": {
      "command": "uvx",
      "args": ["fluid-postgres-mcp", "postgresql://reader:pw@host:5432/db"]
    }
  }
}
```

**Windsurf** — add to `~/.codeium/windsurf/mcp_config.json`
([docs](https://docs.windsurf.com/plugins/cascade/mcp)):

```json
{
  "mcpServers": {
    "fluid-postgres-mcp": {
      "command": "uvx",
      "args": ["fluid-postgres-mcp", "postgresql://reader:pw@host:5432/db"]
    }
  }
}
```

**Zed** — add to `~/.config/zed/settings.json` under
`context_servers` (note: *not* `mcpServers`)
([docs](https://zed.dev/docs/ai/mcp)):

```json
{
  "context_servers": {
    "fluid-postgres-mcp": {
      "command": "uvx",
      "args": ["fluid-postgres-mcp", "postgresql://reader:pw@host:5432/db"]
    }
  }
}
```

### Alternative install methods

If you'd rather have a persistent install than resolve through
`uvx` on every launch:

```bash
pipx install fluid-postgres-mcp        # isolated, on $PATH
pip  install fluid-postgres-mcp        # use a virtualenv to avoid global pollution
```

From source (no editable; for users who clone but don't want a
working tree):

```bash
git clone https://github.com/povesma/fluid-postgres-mcp
pip install ./fluid-postgres-mcp
```

After any of these, the agent snippets above can drop `uvx` and
invoke `fluid-postgres-mcp` directly.

## How to use Fluid Postgres MCP

Tools exposed: `execute_sql`, `status`, `list_schemas`, `list_objects`,
`get_object_details`, `explain_query`, `analyze_db_health`,
`analyze_query_indexes`, `analyze_workload_indexes`, `get_top_queries`.

`execute_sql` accepts `timeout_ms`, `output_file`, and `output_mode`
(`inline` / `file` / `file+inline`) for CSV streaming.

## Configure

Every flag has a matching env var (`PGMCP_*`). CLI wins.

| Flag | Default | What it does |
|---|---|---|
| `database_url` (positional) / `DATABASE_URI` | required | PostgreSQL URL |
| `--default-timeout` | `0` | `statement_timeout` ms (0 = none) |
| `--reconnect-initial-delay` / `--reconnect-max-delay` | `1.0` / `60.0` | Backoff bounds (s) |
| `--reconnect-max-attempts` | `0` | 0 = unlimited |
| `--pre-connect-script` | none | Tunnel/setup script (see below) |
| `--hook-timeout` | `30.0` | Pre-connect-script timeout (s) |
| `--event-buffer-size` | `100` | Per-category ring buffer |
| `--output-dir` | `.` | Default base for CSV output |
| `--transport` | `stdio` | `stdio` / `sse` / `streamable-http` |

## Pre-connect scripts

Two modes, auto-detected from script behaviour. Existing run-and-exit
scripts work unchanged.

**Run-and-exit:** the script runs, exits 0, and the MCP connects.
Suitable when something else owns the tunnel.

**Long-running:** the script owns the tunnel for the lifetime of the
MCP. It speaks a line-prefixed stdout protocol:

```
[MCP] DB_URL postgresql://user:pw@host:port/db    # optional, overrides --database-url
[MCP] READY_TO_CONNECT                            # required, signals readiness
```

If the script process dies (tunnel broke), the MCP detects it within
~1 second, respawns the script, and reconnects with whatever URL the
new instance emits — which is how credential/URL rotation works.

### AWS SSM examples

Two vendored Python reference scripts cover the common AWS topologies.
Each is a single drop-in file: copy it, set the env vars, point
`--pre-connect-script` at it. Both speak the long-running protocol
described above; both supervise their SSM child and exit on its
death so fluid-postgres-mcp respawns them.

#### Choosing a topology

| Topology | Script | When |
|---|---|---|
| **EC2-direct** | [`scripts/examples/aws-ssm-ec2-tunnel.py`](./scripts/examples/aws-ssm-ec2-tunnel.py) | PostgreSQL runs on the EC2 instance itself, or the EC2 hosts a userspace proxy you control. |
| **RDS-via-EC2** | [`scripts/examples/aws-ssm-rds-tunnel.py`](./scripts/examples/aws-ssm-rds-tunnel.py) | PostgreSQL runs on RDS. The EC2 is a pure SSM forwarder — no PG, no proxy on it. Uses `AWS-StartPortForwardingSessionToRemoteHost`. |

True bastion-less SSM-to-RDS is not possible: `ssm:StartSession`
requires an SSM-managed target, and RDS is not one. If you cannot
keep an EC2 in the loop, look at RDS IAM authentication or the EC2
Instance Connect Endpoint (EICE) — both are outside the SSM
port-forwarding model these scripts use.

Passwords in `DB_URL` are obfuscated in every fluid-postgres-mcp
event message and log line.

#### Environment variables

The scripts are configured entirely via environment variables,
passed through your agent's MCP registration (e.g. `claude mcp add
… -e KEY=VALUE`).

| Variable | EC2-direct | RDS-via-EC2 | Purpose |
|---|---|---|---|
| `EC2_INSTANCE_ID` | required | required | SSM target instance |
| `EC2_REGION` | required | required | AWS region of the instance |
| `RDS_ENDPOINT` | — | required | RDS endpoint hostname |
| `DB_NAME` | required | required | PostgreSQL database name |
| `DB_USERNAME` | required | required | PostgreSQL user |
| `DB_PASSWD` | required | required | PostgreSQL password |
| `DB_HOST` | optional (`localhost`) | — | Host PG listens on (EC2-direct only) |
| `DB_PORT` | optional (`5432`) | optional (`5432`) | PostgreSQL port |
| `ASSUME_ROLE_ARN` | optional | optional | Role to assume on top of base credentials |
| `AWS_PROFILE` | optional | optional | Profile (overridden by `--profile` flag) |

Authentication precedence (highest first): `--profile` CLI flag →
`AWS_PROFILE` → SDK default credential chain (env vars,
`~/.aws/credentials`, instance metadata, …). If `ASSUME_ROLE_ARN`
is set, the resolved base credentials drive an `sts:AssumeRole`
call and the resulting STS credentials drive every subsequent AWS
call.

#### Required AWS permissions

The principal that ends up driving the AWS calls (after AssumeRole,
if any) needs:

```
sts:AssumeRole                       (only if ASSUME_ROLE_ARN set)
sts:GetCallerIdentity                (diagnostic)
ec2:DescribeInstances
ec2:StartInstances                   (only to wake a stopped host)
ssm:DescribeInstanceInformation
ssm:StartSession                     (see below for resource scope)
ssm:TerminateSession                 (on the session ARN — clean teardown)
```

The `ssm:StartSession` resource scope differs by topology:

- **EC2-direct**: target = the EC2 instance ARN; document =
  `AWS-StartPortForwardingSession`.
- **RDS-via-EC2**: target = the EC2 instance ARN; document =
  `AWS-StartPortForwardingSessionToRemoteHost`. The EC2's security
  group must allow egress to RDS:5432; the RDS security group must
  allow ingress from the EC2 security group.

#### Stdout protocol

Both scripts emit exactly two lines on stdout (everything else goes
to stderr):

```
[MCP] DB_URL postgresql://<user>:<pw>@127.0.0.1:<local_port>/<db>?...
[MCP] READY_TO_CONNECT
```

After these the script stays alive supervising the SSM child. Exit
on child death is the signal to fluid-postgres-mcp that the tunnel
is gone and the script should be respawned — that is the recovery
loop.

#### EC2-direct

PostgreSQL is reachable on the EC2 itself (running there, or
proxied by the EC2 to a backend it controls). The SSM session
terminates on the EC2 and forwards traffic to whatever
`DB_HOST:DB_PORT` resolves to from the EC2's perspective
(default `localhost:5432`).

```bash
claude mcp add my-pg \
  -e EC2_INSTANCE_ID=i-0123456789abcdef0 \
  -e EC2_REGION=eu-central-1 \
  -e DB_NAME=mydb -e DB_USERNAME=reader -e DB_PASSWD='s3cr3t' \
  -- uvx fluid-postgres-mcp \
       --pre-connect-script /path/to/aws-ssm-ec2-tunnel.py
```

#### RDS-via-EC2

PostgreSQL runs on RDS. The EC2 is a pure SSM forwarder — no PG
process, no userspace proxy. The SSM session uses
`AWS-StartPortForwardingSessionToRemoteHost` with `host=$RDS_ENDPOINT`,
so traffic flows
`localhost:<local_port> → EC2 (SSM forwarder) → $RDS_ENDPOINT:5432`.

```bash
claude mcp add my-pg \
  -e EC2_INSTANCE_ID=i-0123456789abcdef0 \
  -e EC2_REGION=eu-central-1 \
  -e RDS_ENDPOINT=my-db.abcdef.eu-central-1.rds.amazonaws.com \
  -e DB_NAME=mydb -e DB_USERNAME=reader -e DB_PASSWD='s3cr3t' \
  -- uvx fluid-postgres-mcp \
       --pre-connect-script /path/to/aws-ssm-rds-tunnel.py
```

#### Smoke

After registering, restart your agent and run a real-data query —
not `SELECT 1`. A constant query proves only that something
answered on the socket; it doesn't prove the right DB was mapped
or that rows flow:

```sql
SELECT count(*) FROM <a-known-populated-table>;
```

Expect a non-trivial count you can recognise. Zero / empty / NULL
is a failure, not a pass.

### Reference scripts

Working examples — copy and adapt:

- [`scripts/examples/aws-ssm-ec2-tunnel.py`](./scripts/examples/aws-ssm-ec2-tunnel.py)
  — production-shaped Python: credential resolution, optional
  `sts:AssumeRole`, EC2 wake, SSM agent readiness wait, port-forward,
  port-open probe, PG liveness probe, handshake, signal teardown,
  remote session termination. EC2-direct topology.
- [`scripts/examples/aws-ssm-rds-tunnel.py`](./scripts/examples/aws-ssm-rds-tunnel.py)
  — same lifecycle as above, but uses
  `AWS-StartPortForwardingSessionToRemoteHost` so the EC2 acts as a
  pure SSM forwarder to an RDS endpoint.
- [`tests/e2e/fixtures/long_running_passthrough.sh`](./tests/e2e/fixtures/long_running_passthrough.sh)
  — minimal long-running script: emits `DB_URL` + `READY_TO_CONNECT`,
  then blocks on `exec sleep` until SIGTERM. Useful as a starting
  template.
- [`tests/e2e/ssm_fixtures.py`](./tests/e2e/ssm_fixtures.py)
  (`create_long_running_tunnel_script`) — full SSM port-forwarding
  variant: spawns `aws ssm start-session` as a child, fetches the
  password from Parameter Store, emits the protocol lines, then
  `wait`s on the SSM child so tunnel death exits the script.
- [`tests/e2e/ssm_fixtures.py`](./tests/e2e/ssm_fixtures.py)
  (`create_tunnel_script`) — run-and-exit SSM variant for the legacy
  flow (something else owns the tunnel lifecycle).

### Authoring notes

- **Block on the resource that defines liveness.** A long-running
  script must exit when its tunnel/session dies; otherwise the MCP
  has no signal to reconnect. `wait "$TUNNEL_PID"` (foreground
  child) or `exec sleep <large>` (when there's no child to wait on)
  both work; backgrounded `sleep & wait $!` with a `trap` is
  unreliable on macOS (the parent's `proc.wait()` does not
  observe SIGCHLD through it).
- **macOS `sleep` does not accept `infinity`.** Use a large integer
  (`exec sleep 2147483647`).
- **Failure surface.** Exit-before-READY → mode is run-and-exit and
  exit code surfaces as success/failure. No `READY_TO_CONNECT`
  within `--hook-timeout` → script killed, connect fails. Malformed
  `[MCP] DB_URL` payload → warning event recorded, prior override
  retained, MCP falls back to the configured URL. Unknown `[MCP]`
  keywords are warned once per keyword and ignored.
  Long-running script alive but no `[MCP] DB_URL` yet → state is
  `WAITING_FOR_URL`, the reconnect loop keeps polling, recoverable
  as soon as the script emits a URL. Run-and-exit script exited
  *without* emitting `DB_URL` and no `DATABASE_URI` / positional URL
  is set → `_unrecoverable=True`, state `ERROR`, no further retries.
- **Diagnose without shelling onto the box.** All script lifecycle
  events are exposed via the `status` MCP tool — `started`/`pid`,
  `READY_TO_CONNECT`, `DB_URL` (host/db only, password redacted),
  `exited`/`exit_code`. See [`ARCHITECTURE.md`](./ARCHITECTURE.md)
  for how the event store is wired and
  [`TESTING-METHODOLOGY.md`](./TESTING-METHODOLOGY.md) for the faults
  we inject against it.

## Develop

Work from a clone:

```bash
git clone https://github.com/povesma/fluid-postgres-mcp
cd fluid-postgres-mcp
pip install -e ".[dev]"
pytest
```

Design and fault-injection catalogue:
[`ARCHITECTURE.md`](./ARCHITECTURE.md) ·
[`TESTING-METHODOLOGY.md`](./TESTING-METHODOLOGY.md).

### Release

Versioning is SemVer; PyPI is the source of truth. The flow that
produced v0.1.1:

```bash
# 1. Add a `## [X.Y.Z] - YYYY-MM-DD` section to CHANGELOG.md
#    (Added / Changed / Fixed / Removed) and bump version in
#    pyproject.toml. Both go in the release commit:
git add CHANGELOG.md pyproject.toml
git commit -m "chore(release): bump version to X.Y.Z"
git tag -a vX.Y.Z -m "Release X.Y.Z - <one-line summary>"

# 2. Clean and build (build deps via uvx, no global install needed):
rm -rf dist/ build/ *.egg-info
uvx --from build pyproject-build

# 3. Inspect what's actually inside the sdist before publishing.
#    The wheel only ships src/postgres_mcp; the sdist is allowlisted
#    in pyproject.toml [tool.hatch.build.targets.sdist], so anything
#    not in that list must NOT appear here — especially .env, .claude,
#    tasks/, or any other working-tree-only file:
tar -tzf dist/*.tar.gz | sort
.venv/bin/twine check dist/*

# 4. Push commit and tag:
git push
git push origin vX.Y.Z

# 5. Upload to PyPI. Twine's auth contract is TWINE_USERNAME /
#    TWINE_PASSWORD — not PYPI_TOKEN — so source .env to get
#    PYPI_TOKEN into the environment, then pass it via -u/-p so
#    the bridge is explicit. `set -a; source .env; set +a` keeps
#    the value confined to this shell; the token never enters
#    command line history or any tool's stdin/stdout:
set -a; source .env; set +a
.venv/bin/twine upload -u __token__ -p "$PYPI_TOKEN" dist/*
```

Notes:
- `uvx --from build pyproject-build` avoids needing `python -m build`
  installed system-wide; the project's hatchling backend is fetched
  into an isolated env.
- The sdist contents are controlled by an explicit allowlist in
  `[tool.hatch.build.targets.sdist].include`. Any new top-level
  file you add to the repo is excluded from the sdist by default —
  add it to the allowlist if it should ship. Treat the step-3
  `tar -tzf` listing as a release gate, not a curiosity.
- After tagging, optionally create a GitHub Release from the tag,
  using the matching `CHANGELOG.md` section as the body:
  `gh release create vX.Y.Z -t "vX.Y.Z" -F <(awk '/^## \[X.Y.Z\]/,/^## \[/' CHANGELOG.md | sed '$d')`.
- `CHANGELOG.md` follows
  [Keep a Changelog 1.1.0](https://keepachangelog.com/en/1.1.0/);
  the entry is mandatory before the version bump (treat it as a
  release gate alongside the `tar -tzf` listing).
- **Changelog audience & size.** Entries are for **users of the
  package**, not contributors. Each bullet answers "what do I do
  or expect differently?" — skip internal symbol names; those
  belong in the commit message. Size guide by SemVer level:
  *patch* (0.1.x → 0.1.y): 1–4 bullets, ≤ ~10 lines body;
  *minor* (0.x → 0.y): 3–8 bullets grouped Added/Changed/Fixed,
  ≤ ~25 lines; *major*: lead with a 1-paragraph migration note,
  then as long as it needs.

## License

MIT — see [LICENSE](./LICENSE). Forked from
[crystaldba/postgres-mcp](https://github.com/crystaldba/postgres-mcp) (MIT).
