Postgres row-level security: fail-closed, and what it costs

Why RLS is opt-in in this kit

The kit's services run against a single pooled Postgres role — the one in DATABASE_URL. Unlike Supabase, where each request maps to a per-user auth.uid(), a pooled connection has no built-in per-user identity. The postgres.js driver keeps an in-process pool (sized by PG_MAX_CONNECTIONS, default 20), and any connection in the pool could serve any request. That means RLS policies need an explicit identity signal to know which user a query belongs to — the app.current_user_id session GUC.

Because that GUC has to be set manually on every request, the kit deliberately keeps tenancy app-enforced by default (the RBAC service layer) and ships RLS as opt-in hardening in rls/0010_rls_policies.sql rather than enabling it in the base migration. The SQL file's header says it plainly: tenant isolation here is application-enforced by default; RLS is a second safety net you turn on once you adopt the per-request GUC pattern below.

The app-enforced vs DB-enforced boundary

Think of the two layers as complementary, not competing:

• App-layer (default): the service layer verifies membership and permissions on every load and action. Every mutating call re-derives authority from a fresh membership read plus requirePermission(). It is the enforceable tenancy boundary and the only one that knows a user's session and RBAC role.
• DB-layer (opt-in): RLS re-asserts row isolation inside the database. Because the app role holds broad write grants, the policies' job is to constrain what that shared role may see and touch — gating SELECT by membership and using FORCE RLS so the app role cannot bypass the checks.

The honest framing from the docs: app-layer enforcement "shipped by default is sufficient" for single-role demos. RLS reduces the blast radius of a SQL-injection bug from "read everything" to "read what the request's current user may read." That is defense-in-depth — it does not replace the service layer's role checks (which RLS cannot express, e.g. per-org owner-only updates are left to the app).

Step 1 — Create a low-privilege app role

Apply the policies with a dedicated, low-privilege app role — NOT the table owner and NOT postgres. The app role needs only what the app does: data manipulation plus schema usage. The exact role creation from docs/deployment.md:

CREATE ROLE app_rw LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
GRANT USAGE ON SCHEMA public TO app_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_rw;

Keeping the role separate from admin/superuser DDL accounts is what makes FORCE RLS meaningful: if the app connected as the table owner, RLS could be bypassed (owners bypass RLS unless it is FORCEd — and FORCE only helps if the role is the owner). A true low-privilege role plus FORCE gives you a genuine fail-closed boundary. Create the role as a normal login role, not a superuser.

Step 2 — Apply the policies (FORCE RLS = fail-closed)

Apply the shipped file with the app role's connection string:

psql "$DATABASE_URL" -f rls/0010_rls_policies.sql

For every tenant table the file runs the same two lines, then creates policies. The key idea: FORCE ROW LEVEL SECURITY makes the policies apply even to the table owner, so any path that forgets the GUC returns no rows — fail-closed, which surfaces bugs loudly during testing instead of leaking data silently. The audit log is gated for SELECT but has no UPDATE or DELETE policy — audit rows are immutable by construction, and the file's only write policy for it is an INSERT for the authenticated app role:

-- Organizations
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE organizations FORCE ROW LEVEL SECURITY;

CREATE POLICY org_select ON organizations
  FOR SELECT USING (app_is_org_member(id));

-- Audit log (append-only; SELECT gated, writes flow through the app role)
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_log FORCE ROW LEVEL SECURITY;

CREATE POLICY audit_log_select ON audit_log
  FOR SELECT USING (
    org_id IS NULL OR app_is_org_member(org_id::uuid)
  );

CREATE POLICY audit_log_insert ON audit_log
  FOR INSERT WITH CHECK (app_current_user() IS NOT NULL);

Step 3 — The helper functions

The policies lean on four SQL helper functions defined at the top of rls/0010_rls_policies.sql. They read identity from the GUC and answer membership/role questions:

• app_current_user() — returns the current user id from the app.current_user_id GUC, or NULL if unset.
• app_is_org_member(org_uuid) — true if the current user has a memberships row for that org.
• app_org_role(org_uuid) — the current user's role in the org, or NULL if not a member.
• app_has_min_role(org_uuid, min_role) — true if the current user meets a minimum role (owner=2, admin=1, member=0).

CREATE OR REPLACE FUNCTION app_current_user()
RETURNS uuid AS $$
  SELECT NULLIF(current_setting('app.current_user_id', true), '')::uuid;
$$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION app_is_org_member(org_uuid uuid)
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM memberships
    WHERE org_id = org_uuid AND user_id = app_current_user()
  );
$$ LANGUAGE sql STABLE;

current_setting(name, true) returns NULL instead of erroring when the GUC isn't set — so a request that forgets it reads as "no current user," which combined with FORCE RLS yields no rows.

Step 4 — The per-table policies

The file enables and forces RLS on organizations, memberships, invites, and audit_log (users and sessions are identity/credential tables and are not row-gated). The shape of the policies:

