P1: pgschema dump emits owner-REVOKEs; #452 grant reconciliation replays them as superuser on every deploy (Aug 12 mass-REVOKE root cause)

[NOVA-Openclaw]

Summary

The Aug 12 mass-REVOKE (task #586, ~63+ nova tables, 5 other agents still broken) was a two-defect compound failure, root-caused by read-only forensics on 2026-08-13:

1. Generator defect: pgschema dump --user nova computes privileges as a diff against pg_default_acl treated as a universal baseline and skips the table owner's own ACL entry. Result: schema.sql contains 94 spurious REVOKE <default privs> FROM <owner> statements covering ALL 70 nova-owned tables (present since 995279c, 2026-05-07). A fresh dump today — with nova's live ACL fully restored — still emits all 94. Bonus: duplicate contradictory PRIVILEGE sections (e.g. agent_domains FROM newhart emitted twice).
2. Executor defect: the post-schema grant reconciliation step added for P1: pgschema apply silently drops table-level GRANT SELECT for nova on motivation_d100 and d100_roll_log — #448/#449 fixes never reach installed DB #452 (70642e3, PR D100 refinements + grant reconciliation + CTE alias fix (#444, #452, #453) #455) greps every ^GRANT|^REVOKE from schema.sql and pipes them into psql as superuser with output muted (>/dev/null 2>&1). pgschema apply also executes the REVOKE-half of privilege steps, so there are two overlapping paths executing the same spurious statements.

Trigger

Dormant Jul 10–Aug 11 (apply never ran). SE run #643's production deploy (~16:44 UTC Aug 12, nova-mind @ e202692 after the agent_chat extraction created real schema drift) finally ran apply → reconciliation → full REVOKE storm, silently.

Current live damage (verified 2026-08-13 00:30 UTC)

• scout: zero DML on 8 research_* tables
• athena: zero DML on 6 library_* tables
• iris: zero DML on artwork, music_analysis, music_library — Daily Art/Music pipelines fail on next write
• coder: zero DML on git_issue_queue
• newhart: degraded on 10 owned tables (functions partially via role membership)
• nova self-restored ~17:10 UTC Aug 12; memory-maintenance ghost_entity_cleanup fails: nova lacks DELETE on entity_credibility (FK cascade) #589's agent_domains/entity_credibility grants since applied by newhart

Recurrence: certain

Next deploy with any non-privilege schema diff replays the storm — re-breaking all owners AND reverting the #589 fix and hermes's social_interactions grant. The pg-notify listener re-dumps schema.sql on DDL changes, so hand-editing the file is futile — the generator regenerates the artifact.

Recommended remediation

1. Immediate (Newhart/Graybeard): restore owner DML for scout(8)/athena(6)/iris(3)/coder(1)/newhart(10)
2. Generator: post-process pgschema dump output (pg-notify-listener sync path) to strip REVOKE ... FROM <table-owner> lines + dedupe duplicate PRIVILEGE sections; file upstream pgschema bug
3. Executor: filter owner-revokes out of the P1: pgschema apply silently drops table-level GRANT SELECT for nova on motivation_d100 and d100_roll_log — #448/#449 fixes never reach installed DB #452 reconciliation pipe (join against pg_tables ownership) and unmute its output — a superuser REVOKE storm ran invisibly
4. Re-dump schema.sql after memory-maintenance ghost_entity_cleanup fails: nova lacks DELETE on entity_credibility (FK cascade) #589 grants so declared state matches RBAC intent

Related

• P1: pgschema apply silently drops table-level GRANT SELECT for nova on motivation_d100 and d100_roll_log — #448/#449 fixes never reach installed DB #452 (the reconciliation's origin), PR D100 refinements + grant reconciliation + CTE alias fix (#444, #452, #453) #455, installer: pgschema schema-apply step — permission denied on default privileges + destructive-change drift skips apply on peer installs #371 (pgschema apply privilege issues — likely same family), memory-maintenance ghost_entity_cleanup fails: nova lacks DELETE on entity_credibility (FK cascade) #589 (collateral of this event — fold its fix into this remediation), task Extract agent_chat message bus to NOVA-Openclaw/agent-chat (#579) #586 (forensics notes appended)