Story: Strict service table isolation
Table of Contents
This page documents a story in Sprint 17. It captures the goal, current status, acceptance criteria, and the tasks that compose it.
Goal
Enforce strict service table isolation: add SECURITY DEFINER to cross-service validators, eliminate NAMEDATALEN truncation warnings on indexes and RLS policies, replace IAM's direct DB reads on refdata party tables with a NATS-backed party cache (Phase 4.3), remove dead DML grants from workflow and ORE services (Phases 5.2/5.3), and fix refdata validate functions to use the correct tenant scope.
Status
| Field | Value |
|---|---|
| State | DONE |
| Parent sprint | Sprint 17 |
| Now | Completed 2026-05-16. |
| Waiting on | None. |
| Next | None. |
| Last touched | 2026-05-16 |
Acceptance
- All tasks complete; PR-by-PR breakdown in
git log.
Tasks
| Task | State | Start | End | Description |
|---|---|---|---|---|
| Task: [sql] Strict service table isolation: SECURITY DEFINER validators + mandatory role drops | DONE | 2026-05-22 | 2026-05-22 | This PR implements the strict service table isolation invariant described in |
| Task: [sql] Eliminate NAMEDATALEN truncation warnings for indexes and RLS policies | DONE | 2026-05-22 | 2026-05-22 | - Strip redundant `ores_<service>_` prefix from all PostgreSQL index names and RLS policy names across the SQL schema (~ |
| Task: [iam][refdata] Phase 4.3: IAM party cache via NATS, remove cross-service DB reads | DONE | 2026-05-22 | 2026-05-22 | - Removes direct IAM→refdata DB reads by introducing an in-process party cache backed by NATS |
| Task: [sql] Phase 5.2: remove dead workflow DML grants on IAM and refdata tables | DONE | 2026-05-22 | 2026-05-22 | - Verifies `provision_parties` workflow routes all writes through NATS (`refdata.v1.parties.save`, `iam.v1.accounts.save |
| Task: [sql] Phase 5.3: remove dead ORE DML grant and workflow.core link | DONE | 2026-05-22 | 2026-05-22 | - Verifies `ore_import_handler` submits workflows exclusively via `nats_.js_publish(start_workflow_message::nats_subject |
| Task: [sql] Fix refdata validate functions: use p_tenant_id not system_tenant_id | DONE | 2026-05-22 | 2026-05-22 | - `ores_refdata_validate_monetary_nature_fn`, `ores_refdata_validate_currency_market_tier_fn`, and `ores_refdata_validat |
Decisions
- NATS party cache
- replacing IAM's direct DB reads on refdata party tables with a NATS-backed cache is the canonical approach to cross-service data sharing: no cross-service SQL grants, no tight DB coupling.
- Mandatory role drops
- adding mandatory
REVOKE/ role drop sequences to the migration scripts ensures the isolation invariant is mechanically enforced, not just documented.
Out of scope
- Cross-service read patterns for entities other than party (deferred to later isolation phases).