Row-Level Security

Row-Level Security (RLS) makes Postgres decide who may see and write which rows. The rule lives beside the table, declared in TypeScript in your schema, instead of being scattered across handlers where one forgotten WHERE user_id = … leaks somebody's data. Two facts about it decide whether your first deploy works. RLS is on by default, and a table with RLS and no policies denies everything — so a table you have not thought about returns nothing rather than returning every row to every caller. If your endpoints answer [] and null on data you know is there, that default is the first thing to check.

Warning: This is the quickstart's silent killer. Declare todos with columns and no policies, deploy it, and the POST fails while the GET returns an empty array — no error names RLS, because nothing went wrong: the database was asked for rows and answered honestly that there are none this caller may see. The rule the SDK computes is one line, rls = policies.length > 0 || table.rls !== false, and it is fail-closed on purpose. Before that default existed, a read by id returned another user's row in full through the typed client; with RLS on, the same query returned 0 of 165 rows. An empty result is the cost of not thinking about a table. A leak was the old one.

Quick example

// db/public.ts
import {
  boolean, defineSchema, defineTable, ownedByUser, policy, text, timestamp, uuid,
} from "@palbase/backend";

export const todos = defineTable("todos", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    // ownedByUser() IS the owner column: a text FK onto auth.users(id),
    // NOT NULL and ON DELETE CASCADE, because ownership is what erasure walks.
    user_id: ownedByUser(),
    title: text().notNull(),
    done: boolean().default(false),
    created_at: timestamp().defaultNow(),
  },
  // `policies` is a CALLBACK: `p` carries this table's own column names, so a
  // misspelt column is a compile error rather than a refusal from Postgres in
  // the middle of an apply.
  policies: (p) => [
    policy("todos_owner")
      .for("all")
      .to("authenticated")
      .using(p.col("user_id").eq(p.auth.uid()))
      .withCheck(p.col("user_id").eq(p.auth.uid())),
  ],
});

export default defineSchema("public", { tables: [todos] });

With that one policy applied, the handler needs no ownership filter of its own:

// modules/todos/dto/todo.ts
import { z } from "@palbase/backend";

export const Todo = z.object({
  id: z.string(),
  user_id: z.string(),
  title: z.string(),
  done: z.boolean(),
  created_at: z.string(),
});
export type Todo = z.infer<typeof Todo>;

// modules/todos/todo.service.ts
import { Database, Injectable } from "@palbase/backend";
import { Todo } from "./dto/todo";

@Injectable()
export class TodoService {
  open(): Promise<Todo[]> {
    // No user_id filter — the database returns only the caller's rows.
    return Database.public.todos.findMany({ where: { done: false } });
  }
}

// modules/todos/todos.controller.ts
// The controller never touches a table.
import { Controller, Get } from "@palbase/backend";
import { Todo } from "./dto/todo";
import { TodoService } from "./todo.service";

@Controller("/todos")
export class TodosController {
  constructor(private readonly todos: TodoService) {}

  @Get("")
  list(): Promise<Todo[]> {
    return this.todos.open();
  }
}

A signed-in user listing todos sees only their own. A findById on somebody else's todo resolves null, exactly as if the row did not exist — and not telling those two apart is deliberate, because a distinguishable answer is an oracle for rows the caller may not read.

How enforcement reaches the database

Every request opens one transaction, and that transaction begins with a bind statement:

select set_config('role', $1, true),
       set_config('search_path', 'public', true),
       set_config('request.jwt.claims', $2, true)

$1 is the Postgres role, $2 is the verified JWT claims as JSON, and both are bound parameters — a user's identity is never interpolated into SQL. is_local => true scopes them to that transaction exactly like SET LOCAL, so they cannot leak into the next request on a pooled connection. Inside a policy expression, auth.uid() reads the caller's id back out of those claims.

Everything on the default Database surface runs inside that transaction — the typed tables, the $-prefixed string ops, $query(), and a $transaction() plan. There is no opt-in step in handler code and nothing to forget. Bypassing it takes the explicit $asService() call.

The role your policy has to name

The role the runtime binds is backend_authenticated, and it binds that role on every request, signed in or not. Anonymous callers differ by their claims, not by their role.

