# SPIKE PP-1411: Hydrate Workstation Roles for Authorization Checks https://theorchard.atlassian.net/browse/PP-1411 ## Preferred Option Option 2 ## Background Clients with **Workstation Admin** and **Workstation Catalog** roles need to perform `bulk_create digital_audio`. To support this, ows-pdp must hydrate `workstation_roles` onto the Cerbos **Principal** before calling Cerbos. The hydration architecture was established in PP-1388 — this spike answers the open question left there: *which service provides workstation roles, and how?* ### Where workstation roles live An identity's workstation roles are stored in two consistent locations: - **Neo4j** — `LabelProfile`-type `Profile` nodes with a `roles` attribute (e.g. `["administrator", "catalog"]`). The `ProfileId` matches `art_relations.vend_contact.vend_contact_id`. - **MySQL** (`art_relations`) — `vend_contact_roles` table, FK'd to `vend_contact` and `vendor_roles`. The roles we care about are `administrator` and `catalog`. A user can hold both simultaneously. --- ## Option 1: Policy-driven fetch from ows-permissions ### How it works The `PolicyMetadataDatabase` being built in PP-1388 tracks which resource types require `account_feature_controls`. This option extends it to also track `workstation_roles` requirements. `CerbosPolicyParser.build_from_policy_dir` is updated to detect `importDerivedRoles: workstation_roles` in Cerbos YAML files, and a new `requires_workstation_roles(resource_type)` method surfaces the result. The gate mechanism mirrors PP-1388 exactly: - **CI** — `seed_policy_metadata_cache` crawls the Cerbos policy dir and writes the updated database to Redis. - **Startup** — each Fargate task deserializes from Redis via `datasources_lifespan`. No filesystem access at request time. - **Request time** — if any resource in the batch has `requires_workstation_roles == True`, ows-pdp calls a new ows-permissions endpoint once to fetch workstation roles, then injects them via `_build_principal`. ### ows-permissions endpoint The new endpoint returns `LabelProfile` roles for an identity — similar to the existing `get_my_adminable_resources` handler (`permissions/handlers/handlers.py:459`), which already queries Neo4j via `get_resources_for_identity_uuid`. ``` GET /identity/{identity_uuid}/workstation-roles/ ``` Response: ```json { "roles": ["administrator", "catalog"], "vendor_uuid": "..." } ``` ### ows-pdp changes - Extend `CerbosPolicyParser.build_from_policy_dir` to detect `importDerivedRoles: workstation_roles`. - Add `requires_workstation_roles(resource_type: str) -> bool` to `PolicyMetadataDatabase`. - Add `get_workstation_roles_for_identity` to `pdp/connectors/ows_permissions.py`. - Add `_fetch_workstation_roles_if_needed` in `pdp/logic/cerbos.py`, mirroring `_hydrate_resources_with_feature_controls_as_needed` from PP-1388. ### Pros / Cons **Pros** - Extends PP-1388 infrastructure already in flight — no new gate mechanism to design. - ows-permissions already has the data and Neo4j query patterns. - Policy-driven gate ensures we only pay the round-trip cost when actually needed. **Cons** - Adds an endpoint the other team must build and maintain. - Still an extra synchronous HTTP call on the hot path (~30× traffic endpoint). - ows-permissions has its own Redis cache; stale data is possible if invalidation lags. --- ## Option 2: Kafka CDC dual-write → DynamoDB ### How it works The Neo4j Kafka Source Connector (CDC) streams changes to `LabelProfile` nodes into a Kafka topic. A Lambda consumes those events, resolves `identity_uuid` via ows-permissions, then writes workstation roles into the `pp_identity` DynamoDB table alongside existing PDP roles. At authorization time, workstation roles are already in DynamoDB. ows-pdp reads them from the same place it reads everything else — **no extra HTTP call on the hot path**. ### DynamoDB role format Workstation roles land in the existing `roles` list on `pp_identity`: ```json { "roles": { "L": [ { "M": { "role": { "S": "contract_viewer" } } }, { "M": { "role": { "S": "workstation_catalog" } } }, { "M": { "role": { "S": "workstation_admin" } } } ] } } ``` ### Role name mapping Neo4j role names map to PDP role names as follows: | Neo4j `LabelProfile` role | PDP role written to DynamoDB | |---------------------------|------------------------------| | `administrator` | `workstation_admin` | | `catalog` | `workstation_catalog` | ### Resolving identity_uuid from a profile event A CDC event on a `Profile` node gives `Profile.uuid`, but we need both `identity_uuid` and `vendor_uuid` to write the correct `pp_identity` row. A new batch ows-permissions endpoint handles this: ``` POST /lookup/profiles/identity/uuids/ ``` Request: ```json { "uuids": ["profile-uuid-1", "profile-uuid-2"] } ``` Response (ordered, dataloader pattern): ```json { "profiles": [ { "profile_uuid": "profile-uuid-1", "identity_uuid": "...", "vendor_uuid": "..." } ] } ``` The handler runs this Neo4j query. Profile UUIDs with no match are omitted (Lambda treats as no-op): ```cypher MATCH (i:Identity)-[:HAS_PROFILE]-(p:Profile)-[:HAS_ACCESS_TO]-(v:Vendor) WHERE p.uuid IN $profile_uuids RETURN i.uuid AS identity_uuid, p.uuid AS profile_uuid, v.uuid AS vendor_uuid ``` ### Lambda per-event steps 1. Extract `profile_uuid` and updated `roles` array from CDC payload. 2. Call `POST /lookup/profiles/identity/uuids/` to resolve `identity_uuid` and `vendor_uuid`. 3. Diff incoming roles against the known set (`administrator`, `catalog`) to produce `roles_to_attach` / `roles_to_detach`. 4. Call `OwsPdpClient.attach_detach_roles_by_identity_tenant` to write the update. ### Concurrent writers and optimistic locking Option 2 introduces three concurrent writers to the same `pp_identity` row (`identity_uuid + tenant_uuid`): - Settings app / SEAT — synchronous attach-detach via the ows-pdp API - The new Lambda — async writes from Kafka CDC events - The backfill — ad-hoc population of existing workstation roles `DynamoDbConnector.update_item` currently issues a blind `SET` with no `ConditionExpression`. The Lambda's async writes can race with simultaneous Settings/SEAT calls, causing lost updates. The `IdentityTenant` schema (`pdp/fastapi/schemas/identity.py:131`) already carries a `version` field — we should add optimistic locking on this field using a DynamoDB `ConditionExpression`, incrementing on each write and retrying on `ConditionalCheckFailedException`. This must be resolved before shipping Option 2 and should cover the backfill as well. ### Known issue: CDC topic filtering (from PP-1107) Jess Chung explored a similar CDC approach in PP-1107 ([spike doc](https://www.notion.so/SPIKE-PP-1107-ows-pdp-is-the-source-of-truth-for-ows-permissions-rap-admin-role-26b97177520f808cbeccfb9cea4a1c68)). Two issues are unresolved: - A custom CDC topic targeting `SettingsProfile + HAS_ADMIN_ACCESS_TO + Resource` was configured ([terraform PR #26903](https://github.com/theorchard/terraform-infra/pull/26903)) but never produced messages, while the generic `cdc.musicGraphV5.hasAdminAccessTo` topic worked fine. Custom filter/pattern matching may not be supported. - It's unclear if a single topic event contains all needed data (Identity + Profile + roles + Vendor), which is why the Lambda-side lookup step is required. ### Pros / Cons **Pros** - No extra call on the hot path — workstation roles are in DynamoDB alongside PDP roles. - Covers all Neo4j write paths (graphql-user, DB PRs, neo4j refresh). - Aligns with the desired long-term architecture from PP-1107. **Cons** - Significant new infrastructure: Kafka topic, Lambda, IAM roles, DynamoDB update logic, backfill script. - CDC filter issue from PP-1107 is unresolved — may need generic topic + Lambda-side filtering. - Eventually consistent — brief windows of stale authorization are possible. - Optimistic locking must be implemented before shipping. --- ## Comparison | | Option 1 (On-demand fetch) | Option 2 (Kafka CDC → DynamoDB) | |---|---|---| | Hot path latency | Extra HTTP call when needed | No extra call | | Infrastructure | New ows-permissions endpoint | New Kafka topic + Lambda + backfill | | Consistency | Synchronous / always current | Eventually consistent | | Write path coverage | ows-permissions Neo4j | All Neo4j write paths | | Implementation complexity | Low–Medium | High | | Known risks | Hot path load at 30× traffic | CDC filter issue (PP-1107) | --- ## Recommendation **Start with Option 1** for faster delivery and lower risk, with Option 2 as the longer-term target. Option 1 is immediately unblocked and reuses known patterns in both ows-pdp and ows-permissions. The hot-path concern is real but mitigated by the policy-driven gate — only requests containing a `workstation_roles`-dependent resource type pay the extra round-trip. Option 2's CDC filtering issue from PP-1107 is unresolved and would require dedicated investigation before we can rely on it. Option 1 can be replaced by Option 2 later if hot-path latency becomes a measured problem. --- ## Open Questions 1. **Neo4j role coverage** — `administrator` is confirmed written to `LabelProfile`. Is `catalog` also written as a role on the node, or only stored in `vend_contact_roles`? 2. **CDC topic filtering** — Can the Neo4j Kafka Source Connector filter by node property values (e.g. `profileType = 'LabelProfile'`)? Or must we consume the full relationship topic and filter in Lambda? 3. ~~**DynamoDB role naming**~~ — **Resolved**: `workstation_admin` and `workstation_catalog`, matching `cerbos/policies/derived_roles/workstation_roles.yml`. 4. **Cerbos policy scope** — `cerbos/policies/derived_roles/workstation_roles.yml` already exists. Which resource policies currently import it, and is that work part of this ticket or a separate one?