Agent-First PSQL v0.9.0: Making the Illegal Invocation Unwritable

A CLI has two ways to tell an agent that an invocation is wrong. It can accept the arguments, run, and fail with a message. Or it can refuse to recognize the invocation at all.

The first way is what most tools do, and it is where afpsql had accumulated its worst behavior: a runtime compatibility matrix for container drivers, checks that --sql and --sql-file were not both present, a rule that --dry-run silently won over --stream-rows, flags that a particular transport quietly ignored. Every one of those is a sentence in a document that an agent has to have read. v0.9.0 moves them into the shape of the command line itself, where an agent discovers them by being refused.

A registry, not a parser with rules bolted on

The clap surface is gone. In its place is a closed registry of 17 commands and 23 shapes, and an invocation runs only when it matches exactly one shape.

What that buys is not tidiness. It is the deletion of a whole class of check:

• --sql xor --sql-file is two shapes, not a validation.
• --stream-rows changes the event lifecycle, so it is its own pair of shapes with its own output contract — which is why --output-to split is not refused by a rule anymore, it is simply not in that shape’s contract.
• --inline-max-rows is a no-op while streaming and --batch-rows is a no-op when not, so each is listed only on the shapes where it acts.
• --explain and --explain-analyze were two flags that conflicted; they are now one argument with a closed value set, --explain plan|analyze.
• --mode pipe ignoring --sql is not a documented quirk; the pipe shape has no --sql.

Connection arguments were clap globals. A closed registry has none, so they are declared on root and on every inspect leaf — and deliberately not on the psql and skill admin commands, which previously accepted and ignored them.

Rejections classify themselves. cli_unknown_argument, cli_unknown_command, cli_unregistered_combination, cli_missing_argument_value, cli_invalid_argument_value, cli_duplicate_argument, cli_unexpected_positional, and cli_invalid_utf8 all exit 2 with stdout left empty, so an agent branches on the code instead of reading a sentence. cli_unregistered_combination is the interesting one: it means every argument was individually known but the mix is not a registered shape. The response is to read the shapes in --help, not to start dropping arguments and retrying.

One --help per command is now the entire answer for that command — every shape, with its optional arguments and closed value sets inline. There is no second level to ask for and no recursive mode. afpsql --docs renders the whole registry as Markdown, for reading rather than for calling.

The raw-argv scanner that used to install --output-to before the real parser ran is deleted; the stream redirect is installed from the resolved plan instead, so a rejected command line no longer creates a file on its way to being rejected.

Secrets are one typed argument

v0.7.0 added config-file secret sources and, with them, nine flags: three slots × (--*-secret, --*-secret-env, --*-secret-config). The config variant took two space-separated values, which the closed registry has no representation for, and naming only one half was previously ignored rather than refused.

Three arguments replace all nine:

afpsql --dsn env:DATABASE_URL --sql 'select 1'
afpsql --dsn file:config.yaml#database.url --sql 'select 1'
afpsql --conninfo file:.env#PG_CONNINFO --sql 'select 1'
afpsql --host localhost --user app --dbname app \
  --password file:.env#PGPASSWORD --sql 'select 1'

A source is one typed value: a literal, env:NAME, file:PATH#DOT_PATH, or literal:VALUE when the literal itself begins with a source prefix. Mutual exclusion between the three forms is no longer a rule to enforce — there is one argument, and it holds one source. A malformed source is refused rather than partially interpreted, by both the canonical registry and the psql-compatible translator.

The redaction guarantee tightens honestly rather than being restated. A literal source is necessarily present in argv, and the docs now say so and point at env: or file: when process arguments are observable. Resolved values still never enter a temporary environment variable, startup logs, errors, or config responses, and runtime config output still renders them as ***.

The write boundary that the README already promised

The README has said “writes must opt in with explicit permission” for several releases. In pipe mode, inside an explicit transaction, that was not true.

begin never checked that the resolved permission was a write permission. read_only defaulted to false. And once a transaction was open, execute_in_open_tx ignored per-query permissions entirely. Three JSONL lines with no permission field anywhere could delete rows:

{"code":"begin","id":"b"}
{"code":"query","id":"q","sql":"delete from orders"}
{"code":"commit","id":"c"}

In v0.9.0 begin asserts a write-family permission, read_only defaults to true, and the transaction’s read-only state is recorded and re-checked on every query — so a read-write transaction must be acknowledged by each statement in it, not once at the top. A query that declares a read permission inside a read-write transaction is rejected before SQL execution, and a read-only explicit transaction stays enforced by PostgreSQL even if a query supplies a write permission.