That has one sharp consequence: a policy addressed only to authenticated applies to nothing your backend does. With RLS enabled and no applicable policy, Postgres denies everything — reads come back empty, writes are refused — while your code compiles, your tests pass and the deploy reports success.

Policies declared in your schema file are handled for you. Before a policy is created, authenticated gains the twin backend_authenticated and anon gains backend_anon, so .to("authenticated") reaches the database as TO authenticated, backend_authenticated. service_role is deliberately left alone, because backend_service_role carries BYPASSRLS and policies never apply to it. An already-live policy that names only authenticated is repaired the same way on the next apply.

backend_anon exists and is provisioned, and it gets the twin treatment for policies applied through other doors — but your backend never connects as it, so a policy written only for anon will not change what a controller of yours can read.

Hand-written CREATE POLICY SQL goes through raw() in the schema file, and a raw() body is emitted verbatim — nothing rewrites it, so name both roles yourself:

CREATE POLICY todos_owner ON todos FOR ALL
  TO authenticated, backend_authenticated
  USING (user_id = (select auth.uid()))
  WITH CHECK (user_id = (select auth.uid()));

The two API keys and the two Postgres roles

Four role names circulate and it is worth separating them once. Every Environment is minted with two API keys: a publishable pb_project_c… carrying the role anon, and a service-role pb_project_s… carrying service_role. Those roles govern the stack's own data and management surfaces, which a key can address directly.

They do not choose the Postgres role your backend runs as. Your endpoints authenticate from the Bearer JWT, not from the apikey header, and the transaction is bound to backend_authenticated whichever key the client sent. The only switch between the two Postgres roles is which Database surface your code calls:

The practical rule: the service-role key never reaches a browser or an app, and inside your backend the service-role connection is only ever reached through a call you wrote.

Enabling RLS on a table

An insert rejected by a WITH CHECK policy does not come back as an empty result — the typed insert throws, because a write that returns no row was filtered away and handing the author undefined would surface three lines later as a 500 with no cause attached:

insert into todos returned no row — the write was rejected (an RLS policy, most likely).

The policy() builder

policy(name) starts a builder whose methods chain and whose name must be unique per table.

Prefer the typed expression: p.col(…) is checked against this table's columns at compile time, while a string is checked by Postgres while CREATE POLICY runs — which is in the middle of an apply. A raw string stays available for anything the expression language cannot say.

These compile to ordinary Postgres CREATE POLICY clauses, so the usual semantics hold:

• USING filters which existing rows a command may see or touch — SELECT, UPDATE, DELETE.
• WITH CHECK validates rows being written — INSERT, UPDATE.
• For an owner-style rule covering everything, set both, as in the example above: using keeps other people's rows invisible, withCheck stops anyone writing a row they would not own.

Policy expressions are normalised through Postgres' own parser before being compared, because the catalog stores the reparsed form — without that, a hand-written user_id = auth.uid() would differ from the stored (user_id = auth.uid()) and recreate the same policy on every single apply.

auth.uid()

Inside a policy expression, auth.uid() is the calling user's id. Write it wrapped in a sub-select, as in every example on this page:

user_id = (select auth.uid())

Per-command policies

Split the policies when reads and writes have different rules — todos anyone signed in may read, but only the owner may change:

// db/per-command.ts
import { boolean, defineSchema, defineTable, ownedByUser, policy, text, uuid } from "@palbase/backend";

const todos = defineTable("todos", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    user_id: ownedByUser(),
    title: text().notNull(),
    done: boolean().default(false),
  },
  policies: (p) => [
    policy("todos_read_all")
      .for("select")
      .using("true"),
    policy("todos_owner_inserts")
      .for("insert")
      .withCheck(p.col("user_id").eq(p.auth.uid())),
    policy("todos_owner_updates")
      .for("update")
      .using(p.col("user_id").eq(p.auth.uid()))
      .withCheck(p.col("user_id").eq(p.auth.uid())),
    policy("todos_owner_deletes")
      .for("delete")
      .using(p.col("user_id").eq(p.auth.uid())),
  ],
});

export default defineSchema("per_command", { tables: [todos] });

Permissive policies for the same command OR together — a row is visible if any of them allows it. Use .as("restrictive") for a condition that must also hold whatever else is declared, such as a tenant guard.

