The No-Backend Backend: Neon Data API, RLS and 27 Attacks

We built a multi-tenant team task board with no backend. The browser loads static files, signs in with Neon Auth, and reads and writes through the Neon Data API. There is no API server, no serverless function and no middleware. Who belongs to which team is Neon Auth's job; what a signed-in user may read or write is decided in Postgres, by row-level security policies, column grants and one carefully written function.

Then we gave a second team's owner, eve, a valid account and a script, and had her try 25 ways into the first team, plus two tries from the wrong role inside it: crafted filters, embedded joins, aggregate counts, forged tokens, alg: none, a token signed with her own key, bulk updates, upserts over the other team's ids. All 27 were refused. We then broke the security layer four realistic ways, one at a time. Three of them let specific attacks through, and the suite caught each one. The fourth, a sloppy WITH CHECK (true), did nothing on its own, because a second layer stopped it.

The one thing the policies could not stop was time. A member removed from a team kept reading its data for fifteen and a half minutes , the life of the token they already held plus about thirty seconds. That one has a fix. In our latency run its median cost was under a millisecond for reads and writes and about 5 ms for the RPC.

The-DevOps-Daily/neon-data-api-rls on GitHub

TLDR

• The architecture: static React, Neon Auth organizations for teams, the Neon Data API for every query, and Postgres (policies, grants, one function) as the authorization layer. Zero server code.
• The tenant comes from a signed token. Neon Auth puts the active organization in the JWT, and auth.organization_id() reads it inside Postgres. Every policy is org_id = auth.organization_id().
• 27 attacks, 27 refused. Each attack has to return exactly the expected answer, and every column of the victim's rows is compared before and after, so a "refusal" that quietly changed data would have failed.
• Column grants are the second wall. A policy with WITH CHECK (true) let nothing through, because the client is never granted the org_id column. Adding table-wide grants on top is what opened it.
• Removal is not instant. Right after being removed, a member's old token still read, wrote and called the RPC, and it kept reading for its full 900 seconds plus 28. Checking Neon Auth's member table inside the policies cut it off at once; reads and writes stayed within a millisecond, the RPC was 5 ms slower.
• Fast enough not to notice. A Data API request took 41 to 44 ms at p50; a plain TCP connect to the same address took 40 ms.

Prerequisites

• A Neon project with Neon Auth enabled on the branch. Its organization plugin, which this uses for teams, is on by default.
• The Data API provisioned on the same branch with Neon Auth as the provider, in the Console or with neon data-api create.
• Node.js 20 or later for the scripts, and the repository above.
• Some familiarity with Postgres row-level security. Your Tenant Isolation Is One Forgotten WHERE Clause Away covers the model this builds on.

What "no backend" means here

In a usual stack, the browser calls your API, your API authenticates the user, works out their tenant and runs the query with a WHERE tenant_id = ... it wrote itself. Every one of those steps is code you own and can get wrong.

Here, three managed pieces replace it:

1. Browser static files only
2. Neon Auth sign in, token with the team
3. Data API verifies the token, runs as authenticated
4. Postgres policies read auth.organization_id()

The Data API is PostgREST-compatible: GET /tasks?select=id,title&done=eq.false is a query, POST /rpc/org_summary calls a function. It checks the JWT on every request against Neon Auth's keys and runs the query as the authenticated role, with the token's claims available inside Postgres through the auth functions.

So the whole authorization story fits in two SQL files. Everything below is about whether those two files hold.

The tenant is a signed claim

A team is a Neon Auth organization. When a user picks one, Neon Auth issues a token that names it in an o claim, and it only does that for an organization the user belongs to. Asking to make someone else's team active returns 403 User is not a member of the organization, which is attack T7 below.

Inside Postgres, auth.organization_id() returns that claim. The tables default their tenant column to it, and every policy compares against it:

