-- ============================================================================= -- File 04: PG role context_pack_readonly (P10 rev 2) + postgres_fdw + trigger function (P11 rev 2) -- Target DBs: directus + incomex_metadata (cluster-level + per-DB GRANTs + FDW in incomex_metadata) -- Run as: workflow_admin (superuser — needed for CREATE ROLE, CREATE EXTENSION, ALTER DEFAULT PRIVILEGES cross-DB) -- Idempotent: YES (DO blocks + IF NOT EXISTS + CREATE OR REPLACE + DROP-RECREATE trigger) -- Spec: Đ43 v1.2 FINAL rev 2 §11 Bước 6.4 (P10 GRANT DEFAULT PRIVILEGES cross-DB) -- + §11 Bước 12 P11 (trigger function body đọc dot_config runtime) -- ============================================================================= -- DOT-MIGRATION-APPLY-OK -- Author: Claude CLI, S178 Fix 12 Phase 1.5 -- Date: 2026-04-17 -- Order: Run AFTER Files 02+03 (requires dot_config + section_definitions seeded)

-- ARCHITECTURAL NOTE: -- dot_config lives in DB 'directus'. Trigger on kb_documents fires in DB -- 'incomex_metadata'. Cross-DB read via postgres_fdw foreign table. FDW -- credentials piggyback on pg_hba trust auth (local socket). -- =============================================================================

---

-- CLUSTER-LEVEL: CREATE ROLE context_pack_readonly

---

DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='context_pack_readonly') THEN CREATE ROLE context_pack_readonly LOGIN NOINHERIT; COMMENT ON ROLE context_pack_readonly IS 'Đ43 v1.2 P10 rev 2 — read-only role cho SQL executor §5.8. NOINHERIT để ngăn escalate.'; END IF; END $$;

---

-- DB directus: GRANT + DEFAULT PRIVILEGES cho context_pack_readonly

---

\c directus

GRANT CONNECT ON DATABASE directus TO context_pack_readonly; GRANT USAGE ON SCHEMA public TO context_pack_readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO context_pack_readonly;

-- ★ P10 rev 2: DEFAULT PRIVILEGES — tương lai tables mới tự có quyền đọc ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO context_pack_readonly; ALTER DEFAULT PRIVILEGES FOR ROLE directus IN SCHEMA public GRANT SELECT ON TABLES TO context_pack_readonly; ALTER DEFAULT PRIVILEGES FOR ROLE workflow_admin IN SCHEMA public GRANT SELECT ON TABLES TO context_pack_readonly;

---

-- DB incomex_metadata: GRANT + DEFAULT PRIVILEGES + postgres_fdw + trigger function

---

\c incomex_metadata

GRANT CONNECT ON DATABASE incomex_metadata TO context_pack_readonly; GRANT USAGE ON SCHEMA public TO context_pack_readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO context_pack_readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO context_pack_readonly; ALTER DEFAULT PRIVILEGES FOR ROLE incomex IN SCHEMA public GRANT SELECT ON TABLES TO context_pack_readonly; ALTER DEFAULT PRIVILEGES FOR ROLE workflow_admin IN SCHEMA public GRANT SELECT ON TABLES TO context_pack_readonly;

-- FDW: cross-DB read for trigger function CREATE EXTENSION IF NOT EXISTS postgres_fdw;

DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_foreign_server WHERE srvname='directus_srv') THEN CREATE SERVER directus_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'directus', port '5432'); END IF; END $$;

-- User mapping PUBLIC uses trust auth (local socket). Pattern idempotent. DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_user_mappings WHERE srvname='directus_srv' AND usename='workflow_admin') THEN CREATE USER MAPPING FOR workflow_admin SERVER directus_srv OPTIONS (user 'directus'); END IF; IF NOT EXISTS (SELECT 1 FROM pg_user_mappings WHERE srvname='directus_srv' AND usename='incomex') THEN CREATE USER MAPPING FOR incomex SERVER directus_srv OPTIONS (user 'directus'); END IF; END $$;