Membership: the pattern that does not recurse

Anything shared — a channel, a project, an organisation — needs the same shape: a membership table joining users to the thing, and a policy on the thing that asks whether the caller is in it. Written by hand, the two policies point at each other, and Postgres refuses the pair at query time:

ERROR: infinite recursion detected in policy for relation "channel_members"

The error names a relation, not the cycle, so it reads like a problem with one table when it is a problem with the pair. What happened is symmetric reasoning: channels are visible to members, so the policy on channels reads channel_members; memberships are visible to members, so the policy on channel_members reads channels. Evaluating either one requires the other.

The way out is to make it asymmetric. The membership table is protected by the caller's identity ALONE — it never looks at the thing it points to — and every other table subqueries into it:

// db/membership.ts
import { defineSchema, defineTable, ownedByUser, policy, text, uuid } from "@palbase/backend";

// 1. The membership table stops the recursion. Its policy names auth.uid()
//    and nothing else, so evaluating it never reaches another policy.
export const channel_members = defineTable("channel_members", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    channel_id: uuid().references(() => channels.id),
    user_id: ownedByUser(),
  },
  policies: (p) => [
    policy("own_membership")
      .for("all")
      .to("authenticated")
      .using(p.col("user_id").eq(p.auth.uid())),
  ],
});

// 2. Everything else asks the membership table. memberOf() writes the
//    subquery for you, in the shape that terminates.
export const channels = defineTable("channels", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    name: text().notNull(),
  },
  policies: () => [
    policy("member_read")
      .for("select")
      .to("authenticated")
      .memberOf("channel_members", "channel_id"),
  ],
});

// 3. And so does anything hanging off it.
export const channel_messages = defineTable("channel_messages", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    channel_id: uuid().references(() => channels.id),
    body: text().notNull(),
  },
  policies: () => [
    policy("member_read")
      .for("select")
      .to("authenticated")
      .memberOf("channel_members", "channel_id", { column: "channel_id" }),
  ],
});

export default defineSchema("membership", {
  tables: [channel_members, channels, channel_messages],
});

memberOf(membershipTable, foreignKey) compiles to the subquery form:

"id" IN (SELECT "channel_id" FROM "channel_members" WHERE "user_id" = (select auth.uid()))

• column — which column of THIS table the subquery matches. Defaults to id, which is right for the thing itself; pass the FK name for a child table, as channel_messages does above.
• user_id — the membership table's own user column, if it is not called user_id, via { userColumn: "member_id" }.
• (select auth.uid()) rather than a bare auth.uid(): the scalar subquery is evaluated once per statement instead of once per row.

A policy that already has a using(...) refuses memberOf rather than combining with it — memberOf is the using expression, and silently AND-ing two would leave you unable to tell which one is filtering.

Note: writes need their own policy. memberOf on .for("select") only decides what is visible; add a second policy .for("insert") with .withCheck(...) for who may write, or use .for("all") when the same condition governs both.

RLS guards reads, not foreign-key existence checks

RLS isolates rows: a SELECT, UPDATE or DELETE only ever reaches the caller's own. But a foreign-key existence check is not RLS-filtered. When you insert a row whose FK points at a parent, Postgres validates that the referenced id exists, and that validation sees the parent regardless of who owns it. A client can therefore attach its child rows to a parent it does not own.

Nothing leaks — they still cannot read the parent — but it is a sharp edge, and the fix is in your service rather than in a policy. Read the parent on the default RLS-enforced surface, where one the caller does not own is invisible, and reject before writing:

// modules/orders/order-item.service.ts
import { Database, Injectable, NotFound, z } from "@palbase/backend";

export const OrderItem = z.object({ id: z.string(), order_id: z.string(), sku: z.string() });
export type OrderItem = z.infer<typeof OrderItem>;

@Injectable()
export class OrderItemService {
  // RLS makes an order the caller cannot see resolve to null — reject before insert.
  async add(orderId: string, sku: string): Promise<OrderItem> {
    const order = await Database.public.orders.findById(orderId);
    if (!order) throw new NotFound("order not found");

    return Database.public.order_items.insert({ order_id: orderId, sku });
  }
}

