A team task board with no backend

A static React app that talks straight to the Neon Data API, with Neon Auth for sign-in and teams. Neon Auth decides who belongs to which team; Postgres decides what a signed-in user may read or write, with row-level security policies, column grants and one carefully written function. There is no server code in this repository.

Then a hostile client attacks it, and a second script breaks the security layer in four realistic ways to show the attacks would have caught each one.

Run it

You need a Neon project with Neon Auth enabled on the branch (its organization plugin is on by default), then the Data API provisioned with Neon Auth as the provider. Both are in the Console; the Data API step with the CLI is:

neon data-api create --project-id <id> --branch <branch> --database neondb --auth-provider neon_auth

Then:

cp .env.example .env          # DATABASE_URL, NEON_AUTH_BASE_URL, DATA_API_URL
npm install
npm run migrate               # db/*.sql: tables, policies, grants, one RPC
npm run users                 # alice and bob in Acme, eve in Evil Corp, seed data
npm run attack                # 27 attacks from eve and bob, every one checked
npm run break                 # four realistic mistakes, each shown to be caught
npm run strict                # recommended: removals take effect at once (below)

cd web && cp .env.example .env && npm install && npm run dev

The app runs on http://localhost:5173, which Neon Auth allows by default. npm run build in web/ produces plain static files for any host.

Test users are on example.com, which receives no mail. Neon Auth does not send a verification email on sign-up in this configuration.

How it works

The tenant comes from the token. A team is a Neon Auth organization. When a user picks a team, Neon Auth issues a JWT whose o claim names it, and only for an organization they belong to. Inside Postgres, auth.organization_id() reads that claim. Every policy is a variation of:

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

That is the RLS multi-tenant starter's tenant_id = current_tenant() with one change: the tenant used to be a setting the server wrote, and now it is a claim Neon Auth signed.

Grants are the second wall. The client may insert a task's title and done, and nothing else. org_id and created_by come from column defaults that read the token. An UPDATE cannot move a task to another team, whatever a policy says, because the column is not granted.

One RPC, written carefully. org_summary() is security definer, so no policy applies inside it. Its WHERE org_id = auth.organization_id() is the only thing keeping it to the caller's team, and scripts/break.mjs shows what happens without it.

All of it is in db/001_schema.sql and db/002_security.sql.

What the scripts found

The attacks. scripts/attack.mjs signs in as eve, who owns Evil Corp, and aims 25 attacks at Acme: filters, id lookups, OR tricks, embedded joins, exact counts, aggregates, the RPC, another schema by header, inserts with Acme's org_id, bulk updates and deletes, upserts over Acme's ids, comments on Acme's tasks, no token, an edited token, alg: none with and without the real key id, a token signed with her own key, asking Neon Auth to switch her into Acme, and asking it to change her role claim. Two more come from the wrong role inside Acme. Each attack must return exactly the expected status and body; errors, rate limits and unexpected statuses fail. After every attack, every column of Acme's rows is compared through the owner connection, and the run refuses to start if Acme has no data to steal.

27 of 27 held

The breaks. scripts/break.mjs applies each mistake, runs all 27 attacks, checks that exactly the expected attacks got through, and restores. It runs against the token-only policies, so turn strict mode off first:

Mistake	Attacks that got through
tasks_insert with WITH CHECK (true)	none: the column grants still stop the client choosing org_id
the same, plus table-wide grants (what the Data API's default grants option adds)	W1: eve inserts tasks into Acme
org_summary() without its WHERE clause	R9: eve reads Acme's counts
task_comments without row-level security	R6: eve reads Acme's comments; M2: a comment's author rule is gone

Revocation. scripts/revocation.mjs removes bob from Acme and keeps using the token he already had, on every path:

   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

Asking for a new token after the removal fails with HTTP 500. Right after the removal, the old token still read, wrote and called the RPC; the script then followed task reads to the end, and the last one it served was 28 seconds past the token's exp by our clock. That looks like clock-skew leeway, but we did not measure the verifier's setting or the clock offset. Strict mode closes the gap.

Latency. scripts/latency.mjs, 100 requests of each kind, one at a time and interleaved, from a client about 40 ms from the endpoint, strict mode off:

	p50	p95
TCP connect (one network round trip)	39.7 ms	42.8 ms
GET /tasks with embedded comment counts	41.4 ms	46.0 ms
POST /rpc/org_summary	41.3 ms	43.2 ms
PATCH one task	44.1 ms	47.2 ms

This describes the request path as observed. It does not isolate how much of it is JWT verification, the policies or the query, and the tables are tiny.

Which functions can read the token. The auth schema belongs to cloud_admin, and authenticated has no USAGE on it; the database owner cannot grant it (WARNING: no privileges were granted for "auth"). scripts/invoker-bodies.mjs calls three one-line functions:

owner runs: grant usage on schema auth to authenticated
  -> WARNING: no privileges were granted for "auth"
whoami_invoker_string    HTTP 403  permission denied for schema auth
whoami_invoker_standard  HTTP 200  a user id
whoami_definer_string    HTTP 200  a user id

A string body is resolved when the function runs, as the caller. A SQL-standard body is resolved once, when the function is created, which is also why policies can call auth.organization_id().

Strict mode

The token says which team is active, and it stays valid for its whole life. npm run strict applies db/optional/live_membership.sql, which adds a second question to every policy and to the RPC: is the caller still a member, according to Neon Auth's own member table, right now?

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

current_org_role() is a security definer function that looks the caller up in neon_auth.member, which authenticated cannot read directly. Wrapping the call in (select ...) lets Postgres run it once per statement rather than once per row; a statement that involves two policies still does two lookups. The delete policy reads the role from the table too; we did not test a demotion.

With it on, the same revocation test:

   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. Latency at p50, one run of each, about 16 minutes apart:

	token only	strict
TCP connect (one network round trip)	39.7 ms	38.1 ms
GET /tasks with embedded comment counts	41.4 ms	41.8 ms
POST /rpc/org_summary	41.3 ms	46.4 ms
PATCH one task	44.1 ms	45.0 ms

Reads and writes stayed within a millisecond. The RPC, which does two membership lookups, was about 5 ms slower; network drift between the runs may be part of that.

Layout

db/                  schema and security, applied in order by npm run migrate
db/optional/         the live membership check, applied by npm run strict
scripts/
  users.mjs          the cast: alice and bob in Acme, eve in Evil Corp
  attack.mjs         27 attacks, each checked against the real rows
  break.mjs          four mistakes, each shown to be caught, then restored
  revocation.mjs     how long a removed member keeps access
  latency.mjs        request cost next to the network round trip
  concurrency.mjs    parallel requests from two users, compared with the truth
  invoker-bodies.mjs which kinds of function can read the token
  strict.mjs         turn the live membership check on or off
  screenshots.mjs    the images in docs/screenshots
web/                 the app: Vite, React, Tailwind, @neondatabase/neon-js
data/                recorded results from the runs above

License

MIT