-- Foreign table dot_config → directus.dot_config DROP FOREIGN TABLE IF EXISTS dot_config; CREATE FOREIGN TABLE dot_config ( key TEXT, value TEXT, description TEXT, updated_at TIMESTAMPTZ ) SERVER directus_srv OPTIONS (schema_name 'public', table_name 'dot_config');

COMMENT ON FOREIGN TABLE dot_config IS 'Đ43 v1.2 P11 rev 2 — foreign table mirror directus.dot_config. Trigger fn_context_pack_on_law_enact reads patterns runtime via this.';

---

-- Trigger function — P11 rev 2: body đọc patterns RUNTIME từ dot_config

---

CREATE OR REPLACE FUNCTION fn_context_pack_on_law_enact() RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, pg_catalog AS $$ DECLARE p TEXT; v_raw TEXT; BEGIN SELECT value INTO v_raw FROM dot_config WHERE key = 'context_pack_watched_key_patterns';

IF v_raw IS NULL THEN RAISE WARNING 'fn_context_pack_on_law_enact: context_pack_watched_key_patterns missing in dot_config — skip fire'; RETURN NEW; END IF;

FOR p IN SELECT jsonb_array_elements_text(v_raw::jsonb) LOOP IF NEW.key LIKE p THEN PERFORM pg_notify('context_pack_event', json_build_object('kind','law_enact','key',NEW.key,'pattern',p)::text); RETURN NEW; END IF; END LOOP; RETURN NEW; END; $$;

ALTER FUNCTION fn_context_pack_on_law_enact() OWNER TO workflow_admin;

COMMENT ON FUNCTION fn_context_pack_on_law_enact() IS 'Đ43 v1.2 §11 Bước 12 P11 rev 2 — patterns đọc RUNTIME từ dot_config. UPDATE dot_config có hiệu lực ngay, không cần DROP+CREATE trigger.';

-- Attach trigger — idempotent via DO block (tránh DROP+CREATE xung đột fn_evt_trigger_guard_drop) DO $guard$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname='trg_context_pack_law_enact' AND NOT tgisinternal) THEN CREATE TRIGGER trg_context_pack_law_enact AFTER INSERT OR UPDATE OF key, data ON kb_documents FOR EACH ROW EXECUTE FUNCTION fn_context_pack_on_law_enact(); END IF; END $guard$;

---

-- Post-check (both DBs via \c)

---

\c directus

SELECT 'role_context_pack_readonly_exists=' || CASE WHEN EXISTS(SELECT 1 FROM pg_roles WHERE rolname='context_pack_readonly') THEN 'YES' ELSE 'NO' END; SELECT 'directus_readonly_has_connect=' || has_database_privilege('context_pack_readonly','directus','CONNECT')::text; SELECT 'directus_default_priv_for_directus=' || COUNT(*)::text FROM pg_default_acl pa JOIN pg_roles r ON r.oid = pa.defaclrole WHERE r.rolname='directus' AND pa.defaclnamespace=(SELECT oid FROM pg_namespace WHERE nspname='public');

\c incomex_metadata

SELECT 'fdw_extension_installed=' || CASE WHEN EXISTS(SELECT 1 FROM pg_extension WHERE extname='postgres_fdw') THEN 'YES' ELSE 'NO' END; SELECT 'foreign_server_directus_srv=' || CASE WHEN EXISTS(SELECT 1 FROM pg_foreign_server WHERE srvname='directus_srv') THEN 'YES' ELSE 'NO' END; SELECT 'foreign_table_dot_config=' || CASE WHEN EXISTS(SELECT 1 FROM information_schema.foreign_tables WHERE foreign_table_name='dot_config') THEN 'YES' ELSE 'NO' END; SELECT 'trigger_fn_exists=' || CASE WHEN EXISTS(SELECT 1 FROM pg_proc WHERE proname='fn_context_pack_on_law_enact') THEN 'YES' ELSE 'NO' END; SELECT 'trigger_attached=' || CASE WHEN EXISTS(SELECT 1 FROM pg_trigger WHERE tgname='trg_context_pack_law_enact') THEN 'YES' ELSE 'NO' END; SELECT 'fdw_read_test=' || value FROM dot_config WHERE key='context_pack_mode';