• Organizations: SELECT gated by app_is_org_member(id); INSERT allowed for any authenticated user (the creator becomes owner via the memberships insert); UPDATE/DELETE only for the org owner (app_org_role(id) = 'owner').
• Memberships: SELECT gated by membership so a member can render the org's member list and the app can enforce hierarchy; INSERT/UPDATE/DELETE allowed for the authenticated app role, because role changes and seat/join/leave paths are authorized by the RBAC service layer — the docs note you can add stricter policies here for multi-client setups.
• Invites: SELECT gated by org membership (members see pending invites); INSERT/UPDATE for the authenticated app role (the app creates and revokes invites).
• Audit log: SELECT gated by org_id IS NULL OR app_is_org_member(org_id::uuid) (NULL org rows — e.g. global events — remain visible); INSERT for the authenticated app role; no UPDATE or DELETE policy, so audit stays append-only at the DB layer too.

Step 5 — Set per-request identity inside a transaction

Because a pooled connection has no stable user identity, the app must set the GUC on every request, inside a transaction so SET LOCAL rolls back with the request and never leaks into a later request served by the same pooled connection. The exact wiring from docs/deployment.md (in src/hooks.server.ts):

const db = await getDb();
await db.execute(sql`BEGIN`);
await db.execute(sql`SET LOCAL app.current_user_id = ${locals.user?.id ?? null}`);
// ... resolve the request ...
await db.execute(sql`COMMIT`);

SET LOCAL scopes the value to the current transaction only. If you could guarantee a 1:1 app↔connection mapping, the file notes you could set app.current_user_id once on the pooled session instead — but with transaction-mode pooling that guarantee doesn't hold, so the per-request pattern is the correct default.

Tradeoffs to weigh honestly

• Fail-closed blast radius: FORCE RLS changes the consequence of a SQL-injection bug from "read everything" to "read what the request's current user may read." That is the main win — but it only holds if the GUC is actually set correctly per request.
• The GUC must be set every request: a path that forgets it returns no rows. That's fail-closed for safety, but it means you carry a per-request transaction + SET LOCAL cost for every request, and a wiring bug shows up as "silently empty" queries during testing.
• Keep the app role separate from admin: RLS and FORCE only produce a real boundary when the app runs as a low-privilege role, not as postgres or a DDL owner. Managing a separate role is an operational step.
• RLS can't express your RBAC: policies gate rows by membership, but per-org role logic (owner-only updates, hierarchy rules) still lives in the service layer. RLS is a safety net, not a replacement for the app boundary.

For single-role demos or a straightforward app-only tenant path, the app-layer enforcement shipped by default is sufficient — enabling RLS adds per-request GUC management and a separate role for little gain. The tradeoff is real, and the kit's docs call this out explicitly.

When to enable RLS vs. when app-layer is enough

• Enable RLS when: you have a multi-client or high-risk deployment, you want a second boundary limiting the blast radius of an injection or logic bug, you're subject to compliance expectations for defense-in-depth, or you have many concurrent application instances sharing one database role and want the DB to re-assert isolation regardless of app behavior.
• App-layer is enough when: you're a single app process (or small fleet) where the service layer is the only writer, you're still validating the product, or you don't want the operational cost of a separate low-privilege role and per-request GUC wiring. The kit ships this as the default for a reason.

Whichever you choose, keep RLS out of the base migration — it is opt-in hardening in rls/, applied deliberately, so the shipped default stays simple and the safety net stays a conscious decision rather than an implicit behavior.

Production checklist

• Create the app role as a low-privilege role — NOT the table owner and NOT postgres. Grant only SELECT, INSERT, UPDATE, DELETE on tables plus USAGE on the schema and sequences.
• Run the policies with the app role: psql "$DATABASE_URL" -f rls/0010_rls_policies.sql. The file ENABLEs and FORCEs RLS, so every gated table is fail-closed from the start.
• Set app.current_user_id on every request before any query, inside a transaction (BEGIN / SET LOCAL ... / COMMIT) so the GUC rolls back with the request. docs/deployment.md shows the exact hooks.server.ts wiring.
• Understand fail-closed: any path that forgets the GUC returns no rows, because FORCE ROW LEVEL SECURITY applies the policies even to the app role (the table owner). That is the safety-net property you want.
• Keep the app role separate from admin/superuser DDL accounts. Mixing them defeats the blast-radius reduction of FORCE RLS.
• Write policies that SELECT-gate by membership (app_is_org_member()) while permitting INSERT/UPDATE/DELETE for the authenticated app role — the service layer (RBAC) stays the authorizer for writes.
• Leave RLS out of the base migration. It is opt-in hardening in rls/, not the runtime tenancy boundary, because a pooled connection has no per-user identity like Supabase's auth.uid().
• For single-role demos or a single app tenant path, app-layer enforcement shipped by default is sufficient — enabling RLS adds per-request GUC management for little gain.

Related guides: Multi-tenant DB design · Connection pooling