Every email address in my users table was readable with just the anon key. The app worked fine, tests were green, and it sat like that for about th...
For further actions, you may consider blocking this person and/or reporting abuse
Row Level Security in Supabase can be incredibly tricky when dealing with views, especially since views do not automatically inherit the RLS policies of the underlying tables unless you explicitly set the security invoker attribute. It is a common pitfall when relying on AI for database architecture because it often misses these specific Postgres security nuances. I actually had to audit our entire schema for this exact vulnerability when building our SaaS starter, which is why we now enforce strict invoker security on all views in PubliFlow by default to prevent silent data leaks.
Good catch on the precise term, security_invoker is the attribute that matters here (Postgres 15+, Supabase surfaces it on views), and it's different from security_barrier, that one guards against a leaky operator getting pushed ahead of an RLS qual, it doesn't change whose privileges the view runs under. What I did at the time was just add an explicit check inside the view instead of flipping invoker mode, worked but meant repeating it on every new view. Setting security_invoker = true once is the fix I'd point people to now.
That distinction between security_invoker and security_barrier is crucial, especially since the barrier option only prevents operator pushdown without actually changing the execution context. Using an explicit check inside the view is a solid pragmatic workaround when you can't easily flip the invoker attribute, though it definitely adds maintenance overhead. Have you found that explicit check scales well when the underlying RLS policies get more complex?
Haven't tried rewriting them as lateral joins specifically. Most of the RLS policies I've dealt with were simple enough that an index on the membership table's foreign key made the subquery basically free without needing a rewrite. My guess is a lateral join would help more once the policy is doing something the planner can't already flatten on its own, but that's a guess, not something I've measured.
Your intuition about the planner's flattening behavior is exactly where the real risk lies. When a policy gets complex enough that Postgres decides it is cheaper to evaluate a leaky function before the RLS check, forcing a barrier or using a lateral join prevents the optimizer from accidentally exposing data. Have you ever benchmarked a complex policy where the indexed subquery still got flattened in a dangerous way?
Haven't run into that combination directly. The failure mode I associate with security_barrier is a leaky function getting pushed ahead of the RLS qual and leaking values through error messages or side effects, not the planner flattening a subquery into exposing rows outright, those still have to pass through the policy's WHERE clause either way. If flattening itself can bypass the policy's row filter I haven't seen it, would be curious to see a reproducible case if you've got one.
This is a classic Supabase gotcha that catches almost everyone when they first start building public views or joins. The issue usually stems from the view executing with the privileges of the view creator rather than the querying user, effectively bypassing your Row Level Security policies on the underlying tables. To prevent this, you need to explicitly configure the view to respect the querying user context or ensure your RLS policies are strictly enforced on the underlying tables. Have you looked into using the security barrier option on your views to force the RLS conditions to be applied before any other filters or joins are executed?
Not well, in my experience. The explicit check is really the policy's logic copied into the view, so the moment the underlying policy changes, both places have to change together or they drift, and RLS policies are exactly the kind of thing that gets updated in one place and forgotten in the other. Past a handful of views I moved everything to invoker mode for that reason, one source of truth beats remembering to keep two in sync.
You nailed the exact danger of duplicating policy logic in views; that drift is a silent security killer. Switching to invoker mode is definitely the safer architectural choice since it forces the view to respect the caller's RLS context natively. Have you found any performance trade-offs with invoker mode on larger tables, or does the single source of truth completely outweigh the overhead for you?
No meaningful overhead I've noticed from invoker mode itself, the switch just decides whose permissions the view runs with. What costs something is however expensive the RLS policies underneath are, a policy with a subquery against a membership table on every row scan shows up in an EXPLAIN either way, invoker or definer. Never done a proper load test comparing the two directly though, so take that as an anecdote and not a benchmark.
You're right that the execution context switch itself is practically free, and the real bottleneck is always the underlying RLS policy complexity. Since both modes evaluate those expensive subqueries on every row scan anyway, the choice between invoker and definer really just comes down to security boundaries rather than raw performance. Have you found that rewriting those correlated subqueries as lateral joins helps mitigate the scan cost in your experience?
This is exactly why I keep saying AI doesn't remove engineering, it changes where engineering effort goes. The code can be 100% functional and still be 100% wrong from a security perspective. Thanks for sharing this, a lot of people building with AI need to see it.
Appreciate that, and you named the split well. The part I keep hitting building with AI myself is that whether the code runs and whether the policy holds are two different questions, and the model only ever checks the first one. My view ran clean, returned rows, matched the shape I wanted, and when the AI handed back the schema it called the exposure partial user data, technically true since avatar_url and username were meant to be public, but that framing buried the one column that wasn't: email. Took three weeks of it running quiet before I thought to hit it with just the anon key.