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.

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.