How do you test Supabase RLS policies? Lessons from 401 tests

Why do the tests run on a throwaway Postgres cluster without Docker?

RichlyMe is an iOS app we are building with Expo and React Native; it is not on the App Store yet. The backend is entirely Supabase: 49 SQL migrations, 17 Edge Functions and 38 numbered SQL test files under supabase/tests, holding 401 test blocks marked with -- @test.

The runner, supabase/tests/run.sh, is a bash script and needs no Docker. In order, it:

• runs initdb into a temp directory and starts a cluster on port 54329 by default, with fsync and synchronous_commit off;
• applies _bootstrap.sql, which stands in for the roles, auth schema and storage schema Supabase provides;
• applies all 49 migrations in order, then the seed, then _helpers.sql;
• runs each @test block in its own BEGIN … ROLLBACK, prints PASS/FAIL, stops the cluster and deletes the directory.

Four non-SQL checks follow: a parity test proving the score formula matches the TypeScript package, an Edge Function type-check, the 10 Deno unit test files under _shared, and lint.

Given REMOTE_DB_URL, the same SQL tests run against an already migrated hosted database. Helpers are created inside each test transaction and rolled back, so nothing is left behind. A test that passes locally checks the same thing against the live schema.

What does an RLS test look like in practice?

Local Homebrew Postgres has no pgTAP, so _helpers.sql builds a small test schema. It is deliberately minimal:

• test.ok and test.eq raise with code P0099 when the condition fails.
• test.throws(sql, expected, msg) runs the SQL as the current role and passes only if it fails with the expected five-character SQLSTATE or a matching message. If nothing is raised, the test fails.
• test.lives asserts the statement succeeds.
• test.as_user(uid), test.as_anon(), test.as_service() and test.as_super() switch roles.

as_user sets request.jwt.claim.sub, request.jwt.claims and role at transaction scope, and the bootstrap’s auth.uid() reads those settings, so policies are evaluated the same way they would be for a real PostgREST request.

The point is that tests do not read the policy and reason about it; they attempt the forbidden thing. One test in 06_scoring.sql checks that an ordinary member’s update, insert and delete on public.scores, writes to the trust_score or tier columns, and a call to score_proof each fail with 42501. 02_proof_visibility.sql checks that a Member cannot fetch a higher-tier Patron proof’s media object from storage.objects, even knowing the path.

Because every test is rolled back, tests never leak into each other and order does not matter.

How are Supabase’s default grants switched off, and how is that verified?

By default Supabase grants ALL on every new object in public to anon and authenticated. The bootstrap file reproduces that on purpose, so the migrations are tested against the same permissive starting point.

The very first migration, 20261021120000_foundation.sql, reverts it up front: alter default privileges in schema public revoke all on tables from anon, authenticated;. The same lines cover sequences and functions, and default execute is removed in the app and private schemas too. After that, every grant is explicit. Proof evidence, camera codes and the audit log live in private, which authenticated cannot even use.

01_schema.sql tests structural rules rather than individual policies:

• no table in public or private has RLS disabled;
• anon holds no privilege on any table or view in public or private;
• the only function in public that anon can execute is founder_seats_remaining;
• public.proofs has no column like lat, lng, location, exif, address or venue;
• every foreign key column is indexed.

These tests also cover tables added later. If a new migration forgets to enable RLS, the test breaks before anyone notices in production. The migrations contain 131 create policy statements in total; turning the expectations into rules is more reliable than remembering each one.

How is the scoring function restricted to the service role?

The score formula lives in SQL, not on the client: public.score_proof(p_proof_id uuid, p_method verification_method). It is declared security definer with set search_path = ''. Definer lets it update scores, members and trust events without being blocked by RLS; the empty search_path forces every object to be schema-qualified (public., app., private.) and rules out object shadowing through the search path.

The risk with a definer function is that anyone allowed to execute it gets its power. So 20261026130030_a_scoring.sql applies a two-step grant pattern:

• revoke execute … from public, anon; across the list of user-facing functions;
• for score_proof, publish_delayed and refresh_standing, an extra revoke … from authenticated followed by grant … to service_role.

Postgres grants execute on functions to PUBLIC by default, so revoking from authenticated alone would not be enough; public has to go too.

The caller is the score-proof Edge Function. Its first line is requireService(req): the bearer token is checked against the service key with a constant-time comparison, and the RPC goes through the service client. Input validation accepts only five methods: booking_api, c2pa_location_nonce, ai_witnessed, ai_only, rejected.

The tests cover both sides: a client call to score_proof must get 42501, while an end-to-end call as the service role must compute and store the score. Another test asserts the function signature has no money amount parameter, and a retried decision is not counted twice.

What do the Edge Functions do, and what do they leave to SQL?

Most of the 17 Edge Functions are a thin shell: authenticate, validate input, call one RPC. Business rules stay in SQL, because that is where the RLS and grant tests live.

• issue-nonce (14 lines) deliberately does not use the service client. It calls the RPC with the caller’s own JWT, so the active-member check and rate limit inside issue_nonce() run as the user RLS sees.
• submit-proof authenticates, reads at most 262,144 bytes of body and checks the capture receipt against a signing key. It then downloads the uploaded originals from the proof-originals bucket and computes their SHA-256 itself; a hash sent by the client is never used. The proof is created atomically in a single SQL function.
• publish-delayed marks proofs whose delay has passed and queues the notification. Feed visibility depends only on the publish_at column, so a missed cron run never shows anything early or hides anything.
• purge-originals runs daily and deletes originals of proofs whose season closed more than 90 days ago, at most 500 proofs per call, removed in batches of 100. Evidence under a fraud or report hold is kept.

