A small RLS read-test case study: what makes testing a real app difficult? #51878
Replies: 4 comments
|
This is a really well-documented case study on RLS isolation testing. One of the hardest parts of testing cross-user boundaries in Supabase is ensuring that tests run against clean, isolated state without accidentally leaving dangerous policy modifications active or dealing with stale mock data. For applications handling sensitive multi-tenant or private user data, combining automated row-level checks with local testing tools like Supabase CLI's built-in testing framework or pgTAP can also make these assertions repeatable right inside your CI/CD pipeline before code ever hits a staging or production environment. |
|
One thing I'd add to this is testing the negative cases for both reads and writes, not just verifying that each user can read their own rows. For a multi-tenant app, I'd test with two authenticated users belonging to different tenants and verify that Tenant A cannot read, update, or delete Tenant B's records. I'd also test the same paths through any RPCs or server-side functions that can access the database. The part I find especially important is making sure the test setup resets the database state between runs. Otherwise, a previous policy change or stale fixture can make the results misleading. I like that you deliberately introduced a permissive policy to check whether the tests actually catch the regression. That's a much stronger test of the test suite than simply confirming the expected queries pass. |
|
The part that stands out to me is the distinction between a test failing and a test being inconclusive. In permission testing, those are very different outcomes, and treating missing fixtures or request errors as proof that access is blocked can create a false sense of security. One challenge I can see with real applications is that the ownership rule isn't always as simple as I'd be interested in how you'd extend this approach to multi-tenant data, especially when a user belongs to two organizations but should only access records belonging to the currently authorized tenant. That seems like a useful next test case because it checks the actual authorization boundary rather than just whether two users can read each other's rows. Keeping the scope narrow and reporting exactly what was tested is a good approach. The explicit positive controls make the results much more meaningful. |
|
for me the part that gets stuck isn't the two-user test itself, it's coverage. the overwide case you planted (a second permissive policy with USING (true) next to the owner one) only shows up on the rows you seeded, and permissive policies are OR'd, so the owner policy looking right tells you nothing. and the next table someone adds in a migration has no fixture at all. what's worked for me is splitting it: a static check over pg_policies on every table in CI (fails on always-true quals, RLS off, an UPDATE with an open USING), and two-user read tests only for the few tables where a leak would really hurt. the static part catches the table nobody wrote a test for, the dynamic part proves the ones that matter. disclosure: i wrote a small MIT cli for the static half (airlock-rls), and USING (true) beside an owner policy is exactly the case it fails on. happy to share the fixture schema if it's useful for your experiment. |
Uh oh!
There was an error while loading. Please reload this page.
We've been working on AI Launch Check, an AI-assisted experiment around pre-launch permission tests. We ran this against our own isolated Supabase cloud project on October 16, using synthetic data and Node + supabase-js.
The intended rule: each user can read their own private todo, never the other user's. A signed-out visitor can read a separate public probe. Each request below targeted one known record; the numbers are rows returned.
RLS remained enabled. We deliberately added a permissive SELECT policy for
authenticatedwithUSING (true), alongside the owner policy, then removed it. The same checks detected both cross-user reads and confirmed their absence afterward. The three positive controls worked in every run. Request errors or missing fixtures are inconclusive.This establishes behavior for those records only. Writes, views, RPCs and Storage weren't covered. A/B were anonymous Auth accounts, which use the authenticated role; the signed-out guest used anon. Don't enable anonymous sign-ins in production to reproduce this. The experiment's cloud resources have been removed. Historical runs use A/B labels; distinct server identities were rechecked afterward, without historical per-run fingerprints.
We're pausing development of a standalone checker: Supabase already documents two-user tests, and VibePenTester's alpha addresses a very similar read-testing workflow. We haven't benchmarked it. There's no established advantage or paying customer behind our experiment.
If you have an app with private data and an upcoming release, where did your last attempt to test cross-user access get stuck? A small example would be useful: the expected access rule, renamed owner/tenant fields, and what you tried with existing tools. A setup that already works easily is useful counterevidence too.
I'll review up to three small, sanitized examples here and suggest a minimal test to try. Please don't post credentials or private data; any execution would need a separately agreed local/staging scope. We're checking whether setup assistance is useful before offering a paid service. This post is a case study and a request for concrete problems, not an offer to audit a production system.
All reactions