AGENT SKILL

psql-automation

psql from scripts — error propagation, docker wrappers.

License 许可
MIT
最近更新
2026-11-14
Stars
★ 4
Forks
1

01 前言 Front Matter

name
psql-automation
author
Steadfaste Fleet (curator)
license
MIT
version
1.0.0
category
devops
platforms
[ "linux", "macos" ]
description
psql from scripts — error propagation, docker wrappers.

02 资源文件 3

03 SKILL.md 正文

psql Automation — Error Propagation & Verification

Trigger

Use when:

  • Writing a script (Python/shell) that calls psql against a dockerized Postgres
  • A script prints "✅ success" but the DB row never appears — silent write failure
  • Debugging why todo-db.py-style CLIs report success for queries that error
  • A design doc or peer agent claims "schema verified — table exists" and you're about to build on it
  • You need to inspect data inside a STOPPED postgres container's volume
  • Security-reviewing a psql call path (injection, shell embedding, RLS/authz)

Core pitfall: stdin-mode psql swallows SQL errors (rc=0)

psql -t -A <<< "SELECT ..." or subprocess.run(..., input=query) (stdin mode) returns exit code 0 even when the query fails — relation not found, function missing, syntax error, all of them. A script that checks returncode != 0 then prints "✅ Todo added" is silently lying: the row never landed.

Verified 2026-08-06: todo-db.py printed "✅ Todo added" for months while bus.todos never existed on the migrated DB. Every write was a silent no-op.

The fix: use -c mode

# BROKEN — stdin mode, errors swallowed (rc always 0 on SQL failure)
result = subprocess.run(["psql", "-t", "-A", "-F", "||"], input=query, ...)

# FIXED — -c mode propagates the real psql exit code
result = subprocess.run(["psql", "-t", "-A", "-F", "||", "-c", query], ...)
if result.returncode != 0:
    print(result.stderr, file=sys.stderr); sys.exit(1)

-c mode exits non-zero on query error → the script's returncode check actually fires. Belt-and-suspenders: also scan stderr for ERROR:.

Wrapper note: sg docker -c is NOT the problem — stdin mode is

Early diagnosis blamed sg docker -c for masking the inner rc. Clean no-pipe tests (2026-08-06) proved otherwise — sg propagates rc correctly when the query is embedded via -c:

Invocation rc on SQL error
docker exec ... psql ... -c "<query>" (direct) 1 ✓
sg docker -c "docker exec ... psql ... -c '<query>'" 1 ✓ (propagates)
echo "<query>" | docker exec ... psql (stdin) 0 ✗
echo "<query>" | sg docker -c "docker exec ... psql" (stdin) 0 ✗

The single root cause is stdin-mode psql returning rc=0 on SQL failure; the wrapper is irrelevant. The earlier "sg masks rc" observation was a measurement artifact — the command was piped through head, so $? was head's exit code, not sg's/psql's. Always check rc WITHOUT a pipe.

Two safe patterns:

  1. Direct docker exec when the user is in the docker group (check with docker exec <container> true; group membership via id -nG | grep docker):

    ["docker", "exec", "-i", "mycortex-postgres", "psql", "-U", "mycortex", "-d", "mycortex", "-t", "-A", "-F", "||", "-c", query]
    

    rc propagates correctly. -c is fine HERE because the query is an argv element — no shell interprets it.

  2. Universal safe pattern — SQL via STDIN + -v ON_ERROR_STOP=1 (works on BOTH paths, kills the rc=0 lie AND the shell-embedding risk):

    # Query flows through stdin — the command string contains ONLY fixed
    # names/flags, never user data → not shell-injectable.
    cmd = ["sg", "docker", "-c",
           "docker exec -i mycortex-postgres psql -U mycortex -d mycortex "
           "-v ON_ERROR_STOP=1 -t -A -F '||'"]
    proc = subprocess.run(cmd, input=sql, capture_output=True, text=True, timeout=30)
    if proc.returncode != 0 or "ERROR:" in proc.stderr:
        sys.exit(1)
    

    With ON_ERROR_STOP=1, psql exits rc=3 on SQL error even in stdin mode — the rc=0-swallows-errors failure is structurally impossible, and no user input ever reaches a shell. This is the pattern used by ops/services/tasks/migrate.py and the rewritten task-db.py (2026-08-06).

    ⚠️ Shell-quote the -F separator inside an sg string: -F '||' (unquoted -F || becomes shell OR → "Syntax error: end of file unexpected").

    ❌ NEVER embed -c {query!r} into an sg/shell command string. Python's repr() quoting is NOT POSIX shell quoting: a ' inside the value closes the shell string and appends arbitrary commands. SQL injection escalates to arbitrary shell command execution as the agent user on any host where the script falls back to the sg path. Verified exploitable in todo-db.py (2026-08-06 party, Security role, finding B-1).

