Orchid ORM
Stand With Ukraine

Appearance

Row Level Security ​

RLS support in Orchid ORM is currently work in progress. You can already set up everything needed for RLS yourself with raw SQL in migrations, and Orchid provides some supporting features listed below.

RLS intro ​

Row Level Security (RLS) is a PostgreSQL feature that filters which rows a role can read or modify. Policies run in the database, so tenant and user isolation is enforced even if application code has a missing check.

RLS is usually a good fit for multi-tenant apps and security-sensitive data isolation, especially when many queries touch shared tables. It can be excessive for simple single-tenant systems, or for apps where keeping authorization checks in application code is simpler to reason about and operate.

How RLS works in practice ​

  1. Create an application role used by requests. A separate role is not strictly required, but a separate non-owner role is strongly recommended: table owners bypass RLS by default, and superusers or roles with BYPASSRLS bypass it as well.
  2. Grant required privileges to that role. RLS does not replace normal GRANT privileges, it adds row filtering on top. Prefer default privileges so new objects stay consistent.
  3. Enable RLS and define table policies in SQL migrations. Once RLS is enabled, if no applicable policy exists, PostgreSQL falls back to default deny.
  4. Set per-request context (such as user id) in SQL session for every incoming request. Policies can read such values with current_setting(...). This must be isolated per request so one user cannot accidentally reuse another user's session context.

Use transaction-scoped $transaction({ role, setConfig }, cb) when request DB work should run in one transaction. Use query-scoped $withOptions({ role, setConfig }, cb) when the request should not be wrapped in one transaction.

Request-scoped RLS context ​

When a request's database work should be atomic, wrap that work in $transaction and pass the RLS role and settings in the transaction options:

ts
async function runRequestDbWork<T>(
  tenantId: string,
  userId: string,
  cb: () => Promise<T>,
) {
  return db.$transaction(
    {
      role: 'app_user',
      setConfig: {
        'app.tenant_id': tenantId,
        'app.user_id': userId,
      },
    },
    cb,
  );
}

await runRequestDbWork(tenantId, userId, async () => {
  const projects = await db.project.all();
  await db.project.create({ name: 'Private project' });
  return projects;
});

Orchid applies this role and config with transaction-local Postgres semantics, so RLS policies can read the values with current_setting('app.tenant_id', true) and current_setting('app.user_id', true) for the whole transaction. Keep the transaction around the database work only; avoid holding it open while waiting on remote services, user input, or streaming responses.

Transaction-scoped role and setConfig are an alternative to query-scoped $withOptions. $withOptions applies and restores SQL session context around each query, and a transaction opened inside that callback inherits the same query-scoped context. $transaction({ role, setConfig }, cb) applies the context once for the transaction, which is lower overhead when the request is intentionally transaction-bound.

Nested transactions may temporarily override the parent transaction role and config:

ts
await db.$transaction(
  {
    role: 'app_user',
    setConfig: { 'app.tenant_id': tenantId },
  },
  async () => {
    await db.project.find(projectId);

    await db.$transaction(
      {
        role: 'project_admin',
        setConfig: { 'app.audit_reason': 'manual-review' },
      },
      async () => {
        await db.project.find(projectId).update({ reviewedAt: new Date() });
      },
    );

    // Back to app_user and the outer transaction config.
    await db.project.find(projectId);
  },
);

The nested role replaces the parent role only for the nested callback, and nested setConfig is shallow-merged over the parent config. When the nested transaction finishes, Orchid restores the parent transaction context before the outer callback continues.

RLS alternatives and trade-offs ​

RLS ​

Pros: centralized enforcement in the database, harder to bypass by forgetting an app-side filter, applies consistently across different query paths. Cons: extra setup for roles, grants, policies, and per-request SQL session context, and policy design/debugging can become subtle.

App-side checks (tenantId in queries) ​

Keep tenant filtering in app logic by including tenantId in queries and validating permissions in code. Orchid can help keep this consistent with Scopes, and the tenant column can be marked as readOnly while being set automatically with setOnSave.

Pros: simplest operational model, no DB policy layer. Cons: easier to miss a check in one code path, especially in larger codebases.

Schema-based multi-tenancy ​

Use separate schemas per tenant. Orchid supports dynamic schema selection at runtime (for example with ALS) via global db schema, and migrations support dynamic schema too via migrations db schema.

Pros: strong isolation between tenants and simpler per-tenant backup/export flows. Cons: tenant provisioning, lifecycle management, and invoking migrations per tenant are up to you.

Multi-database multi-tenancy ​

This is not supported as a dedicated Orchid multi-tenancy feature. It is possible in principle, but setup and operations are usually too cumbersome for most Orchid use cases.

Pros: strongest tenant isolation and simplest tenant-level backup/restore boundaries. Cons: highest operational overhead for provisioning, routing, connections, and running migrations across tenants.

Supported features for RLS ​