Strict Service Table Isolation
Mandatory role drops + zero cross-component DB access

Two findings in Bootstrap and Architecture Violations motivate this plan:

1. recreate_database.sh does not drop cluster-level roles, so historical group memberships silently mask missing per-service GRANTs. The system is therefore not reproducible across machines.
2. Several service users hold cross-component table grants. The Service Account RBAC Design (2026-03-28-service-rbac-design.org) stipulated that cross-component access should be the exception.

This plan establishes a strict architectural invariant — a domain service user holds DB privileges on its own component's tables only — and lays out an incremental migration to reach it.

The heavier architectural work (IAM party cache, workflow/ORE NATS write APIs) is tracked separately in cross-service-write-decoupling.org.

For every domain service S with DB user ores_<env>_<S>_service:

1. S has SELECT/INSERT/UPDATE/DELETE only on tables matching ores_<S>_% in the public schema.
2. S has USAGE/SELECT on sequences only for its own tables.
3. S has no grants on any table prefixed with another component.
4. S has no membership of the broad rw_role or owner_role.
5. Cross-component validation (tenant existence, change-reason validity, refdata entity existence) is enforced by SECURITY DEFINER functions owned by the defining service — no SELECT grant on cross-component tables is needed by the calling service user.

Status: COMPLETE (commit 6eb3930e4)

recreate_database.sh now drops all environment roles before recreating them, ensuring reproducible cluster state regardless of history.

Status: COMPLETE (commit f94fda946)

ores_iam_validate_tenant_fn and ores_dq_validate_change_reason_fn converted to SECURITY DEFINER. 24 cross-component SELECT grants removed. Two confirmed-incorrect grants (compute→refdata_parties, reporting→scheduler prefix) also removed.

Remaining cross-component grants after Phase 1:

• IAM: SELECT ores_variability_system_settings_tbl (Phase 2.1)
• IAM: DML ores_refdata_parties (Phase 2.2 + deferred plan)
• Trading: SELECT ores_refdata_* prefix (Phase 3)
• Workflow: DML ores_iam_* and ores_refdata_parties (deferred plan)
• ORE: DML ores_workflow_* (deferred plan)

Effort: S–M. Risk: Low–Medium.

Effort: S. Risk: Low.

Trading table triggers contain inline SELECT statements against refdata tables (books, portfolios, counterparties, parties, business_units) for soft-FK validation. Because these run in a SECURITY INVOKER context, the trading service user holds a broad SELECT ON ores_refdata_* prefix grant.

Fix: wrap each inline SELECT in a named SECURITY DEFINER function.

Steps:

1. In projects/ores.sql/create/refdata/ create a new file refdata_trading_validation_fns_create.sql defining:

   • ores_refdata_validate_book_fn(tenant_id uuid, book_id uuid)
   • ores_refdata_validate_portfolio_fn(tenant_id uuid, portfolio_id uuid)
   • ores_refdata_validate_counterparty_fn(tenant_id uuid, counterparty_id uuid)
   • ores_refdata_validate_party_fn(tenant_id uuid, party_id uuid)
   • ores_refdata_validate_business_unit_fn(tenant_id uuid, bu_id uuid)

   Each function is SECURITY DEFINER with SET search_path = public, pg_temp.

2. Replace the inline SELECT blocks in:

   • trading_trades_create.sql
   • trading_trades_functions_create.sql
   • trading_trades_bu_functions_create.sql
   • trading_identifiers_create.sql
   • trading_party_roles_create.sql

   with calls to the corresponding SECURITY DEFINER functions.

3. Remove select _ores_grant_select_fn('ores_refdata_', :'trading_service_user') from the service registry and regenerate the grants file.

Status: COMPLETE

trade_status_service.cpp previously called dq::repository::fsm_transition_repository::find_by_id() directly, violating service isolation.

Fix applied:

1. trade_handler::save() calls dq.v1.fsm-transitions.list once per request via the private fetch_fsm_transitions() helper, returning a service::fsm_transition_map keyed by UUID.
2. The map is passed into trade_service::save_trades() → trade_status_service::resolve_status(), which looks up the transition by UUID without any DB access to ores_dq_fsm_transitions_tbl.
3. #include "ores.dq.core/repository/fsm_transition_repository.hpp" removed from trade_status_service.cpp.

Status: COMPLETE

Every domain service user now holds DML only on its own ores_<S>_* prefix, with no cross-component SELECT grants. The three remaining cross-component DML grants (IAM→refdata parties, workflow→IAM/refdata, ORE→workflow) require NATS write APIs and are deferred to cross-service-write-decoupling.org.

CLAUDE.md and system_model.org reflect current state.

The following items require significant NATS API work and multi-service refactoring. They are tracked in a separate plan: cross-service-write-decoupling.org

• IAM hot-path party reads (auth_handler, account_handler) replace direct ores_refdata_parties_tbl access with a NATS-backed cache.
• Workflow writes to IAM and refdata tables during onboarding → replace with IAM and refdata NATS write APIs.
• ORE import writes to workflow tables → replace with workflow NATS write APIs.

Bootstrap remains the one place where IAM legitimately crosses domain boundaries. After this plan:

1. SECURITY DEFINER provisioning function continues to handle tenant + system-party creation in a single transaction.
2. After Phase 2.2, the function returns system_party_id, so IAM no longer reads ores_refdata_parties_tbl during provisioning.
3. Bootstrap-mode gating is read via NATS from variability (Phase 2.1), so no DB grant is needed.

1. Schema-per-component. Moving to per-component Postgres schemas would make GRANTs trivial. Out of scope here; natural next step once the invariant holds.
2. Trading refdata coupling. The SECURITY DEFINER function approach keeps DB-level referential integrity for trading entities. Consider whether this is the right long-term model or whether trading should hold a local refdata cache instead.
3. Synthetic service. Currently classified role: "tooling" with broad SELECT on all domains. The cleaner option is to run it as the DDL user.