Skip to main content

Multi-tenancy & Row-Level Security

GreekManage hosts multiple national organizations (tenants) in one shared Postgres database. Tenant isolation is enforced at two layers:

  1. Application layer — permission classes verify the requesting user belongs to the right tenant
  2. Database layer — PostgreSQL Row-Level Security policies filter rows so even a buggy ORM query can't leak data across tenants

Defense in depth: even if a permission check is missed in a view, the DB still won't return another tenant's data.

Tenant boundaries​

A "tenant" in GreekManage = an Organization. Everything below it (regions, chapters, members, forums, invoices, donations…) is scoped to one organization.

How RLS works​

PostgreSQL Row-Level Security lets you attach a policy to a table that filters rows automatically based on a session variable.

The policy pattern used in GreekManage:

CREATE POLICY {table}_org_isolation ON {table}
  USING (
    current_setting('app.current_org_id', true) = ''
    OR current_setting('app.current_org_id', true) IS NULL
    OR {org_lookup}::text = current_setting('app.current_org_id', true)
  );

Where {org_lookup} is the column or join path that resolves the row's owning organization. For most tables it's a direct org_id column; for nested entities (e.g., compliance_submission), it's a chain like chapter.organization_id.

The fallback IS NULL OR '' lets unauthenticated and platform-admin requests bypass the filter. Platform admins can opt into a specific tenant via the X-Organization-Id header.

Reference migration: backend/apps/retention/migrations/0002_row_level_security.py.

OrganizationContextMiddleware​

The middleware is the bridge between the authenticated user and the Postgres session variable.

File: backend/apps/common/middleware.py:14-126

Key design choices​

  • SET LOCAL scopes the variable to the current transaction. As soon as the transaction commits or rolls back, the variable resets. No cross-request leakage.
  • transaction.atomic() wraps the entire view dispatch so the variable is in scope for every query the view makes.
  • The middleware runs after authentication so request.user is populated. If unauthenticated, no variable is set; queries return 0 rows or whatever public scope allows.

Resolution priority​

If a user has multiple roles (e.g., org admin in one org + member of a chapter in another), the middleware picks the highest tier:

# Pseudocode of middleware logic
if user.is_platform_admin:
    org_id = request.headers.get("X-Organization-Id")  # may be None
elif user.organization_admins.filter(is_active=True).exists():
    org_id = user.organization_admins.filter(is_active=True).first().org_id
elif user.regional_admins.filter(is_active=True).exists():
    org_id = user.regional_admins.filter(is_active=True).first().region.organization_id
elif user.memberships.filter(status__in=PLATFORM_ACCESS_STATUSES).exists():
    org_id = user.memberships.filter(...).first().chapter.organization_id
else:
    org_id = None

Platform admin scoping​

Platform admins bypass RLS by default — they need to see all orgs (e.g., for backup tasks, support, audit).

To act as a specific org (for testing, support, or running an org-scoped task), they pass:

X-Organization-Id: <org-uuid>

The middleware honors this header only when the user is a platform admin; everyone else gets 403.

Sub-org isolation (region / chapter) — Wave 3​