create table tasks (
  org_id uuid not null default auth.organization_id(),
  id uuid not null default gen_random_uuid(),
  title text not null check (length(title) between 1 and 200),
  done boolean not null default false,
  created_by text not null default auth.user_id(),
  created_at timestamptz not null default now(),
  primary key (org_id, id)
);

alter table tasks enable row level security;

create policy tasks_read on tasks for select
  using (org_id = auth.organization_id());

create policy tasks_insert on tasks for insert
  with check (org_id = auth.organization_id());

-- Only owners and admins delete. The role is in the same signed claim.
create policy tasks_delete on tasks for delete
  using (
    org_id = auth.organization_id()
    and auth.organization() ->> 'role' in ('owner', 'admin')
  );

If you read the RLS starter post, this is the same shape as its tenant_id = current_tenant(). The difference is where the tenant comes from. There, the application server wrote it into a session setting before each query, and a bug in that server could set it wrong. Here there is no server; the tenant arrives in a token that Neon Auth signed and the Data API verified.

The primary key is (org_id, id) for the reason the starter gives: with a globally unique id, a failed insert could tell eve that an id exists in another team. Scoped to the tenant, her copy of an id lands in her own team.

Grants are the wall behind the wall

The Data API exposes whatever the grants allow. The policies decide which rows; the grants decide which tables, columns and functions. We made the grants as narrow as the app is:

grant select on tasks, task_comments to authenticated;
grant insert (title, done) on tasks to authenticated;
grant update (title, done) on tasks to authenticated;
grant delete on tasks to authenticated;

revoke all on tasks, task_comments from anonymous;

The migration revokes everything from authenticated on these tables first, so a table-wide grant made earlier cannot outlive the narrow ones. After that, the client can insert a title and a done flag, and nothing else. It never sends org_id or created_by: the column defaults fill them from the token. An update cannot move a task to another team, whatever a policy says, because org_id is not an updatable column for this role.

That turned out to matter more than it looks, as the break section shows.

There is one RPC, a dashboard summary. It is security definer, which means it runs with the owner's rights and no policy applies inside it. Its WHERE clause is the only thing keeping it to the caller's team:

create function org_summary()
returns table (total bigint, done bigint, comments bigint)
language sql stable security definer set search_path = public, pg_temp as $$
  select count(*), count(*) filter (where t.done),
    (select count(*) from task_comments c where c.org_id = auth.organization_id())
  from tasks t
  where t.org_id = auth.organization_id()
$$;

-- Functions are executable by PUBLIC by default, which includes anonymous.
revoke execute on function org_summary() from public;
grant execute on function org_summary() to authenticated;

A hostile client, 27 ways

The attack script signs in three users: alice owns Acme, bob is a member of Acme, and eve owns Evil Corp. Twenty-five of the attacks are eve's, all aimed at Acme; M1 and M2 are the wrong role inside Acme. The requests are raw HTTP, not a client library, because a library tidies up exactly the requests an attacker would not.

Each attack states exactly what must come back, down to the status code; an error, a rate limit or an unexpected status counts as a failure, not a pass. Then the harness reads every column of every Acme row through the owner connection and compares it with a copy taken just before, so an attack that returned an error but still changed something fails too. It also refuses to run if Acme has nothing to steal.

attack.mjs

