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)

Two middlewares do this, in order. common.middleware.get_company.GetProfileAndOrg resolves the profile and org from the JWT, then common.middleware.rls_context.RequireOrgContext sets the variable on the connection before the view runs. From that point the database simply hides every other org's rows.

The statement it runs is:

SELECT set_config('app.current_org', '<uuid>', false)

That third argument matters. false means session scope, not transaction scope, which is deliberate: a SET LOCAL would be reverted by any rollback, and the ORM rolls back more often than you would think. The consequence is that the variable outlives the request on a pooled connection, so the reset must run even when the view raises, and a pool needs a reset callback on check-in. Get that wrong and a connection carries one tenant's org id into the next tenant's request.

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.