O3: add grant script so future partitions inherit the app-role ACL

0f94115dc632bab44e7d8aec0cca403fb01f5ea2 · 2026-09-10 11:46:02 -0700 · Steve

The inline paste kept line-wrapping in the terminal, which split 'sudo -u' from
its argument. Same file-pipe pattern as o3-apply.sh: short command, no wrapping.

Sets ALTER DEFAULT PRIVILEGES so a partition created later by postgres is
automatically granted to the app role instead of silently 42501-ing writes to
that range, backfills any partition currently missing the grant, and verifies
both the ACL and that a real home detail page still returns 200.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01HpKbjp2febJ1r8BNTyZwvP

Files touched

A ops/o3-grant.sh

Diff

commit 0f94115dc632bab44e7d8aec0cca403fb01f5ea2
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Sep 10 11:46:02 2026 -0700

    O3: add grant script so future partitions inherit the app-role ACL
    
    The inline paste kept line-wrapping in the terminal, which split 'sudo -u' from
    its argument. Same file-pipe pattern as o3-apply.sh: short command, no wrapping.
    
    Sets ALTER DEFAULT PRIVILEGES so a partition created later by postgres is
    automatically granted to the app role instead of silently 42501-ing writes to
    that range, backfills any partition currently missing the grant, and verifies
    both the ACL and that a real home detail page still returns 200.
    
    Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01HpKbjp2febJ1r8BNTyZwvP
---
 ops/o3-grant.sh | 24 ++++++++++++++++++++++++
 1 file changed, 24 insertions(+)

diff --git a/ops/o3-grant.sh b/ops/o3-grant.sh
new file mode 100644
index 00000000..32fef6da
--- /dev/null
+++ b/ops/o3-grant.sh
@@ -0,0 +1,24 @@
+#!/usr/bin/env bash
+# TK-11363 — make future SourceEvidence partitions inherit the app-role grant.
+# Without this, any new partition created by postgres has no ACL for the app
+# role and writes to that range fail with 42501 "permission denied".
+set -euo pipefail
+P="sudo -u postgres psql -d homesonspec -v ON_ERROR_STOP=1"
+
+echo "== applying default privileges =="
+$P -c "ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO homesonspec;"
+
+echo "== backfill: any partition still missing the grant =="
+$P -tAc "SELECT c.relname FROM pg_class c WHERE (c.relname='SourceEvidence' OR c.oid IN (SELECT inhrelid FROM pg_inherits WHERE inhparent=(SELECT oid FROM pg_class WHERE relname='SourceEvidence'))) AND (c.relacl IS NULL OR NOT c.relacl::text LIKE '%homesonspec%');" \
+  | while read -r t; do [ -n "$t" ] && $P -c "GRANT ALL PRIVILEGES ON TABLE \"$t\" TO homesonspec;"; done
+
+echo "== verify: default ACL now set =="
+$P -tAc "SELECT defaclrole::regrole||' -> '||defaclacl::text FROM pg_default_acl;"
+
+echo "== verify: every partition has the app-role grant =="
+$P -tAc "SELECT count(*)||' partitions WITHOUT grant (want 0)' FROM pg_class c WHERE (c.relname='SourceEvidence' OR c.oid IN (SELECT inhrelid FROM pg_inherits WHERE inhparent=(SELECT oid FROM pg_class WHERE relname='SourceEvidence'))) AND (c.relacl IS NULL OR NOT c.relacl::text LIKE '%homesonspec%');"
+
+echo "== verify: live page still 200 =="
+HID=$($P -tAc "SELECT id FROM \"InventoryHome\" WHERE status='PUBLISHED' AND \"isDemo\"=false LIMIT 1;")
+curl -s -o /dev/null -w "  /homes/$HID -> %{http_code}\n" -m 25 "https://homesonspec.com/homes/$HID"
+echo "== done =="

← cc0a0516 O3: fix the two bugs the live run exposed (FK-before-attach · back to Homesonspec · O3b: ValidationEvent partition conversion script (corrected 3e4d499f →