Postgres Row-Level Security

Two locks, one key — how Chatly enforces tenant isolation at the database layer, not just the application layer.

Tenant isolation in Chatly is enforced twice: once at the application layer (every query is built with a workspace_id filter) and again at the database layer (Postgres Row-Level Security policies reject any query that doesn't match the workspace context).

Two locks. One key. The key is a per-transaction Postgres setting, app.current_workspace_id, derived from the authenticated request.

Info — Why two layers?

Application-layer filtering is necessary. Database-layer filtering is sufficient — as long as the connection role is subject to RLS, which is the one deployment detail on this page you must get right. See Database roles below before you ship.

How it works

Every tenant table carries the same policy pattern:

ALTER TABLE conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE conversations FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS conversations_workspace_isolation ON conversations;
CREATE POLICY conversations_workspace_isolation
  ON conversations
  USING      (workspace_id::text = current_setting('app.current_workspace_id', true))
  WITH CHECK (workspace_id::text = current_setting('app.current_workspace_id', true));

USING controls reads (SELECT, UPDATE, DELETE source rows). WITH CHECK controls writes (INSERT, UPDATE target rows). Both must match the current workspace claim.

FORCE ROW LEVEL SECURITY matters here specifically because the application connects as the owner of these tables, and Postgres exempts a table's owner from its own policies unless FORCE is set.

Warning — FORCE does not stop a superuser

This is the single most expensive misunderstanding in this codebase, so it gets stated plainly: FORCE lifts the owner's exemption, never the superuser's. A Postgres superuser (and any role with BYPASSRLS) ignores every policy on this page, unconditionally. If your DATABASE_URL points at postgres or any other superuser, tenant isolation at the database layer is off and nothing will tell you — no error, no log line. Roughly fifteen shipped bugs here traced back to a test harness that connected as the container superuser and therefore proved nothing.

Setting the workspace claim

Workspace-scoped queries run inside a helper that opens a transaction and sets the claim on it:

// packages/db/src/rls.ts
export async function withWorkspaceContext<T>(
  db: Database,
  workspaceId: string,
  fn: (tx: Database) => Promise<T>,
): Promise<T> {
  return db.transaction(async (tx) => {
    await tx.execute(sql`SELECT set_config('app.current_workspace_id', ${workspaceId}, true)`);
    return fn(tx as unknown as Database);
  });
}

The third argument to set_config is is_local: true, which makes the setting transaction-local — it is cleared at COMMIT/ROLLBACK, so a pooled connection reused by the next request carries no leftover claim.

The true in current_setting(…, true) is the missing_ok flag, and it has a consequence you have to internalise:

Warning — An unscoped query returns nothing — it does not error

With no claim set, current_setting returns NULL, the policy predicate is NULL, and the query matches zero rows and returns cleanly. A feature that forgot its scope therefore looks exactly like "no customer has configured this yet". That is how the widget's channel config read, RAG retrieval, calendar sync, seat counting and billing usage all shipped broken and stayed broken — each returning an empty set, each looking idle rather than failing.

Four gate scripts exist purely to catch that shape before it ships, and they run in CI on every PR:

Isolation gates

The tenant-table list those checks work from is parsed out of the migrations by scripts/lib/rls-tables.mjs rather than hand-maintained — an earlier hand-written list named twelve tables out of a hundred-plus, and every RLS bug found here was on a table it did not mention.

Tables under RLS

144 tables, as of the current migration head — parsed out of the migrations, not counted by hand. Every table with a workspace_id column carries the isolation policy except four, each excluded deliberately and each documented in the migration that decided it — listed below alongside the tenant root itself:

Tables outside RLS, and why

Representative tenant tables — contacts, conversations, messages, channels, memberships, tickets, kb_articles, kb_embeddings, triggers, workflows, workflow_runs, campaigns, audiences, webhook_endpoints, webhook_deliveries, integration_installs, gdpr_requests, contact_channel_consent — all carry ENABLE + FORCE + the isolation policy above.

The wildcard arm

A few tables honour a '*' sentinel in the GUC, set by withAdminContext, so the platform-operator console can read across tenants:

• Read + write: admin_impersonations, voice_call_events — the admin surface mints rows here.

• Read only: push_tokens, integration_installs, experiment_assignments, workspace_feature_flags, memberships — a separate FOR SELECT policy beside an untouched isolation policy, so cross-tenant INSERT is still refused and cross-tenant UPDATE/DELETE still matches zero rows.

Per-tenant content tables (contacts, conversations, messages, …) do not honour the wildcard. Routing a tenant query through withAdminContext by mistake returns zero rows rather than another tenant's data. The contract is asserted end to end over a NOSUPERUSER NOBYPASSRLS role in apps/api/src/__tests__/admin-wildcard-rls.integration.spec.ts.

Database roles

Chatly connects with a single DATABASE_URL. There is no separate runtime/migrator/break-glass role split — migrations run over the same connection string as the API.

That makes one requirement load-bearing:

Warning — The connection role must be NOSUPERUSER and NOBYPASSRLS

Owning the tables is fine — FORCE handles that. Being a superuser is not. Create a dedicated role for the application, let it own the schema, and never point DATABASE_URL at postgres. Verify on any deployment you inherit:

SELECT rolname, rolsuper, rolbypassrls
  FROM pg_roles
 WHERE rolname = current_user;

Both flags must read false. If either is true, every policy on this page is decoration.

The test harness models exactly this: createTestPostgres() hands back the container superuser URL (for migrations, seeding and cross-tenant verification reads) and a separate chatly_app role that is NOSUPERUSER NOBYPASSRLS and owns nothing. Isolation is only ever asserted over the second one.

Audit considerations

• audit_log has no RLS policy (see the exceptions table above). Its workspace boundary is the application predicate, and the reader gate is audit:read — owner and admin only.

• Migration 0001 runs REVOKE UPDATE, DELETE ON audit_log FROM PUBLIC to keep the table append-only. Read that as a guardrail rather than a guarantee: a REVOKE … FROM PUBLIC does not bind the table's owner, and the application connects as the owner. If tamper-evidence is a requirement for you, ship the audit stream off-box.

• Cross-tenant audit reads live behind the platform-operator surface, which authenticates with the x-admin-api-key header or a user id listed in ADMIN_USER_IDS — not a workspace session.

Testing isolation

Two suites, and they check different things.

packages/db/src/rls.spec.ts compares RLS_TABLES (the list the isolation suite reflects over) against the tables the migrations actually protect, in both directions. A table protected in SQL but missing from the list is a table whose isolation nothing tests; a name in the list that no migration protects makes the suite generate a test that can pass for the wrong reason.

apps/api/src/__tests__/rls-isolation.integration.spec.ts then runs over the restricted role:

• a catalog sweep over every entry in RLS_TABLES, asserting each is ENABLE'd, FORCE'd and carries at least one policy;

• behavioural tests on a hand-written representative sample with real fixture parents, exercising cross-tenant SELECT, INSERT (WITH CHECK), UPDATE and DELETE.

The list comparison runs with the ordinary unit tests; the isolation suite runs in the CI integration job, and it refuses to run rather than soft-skipping when Postgres is unreachable — a green tick for tests that never executed is how the previous version of that suite passed for years while asserting nothing.

Performance

RLS adds a workspace_id predicate to every query. Combined with composite indexes on (workspace_id, …) for the hot query patterns, the cost is small — the predicate is one the planner would want anyway, and the leading column is already indexed.

There is no table partitioning in the schema. If a single workspace's table grows past what a composite index comfortably serves, that is a schema change to design at the time, not a switch to flip.

Why not application-only?

Common argument: "We always filter by workspace_id in the app, so RLS is redundant." Common reality:

• One forgotten filter in a hot SQL path leaks every tenant's data.

• One bug in a service-bus consumer that processes events without re-setting the scope.

• One misconfigured ORM that auto-joins across tables.

• One developer running a one-off script in prod without setting the scope.

Each of those is a "we'd never do that" failure mode until the postmortem. RLS is the layer that says "even if you did do that, the DB still says no."

audit_log is the standing counter-example on this deployment, and it is instructive: on every other tenant table a forgotten predicate returns zero rows and the bug is obvious in five seconds. On that one, the same mistake returns every tenant's rows.

How to add an RLS policy to a new table

-- migration NNNN_create_my_table.sql
CREATE TABLE IF NOT EXISTS my_table (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  -- …
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS my_table_workspace_idx ON my_table (workspace_id, created_at);

ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;
ALTER TABLE my_table FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS my_table_workspace_isolation ON my_table;
CREATE POLICY my_table_workspace_isolation
  ON my_table
  USING      (workspace_id::text = current_setting('app.current_workspace_id', true))
  WITH CHECK (workspace_id::text = current_setting('app.current_workspace_id', true));

No GRANT line: the application role owns the schema, which is why FORCE is mandatory rather than optional.

Then add the table name to RLS_TABLES in packages/db/src/rls.ts — rls.spec.ts fails until you do, and that failure is the point. It means the isolation suite has just started generating a test for your table.

Info — There is no lint that requires RLS on a new tenant table

Nothing rejects a migration that creates a table with a workspace_id and no policy. What the gates enforce is consistency once the policy exists — the migrations, RLS_TABLES and the isolation suite must agree. Adding the two ALTER TABLE lines is on you and on review.