Row Level Security in SQLite on Cloudflare

October 17, 2026 · Andrew Arrow

Alice should be able to edit her message. Bob should be able to edit his. Giving both of them an API to a shared table is easy; making sure neither can change the other's rows is the part you can't leave to the frontend.

Supabase makes this a database feature through PostgreSQL's row level security. We want that kind of developer experience on Cloudflare, with SQLite for the data and JavaScript for backend logic. In Mainbrella BFF, we put a security guard in front of the database: a trusted Cloudflare Worker. SQLite doesn't know who's making an HTTP request. Our Worker does.

Moving the guard means taking responsibility for every route into the database. We run customer JavaScript separately from the Worker that holds database credentials, so those functions have to use the same protected API as the frontend.

Try editing somebody else's message

Open the message board demo, choose Alice, and click Log in. Both Alice's and Bob's posts are readable. Every row has an Edit action, including the ones Alice doesn't own. Open Bob's message, change the text, and click Save message.

The request reaches the backend, which returns HTTP 403 with permission_denied. Bob's message stays as it was, and your unsaved text stays in the editor. This capture shows that attempt against the live demo:

You can edit Alice's own message successfully. Log out, choose Bob, and their permissions reverse. Leaving the other user's Edit action available is deliberate: hiding a button would only demonstrate that we can hide a button. Here you can send the forbidden request and see the server refuse it.

The dropdown is a shortcut for this public example. It issues a real, expiring project user session for one of two fixed demo identities. General public signup and password login aren't part of this first version; other projects don't accept arbitrary user IDs from a browser as proof of identity.

Connect to the same table

This is the connection code for that board. Paste it into your frontend to read the messages:

const config = {
  "apiOrigin": "https://api.mainbrella.com",
  "projectId": "90da3093-84b6-4cd7-a39a-a8afc39403b8",
  "publicKey": "bff_pk_1f9bb9fd543d6a5476ad7ce210577c2479d290a5cdc06282de9eb884d57005c4",
  "table": "messages"
};

const response = await fetch(
  `${config.apiOrigin}/bff/data/${config.projectId}/tables/${config.table}?select=*`,
  { headers: { apikey: config.publicKey }, credentials: 'omit' }
);
const result = await response.json();
if (!response.ok) throw new Error(result.error || 'Could not load data.');
const { data } = result;

This key is intentionally public. It identifies the project and limits the operations available to the app; it doesn't identify Alice. The table explicitly allows public reads, so this request needs no login. Browser origins are also restricted by the key's configuration, so your own deployment needs a key configured for its origin. CORS isn't a substitute for permissions.

For a write, the demo also sends Authorization: Bearer <userToken>. The Worker checks that session, its expiry, and its project before accepting the user's identity. These sessions use random opaque tokens, with only their hashes stored server-side. Putting user_id: "alice" into a request body cannot sign you in as Alice.

The Worker adds the ownership condition

The project owner configures a policy for messages. This is the current policy shape, expressed as JSON:

{
  "select": "public",
  "insert": "own",
  "update": "own",
  "delete": "own",
  "ownerColumn": "user_id"
}

The Worker checks the key's table grant and the saved policy on each operation. A missing policy denies app access. It validates table and column identifiers, checks that a configured ownership column exists, and builds SQL with bound values. The same engine can use a different ownership column in another project's table; we don't write a custom Worker for each schema.

Suppose Alice sends an update targeting Bob's row, reply. If she submits only the new body, the generated statement has this form:

UPDATE "messages" SET "body"=?
WHERE "id" IS ? AND "user_id" IS ?
RETURNING *

-- Parameters: ["Alice's proposed edit", "reply", "alice"]

Bob's row fails the ownership condition, so zero rows change. Alice can pick a row ID, but the Worker supplies the identity parameter from her verified session. Even if her frontend sends no ownership filter, the Worker adds that last condition. The check and mutation happen in one statement, avoiding a separate read followed by a write that might race with another change.

