My database is the referee: a fair exchange enforced by Supabase RLS

I'm building Nearside, an end-to-end encrypted messenger on Supabase. The server never holds a readable message body. Every row is ciphertext sealed on the device, and the keys never leave the phone.

One feature needed something the encryption couldn't give me. I call it a sealed exchange. You ask a question and answer it yourself at the same time. Your friend answers too, and neither of you can read the other's answer until both answers exist. Think "what should we name the dog", where you don't want the second person copying the first.

Below is how the database enforces that, a bug I shipped in the first version, and the two scripts I use to check that the live project actually runs the SQL in the repo.

Why the app can't be the one checking

The obvious version is a check in the client: don't show the other answer until I've answered. But the repo is public, and the Supabase URL and anon key ship inside the app. Anyone can delete that check, or skip the app and query the table directly with their own session token. A client-side rule in an open-source app is a suggestion.

A fair exchange between two people who don't trust each other needs a referee. Whoever reads second can always read and walk away. So the database is the referee. It holds two ciphertexts it can't open, and it decides one thing: when each side gets handed the other's.

The table

CREATE TABLE public.sealed_answers (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  prompt_id  uuid NOT NULL REFERENCES public.messages(id) ON DELETE CASCADE,
  user_id    uuid NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  ciphertext text NOT NULL,
  nonce      text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT sealed_answers_one_each UNIQUE (prompt_id, user_id)
);

The question itself is an ordinary message row with a sealed_prompt flag. Both people can read the question right away, which is what makes it a question and not a puzzle. The unique constraint means one answer each. Without it, someone could stack several answers, and "which one counts" has no good answer.

The rule, in one policy

CREATE POLICY "sealed_answers_select_after_own" ON public.sealed_answers
  FOR SELECT TO authenticated
  USING (
    user_id = (select auth.uid())
    OR public.has_answered(prompt_id, (select auth.uid()))
  );

You can always see your own answer, because the app needs it to render your side while you wait. You can see anyone else's only once you've committed one of your own. Someone outside the conversation can't insert an answer (the INSERT policy checks they're one of the two participants), so they never get past the second branch.

The recursion trap

The obvious way to write the second branch is inline:

OR EXISTS (
  SELECT 1 FROM public.sealed_answers a
  WHERE a.prompt_id = sealed_answers.prompt_id
    AND a.user_id = auth.uid()
)

Postgres rejects this with infinite recursion detected in policy for relation "sealed_answers". Evaluating the policy runs the subquery, the subquery reads sealed_answers, and reading sealed_answers evaluates the policy.

The fix is a SECURITY DEFINER function. It runs as its owner, so RLS doesn't apply inside it, and the loop ends:

CREATE OR REPLACE FUNCTION public.has_answered(prompt uuid, who uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
  SELECT who = (SELECT auth.uid()) AND EXISTS (
    SELECT 1 FROM public.sealed_answers a
    WHERE a.prompt_id = prompt AND a.user_id = who
  );
$$;

REVOKE ALL ON FUNCTION public.has_answered(uuid, uuid) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.has_answered(uuid, uuid) TO authenticated;

SET search_path = '' matters for any definer function. Without it, a caller who can create objects in a schema on the search path can shadow the tables the function reads.

The bug I shipped

Look at the first line of that function body: who = (SELECT auth.uid()) AND. The first version didn't have it.

The policy only ever asks about the caller. But the function lives in public, and PostgREST exposes every function in an exposed schema that the role can execute. So has_answered was also an endpoint at /rest/v1/rpc/has_answered, and any signed-in user could ask it about any prompt and any user id. Put someone else's id in the who slot and you get back "has this person answered yet?", which is exactly the fact the policy exists to hold back.

Nothing broke and no test failed. It came up in a later security pass, and the fix was to make the function answer only about whoever is calling it. What I took away from it: every SECURITY DEFINER function in public is a public API. Write it to answer exactly the question your policy asks and nothing wider, or put it in a schema PostgREST doesn't expose.

No UPDATE, no DELETE, and no grant either

REVOKE ALL ON public.sealed_answers FROM anon;
REVOKE ALL ON public.sealed_answers FROM authenticated;
GRANT SELECT, INSERT ON public.sealed_answers TO authenticated;

Immutability is half the protocol. If you can edit your answer after the reveal, it wasn't committed before it. If you can delete it, the second person can read and then take their half back.

With RLS on and no UPDATE policy, updates are already denied. I revoke the grant as well, so there are two locks. If someone adds a permissive policy by accident a year from now, the missing grant still says no. Answers do disappear when their question is deleted, because a cascade runs as the table owner and isn't subject to RLS.

Asking a question takes two inserts (the question and the asker's answer), and between them there's a moment where the question exists unanswered. The other person could answer into that gap and unlock nothing. So asking goes through one function that does both inserts in a single transaction. That function is SECURITY INVOKER on purpose, so every policy, the rate limit and the expiry trigger still apply to it.

What this doesn't protect against

Two limits, so nobody has to find them in the comments:

• You can answer with nonsense to force the reveal. What makes that costly is that the nonsense is permanent and sits in the thread under your name.
• The table owner and the service_role key bypass RLS. Whoever runs the server could hand out answers in the wrong order. They still can't read them, because all they ever get is ciphertext.

The other half: is the live database the one in the repo?

All of this is only as good as the SQL actually running on the live project. I apply migrations by hand in the Supabase SQL editor, which means "I ran it" is a memory, not a fact.

The repo keeps two descriptions of the database: a folder of migrations (the history) and a single schema.sql (the current shape). Two scripts keep them honest.

npm run db:verify starts a throwaway Postgres in Docker, builds one database by replaying every migration in order and another from schema.sql, then fingerprints both catalogs and diffs them. The fingerprint covers tables and columns, constraints, policies, table and function grants, storage bucket settings, and an md5 of every function body. If I change one file and forget the other, the diff isn't empty.

npm run db:audit runs the same fingerprint query against the live project and diffs it against schema.sql. It only runs catalog SELECTs and writes nothing.

The first time I ran the audit, it found two things my notes had wrong. One migration was written down as applied and had never run. Two others had been pasted in the same sitting, and only the second one took. The worst finding was a function that existed on the live project and was granted, but still had the previous migration's body. Postgres doesn't resolve a plpgsql body until you call it, so nothing errors until a user taps the button.

It has also raised a false alarm. Two functions came back as "wrong body", and when I pulled them from the live database they were character for character the repo's code, minus the -- comments. Somewhere between my editor and Postgres the comments had been stripped. Comments don't run, so the fingerprint now hashes function bodies with comments removed and whitespace collapsed. An audit that cries wolf over text that never executes is one you stop reading.

One small trap from writing it: I compare the two sorted fingerprints with comm. If sort and comm disagree on collation, comm decides its input isn't sorted and quietly compares nothing, which looks exactly like a clean audit. LC_ALL=C on both fixes it.

The code

Everything is in the repo, with the reasoning in the SQL comments:

• the policy, the function and the grants: supabase/schema.sql (section 5c)
• the replay diff: supabase/verify/verify.sh
• the live audit: supabase/verify/live-audit.sh

If you see a way around the policy, I want to hear it. Open an issue or leave a comment here.

Comments

[Ahmad Mukhtiar]

The has_answered leak is such a common trap: a helper written for a policy quietly becomes a public /rpc endpoint. For new helpers, would you now default to a schema PostgREST doesn't expose, or keep the auth.uid() guard inside the function so your fingerprint audit covers it? Also, the unsorted comm check that makes the audit silently pass is a nasty one; thanks for flagging it.

[Peter Panzer]

Mostly neither. For new helpers I'd drop the user id parameter entirely, the way is_room_member(target) already works: it reads auth.uid() inside, so there's no slot to put someone else's id in. A private schema on top of that is fine, but my audit only fingerprints public right now, so that would mean widening it first. And the audit wouldn't have caught this bug anyway. It checks that live matches the repo, not that the repo asks the right question.

[Ahmad Mukhtiar]

Dropping the parameter is cleaner, since there's no slot left to misuse. For the gap you named, a small SQL test could catch "wrong question" bugs that the audit can't. It would run as user A (role authenticated plus request.jwt.claims set via set_config) and assert that A can't learn anything about user B. Even a simple catalog check that flags SECURITY DEFINER functions in public taking a uuid argument would have caught this one before it shipped.