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
psqlagainst 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:
-
Direct docker exec when the user is in the docker group (check with
docker exec <container> true; group membership viaid -nG | grep docker):["docker", "exec", "-i", "mycortex-postgres", "psql", "-U", "mycortex", "-d", "mycortex", "-t", "-A", "-F", "||", "-c", query]rc propagates correctly.
-cis fine HERE because the query is an argv element — no shell interprets it. -
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 byops/services/tasks/migrate.pyand the rewrittentask-db.py(2026-08-06).⚠️ Shell-quote the
-Fseparator 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'srepr()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 understandard_conforming_strings=on(PG default). - Extract
build_query(sql, params) -> sqlas a pure function — that's the unit-test seam for injection regressions (--agent "x' OR 1=1--"must return a validation error, not rows).
- Allowlist every identifier-ish value (
-
columnandpositionare PostgreSQL RESERVED words — must be quoted as"column"/"position"in EVERY query. Unquotedcolumnfails with "syntax error at or near "column"". Cost two migration re-runs (2026-08-06). -
INSERT ... ON CONFLICT DO UPDATEevaluates 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, andCOALESCE(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_idandp_parent_idareuuidwhilep_session_idisTEXT(fleet session ids look likecron_1954deb5ad76_20261015_142148), so a uuid cast one slot early fails the whole write withinvalid 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 withoperator does not exist: uuid = textwhen the param builder renders text literals; writeid = 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 bitestext[]columns read witharray_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::textmatters: 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 (seeadversarial-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
Noneand a caller that skipsNonesilently 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 asarray_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 REPLACEmeans 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.pywas fixed to write~/brain/lessons/, butlessons.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 withERROR: 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 withnow(). -
Check
psqlvia the-t -Aflags 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
macbook-linux-maintenance
Legacy MacBook Linux fixes: GPU wedge, reboot hang, thermal.
governance-regression-triage
Triage a host's governance issues: what changed.
enforcement-immutability
Use when enforcement files lose their immutable flag.
steadfaste-fleet-repo-deploy
Use when committing to the steadfaste-fleet repo.
steadfaste-fleet-maintenance
Maintain an installed Steadfaste Fleet instance — update both the upstream Hermes Agent and the fleet repo layer, merge diverged histories, sync skills safely, and troubleshoot common issues. Covers the dual-update workflow (hermes update + fleet-update.sh), encrypted brain data backup, mycortex import, and recovery techniques.
fleet-preflight
DEPRECATED alias — merged into survey-before-action (2026-08-20). Load survey-before-action instead; its "Repo-Specific Pre-Flight" section covers git search, Hermes boundary, and deployment verification.