$ node scripts/attack.mjs --json
id attack status rows result
R1 list every task 200 2 held
R2 filter on Acme's org_id 200 0 held
R3 ask for Acme's task ids directly 200 0 held
R4 OR the filter with her own org 200 2 held
R5 embed comments inside tasks 200 2 held
R6 read comments on an Acme task 200 0 held
R7 exact count of Acme's tasks 200 0 held
R8 aggregate count() over all tasks 200 1 held
R9 the org_summary RPC 200 1 held
R10 ask for the neon_auth schema by header 404 - held
W1 insert a task with Acme's org_id 403 - held
W2 update every task in Acme 200 0 held
W3 move her task into Acme 403 - held
W4 delete Acme's tasks 200 0 held
W5 upsert over an Acme task id 403 - held
W6 comment on an Acme task 409 - held
W7 comment as someone else 403 - held
M1 bob (member) deletes an Acme task 200 0 held
M2 alice (owner) edits bob's comment 200 0 held
T1 no token at all 400 - held
T2 no token, call the RPC 400 - held
T3 her token with the org claim edited to Acme 400 - held
T4 alg none, no key id 400 - held
T5 alg none, with the real key id 400 - held
T6 signed by her own key, with the real key id 400 - held
T7 ask Neon Auth to make Acme her active org 403 - held
T8 ask Neon Auth to change her role claim 400 - held

27 of 27 held

A few of these are worth reading closely.

R7 and R8 ask for counts, not rows. Prefer: count=exact and select=count() are how a PostgREST client learns totals. Both counted only what the policy let eve see: zero Acme tasks, and exactly her own.

W2 and W4 return 200 with no rows. Row-level security does not raise an error when a statement touches rows you may not see; it leaves them out. An UPDATE across all of Acme succeeds and updates nothing. That is correct, and it is also why the app treats an empty result from a delete as the refusal. We come back to this in the gotchas.

W1, W3, W5 and W7 never reached a policy. They tried to set org_id, id or created_by, and the column grants refused them with 403 permission denied for table.

T1 to T6 never reached Postgres. No token, an edited claim, alg: none with and without the real key id, and a token signed with eve's own key were all rejected at the Data API with 400: missing authentication credentials, signature error, missing key id and signature algorithm not supported.

T8 asked Neon Auth to change her role claim. The Data API picks the Postgres role from the token's role claim, so a user who could set it would pick their own database role. Neon Auth refused the update with 400; the stored role stayed user and her next token still said authenticated.

R10 is weaker than it looks. It asks for the neon_auth schema with an Accept-Profile header. The Data API ignored the header and looked in public, where there is no user table, so it answered 404. That shows the Data API only serves public here; it does not test the permissions on Neon Auth's own tables.

Four ways to break it

A test suite that has only ever passed proves little. The break script applies one realistic mistake, runs all 27 attacks against it, checks that exactly the expected attacks got through, and restores:

break.mjs

$ node scripts/break.mjs
weak-with-check: tasks_insert says WITH CHECK (true), everything else unchanged
  still held: all 27