Org-level RLS stops cross-tenant leakage, but within one org it does not stop a regional admin from acting across regions, or read below the chapter line. Wave 3 (audit #392) adds a second RLS dimension — region and chapter — on the 51 sub-org-owned tables — every table with a chapter_id/region_id column, directly or derivable (events, governing documents, memberships, chapter advisors, forums and their descendants, compliance statuses/submissions, chapter invoices and line items, PNM records, chapter photos, service hours, retention alerts/responses/answers…). The full list is the single source of truth in backend/apps/common/suborg_rls.py (SUBORG_TABLES).

:::note Derived-chapter tables and the carve-out For a table whose chapter is derived through a scoped parent (e.g. service hours → membership, invoice line item → chapter invoice), the org_lookup must read the same scoped parent as the chapter_lookup. Then a hidden parent NULLs the org gate and blocks the row — otherwise the chapter_id IS NULL org-wide carve-out would treat a hidden-parent row as org-wide and leak it. retention_surveyquestion is deliberately left org-level (a survey question has no chapter dimension — only responses do); learning_course is scoped by chapter only, so a region-scoped course (region_id set, chapter_id NULL) reads as org-wide. :::

Kill-switch — SUBORG_RLS_ENFORCED​

The composed policies ship disabled. The setting SUBORG_RLS_ENFORCED (env SUBORG_RLS_ENFORCED, default False) gates everything:

  • Off (default): the middleware does not set app.suborg_enforced, so the sub-org half of every composed policy short-circuits to true. Row visibility is identical to today's org-level isolation — zero behaviour change on deploy.
  • On: the middleware sets app.suborg_enforced='on' and the region/chapter predicate is enforced.

The flag is a permanent instant kill-switch: flip it off and the middleware stops setting the var — policies fall back to org-level with no migration.

How scope is derived​

OrganizationContextMiddleware._suborg_scope(user, org_id) computes, from the user's active roles, two comma-separated session vars set via SET LOCAL alongside app.current_org_id:

VarContents
app.current_region_idregion ids the user is a regional admin of (scopes the region tables)
app.current_chapter_idthe user's own chapter memberships plus every chapter in their admin regions
app.suborg_enforced'on' iff SUBORG_RLS_ENFORCED, else empty

A national admin of the selected org gets both sets empty → unrestricted.

Why regions are expanded to chapters in Python​

An RLS predicate that reads another RLS-protected table is itself subject to that table's policies. So a chapter-owned table cannot re-derive its region by joining to organizations_chapter inside its own policy: under a narrow scope the chapter row is invisible, the subquery returns NULL, and in-scope rows would be wrongly hidden. The fix is to expand a regional admin's scope into the set of chapter ids in their region(s) in the middleware, so every chapter-owned table scopes purely by its own chapter_id. Only the region tables carry a region branch (their region column is their own), and organizations_chapter also gets an own-id chapter branch so officers see their chapter and the org-gate subqueries on it resolve for in-scope chapters.

Composed predicate​

CREATE POLICY {table}_suborg_isolation ON {table}
USING (
  ( {org_lookup}::text = current_setting('app.current_org_id', true)
    OR current_setting('app.current_org_id', true) = ''
    OR current_setting('app.current_org_id', true) IS NULL )      -- org gate (unchanged)
  AND (
    current_setting('app.suborg_enforced', true) IS DISTINCT FROM 'on'  -- flag off → today's behaviour
    OR (current_setting('app.current_region_id', true) = ''             -- national: both empty → unrestricted
        AND current_setting('app.current_chapter_id', true) = '')
    OR {region_lookup}::text = ANY(string_to_array(current_setting('app.current_region_id', true), ','))
    OR {chapter_lookup} IS NULL                                         -- org-wide rows visible to all
    OR {chapter_lookup}::text = ANY(string_to_array(current_setting('app.current_chapter_id', true), ','))
  )
)
WITH CHECK (…same…);   -- writes scoped both directions

IS DISTINCT FROM 'on' (not <> 'on') is deliberate: background / org_context callers set only app.current_org_id, leaving app.suborg_enforced NULL; <> would make the whole clause NULL and hide chapter rows even with the flag off.

Migration: backend/apps/common/migrations/0005_suborg_rls.py (reversible to the org-only policies).

Deploy discipline​

  1. Deploy with the flag off (permissive) — verify no behaviour change.
  2. Set SUBORG_RLS_ENFORCED=True on staging; run the RLS suite (apps.common.tests.test_suborg_rls) + role-walkthroughs on seeded multi-region / multi-chapter data.
  3. Flip it on prod → fully enforced.

What's protected, what isn't​

Protected (RLS policies in place)​

  • retention.* (snapshots, alerts, surveys, responses)
  • compliance.* (submissions, statuses, alerts)
  • elections.* (elections, votes, candidates)
  • ai_services.report_request, ai_services.content_embedding

Not yet protected (relies solely on app-layer permission checks)​

  • Most other tables (forums, messaging, learning, etc.)

This is an active migration. New tables added since RLS adoption have policies; older tables are being backfilled. The application layer still enforces tenant scoping in querysets — RLS is a backstop, not the primary defense.

To add RLS to a new table, see the pattern in the retention migration.

Testing tenant isolation​

End-to-end tests in e2e/ include scenarios where:

  1. User A in Org X tries to fetch a chapter in Org Y → expects 404 (not 403 — we don't reveal existence)
  2. Direct ORM access in tests verifies RLS by setting and unsetting the org context
  3. ZAP credentialed scans probe for tenant cross-access

Common pitfalls​

PitfallWhy it's badFix
Using transaction.atomic() inside a Celery task without setting app.current_org_idTask queries return 0 rows or all rowsSet the variable explicitly: connection.cursor().execute("SET LOCAL ...")
Background job runs without org contextSameWrap in a custom with org_context(org_id): helper
Direct SQL via connection.execute()Bypasses RLS context if not in the right transactionRun inside transaction.atomic() and re-set SET LOCAL
Test fixture that creates rows for multiple orgs without contextTests pass with leakageAlways wrap fixture creation in org context, or mark RLS-bypassing tests explicitly

Helper for background tasks​

For Celery tasks that need org scope:

from apps.common.context import org_context

@shared_task
def calculate_chapter_health(org_id, chapter_id):
    with org_context(org_id):
        # All queries inside this block respect RLS for the given org
        chapter = Chapter.objects.get(id=chapter_id)
        ...

Implementation: apps/common/context.py (helper that opens a transaction + sets SET LOCAL).

Diagnosing RLS issues​

If a query returns surprising rows:

-- In psql, check the current session variable
SELECT current_setting('app.current_org_id', true);

-- Inspect policies on a table
SELECT * FROM pg_policies WHERE tablename = 'my_table';

-- Run a query as if RLS were off (superuser only)
SET LOCAL app.current_org_id = '';
SELECT * FROM my_table LIMIT 10;

The BYPASSRLS role attribute also lets a superuser inspect raw rows. Don't enable it on app-tier connections.