sg docker dies under systemd hardening — never make it the only path

A harness whose systemd unit sets NoNewPrivileges=true (standard hardening) cannot reach a dockerized Postgres through the sg docker -c "docker exec …" path: sg/newgrp calls setgroups()/setgid() unconditionally, and NoNewPrivileges makes those fail — even when the process already holds the docker group. The store then reports "memory unavailable / store not reachable", which reads as a DB outage and is not one.

Reproduce faithfully without systemd (keeps the real group list, unlike systemd-run, which drops supplementary groups and fails for the wrong reason):

setpriv --no-new-privs -- sg docker -c "docker exec -i <c> psql …"
#   -> setgid: Operation not permitted   (rc 1)
setpriv --no-new-privs -- docker exec -i <c> psql …
#   -> works: the process is already in the group, no setgid needed

Diagnosis signature: the unit has NoNewPrivileges=yes, the store's Linux _cmd() returns ["sg", "docker", "-c", …], and Store.available() returns False → the bare "unavailable" string with no (error) suffix (the real exception is swallowed by available()). Check the serving unit, not just your shell: a sibling service without hardening keeps working, so "it works for me" proves nothing. systemctl --user show <unit> -p NoNewPrivileges -p PrivateTmp.

Fix: prefer the direct-docker exec path when the process is already in the docker group (check os.getgroups()), keeping sg only as the fallback for hosts that genuinely lack the group. Do not "fix" it by dropping NoNewPrivileges — that trades a security control for a convenience, and the sg indirection is unnecessary whenever the group is already held.

Express the fix as an ordered LADDER, not as a swapped-in command. A single _cmd() that returns one argv cannot express "try this, then that": give the seam an _attempts(role) -> [(argv, env), …] in preference order, keep _cmd() returning the FIRST form so existing callers still work, and have the runner walk the ladder — collecting EVERY failure reason and raising them together ("docker: … | sg: …"), so one line still tells "the container is down" from "the hardening blocks us". Apply it to every copy of the seam (store module AND plugin wrapper) in the same change.

Keep the fail-open contract, but RECORD why. available() must still return False rather than raise — but a bare "unavailable" is indistinguishable from a DB outage, so store the reason on the instance (self.last_error) as the runner walks the ladder, and let diagnostics read it. A fail-open that discards its cause turns a misconfiguration into an outage report.

Existing guards that pin the OLD argv shape must be rewritten to intent, not to the new literal. Tests asserting a prefix (cmd[:3] == ["sg", "docker", "-c"], cmd[3] being the inner shell string) encode one implementation: after the swap they fail on correct code. Rewriting them to the NEW prefix is nearly as bad — a prefix assertion still cannot see the regression that matters. Re-express each as the properties the guard exists for, checked across the whole ladder: the direct form comes first and contains no group switch, EVERY form is non-interactive (-w), and the sg fallback still EXISTS (dropping it silently breaks every host whose user is not in the docker group). Grep the test tree for the old shape before shipping — the guards are usually in more than one file.

Careful reading, not just careful writing (2026-10-08): sg fails only because of NoNewPrivileges. Under setpriv --no-new-privs the sg form exits rc=1 with setgid: Operation not permitted — so a piped query reports "X absent" for a command that never ran, and a later || exit 1 turns a capability failure into a false negative on the data itself. Assert on the producer's exit code first, and only then parse its output. Apply the same rule to every wrapper whose failure is silent by design (a swallowed store available(), a 2>/dev/null). Reading a failure as absence is how a diagnosis becomes a wrong conclusion.

