[SECURITY] Ex-members can still view (and access) org properties after leaving an organization

[CRamsan]

Summary

After a user leaves an organization (via the Leave Organization flow, #544), they can still view — and likely still fetch/update/delete — properties belonging to the organization they just left. This is a broken-access-control bug, not a UI/cache staleness issue: the backend genuinely still authorizes and returns the data.

Root cause

Two compounding backend defects:

1. getUserRole ignores membership status.
   SupabaseOrganizationDatastore.getUserRole(userId, orgId) (edifikana/back-end/src/main/kotlin/com/cramsan/edifikana/server/datastore/supabase/SupabaseOrganizationDatastore.kt:198-212) filters only by organizationId + userId, with no status = ACTIVE filter. leaveOrganization/removeMember (.../service/MembershipService.kt:77-107, via SupabaseMembershipDatastore.removeMember at .../datastore/supabase/SupabaseMembershipDatastore.kt:89-100) soft-deactivate membership by setting user_organization_mapping.status = INACTIVE rather than deleting the row. Since getUserRole doesn't check status, it keeps returning the user's old role after they've left.
   RBACService.getUserRoleForOrganizationAction / getUserRoleForPropertyAction (.../service/authorization/RBACService.kt:424-460) consume this role directly, so hasRoleOrHigher still returns true post-leave — this affects every RBAC-gated endpoint that checks org/property role, not just property listing (e.g. getProperty, updateProperty, deleteProperty on PropertyController).

2. getAssignedProperties has no RBAC check at all, and relies on a stale mapping table.
   PropertyController.getAssignedProperties (edifikana/back-end/src/main/kotlin/com/cramsan/edifikana/server/controller/PropertyController.kt:87-98) returns all properties for context.payload.userId with no rbacService.hasRoleOrHigher check (unlike getProperty/updateProperty/deleteProperty, which do call it). It calls PropertyService.getProperties(userId) (.../service/PropertyService.kt:58-68) → SupabasePropertyDatastore.getProperties (.../datastore/supabase/SupabasePropertyDatastore.kt:99-112), which queries the view v_user_properties (edifikana/back-end/supabase/migrations/20260317000004_membership_view_rpc_and_security_fix.sql:20-25), defined as properties JOIN user_property_mapping. This user_property_mapping table is populated only when a property is created and is never cleaned up when a user leaves an org — leaveOrganization/removeMember only touch user_organization_mapping, never user_property_mapping. So stale rows persist indefinitely and getAssignedProperties keeps returning properties for orgs the caller is no longer a member of.

Front-end was ruled out as a contributing cause: PropertyManager/PropertyServiceImpl have no local cache — they hit the network every time, so the server is genuinely still authorizing and serving this data.

Impact

An ex-member retains read (and possibly write/delete, per defect #1) access to an organization's properties indefinitely after leaving — a real data-access boundary violation, not just a stale UI list.

Suggested fix (needs backend work in both places)

• Add a status = ACTIVE filter to getUserRole in SupabaseOrganizationDatastore, so RBAC checks correctly stop authorizing deactivated memberships.
• Either add an RBAC check to getAssignedProperties, and/or clean up (delete/deactivate) user_property_mapping rows for a user when they leave an org / are removed, so the underlying v_user_properties view stops returning stale rows.
• Fixing only one of the two leaves a gap: fixing Transfer Repos #1 alone still leaves stale property-list entries because of Setup discord server #2; fixing Setup discord server #2 alone still leaves other RBAC-gated property endpoints (getProperty, etc.) exploitable via Transfer Repos #1.

Related

• [FEATURE] Leave Organization flow with sole-owner validation block #544 (Leave Organization flow) — this bug means the "leave" action doesn't actually revoke property access server-side.
• Parent: [ORG] My Organizations screen + Org Detail + Leave/Transfer ownership (Phase 1) #392.