Nine of the 17 functions go through the same requireService gate and can only be called with the service role, so exposing one of them by accident would take a deliberate code change.

How does the single-use 120-second camera code work, and how is it tested?

In the app, a proof can only come from the in-app camera. When the camera opens, issue_nonce() generates a 10-character code from a 31-character unambiguous alphabet (no 0, O, 1, I or L). The code is shown in frame during capture and bound to the proof at submission.

The rules live in the database:

• private.nonces carries check (expires_at = issued_at + interval '120 seconds'). The lifetime is a schema constraint, not an app constant, and cannot be stretched.
• code is unique, and consumed_at is set on use.
• A member can hold at most 5 live codes at once.
• At most 10 codes can be issued in 24 hours; the eleventh is refused with the nonce_cap hint.
• The capture time must fall inside the code’s window, with a 5-second tolerance.

07_nonces_submission.sql tests these limits at their edges: the code is still valid at 119 seconds and invalid at 121. A second use returns false. Another member’s code is refused. Writing a 10-minute code directly into the table fails with 23514, a check violation. Clients can neither call consume_nonce nor read private.nonces. Candidates and anon cannot get a code.

The test does not wait for the clock. It switches to superuser, moves issued_at back by 121 seconds, then tries to consume the code as the service role. That keeps the test fast and deterministic.

How does the database guarantee the 24-hour delay and city-level privacy?

In RichlyMe an approved proof becomes visible no earlier than 24 hours later, and only at city level. The goal is that a post never reveals where someone is right now. We do not leave that to the app screen; the database holds it in three layers.

First, a table constraint on public.proofs: check (publish_at is null or publish_at >= approved_at + interval '24 hours'). An earlier time is not quietly corrected; it is an error.

Second, the member’s own delay, which can be raised above 24 hours. The proofs_respect_delay trigger lifts publish_at to that delay on every write path: approval, the "I’m back" action, appeals and staff fixes. Extending the delay also pushes already-scheduled proofs later; shortening it never pulls one forward.

Third, location is never stored. app.resolve_city uses the haversine formula to find the nearest configured city within its radius, and coordinates never leave that function. A structural test asserts proofs has no location-like column, and coordinates far from every city resolve to no city.

The feed view, proof_feed, is declared with security_barrier and truncates publication time to the hour for full works and to the day for blurred ones. With the delay known, a timestamp precise to the second would let anyone work backwards to the moment of capture. Separate tests check that a proof is hidden from others before publish_at, visible to its owner, and visible the moment the time passes, with no cron involved.

What did scenario testing catch that the policies missed?

Table-level RLS tests do not catch everything. The migration 20261029120000_scenarios_proof_privacy.sql, dated 29 October 2026, closes issues found by walking member stories end to end. Its header records each one, and all of them are fixed:

• The screen hid it, the API handed it out: a blurred proof’s photo and caption were hidden, but the feed row still carried the owner’s name and handle, the city, the task, the points, the witness count and the publication time to the second. Blurred rows now carry only what the blurred card draws.
• The delay did not hold on every path: a member’s longer delay was only read for an at-home approval. A travelling member’s proof went public after a fixed 7 days, even with a 30-day delay chosen. One invariant now covers every write path.
• The limit only existed in the client: the app stopped at 10 codes a night, the server did not. A script could keep generating codes within the 5-live limit indefinitely. That is why the 10-per-24-hours cap was added.
• Tier ranking was wrong: the server ranked Founder equal to Patron, so a Patron could reach a Founder’s media through the storage policy. Each tier now has its own rank: Member 1, Patron 2, Founder 3.

The shared lesson is that "the client doesn’t show it" is not a security control. Each of these findings is now pinned by a test in 81_scenarios_proof.sql or 02_proof_visibility.sql.

What should you check when building a similar Supabase backend?

A checklist from RichlyMe that carries over to other projects:

• In the first migration, revoke anon and authenticated with alter default privileges, then grant everything explicitly.
• Write structural tests such as "no table without RLS" and "anon can reach nothing"; they cover new tables automatically.
• Write tests by attempting the forbidden operation as that role, not by reading the policy, and assert the expected SQLSTATE.
• Run every test inside BEGIN … ROLLBACK so order never matters.
• Always add set search_path = '' to security definer functions. Revoke from public, anon and authenticated, then grant only to the role that needs it.
• Keep lifetimes and limits as check constraints where you can, not app constants, and test both sides of the boundary (119 and 121 seconds, for example).
• Never trust hashes, locations or timestamps sent by the client; recompute them on the server.
• Tie visibility to a column, not to a cron job, so a missed run cannot leak data.
• Check that views return no more than the screen shows by reading the actual API response.
• If a calculation exists in both SQL and TypeScript, add a parity test over a shared fixture file. In RichlyMe, 27 fixtures pin both sides to the same result.

At AMG Software Studio we build mobile apps and backends like this, where privacy and the permission model are central to the product.