Every harness-invoked psql must be non-interactive: always pass -w

psql prompts for a password on /dev/tty whenever no usable credential is available (MYFLEET_MEM_PASSWORD empty, wrong or missing PGPASSFILE). That prompt is invisible to the caller — it is neither stdout nor stderr — so a script or an agent harness running psql sees NO output and blocks instead of failing. A fail-open contract then reads the call as "store unavailable" only after its timeout, or never, and in a TUI harness the prompt text lands in the user's input area.

Rule: every psql invocation a script builds carries -w (--no-password) — a child that cannot answer a prompt must never be given the chance to ask. Same family: ssh -o BatchMode=yes, sudo -n, git with GIT_TERMINAL_PROMPT=0; a non-tty child that prompts is a hang, not an error.

Check EVERY copy of the command builder, not just the file you were pointed at: the platform branches differ (macOS runs psql directly; Linux goes through sg docker -c "docker exec … psql …") and the same seam is often duplicated in a store module and a plugin wrapper. Put -w with the other option flags (before -v ON_ERROR_STOP=1) in all of them.

A flag assertion is necessary but NOT sufficient. Pair it with a runtime test: put a psql stand-in on PATH that implements the documented contract — with -w it exits non-zero immediately ("no password supplied"); without -w it takes the prompt path and blocks — then drive the REAL code path (the store's run_sql) with no usable password and assert it raises StoreUnavailable promptly. Keep the test non-vacuous by asserting on the auth-refusal text, not only on the duration, so it fails when the flag is dropped.

Do not expect a live before/after demo to be easy: demonstrating the prompt needs a password-requiring endpoint the host can actually reach (published container ports may not be reachable from the host namespace at all) — state that limitation rather than claiming the demo ran.

Verify "verified" schema claims before building on them

A design doc or peer's "Existing plumbing (verified YYYY-MM-DD)" is a claim, not proof. Five-minute check:

# current DB
docker exec -i mycortex-postgres psql -U mycortex -d mycortex -t -A \
  -c "SELECT schemaname||'.'||tablename FROM pg_tables WHERE tablename LIKE '%todo%';"
# functions too
docker exec -i mycortex-postgres psql -U mycortex -d mycortex -t -A \
  -c "SELECT proname FROM pg_proc WHERE proname LIKE '%todo%';"
# migration dump (if one exists) — pg_restore -l lists objects; grep for the table
pg_restore -l ~/.steadfaste-fleet/backups/*.dump | grep -i todo

If the table/function is missing from BOTH the current DB and the migration dump, it never existed — the CLI's "✅" was the rc=0 lie. Fix the schema (idempotent CREATE TABLE IF NOT EXISTS + CREATE OR REPLACE FUNCTION), then fix the CLI's psql invocation, then re-test with a real add/list.

⚠️ Verify against EVERY agent type, not just your host (2026-08-06)

A schema that exists on the ORCHESTRATOR host may not exist on WORKERS. The bus schema is created by setup-fleet-bus.sh — a register_orch (orchestrator-only) script. Workers (Gisu, Joseph, Kustos, Titus) run mycortex-postgres (the doctor checks the container on every host) but never get the bus schema. Building a "fleet-wide" feature on bus.* silently locks out every worker — the 2026-08-06 todo-system flaw: bus.todos never existed on workers, and even the orchestrator's CLI printed ✅ while rows vanished.

Check the role matrix, not just your DB: docs/bus-architecture.md lists what each agent type RUNS (workers: HTTP client only — never the bus Postgres/schema). Before designing around a schema, verify it will exist on the hosts that must use it.

Rule (Luke directive 2026-08-06): features ALL agents should have must not depend on bus infrastructure or bus nomenclature. Give them a dedicated schema (todos.*, not bus.*) applied on every host's mycortex-postgres via fleet-update.sh, with a platform-aware apply path — docker exec on Linux, direct psql via mycortex.conf/config.json on macOS.

Inspect a stopped container's data volume

The old container is Exited (0) (not removed) — its volume is intact. Query it WITHOUT port conflicts by running a temp container on the same volume:

docker run --rm -d --name inspect \
  -v <old-volume>:/var/lib/postgresql/data <same-image>   # no -p publish = no host port conflict
sleep 7   # wait for "database system is ready to accept connections" in docker logs
docker exec -i inspect psql -U <user> -d <db> -t -A -c "<query>"
docker rm -f inspect

⚠️ The mount path is image-major-version specific. postgres:16- and earlier expect the volume at /var/lib/postgresql/data; postgres:18+ stores data in a major-versioned subdirectory (/var/lib/postgresql/18/…) and crash-loops on startup if you mount at the old /var/lib/postgresql/data path (Error: in 18+, … appears to be PostgreSQL data in /var/lib/postgresql/data (unused mount/volume)). For 18+, mount at /var/lib/postgresql and use the SAME image major version that wrote the volume — inspecting a 16 volume with an 18 image (or vice versa) fails for the same reason.

Pitfalls: don't pass -c port=NNNN to postgres — that changes the listening port while psql inside still tries the 5432 socket; and don't pipe the query through head when checking rc (head's rc masks psql's).

Pitfalls

  • Injection defense for psql-backed CLIs (2026-08-06, task-db.py rewrite):

    • Allowlist every identifier-ish value (agent, project, repo, target, assignee, scope, status, source, column) against ^[A-Za-z0-9][A-Za-z0-9._-]{0,63}$ + enum allowlists (personal|fleet, pending|in_progress|completed|cancelled, …) BEFORE it touches SQL.
    • Never string-build WHERE clauses. Route all DML through a parameterized function (tasks.task_upsert(...)) with positional params.
    • Free text (content, tags) is quote-doubled (' → '') into string literals only — safe under standard_conforming_strings=on (PG default).
    • Extract build_query(sql, params) -> sql as a pure function — that's the unit-test seam for injection regressions (--agent "x' OR 1=1--" must return a validation error, not rows).
  • column and position are PostgreSQL RESERVED words — must be quoted as "column"/"position" in EVERY query. Unquoted column fails with "syntax error at or near "column"". Cost two migration re-runs (2026-08-06).

  • INSERT ... ON CONFLICT DO UPDATE evaluates NOT NULL and RLS WITH CHECK on the PROPOSED insert row BEFORE conflict resolution — a status-only update passing NULL content fails with "null value in column". Fix inside the upsert function: COALESCE defaults for non-nullable columns (scope/status/ project/source) so partial updates propose a valid row, and COALESCE(p_content, (SELECT content FROM t WHERE id = p_id)) to fetch the existing content. ON CONFLICT DO UPDATE SET x = COALESCE(EXCLUDED.x, t.x) so NULL params don't clobber existing values.

  • Role creation in migrations needs CREATEROLE — only the DB owner has it. Run DDL as the owner (house precedent: mycortex migrate.py connects as mycortex), not a dedicated admin role, or the migration fails with "permission denied to create role". Granting CREATEROLE to a non-owner role is cluster-wide surface — prefer owner-run DDL.

  • Read a function's argument list from the DATABASE before hand-writing a positional call. SELECT pg_get_function_arguments('tasks.task_upsert'::regproc) prints the real order and TYPES in one line; the call sites you are copying from do not. Two casts in that signature look interchangeable and are not: p_id and p_parent_id are uuid while p_session_id is TEXT (fleet session ids look like cron_1954deb5ad76_20261015_142148), so a uuid cast one slot early fails the whole write with invalid input syntax for type uuid — and a mocked psql cannot see it. Give the writers ONE shared SQL constant once a second caller appears.

  • An id list in = ANY(...) needs its element type cast. id = ANY(ARRAY[?]) dies with operator does not exist: uuid = text when the param builder renders text literals; write id = ANY(ARRAY[?]::uuid[]) — the array literal is the builder's job, the cast is yours.

  • Any ||-delimited row format breaks the moment a FIELD'S CONTENT can contain ||. The separator is only safe when every field is drawn from a constrained alphabet. When one of them is free text a worker wrote (a review note, a task description), that text can contain the delimiter, and the row shifts: field 3's tail lands in field 4, counts and judgements come out wrong, and the parser cannot tell a shifted row from a good one. The same class bites text[] columns read with array_to_string(tags, ',') — a comma in a tag corrupts the list.

    Do not fix it by picking a rarer separator (chr(31), \x1f, a UUID-ish token) — that is a race against the content, and it leaves the parser unable to PROVE a row was intact. Fix it at the source: emit each row as a single JSON value and parse it as JSON.

    -- BROKEN: content containing '||' shifts every field after it
    SELECT id, content, plan, created_by FROM tasks.slices;
    
    -- FIXED: one JSON value per row — the separator can no longer collide with content,
    -- and json_build_object escapes whatever the content contains
    SELECT json_build_object(
             'id', id, 'content', content,
             'plan', plan, 'created_by', created_by)::text
      FROM tasks.slices;
    
    # The parser then rejects a malformed row instead of silently mis-splitting it
    def _parse_row(line: str) -> dict | None:
        try:
            return json.loads(line)
        except json.JSONDecodeError:
            return None          # caller must STOP, not treat the row as empty
    

    ::text matters: without it psql renders the object with its own spacing, and a caller comparing raw lines against a fixture sees a difference that is not a bug.

    This changes the FIXTURES, so the suite goes red in the same commit. Every test row fixture still feeding the old || text must be rewritten to JSON before the change can land — and the case worth writing is the one that used to break: a row whose content ITSELF contains ||, asserted to still parse with every field intact. If the fixtures are not updated yet, park the whole change (see adversarial-finding-fix-patterns: never commit a red suite into a shared repo) rather than landing production alone.

    Make an unreadable row STOP the sweep, not vanish. A parser that returns None and a caller that skips None silently reports "no slices to review" for a query that returned rows — the same read-a-failure-as-absence trap as the rc=0 lie above. Exit with a distinct code and a message naming the row format.

  • Round-trip a text[] column through a non-comma separator. ||-delimited row output splits fields on the delimiter, so read tags as array_to_string(tags, chr(31)) and split on "\x1f" — a comma corrupts any tag that contains one. (Narrower case of the JSON-row rule above: prefer JSON when the field's content is unconstrained; a non-comma separator is only safe for constrained values.)

  • --source-style filters never grant access — RLS is the enforcement; a filter is just a filter.

  • Idempotent schema files are your friend — IF NOT EXISTS + CREATE OR REPLACE means you can apply on every deploy/update; wire it into the update script so Linux (docker exec) and macOS (direct psql via config URL) both converge.

  • When a path-hardcoding bug is fixed in ONE of two sibling files, grep for the sibling. 2026-08-06: session_mine.py was fixed to write ~/brain/lessons/, but lessons.py (the module the index/search imports) still hardcoded ~/brain/kustos/lessons/ — offline search read a stale 241-file dir while 631 live lessons sat elsewhere. git log -S '<bad-string>' finds every file that ever contained it.

  • now() is STABLE, not IMMUTABLE — it cannot appear in a partial-index WHERE predicate. CREATE UNIQUE INDEX ... WHERE ... > now() fails with ERROR: functions in index predicate must be marked IMMUTABLE. Drop the time condition from the index and filter at query time; the "one live row" uniqueness is then enforced by the app, not the DB (note the trade-off). A DB-level time-varying guarantee needs an EXCLUDE constraint or trigger, never a partial unique index with now().

  • Check psql via the -t -A flags when parsing — column headers break naive parsers on macOS without -t -A.

References

  • references/todo-silent-failure-2026-08-06.md — full session trace: the todo-db.py rc=0 bug, sg masking, old-container inspection, dream→todo bridge build, and the bus.todos-never-existed diagnosis.
  • references/psql-cli-security-hardening.md — injection-proof psql CLI rewrite: allowlists, stdin+ON_ERROR_STOP pattern, profile-role CRUD + RLS WITH CHECK, guarded table-scoped migration, verification transcript (2026-08-06 task-db.py / tasks schema).

04 同仓库更多 Skills