weak-with-check-and-default-grants: the same policy, plus the table-wide grants the Data API can add for you
  BROKEN 1: W1 (insert a task with Acme's org_id)

definer-without-filter: org_summary() loses its WHERE clause
  BROKEN 1: R9 (the org_summary RPC)

table-without-rls: task_comments ships with row-level security off
  BROKEN 2: R6 (read comments on an Acme task), M2 (alice (owner) edits bob's comment)

restored: 27 of 27 held again

WITH CHECK (true) on its own let nothing through. This is the mistake the RLS starter post warns about, and with a server in front it would let a client write into any tenant. Here the client cannot choose org_id at all, so the weak policy had nothing to wave through. The column grants caught it.

The same policy plus table-wide grants opened it. The Data API setup offers to grant SELECT, INSERT, UPDATE, DELETE on every table in public to authenticated. That is convenient, and it is exactly what turned a harmless mistake into eve inserting tasks into Acme. If you take the default grants, your policies are the only wall, and every WITH CHECK has to be right.

A security definer function without its filter leaked counts. No policy applies inside it, so the WHERE clause is everything. This is the mistake most likely to survive review, because the function looks like a harmless dashboard query.

A table without row-level security leaked its rows. task_comments with RLS off let eve read Acme's comments by task id. New tables are where this happens, because enable row level security is a separate statement nobody remembers until it is missing. It also broke M2 inside Acme: the rule that only a comment's author can edit it disappeared with the policy.

The fifteen minutes

Policies decide what a token may do. They do not decide how long a token lives.

The revocation script signs bob in, removes him from Acme through Neon Auth, and keeps using the token he already had:

revocation.mjs, token only

# about 16 minutes: it waits for the old token to die
$ node scripts/revocation.mjs
   2s bob's token: org Acme, expires in 900s
   2s before removal, bob's token: tasks 3, comments 1, org_summary total 3, insert inserted
   3s alice removes bob from Acme: HTTP 200
   3s bob asks for a new token: HTTP 500, no token returned
   3s right after removal, bob's OLD token: tasks 4, comments 1, org_summary total 4, insert inserted
 932s the old token is refused (HTTP 400, 0 rows), 30s after its exp
      the last request it served was 28s after its exp
 933s cleaned up 2 probe task(s) the inserts above wrote
 934s bob invited back into Acme

Right after the removal, bob's request for a new token fails with HTTP 500. The token he already holds does not notice: it still reads Acme's tasks and comments, calls the RPC, and writes a new task (the fourth task in the list is the probe it wrote a line earlier). We then followed task reads to the end: they kept working for the rest of its 900-second life, and past it. The last read it served was 28 seconds after its exp by our client's clock, and the next check two seconds later was refused. Writes and the RPC were checked right after the removal, not followed to the end. That looks like leeway for clock skew; we did not measure the verifier's setting or the offset between the clocks. If "removed from the team" has to mean "cannot touch the team's data", fifteen and a half minutes is a long time.

The fix is to ask a second question in every policy: is this user still a member right now? Neon Auth keeps memberships in neon_auth.member, which the authenticated role cannot read, so a security definer function looks it up:

create function current_org_role() returns text
language sql stable security definer set search_path = pg_catalog, pg_temp as $$
  select m.role from neon_auth.member m
  where m."organizationId" = auth.organization_id()
    and m."userId" = auth.user_id()::uuid
$$;

alter policy tasks_read on tasks
  using (org_id = auth.organization_id() and (select current_org_role()) is not null);

Wrapping the call in (select ...) lets Postgres evaluate it once per statement instead of once per row; a statement that involves two policies still does two lookups. The delete policy reads the role from the table as well, though we did not test a demotion. The RPC needs the same condition in its WHERE clause, because policies do not apply inside it.

With it on, the same test, every path refused the moment bob was removed:

revocation.mjs, strict

$ node scripts/strict.mjs
strict mode on: policies also check Neon Auth's member table
$ node scripts/revocation.mjs
   1s bob's token: org Acme, expires in 900s
   1s before removal, bob's token: tasks 3, comments 1, org_summary total 3, insert inserted
   1s alice removes bob from Acme: HTTP 200
   1s bob asks for a new token: HTTP 500, no token returned
   1s right after removal, bob's OLD token: tasks 0, comments 0, org_summary total 0, insert HTTP 403 new row violates row-level security policy for table "tasks"
   2s the old token got nothing after the removal (HTTP 200, 0 rows), 899s before its exp
   2s cleaned up 1 probe task(s) the inserts above wrote
   2s bob invited back into Acme

All 27 attacks still hold with it on. The cost, in one run of each:

Request time at p50, token only against strict

100 requests of each kind, one at a time and interleaved, from a client about 40 ms from the Data API endpoint. Strict adds a Neon Auth membership lookup to every policy, and two to the RPC. One run of each, about 16 minutes apart, so network drift may be part of the difference.

Reads and writes stayed within a millisecond of the token-only run. The RPC, which does two lookups, was 5 ms slower at p50. The two runs were about sixteen minutes apart, so some of that may be the network; we would not quote it as more than a few milliseconds. We would turn it on for anything where removal matters, which is most things. It lives in the repository as db/optional/live_membership.sql, applied with npm run strict.

What it costs

Per request, about one network round trip. 100 requests of each kind, one at a time and interleaved so they shared the same network conditions, from a client about 40 ms from the endpoint, with strict mode off:

A read with embedded counts and an RPC took a millisecond or two more than a bare TCP connect to the same address; a write took about four. We did not isolate how much of that is JWT verification, the policies or the query, and the tables are tiny: a policy that has to join or scan grows with the data, and this does not measure that.

On the page, one JavaScript file. The production build is three files: 587 kB of JavaScript (161 kB gzipped), 11 kB of CSS and the HTML. It includes React, the Neon Auth client with its organization plugin, and the PostgREST client; we did not break down which part weighs what.

In operations, nothing to run. There is no server to deploy, patch or scale. The trade is that your security review moves into SQL, and SQL is where the four breaks above live.

Gotchas we hit

• A security invoker function with a normal body cannot see the token. The auth schema belongs to cloud_admin, and authenticated has no USAGE on it. Your owner role cannot grant it: ours tried and got WARNING: no privileges were granted for "auth". So an invoker function whose body is a string (as $$ select auth.user_id() $$) fails with permission denied for schema auth, because Postgres resolves that name when the function runs, as the caller. The same function with a SQL-standard body (return auth.user_id()) works, because Postgres resolves it once, when the function is created. Policies work for the same reason, and security definer functions work because they run as the owner. scripts/invoker-bodies.mjs shows all three.
• After a removal, asking for a new token fails with HTTP 500. Bob's /token call right after alice removed him returned 500 and no token, in each of our runs. It gives him nothing, so it is safe, but an app should treat it as "no longer in this team" rather than an outage.
• New functions need a schema refresh. The Data API caches the schema. After adding a function, some requests returned 404 until we ran neon data-api refresh-schema.
• An empty result is the refusal. A delete that RLS blocks returns 200 and zero rows, not an error. The app checks the returned rows and tells the user.
• Switching teams means a new token. The client caches the token until shortly before it expires, and the cached one names the old team. Our app reloads after switching.
• Scripts must look like a browser to Neon Auth. Sign-in from Node fails with Origin header is required until you send one, and Neon Auth rate-limits sign-ins, so the test harness reuses sessions.
• Listing your own invitations returned 403 for our unverified test users. In our app that call failed and, because we loaded it alongside the team list with Promise.all, took the team list down with it. Each call now fails on its own.

What we could not conclude

• Performance at scale. Five tasks and one comment say nothing about how a policy with a subquery behaves over millions of rows. The latency numbers are about the request path, not the data.
• Whether fifteen minutes is fixed. We measured the token lifetime we were given. We did not look for a way to shorten it, and a shorter one would narrow the gap without closing it.
• Demotion. Strict mode reads the role from Neon Auth's table, so a demoted owner should lose delete at once. We did not test it.
• Anything about the anonymous role. Requests without a token were rejected before they reached Postgres; we never exercised the anonymous role itself.
• The limits of Neon itself. This tests an application's security layer through the Data API. It is not a penetration test of Neon Auth or the Data API.
• Browser behaviour beyond Chromium. The session cookie is a partitioned third-party cookie. We drove the app in headless Chromium only, and did not test Firefox or Safari.

What we would do

1. Take the tenant from the token and default the column to it. default auth.organization_id() means the client never names a tenant, so it cannot name the wrong one.
2. Grant columns, not tables. It is what turned our weakest policy into a non-event. Skip the table-wide default grants unless you are sure every WITH CHECK is right.
3. Treat every security definer function as a policy of its own. Put the tenant filter in it, revoke it from public, and test it with a hostile token.
4. Check live membership in the policies. A lookup in Neon Auth's member table, once per statement, closes a fifteen-minute gap.
5. Keep a hostile client in the repository. Twenty-seven requests take seconds to run. The break script, which checks that each mistake opens exactly the attacks it should, is what shows they would notice.

The schema, the attacks, the breaks and every recorded run are in the repository.

Originally published at devops-daily.com.