عزل البيانات & Multi-Tenancy

PostgreSQL RLS, org-scoped queries, and complete tenant data isolation.

Isolation Layers

Row-Level Security (RLS)

How RLS Works

PostgreSQL RLS policies are database-level guards:

-- Enable RLS on a table
ALTER TABLE engagement ENABLE ROW LEVEL SECURITY;

-- Create isolation policy
CREATE POLICY org_isolation ON engagement
  USING (org_id = current_setting('app.current_org_id')::int);

Protected Tables (19 policies)

Table	Policy Type
engagement	Direct org_id
workpaper	Direct org_id
finding	Direct org_id
evidence	Direct org_id
audit_log_event	Direct org_id
pbc_request	Direct org_id
report	Direct org_id
qa_review	Direct org_id
monitoring_test	Direct org_id
kri_threshold	Direct org_id
action_plan	FK via finding → org_id
test_procedure	FK via workpaper → org_id
phase	FK via engagement → org_id
milestone	FK via engagement → org_id
review_point	FK via engagement → org_id
signoff	FK via engagement → org_id
plan_item	FK via annual_plan → org_id
questionnaire	Direct org_id
questionnaire_response	FK via questionnaire → org_id

OrgScopedDB Dependency

The OrgScopedDB FastAPI dependency:

1. Extracts org_id from the authenticated user's JWT
2. Creates a database session
3. Sets SET LOCAL app.current_org_id = {org_id}
4. All subsequent queries automatically filtered by RLS

# In routers
async def list_engagements(session: AsyncSession = OrgScopedDB):
    # This query is automatically filtered by org_id
    result = await session.execute(select(Engagement))
    return result.scalars().all()

Storage Isolation

MinIO (object storage) uses org-prefixed paths:

bucket/org-{org_id}/evidence/{file_hash}

Network Isolation (Private Tenant)

For Enterprise/Sovereign tiers:

• Dedicated OKE namespace
• Network policies restricting cross-namespace traffic
• Dedicated database instance (optional)
• Separate MinIO bucket