Agent-First PSQL v0.8.0: The DSN Crosses the Boundary Intact

An application that lives behind an SSH boundary usually stores its database credentials the same way every other application does: as one connection string in one config file. Until v0.8.0, afpsql made an agent take that string apart to get through the boundary. --ssh accepted discrete fields only, so the DSN had to be split — in the shell, almost always — into --host, --port, --user, --dbname, and a password source. The skill said as much, in plain words: avoid --dsn-secret and --conninfo-secret with --ssh.

That instruction asked the agent to do the one thing v0.7.0 had just made unnecessary everywhere else. v0.8.0 removes it.

One connection string, any transport

afpsql --ssh user@server \
  --dsn-secret-config config.yaml database.url --sql 'select now()'

afpsql parses the DSN locally, in-process, into a typed connection config — the same one the direct path uses. The SSH tunnel then takes exactly one thing from it: the PostgreSQL host and port to reach from the far side of the boundary. Everything else stays attached to the connection. The same holds for --container, and for --ssh and --container combined.

The distinction matters more than it sounds. Previously the tunnel rebuilt the session from --host/--port, which is why a connection string could not survive it — there was nothing to survive in. Now the local tunnel replaces only the network endpoint, and the parsed connection keeps its user, password, database, startup options, application name, connect timeout, TCP user timeout, keepalive settings, target_session_attrs, channel binding, and sslmode. A DSN that carries ?options=-csearch_path%3Dapp&connect_timeout=10 means the same thing through a tunnel as it does directly.

TLS gets the same care. When the tunnel forwards to a named host, afpsql connects to the local forwarded address but keeps the original hostname on the connection, so PostgreSQL TLS still sees the server’s real name rather than 127.0.0.1. And both stdio bridges — the SSH one behind --ssh-via / --ssh-sudo-user, and the container one — now negotiate the same supported TLS modes as every other path. Previously each forced an unencrypted connection regardless of sslmode, which meant a transport that had just learned to read a full DSN would quietly ignore the sslmode in it.

One endpoint, stated as such

A boundary transport is one tunnel to one place, and a DSN with a failover host list is several places. v0.8.0 does not silently pick the first one. It fails:

{"kind":"error","error":{"code":"connect_failed","message":"SSH transport supports a single PostgreSQL host and port; the connection source resolved to multiple targets","hint":"SSH and container transports currently target one PostgreSQL endpoint; use a DSN/conninfo with one host, or choose one host explicitly with discrete connection fields","retryable":false}}

Not retryable, with the fix in the hint. Discrete connection fields remain fully supported and are the answer when an application DSN lists failover hosts.

Where events go now follows what the command is

Through v0.7.x, afpsql’s contract was blunt: stdout is the protocol, stderr is not a runtime channel. Every event — results, errors, logs, progress — went to one stream, and a caller who wanted just the rows had to filter by kind.

v0.8.0 replaces that with a rule that reads the invocation instead of asking the caller. A plain query emits at most one terminal event and exits, so it is a finite command and splits by kind:

rows=$(afpsql --sql 'select id from users')   # the result, and nothing else

The result payload is what a caller captures, so it goes to stdout. Errors, logs, and progress are diagnostics and go to stderr. Both remain strict Agent-First Data envelopes tagged by top-level kind — the split is about routing, not about degrading diagnostics into prose. A failed run now leaves stdout empty with a structured error on stderr, so command substitution can never mistake a failure for data.

Streaming is the other case, and it is genuinely different. --mode pipe and --stream-rows emit an interleaved multi-event stream whose consumer reads it in order, so every event goes to one stream — stdout by default:

afpsql --mode pipe < requests.jsonl    # one ordered stream, branch on kind

That is not a convenience default. Splitting a stream across stdout and stderr destroys the ordering that makes it a stream: a pipe consumer reading stdout would see requests q1 and q3 succeed and never learn that q2 failed in between. Worse for --stream-rows, the row batches ride in result_rows while the terminator is result_end — split them and the actual rows land on the diagnostic stream while stdout receives a row-less terminator. So --output-to overrides the destination but cannot override the mode: asking a stream for split is a usage error rather than a silent reordering.

The destination is resolved from a raw-argument scan before the parser runs, so it governs every emission: not just query results, but startup argument errors, stream-redirect failures, --help, and --version.

Long flags only, so psql’s shorts stay psql’s

The canonical afpsql surface now has no short aliases. --help, --version, --output, and --host are spelled out; -h, -V, and -o are rejected with a structured invalid_request. Psql-compatible shorts keep working exactly where they mean what a psql user expects — inside --mode psql and the managed psql wrapper, where -h is the host, not help.

This resolves a genuine collision rather than merely tidying the surface. In psql, -o / --output names an output file. In canonical afpsql, --output selects the rendering format. One spelling, two incompatible meanings, and previously psql mode quietly resolved it in afpsql’s favor — a script that passed -o results.txt got a rendering-format error at best, and a misread argument at worst. Psql mode now rejects both spellings outright and points at the flags that do the job: --stdout-file for process-level redirection, --output in canonical mode for json|yaml|plain.

--version is also now a structured event rather than a line of text, carrying name, display_name, version, and build under result.code: "version".

A locked profile is now the whole connection

afpsql-readonly-NAME plus a root-owned /etc/afpsql/readonly-profiles/NAME.json exists so a host can authorize one executable instead of trying to parse flags in a shell rule: the administrator fixes the endpoint, and the agent supplies only SQL. v0.7.0 enforced that against the command line, rejecting every connection and transport flag.

It did not enforce it against the environment. resolve_pg_config consulted AFPSQL_DSN_SECRET before it ever read the profile’s host and port, so a caller who could set one variable could point a “locked” executable at any database — or set PGSSLMODE=disable to strip TLS from a connection it did not own. The ordinary afpsql-readonly is documented to accept those variables, because it lets the caller choose a target; the locked profile inherited that behavior along with it, which is exactly the freedom the profile exists to remove.

In v0.8.0 a locked profile reads no connection environment variables at all. Put every field it needs in the profile file, including the password, and use dsn_secret there when you need an sslmode. Nothing changes for the ordinary readonly executable.

Upgrading

afpsql 0.8.0 changes the CLI surface, so it is not a drop-in for scripts that used the removed spellings:

• replace -o json with --output json, -h with --help, -V with --version in canonical mode — --mode psql and the psql wrapper are unaffected
• a script that read every event from stdout keeps working for --mode pipe and --stream-rows, which still put the whole stream there; for a plain query it now gets the result on stdout and diagnostics on stderr, which is usually what it wanted
• --output-to split is rejected with --mode pipe or --stream-rows
• psql mode no longer accepts -o / --output; use --stdout-file
• runtime config secret metadata renames its fields to config_file_path and dot_path, matching the Agent-First Data naming convention
• a locked readonly profile that relied on environment variables for part of its connection must move those fields into the profile file

Nothing changes for existing SSH or container invocations that already pass discrete connection fields — they behave as before. The new capability is that they no longer have to.