The demo submits the original user_id along with the body. When Alice edits Bob's post, that value is bob, so the Worker rejects the ownership mismatch with 403 before sending an update to D1. Omitting the author field just reaches the zero-row case above. Neither request changes Bob's message.

Each project has its own D1 database. The trusted gateway selects it from server-side project records and sends parameterized queries through Cloudflare's account API. Account credentials stay in the gateway. D1 also supports prepared statements through Worker bindings, but we use the account API so new projects don't require redeploying static database bindings.

This is a small policy system today: public or denied reads, and own-row writes. Private todo lists would need own-row reads, too. Arbitrary SQL policy expressions, joins through the Data API, and a general field permission system aren't implemented. The API accepts a restricted set of table operations, rather than trying to make arbitrary client SQL safe by appending a string.

Backend logic in JavaScript

Supabase already has the row security feature we're borrowing from. A PostgreSQL update policy can express the same ownership rule with USING for the existing row and WITH CHECK for the resulting row. With RLS enabled, grants in place, and a SELECT policy allowing reads, the update policy looks like this:

CREATE POLICY "Authors can edit their messages"
ON messages FOR UPDATE TO authenticated
USING ((SELECT auth.uid()) = user_id)
WITH CHECK ((SELECT auth.uid()) = user_id);

Those are SQL policy expressions. More involved database functions can use PL/pgSQL, PostgreSQL's procedural language, with its own variables, branching, and control flow.

What I want from BFF is to keep the app's procedural backend logic in JavaScript. The message-board starter includes an edit_message function. After its sign-in and input checks, it does this:

return (await ctx.db.update(
  'messages',
  { id: input.id },
  { body: input.body.trim() }
)).results;

There is no hand-written ownership filter in that function. The protected ctx.db API applies it using the caller's verified identity. A developer can add validation and branching in the language they already use for the frontend without remembering to copy a permission check into every database call. That's a big win for the kind of small app backend we're building.

Supabase also offers TypeScript Edge Functions, so it has a path for developers who prefer JavaScript, too. With BFF, we're choosing to put the managed permission engine and the API used by custom JavaScript on Cloudflare, with SQLite underneath.

Customer functions run in fresh Dynamic Workers, separate from our gateway. They receive a narrow database capability with select, insert, update, and remove methods. For an app caller, those methods go back through the same policy checks as direct table requests:

We don't hand those functions an unrestricted D1 binding, account token, or raw SQL method. The runtime has an empty environment and globalOutbound: null, which blocks direct HTTP and TCP egress. Database access starts at none and must be granted explicitly. A sandbox that hides another customer's memory but hands out an all-powerful database connection would miss the point.

The database capability lives in trusted code and carries the project and caller identity. Customer code gets methods it can call across the isolate boundary, not the credentials behind them. Owner-invoked functions and the owner's SQL editor retain administrative access; a public key or user session cannot select that role.

SQLite won't catch a bypass for us

PostgreSQL enforces RLS inside the database for roles subject to those policies. That protection can survive an application accidentally leaving a condition out of a query. There are privileged exceptions, including superusers and roles with BYPASSRLS, but the database owns the enforcement.

Our D1 database will execute whatever SQL the trusted gateway is authorized to send. Every app access path therefore has to pass through our checks, including calls from customer functions. A forgotten route with unrestricted database access would be a security bug. Parameterizing a query prevents values from becoming SQL syntax; it doesn't establish who owns the rows.

The recent policy work and demo login change make this concrete enough to try. The shared authorization code handles both routes. Expanding its query language means testing more ways to cross that line, especially joins, ownership changes, revoked sessions, and concurrent writes. Matching PostgreSQL's policy system is substantially more work than supporting this board.

I like the result we can show now: a frontend can read a shared table, a user can change their own rows, and a JavaScript function uses those same permissions. BFF's job is to make that ordinary on Cloudflare. Log in as Alice and try Bob's Edit button. Being able to send that request, and having the backend refuse it, is the behavior the rest of the product has to preserve.