KB-45B5 rev 6
22-P3-P1 — IU Gateway Policy Keys + Canonical Marker Patch Prompt (rev6)
19 min read Revision 6
pack-22p3p1gatewaypolicycanonical-markerpromptrev6
22-P3-P1 — IU Gateway Policy Keys + Canonical Marker Patch (rev6)
Date: 2026-05-06 | Status: PROMPT rev6 — chờ final approve Rev6: 1 fix — move temp table baseline inside BEGIN (ON COMMIT DROP + autocommit issue) Does NOT create trigger guard (P3-P2). Marker is wrong-door blocker, not security boundary.
#!/usr/bin/env bash
# 22-P3-P1 rev6 — L0 Policy + Canonical Marker Patch
set -uo pipefail
PG_CONTAINER="${PG_CONTAINER:-postgres}"
PG_USER="${PG_USER:-directus}"
PG_DB="${PG_DB:-directus}"
PSQL_CMD=(docker exec -i "$PG_CONTAINER" psql -v ON_ERROR_STOP=1 -U "$PG_USER" -d "$PG_DB")
PSQL_RO=(docker exec -i "$PG_CONTAINER" psql -v ON_ERROR_STOP=1 -U "$PG_USER" -d "$PG_DB" -tA)
LOG_PATH="/tmp/22-p3-p1.$(date -u +%Y%m%d-%H%M%S).log"
PSQL_EXIT=0
POST_EXIT=0
POST_STATUS="NOT_RUN"
LEAK_RESULT="NOT_RUN"
LEAK_STATUS="NOT_RUN"
KEY_COUNT="NOT_RUN"
make_uuid() {
if command -v uuidgen >/dev/null 2>&1; then uuidgen | tr 'A-F' 'a-f'
elif [ -r /proc/sys/kernel/random/uuid ]; then cat /proc/sys/kernel/random/uuid
else "${PSQL_RO[@]}" -c "SELECT gen_random_uuid()" 2>/dev/null; fi
}
early_stop() {
echo "STOP: $1" | tee -a "$LOG_PATH"
echo "=== FINAL VERDICT ===" | tee -a "$LOG_PATH"
echo "patch_state=${PATCH_STATE:-UNKNOWN}" | tee -a "$LOG_PATH"
echo "function_replace=NOT_RUN" | tee -a "$LOG_PATH"
echo "reason=$1" | tee -a "$LOG_PATH"
echo "phase_status=BLOCKED" | tee -a "$LOG_PATH"
echo "p3p2_readiness=BLOCKED" | tee -a "$LOG_PATH"
echo "=== UPLOAD REPORT NOW ===" | tee -a "$LOG_PATH"
exit 0
}
PILOT_ADDRESS="pilot.p3.p1.$(date -u +%Y%m%d-%H%M%S).$(make_uuid | cut -c1-8)"
echo "=== P3-P1 rev6 ===" | tee "$LOG_PATH"
echo "PILOT=$PILOT_ADDRESS" | tee -a "$LOG_PATH"
if ! [[ "$PILOT_ADDRESS" =~ ^pilot\.p3\.p1\.[0-9]{8}-[0-9]{6}\.[0-9a-f]{8}$ ]]; then
early_stop "invalid pilot address: $PILOT_ADDRESS"
fi
PATCH_STATE=$("${PSQL_RO[@]}" -c "
SELECT CASE
WHEN prosrc LIKE E'%set\\_config(''app.canonical\\_writer''%' THEN 'PATCHED_EXACT'
WHEN prosrc LIKE '%canonical_writer%' THEN 'UNKNOWN_PATCH_STATE'
ELSE 'UNPATCHED'
END FROM pg_proc WHERE proname='fn_iu_create';" 2>/dev/null | tr -d '[:space:]')
echo "patch_state=$PATCH_STATE" | tee -a "$LOG_PATH"
if [ "$PATCH_STATE" != "PATCHED_EXACT" ] && [ "$PATCH_STATE" != "UNPATCHED" ]; then
early_stop "invalid or unknown patch_state=$PATCH_STATE"
fi
set +e
{
# --- Preflight (non-mutating, before transaction) ---
cat <<'PREFLIGHT_SQL'
DO $$ DECLARE v_constraint int; v_index int; v_total int; BEGIN
SELECT count(*) INTO v_constraint FROM pg_constraint
WHERE conrelid = 'public.dot_config'::regclass AND contype IN ('p','u')
AND (SELECT count(*) FROM unnest(conkey) k JOIN pg_attribute a ON a.attrelid=conrelid AND a.attnum=k WHERE a.attname='key') = 1;
SELECT count(*) INTO v_index FROM pg_indexes
WHERE schemaname='public' AND tablename='dot_config'
AND indexdef LIKE '%UNIQUE%' AND indexdef LIKE '%key%'
AND indexdef NOT LIKE '%,%';
v_total := v_constraint + v_index;
IF v_total < 1 THEN RAISE EXCEPTION 'dot_config.key has no unique guard (constraint=% index=%)', v_constraint, v_index; END IF;
RAISE NOTICE 'dot_config.key guard: constraint=% index=% (total=%)', v_constraint, v_index, v_total;
END $$;
DO $$ DECLARE v_c int; BEGIN
SELECT count(*) INTO v_c FROM (
SELECT key FROM public.dot_config WHERE key LIKE 'iu_create.gateway.%' GROUP BY key HAVING count(*) > 1
) d;
IF v_c != 0 THEN RAISE EXCEPTION 'duplicate gateway keys: %', v_c; END IF;
END $$;
SELECT proname, md5(prosrc) AS source_hash_before, prosecdef, provolatile
FROM pg_proc WHERE proname = 'fn_iu_create';
PREFLIGHT_SQL
# --- BEGIN (temp table + policy + patch + pilot + verify = all-or-nothing) ---
cat <<'BEGIN_SQL'
BEGIN;
-- [rev6 fix] Trigger baseline INSIDE transaction (ON COMMIT DROP safe)
CREATE TEMP TABLE _p3p1_trg_baseline(k text primary key, v int) ON COMMIT DROP;
INSERT INTO _p3p1_trg_baseline(k, v) VALUES
('iu_triggers', (SELECT count(*) FROM pg_trigger WHERE tgrelid='public.information_unit'::regclass AND NOT tgisinternal)),
('uv_triggers', (SELECT count(*) FROM pg_trigger WHERE tgrelid='public.unit_version'::regclass AND NOT tgisinternal));
SELECT k, v FROM _p3p1_trg_baseline ORDER BY k;
BEGIN_SQL
# --- Policy keys (always, idempotent) ---
cat <<'POLICY_SQL'
INSERT INTO public.dot_config (key, value, description, updated_at) VALUES
('iu_create.gateway.mode', 'prepared', 'Gateway state: prepared (marker active, no guard yet)', now()),
('iu_create.gateway.canonical_function', 'public.fn_iu_create(text,text,text,text,text,text,text,text,uuid)', 'Canonical IU creation function', now()),
('iu_create.gateway.plan_function', 'public.fn_iu_create_plan(text,text,text,text,text,text,text,text,uuid)', 'Canonical IU plan/dry-run function', now()),
('iu_create.gateway.marker_key', 'app.canonical_writer', 'GUC key set by canonical writer', now()),
('iu_create.gateway.marker_value', 'fn_iu_create', 'Expected marker value', now()),
('iu_create.gateway.direct_insert_policy', 'block_after_guard', 'Direct INSERT blocked after trigger guard deployed', now()),
('iu_create.gateway.policy_doc_path', 'knowledge/dev/laws/dieu44-trien-khai/design/22-p3-iu-creation-gateway-scope.md', 'Gateway design document', now()),
('iu_create.gateway.readme_path', 'knowledge/dev/laws/dieu44-trien-khai/readme/iu-create-gateway-readme.md', 'Gateway README (to be created)', now()),
('iu_create.gateway.exempt_policy', 'none_active', 'No exemptions while in prepared mode', now())
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value, description = EXCLUDED.description, updated_at = now();
SELECT key, value FROM public.dot_config WHERE key LIKE 'iu_create.gateway.%' ORDER BY key;
POLICY_SQL
# --- Function patch (conditional) ---
if [ "$PATCH_STATE" = "UNPATCHED" ]; then
cat <<'FNPATCH_SQL'
CREATE OR REPLACE FUNCTION public.fn_iu_create(
p_canonical_address text,p_title text,p_body text,p_actor text,
p_unit_kind text DEFAULT NULL,p_section_type text DEFAULT NULL,
p_owner_ref text DEFAULT NULL,p_publication_type text DEFAULT NULL,
p_parent_ref uuid DEFAULT NULL
) RETURNS jsonb LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path=pg_catalog,public AS $$
DECLARE
v_iu_id uuid;v_uv_id uuid;v_hash text;v_identity jsonb;v_birth_ok boolean;
v_uk jsonb;v_st jsonb;v_pt jsonb;v_unit_kind text;v_section_type text;v_owner text;
v_existing jsonb;v_existing_status text;
v_preflight jsonb;v_fk_name text;v_fk_deferred boolean;
v_constraint_name text;v_ca_unique_guard text;v_guard_count int;
v_verify jsonb;
BEGIN
IF p_canonical_address IS NULL OR btrim(p_canonical_address)='' THEN RAISE EXCEPTION 'canonical_address required'; END IF;
IF p_title IS NULL OR btrim(p_title)='' THEN RAISE EXCEPTION 'title required'; END IF;
IF p_body IS NULL THEN RAISE EXCEPTION 'body required'; END IF;
IF p_actor IS NULL OR btrim(p_actor)='' THEN RAISE EXCEPTION 'actor required'; END IF;
v_preflight:=public.fn_iu_create_preflight();
IF v_preflight->>'status'!='pass' THEN RAISE EXCEPTION 'preflight: %',v_preflight; END IF;
v_fk_name:=v_preflight->>'fk_name';v_fk_deferred:=(v_preflight->>'fk_initially_deferred')::boolean;
SELECT count(*) INTO v_guard_count FROM pg_constraint c WHERE c.conrelid='public.information_unit'::regclass AND c.contype='u'
AND (SELECT count(*) FROM unnest(c.conkey))=1 AND (SELECT count(*) FROM unnest(c.conkey) k JOIN pg_attribute a ON a.attrelid=c.conrelid AND a.attnum=k WHERE a.attname='canonical_address')=1;
IF v_guard_count!=1 THEN RAISE EXCEPTION 'unique=%',v_guard_count; END IF;
SELECT conname INTO v_ca_unique_guard FROM pg_constraint c WHERE c.conrelid='public.information_unit'::regclass AND c.contype='u'
AND (SELECT count(*) FROM unnest(c.conkey))=1 AND (SELECT count(*) FROM unnest(c.conkey) k JOIN pg_attribute a ON a.attrelid=c.conrelid AND a.attnum=k WHERE a.attname='canonical_address')=1;
IF v_ca_unique_guard IS NULL THEN RAISE EXCEPTION 'guard name'; END IF;
v_uk:=public.fn_iu_resolve_default(p_unit_kind,'iu_create.default_unit_kind','vocab.unit_kind.');
IF v_uk->>'status' NOT IN ('explicit','default','auto_single') THEN RAISE EXCEPTION 'unit_kind: %',v_uk->>'message'; END IF;
v_unit_kind:=v_uk->>'value';
v_st:=public.fn_iu_resolve_default(p_section_type,'iu_create.default_section_type','vocab.section_type.');
IF v_st->>'status' NOT IN ('explicit','default','auto_single') THEN RAISE EXCEPTION 'section_type: %',v_st->>'message'; END IF;
v_section_type:=v_st->>'value';
IF p_publication_type IS NOT NULL AND btrim(p_publication_type)!='' THEN
v_pt:=public.fn_iu_resolve_default(p_publication_type,'__no_default__','vocab.publication_type.');
IF v_pt->>'status'!='explicit' THEN RAISE EXCEPTION 'publication_type: %',v_pt->>'message'; END IF; END IF;
v_owner:=COALESCE(NULLIF(btrim(COALESCE(p_owner_ref,'')),''),p_actor);
PERFORM pg_advisory_xact_lock(hashtext(btrim(p_canonical_address)));
v_existing:=public.fn_iu_classify_existing(btrim(p_canonical_address));
v_existing_status:=COALESCE(v_existing->>'status','not_found');
IF v_existing_status!='not_found' THEN
IF v_existing_status IN ('exists_missing_birth','exists_missing_version','exists_anchor_invalid','exists_duplicate_version','exists_unknown_state') THEN
RETURN v_existing||jsonb_build_object('action_required',true,'health_signal_needed',true); END IF;
RETURN v_existing; END IF;
IF NOT v_fk_deferred THEN EXECUTE format('SET CONSTRAINTS %I DEFERRED',v_fk_name); END IF;
PERFORM set_config('app.canonical_writer', 'fn_iu_create', true);
v_iu_id:=gen_random_uuid();v_uv_id:=gen_random_uuid();v_hash:=public.fn_content_hash(p_body);
v_identity:=jsonb_build_object('title',btrim(p_title),'owner_lookup_ref',v_owner,'primary_section_type_ref',v_section_type);
IF p_publication_type IS NOT NULL AND btrim(p_publication_type)!='' THEN
v_identity:=v_identity||jsonb_build_object('publication_type_ref',btrim(p_publication_type)); END IF;
BEGIN
INSERT INTO public.information_unit(id,canonical_address,unit_kind,owner_ref,created_by,updated_by,identity_profile,parent_or_container_ref)
VALUES(v_iu_id,btrim(p_canonical_address),v_unit_kind,v_owner,btrim(p_actor),btrim(p_actor),v_identity,p_parent_ref);
EXCEPTION WHEN unique_violation THEN
GET STACKED DIAGNOSTICS v_constraint_name=CONSTRAINT_NAME;
IF v_constraint_name=v_ca_unique_guard THEN
v_existing:=public.fn_iu_classify_existing(btrim(p_canonical_address));v_existing_status:=COALESCE(v_existing->>'status','not_found');
IF v_existing_status IN ('exists_missing_birth','exists_missing_version','exists_anchor_invalid','exists_duplicate_version','exists_unknown_state') THEN
RETURN v_existing||jsonb_build_object('action_required',true,'health_signal_needed',true); END IF;
RETURN v_existing;
ELSE RAISE; END IF; END;
INSERT INTO public.unit_version(id,unit_id,body,content_hash,version_seq,created_by) VALUES(v_uv_id,v_iu_id,p_body,v_hash,1,btrim(p_actor));
UPDATE public.information_unit SET version_anchor_ref=v_uv_id,content_anchor_ref=v_uv_id::text WHERE id=v_iu_id;
v_verify:=public.fn_iu_verify_invariants(btrim(p_canonical_address));
IF NOT COALESCE((v_verify->>'all_pass')::boolean,false) THEN
RAISE EXCEPTION 'fn_iu_create invariant fail: % — canonical path rollback',v_verify;
END IF;
RETURN jsonb_build_object('status','created','iu_id',v_iu_id,'uv_id',v_uv_id,'canonical_address',btrim(p_canonical_address),
'content_hash',v_hash,'birth_verified',true,'invariants_verified',true,
'unit_kind',v_unit_kind,'section_type',v_section_type,'version_seq',1);
END;$$;
FNPATCH_SQL
else
echo "-- function_replace=SKIPPED_ALREADY_PATCHED"
fi
# --- Post-patch audit + pilot ---
cat <<'POSTPATCH_SQL'
SELECT proname, md5(prosrc) AS source_hash_after, prosecdef, provolatile,
COALESCE(array_to_string(proconfig, ', '), '') AS config,
CASE WHEN prosrc LIKE '%canonical_writer%' THEN 'MARKER_PRESENT' ELSE 'MARKER_MISSING' END AS marker_check
FROM pg_proc WHERE proname = 'fn_iu_create';
DO $$ DECLARE v_c int; BEGIN
SELECT count(*) INTO v_c FROM information_schema.routine_privileges
WHERE routine_schema='public' AND routine_name IN ('fn_iu_create','fn_iu_create_plan') AND grantee='PUBLIC';
IF v_c > 0 THEN RAISE EXCEPTION 'PUBLIC has EXECUTE: %', v_c; END IF;
END $$;
DO $$ BEGIN
IF NOT has_function_privilege('directus', 'public.fn_iu_create(text,text,text,text,text,text,text,text,uuid)', 'EXECUTE') THEN
RAISE EXCEPTION 'directus lacks EXECUTE on fn_iu_create';
END IF;
IF NOT has_function_privilege('directus', 'public.fn_iu_create_plan(text,text,text,text,text,text,text,text,uuid)', 'EXECUTE') THEN
RAISE EXCEPTION 'directus lacks EXECUTE on fn_iu_create_plan';
END IF;
END $$;
SELECT routine_name, grantee, privilege_type
FROM information_schema.routine_privileges
WHERE routine_schema = 'public' AND routine_name IN ('fn_iu_create', 'fn_iu_create_plan')
ORDER BY routine_name, grantee;
POSTPATCH_SQL
cat <<PILOT_SQL
DO \$\$ DECLARE v_r jsonb; v_v jsonb; v_i jsonb; v_c bigint; v_a text; BEGIN
v_a := '$PILOT_ADDRESS';
v_r := public.fn_iu_create(v_a, 'P3-P1 Marker Test', 'Content by P3-P1.', 'agent:p3-p1-test');
IF v_r->>'status' != 'created' THEN RAISE EXCEPTION 'create: %', v_r->>'status'; END IF;
IF NOT COALESCE((v_r->>'invariants_verified')::boolean, false) THEN RAISE EXCEPTION 'inv_flag'; END IF;
v_v := public.fn_iu_verify_invariants(v_a);
IF NOT COALESCE((v_v->>'all_pass')::boolean, false) THEN RAISE EXCEPTION 'inv: %', v_v; END IF;
SELECT count(*) INTO v_c FROM public.information_unit WHERE canonical_address = v_a;
IF v_c != 1 THEN RAISE EXCEPTION 'IU=%', v_c; END IF;
SELECT count(*) INTO v_c FROM public.unit_version WHERE unit_id = (SELECT id FROM public.information_unit WHERE canonical_address = v_a);
IF v_c != 1 THEN RAISE EXCEPTION 'UV=%', v_c; END IF;
SELECT count(*) INTO v_c FROM public.birth_registry WHERE collection_name = 'information_unit'
AND entity_code = 'information_unit::' || (SELECT id FROM public.information_unit WHERE canonical_address = v_a)::text;
IF v_c != 1 THEN RAISE EXCEPTION 'br=%', v_c; END IF;
v_i := public.fn_iu_create(v_a, 'X', 'X', 'a');
IF v_i->>'status' != 'exists_complete' THEN RAISE EXCEPTION 'idem: %', v_i->>'status'; END IF;
SELECT count(*) INTO v_c FROM public.information_unit WHERE canonical_address = v_a;
IF v_c != 1 THEN RAISE EXCEPTION 'dbl'; END IF;
RAISE NOTICE 'P3-P1 ALL PASS: %', v_a;
END \$\$;
PILOT_SQL
cat <<'FINAL_SQL'
DO $$ DECLARE v_iu_b int; v_uv_b int; v_iu_a int; v_uv_a int; BEGIN
SELECT v INTO v_iu_b FROM _p3p1_trg_baseline WHERE k = 'iu_triggers';
SELECT v INTO v_uv_b FROM _p3p1_trg_baseline WHERE k = 'uv_triggers';
SELECT count(*) INTO v_iu_a FROM pg_trigger WHERE tgrelid='public.information_unit'::regclass AND NOT tgisinternal;
SELECT count(*) INTO v_uv_a FROM pg_trigger WHERE tgrelid='public.unit_version'::regclass AND NOT tgisinternal;
IF v_iu_b != v_iu_a THEN RAISE EXCEPTION 'IU triggers changed: % -> %', v_iu_b, v_iu_a; END IF;
IF v_uv_b != v_uv_a THEN RAISE EXCEPTION 'UV triggers changed: % -> %', v_uv_b, v_uv_a; END IF;
RAISE NOTICE 'triggers unchanged: IU=% UV=%', v_iu_a, v_uv_a;
END $$;
SELECT 'information_unit' AS tbl, count(*) FROM public.information_unit
UNION ALL SELECT 'unit_version', count(*) FROM public.unit_version
UNION ALL SELECT 'birth_iu', count(*) FROM public.birth_registry WHERE collection_name = 'information_unit';
COMMIT;
FINAL_SQL
} | "${PSQL_CMD[@]}" > "$LOG_PATH.sql" 2>&1
PSQL_EXIT=$?
echo "PSQL_EXIT=$PSQL_EXIT" | tee -a "$LOG_PATH"
cat "$LOG_PATH.sql" >> "$LOG_PATH"
cat "$LOG_PATH.sql"
set -uo pipefail
if [ $PSQL_EXIT -eq 0 ]; then
set +e
LEAK_RESULT=$("${PSQL_RO[@]}" -c "SELECT COALESCE(current_setting('app.canonical_writer', true), '(not set)');" 2>/dev/null | tr -d '[:space:]')
if [ "$LEAK_RESULT" = "(notset)" ] || [ "$LEAK_RESULT" = "" ] || [ "$LEAK_RESULT" = "(not set)" ]; then LEAK_STATUS="PASS"; else LEAK_STATUS="LEAK"; fi
POST_RESULT=$("${PSQL_RO[@]}" -v pilot_addr="$PILOT_ADDRESS" <<'POSTQL'
SELECT CASE WHEN COALESCE((v.vj->>'all_pass')::boolean,false) THEN 'PASS' ELSE 'CRITICAL' END
FROM (SELECT public.fn_iu_verify_invariants(:'pilot_addr') AS vj) v;
POSTQL
)
POST_EXIT=$?
POST_STATUS=$(echo "$POST_RESULT" | tr -d '[:space:]')
if [ "$POST_STATUS" != "PASS" ] && [ "$POST_STATUS" != "CRITICAL" ]; then POST_STATUS="INVALID_OUTPUT"; fi
KEY_COUNT=$("${PSQL_RO[@]}" <<'KEYQL'
WITH required(key) AS (VALUES
('iu_create.gateway.mode'),('iu_create.gateway.canonical_function'),('iu_create.gateway.plan_function'),
('iu_create.gateway.marker_key'),('iu_create.gateway.marker_value'),('iu_create.gateway.direct_insert_policy'),
('iu_create.gateway.policy_doc_path'),('iu_create.gateway.readme_path'),('iu_create.gateway.exempt_policy')
)
SELECT count(*) FROM required r JOIN public.dot_config d USING (key);
KEYQL
)
KEY_COUNT=$(echo "$KEY_COUNT" | tr -d '[:space:]')
if ! [[ "$KEY_COUNT" =~ ^[0-9]+$ ]]; then KEY_COUNT="INVALID_OUTPUT"; fi
set -uo pipefail
fi
echo "=== FINAL VERDICT ===" | tee -a "$LOG_PATH"
echo "patch_state=$PATCH_STATE" | tee -a "$LOG_PATH"
echo "function_replace=$([ "$PATCH_STATE" = "UNPATCHED" ] && echo 'EXECUTED' || echo 'SKIPPED_ALREADY_PATCHED')" | tee -a "$LOG_PATH"
echo "sql_exit=$PSQL_EXIT" | tee -a "$LOG_PATH"
echo "post_exit=$POST_EXIT" | tee -a "$LOG_PATH"
echo "post_commit_status=$POST_STATUS" | tee -a "$LOG_PATH"
echo "leak_status=$LEAK_STATUS" | tee -a "$LOG_PATH"
echo "gateway_keys=$KEY_COUNT" | tee -a "$LOG_PATH"
echo "pilot=$PILOT_ADDRESS" | tee -a "$LOG_PATH"
if [ $PSQL_EXIT -eq 0 ] && [ $POST_EXIT -eq 0 ] && [ "$POST_STATUS" = "PASS" ] && [ "$LEAK_STATUS" = "PASS" ] && [ "$KEY_COUNT" = "9" ]; then
echo "phase_status=PASS" | tee -a "$LOG_PATH"
echo "p3p2_readiness=READY" | tee -a "$LOG_PATH"
else
echo "phase_status=FAIL" | tee -a "$LOG_PATH"
echo "p3p2_readiness=BLOCKED" | tee -a "$LOG_PATH"
fi
echo "=== UPLOAD REPORT NOW ===" | tee -a "$LOG_PATH"
Report path
knowledge/dev/laws/dieu44-trien-khai/reports/22-p3-p1-iu-gateway-policy-and-canonical-marker-report.md
Hard Boundaries
❌ No trigger guard (P3-P2) — No GRANT/REVOKE — No role separation — No Directus changes — No cleanup — No Pack 2C
22-P3-P1 rev6 | 2026-05-06 | Temp table inside BEGIN | Chờ final approve.