Skip to content

Tenancy and row-level security

How one database keeps every workspace’s data apart, and the rules that keep it so.

On this page

Every workspace's rows sit in the same tables of one Postgres database. What keeps workspace A from ever reading workspace B is Postgres row-level security (RLS), enforced by the database itself, not by remembering a WHERE clause. These rules are the ones that, if broken, leak one customer's data to another.

The idea in one picture

  1. 1Request for workspace A
  2. 2withTenant(A)opens a transaction
  3. 3set_config('app.current_tenant_id', A, true)
  4. 4Every query in itpolicy: organization_id = current_tenant_id()
  5. 5Transaction endsthe setting is discarded

Each tenant table has a policy like this:

sql
CREATE POLICY widgets_tenant_isolation ON public.widgets
  FOR ALL
  USING      (organization_id = public.current_tenant_id())
  WITH CHECK (organization_id = public.current_tenant_id());

Even a query with no WHERE returns only the current workspace's rows. A query with no tenant set returns nothing.

Two database roles

The separation between the two roles is what makes the policies real:

RoleUsed byCan
Dokploy superuserMigrations only, from your machine (MIGRATION_DATABASE_URL in .env.migrate)Own the schema, run DDL. RLS does not apply to it.
ikazebot_appThe API, the worker, the test suites (DATABASE_URL)Read and write rows only. No DDL, NOBYPASSRLS, owns nothing.

The API refuses to boot on a role that bypasses RLS, in every environment. Development once ran as the superuser and hid exactly the bugs RLS exists to catch.

Querying: TenantContextService

ts
// Normal tenant work: the default.
const rows = await this.tenants.withTenant(organizationId, (tx) =>
  tx.select().from(widgets).where(eq(widgets.organizationId, organizationId)),
);
MethodUse for
withTenant(orgId, fn)Everything tenant-scoped. Requests, jobs, all of it.
withUser(userId, fn)The auth path only, before a workspace is chosen
withNewOrganization(…)Creating a workspace (its id does not exist yet)
unscopedOnly truly global tables: users, sessions, accounts, verifications

Reaching for unscoped to touch a tenant table is a review failure. The bug looks like missing data, not like a security hole, which makes it easy to "fix" the wrong way.

The rules

  1. Every tenant table has organization_id: NOT NULL, referencing organizations, ON DELETE CASCADE.
  2. ENABLE and FORCE row-level security. Without FORCE, the table's owner is exempt, and the owner runs migrations and maintenance scripts.
  3. Policies have USING and WITH CHECK. USING governs reads; WITH CHECK governs writes. With USING alone, tenant A can insert a row stamped with tenant B's id.
  4. Call public.current_tenant_id(), never current_setting(...)::uuid inline. The helper's NULLIF(…, '') turns a missing tenant into zero rows instead of a 500.
  5. RLS is the net, not the filter. Keep the explicit where organizationId = … in application queries. The predicate prevents the bug; the policy catches it if the predicate is forgotten.
  6. Policies are hand-written SQL, in their own migration, in the same pull request as the table. drizzle-kit neither generates nor diffs them.
  7. No soft delete. No deleted_at, ever. Deletion cascades, because an orphan row whose parent is gone matches no policy again.
  8. Ids are uuidv7(), minted by Postgres 18, not by the application.
  9. updated_at is maintained by a trigger, not Drizzle's $onUpdate, which only covers writes that go through Drizzle.

Defence in depth around it

  • Secrets (action API keys, channel tokens) are encrypted with AES-256-GCM in a dedicated table, never in a plain column or metadata.
  • Logs never contain message bodies, contact PII, tokens or connection strings; redaction is central in logger.config.ts.
  • Authorisation (who may do what within a workspace) is CASL, defined once in auth/ability.factory.ts. The web app's role checks are convenience only, and the API checks again on every request.

To add a table safely, follow Adding a tenant table step by step.

Get started free — 50 credits a month