A withCheck policy on the child table validates the child's own columns; it cannot reach across the foreign key to check the parent's owner. Ownership of a referenced parent is an application-layer check.

$asService(): the explicit bypass

Some code legitimately needs cross-user reach — an admin endpoint, a job fanning notifications out, a cleanup sweep. Database.$asService() returns a sibling with the same typed surface, running as backend_service_role, which carries BYPASSRLS:

import { Database } from "@palbase/backend";

// RLS-enforced (the default): only the caller's own rows.
const mine = await Database.public.todos.findMany();

// Service-role: every user's rows. Explicit, and greppable in review.
const all = await Database.$asService().public.todos.findMany();
const rows = await Database.$asService().$query("select count(*) from todos");

A service-role transaction is a plan like any other, so the callback is synchronous and tx.public.* offers updateWhere, not an update addressing one row by id:

import { Database, Injectable, NotFound } from "@palbase/backend";

@Injectable()
export class TodoAdminService {
  async complete(id: string): Promise<void> {
    await Database.$asService().$transaction((tx) => {
      tx.public.todos
        .updateWhere({ id }, { done: true })
        .expectOne(new NotFound("todo not found"));
    });
  }
}

The transaction rules are on Database; the rest of what $asService() is, and is not:

• It is a second transaction, on a second connection. The role reaches Postgres once, in the BEGIN's bind statement, and it is transaction-scoped — a sibling riding the request's own transaction would still be backend_authenticated, and $asService() would silently mean nothing: no throw, no log, just the caller's own rows where the author asked for everyone's. The obvious repair, re-issuing set_config('role', …) per operation, is worse: Promise.all([Database.$query(…), Database.$asService().$query(…)]) interleaves them on one connection and the user's statement can land between the service's set-role and its own — RLS silently off on the default path, which is the one direction a security seam must never fail.
• It carries the same request.jwt.claims. $asService() changes what the caller may touch, not who they are, so auth.uid() still resolves — and a policy written TO service_role still sees the real user.
• It fails closed. A service role provisioned without BYPASSRLS is named by no policy, so it reads zero rows rather than quietly reading everyone's. That was measured on a live stack, on a role created by hand during a diagnosis.
• One transaction per request, opened lazily. $asService() called and never used opens nothing; called twice it is the same surface. Both transactions settle with the request, but as two — they are not atomic with each other.
• There is no double bypass. The returned client does not re-expose $asService().

Two habits keep it honest. Validate before you bypass — inside $asService() your own code is the only authorisation layer left, so check the caller's right to the operation first. And call it inline, at the point of use, rather than stashing the client in module scope; the whole value of an escape hatch is that a reviewer can find it.

Changing a policy

Policies live in db/public.ts beside their table and reach a database the same way every other schema change does — there are no migration files and nothing to commit alongside them.

# 1. Edit rls / policies in db/public.ts
# 2. Read the statements against the stack on this machine
palbase db plan
palbase db apply
# 3. Carry it to the linked project — schema and code in one request
palbase push

The plan names each one:

  enable RLS     todos
  add policy     todos_owner on todos
  change policy  todos_owner on todos
  drop policy    todos_public on todos

The rail converges — this is not additive-only. A policy removed from the declaration is dropped from the database. A policy whose command, roles, USING, WITH CHECK or permissiveness changed is applied as a DROP POLICY followed by a CREATE POLICY, because a policy body cannot be altered in place idempotently. Policies are keyed by (table, name), so a rename is a drop plus an add.

Dropping a policy is listed among the ordinary changes rather than the destructive ones, and it is applied before any column drop. Both halves of that matter: dropping a policy takes no data away and a table with RLS and no policy passes nothing, so it closes rather than opens; and Postgres refuses to drop a column a policy still references, which is how removing a feature's column and its policy together used to fail with cannot drop column … because other objects depend on it.

Related

• Schema — defineSchema, the column builders, and where policies live
• Database — the RLS-enforced surface, null for hidden rows, and transaction plans
• Authentication — how the calling user is established before any of this runs
• Schema changes — how an edit to db/public.ts reaches a database
• the two API keys — the publishable and service-role keys, and where each belongs
• Database — palbase db plan and db apply against the stack on your machine