Postgres + Row-Level Security

BottleCRM enforces tenant isolation in the database using PostgreSQL row-level security (RLS). Application code still filters every query by organization — RLS is the safety net, not a substitute. This page explains how to set it up and how to confirm it's working.

The model

Every org-scoped table inherits from BaseOrgModel, which adds an org_id foreign key. A policy on each such table allows a row to be visible only when:

org_id::text = current_setting('app.current_org', true)

A request-scoped middleware (RLSContextMiddleware) reads the org_id claim from the JWT and runs SET LOCAL app.current_org = '<uuid>' on the connection before the view executes. Inside the transaction, the database simply hides every other org's rows.

Why the app must not be superuser

PostgreSQL superusers bypass RLS entirely. If the app connects as postgres, the safety net is gone. The shipped init-rls-user.sql creates a dedicated app_user role without BYPASSRLS:

CREATE ROLE app_user LOGIN PASSWORD :'app_password';
GRANT CONNECT ON DATABASE bottlecrm TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

Put app_user in DATABASE_URL, not postgres.

Checking status

docker compose exec backend python manage.py manage_rls --status

The output lists every registered org-scoped table and whether its policy is enabled. The full table list lives in common/rls/__init__.py under ORG_SCOPED_TABLES — adding a new tenant-scoped model means registering it there and generating a migration that calls get_enable_policy_sql(table_name).

Celery workers

Celery has no middleware. Any task that touches the ORM must re-establish the tenant context first:

from common.tasks import set_rls_context

@shared_task
def send_invoice_email(invoice_id, org_id):
    set_rls_context(org_id)
    invoice = Invoice.objects.get(id=invoice_id)
    ...

Failure to do this either errors out (RLS hides the row) or — much worse, if the worker connects with elevated privileges — could leak data across tenants.

Superadmin queries

A separate "superadmin" Postgres role with BYPASSRLS exists for the Enterprise dashboard's cross-org analytics. Use it only for read-only aggregations; never wire it into request-path code.