A driver cannot be asked for an option it does not have

Container transport had --container NAME, --container-driver DRIVER, and a set of option flags that applied to some drivers and were ignored by others — mediated by roughly ninety lines of runtime compatibility matrix. --container-user did nothing under the kubectl driver, because kubectl exec has no exec-as-user option, and finding that out required running the command.

Flags are now one per (driver, option) pair:

afpsql --container-docker-name pg --dsn env:DATABASE_URL --sql 'select now()'
afpsql --container-kubectl-pod pg --container-kubectl-namespace prod --sql 'select now()'
afpsql --container-compose-service db --container-compose-file docker-compose.yml --sql 'select now()'

The driver is inferred from the prefix family, so --container and --container-driver are gone and the matrix is deleted. There is no --container-kubectl-user to write, which is a stronger statement than a matrix entry saying it would be ignored. Mixing two families names two drivers and is rejected with an error naming both offending flags. Illegal combinations are unknown arguments at parse time.

SSH stops opening a local port

The SSH transport used to start an OpenSSH local forward — a TcpListener on 127.0.0.1, a readiness poll, a settle delay — and connect PostgreSQL through it. Anything else running on the workstation could reach that port for as long as it was open.

Every SSH connection now rides the ssh child process’s own stdin and stdout. afpsql opens no local listening port, and no other process on the machine can reach the connection. The cost is stated plainly in the reference: the host running the bridge needs sh plus one of python3, python, or perl, tried in that order, and a host with none of them fails with exit 127 and a message naming all three. In an --ssh-via chain only the final host needs one.

That bridge source also had to keep working where python is still Python 2. socket.error did not become an alias for OSError until Python 3.3, so the bridge’s except OSError let connection failures escape as a traceback on exactly the older hosts most likely to need the bridge in the first place.

When a narrower decoder produced the rows

Rows are normally encoded by PostgreSQL itself through a JSON wrapper, so numeric, timestamptz, uuid, and interval keep their exact server representation. Utility statements such as EXPLAIN and SHOW cannot sit inside that wrapper. The explicit-transaction paths had lost their fallback for those, so EXPLAIN inside a transaction failed as a wrapper syntax error whose error position pointed into SQL the caller never wrote.

The fallback is restored, and the degradation is now something a caller can observe: a query.row_encoding_degraded log event with structured sqlstate and message fields, rather than the Rust Debug dump of an error type that had no Display at all. Ask for it with --log query.row_encoding_degraded whenever exact value fidelity matters. A column the narrow decoder cannot represent fails loudly instead of being approximated, so a kind:"result" remains trustworthy — the log only tells you which decoder produced it.

The streaming sink now starts only after the wrapper is known usable, so a wrap failure can no longer emit result_start with no result_end.

Smaller, and one gate

--param values still pass to PostgreSQL as text by default, but bare null, true, and false now bind as JSON primitives. Write text:null when the literal string is what you meant.

CI no longer runs a narrower suite than the release does. It runs the release gate itself, with the container end-to-end suite folded into all — and that suite no longer returns early and reports green when its environment is unset, which is the failure mode that let a removed flag survive in CI at all.

Upgrading

v0.9.0 rewrites the CLI surface. Scripts need edits:

• arguments must follow their subcommand — afpsql inspect tables --dsn ..., never the reverse. Nothing is global.
• --dsn-secret V → --dsn V; --dsn-secret-env NAME → --dsn env:NAME; --dsn-secret-config FILE PATH → --dsn file:FILE#PATH. Same for --conninfo and --password.
• --container NAME + --container-driver docker → --container-docker-name NAME, and likewise for podman, nerdctl, compose, and kubectl.
• --explain → --explain plan; --explain-analyze → --explain analyze.
• a value is never taken from a token starting with -; SQL that looks like a flag is written --sql=<value>.
• pipe begin now requires read_only:false plus a write-family permission for a read-write transaction, and every query inside it must repeat that permission. A begin that relied on the old defaults becomes read-only.
• skill_admin emitted a literal cli_error code; it is now skill_error.

Anything that was already explicit — a read query with a named permission, a discrete-field SSH connection, --mode psql scripts — is unaffected. The invocations that break are the ones that were relying on a check afpsql no longer needs to make.