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 three weeks.
I was building a side project with Claude earlier this year. At some point I asked for a public profiles page, the kind where visitors can see usernames and avatars without logging in. It did what I asked and created a view:
create view public_profiles as
select id, username, avatar_url, email
from users;
I remember skimming this and thinking it looked reasonable. I had RLS enabled on users, policies tested, the whole thing. When I later asked the AI what the view returned, it described it as "partial user data." Which is technically true. It just didn't mention that the partial data included the one column I'd never want public.
Why RLS didn't save me
Two Postgres behaviors stack up here, and neither one is obvious if you came in through Supabase:
Views run with the owner's permissions by default. My RLS policies on
userswere fine. But a view created bypostgresreads the table aspostgres, and the owner isn't subject to my policies. Readingusersthrough the view skips RLS entirely, with no warning anywhere.Supabase exposes the whole public schema through its API. Anything I create in
publicgets an endpoint. So the view wasn't sitting unused in the database, it was one HTTP request away for anyone holding the anon key. And the anon key ships in the frontend bundle by design.
So: RLS on, policies correct, and every email still public. Each piece works exactly as documented; together they published a column I never meant to share.
Why the AI didn't warn me
I don't think the AI did anything wrong by its own lights. I asked for a public profiles page and got a working one. The code ran, the page rendered, my tests (which check what the app shows) all passed. There was no test for "what else can be read that I never render." That question only comes up when someone is thinking like an attacker, and code generation doesn't.
Most of the security issues I've found in my own AI-built projects follow this shape. Working code that does slightly more than I intended.
Check your own project in two minutes
List the views in your public schema:
select viewname from pg_views where schemaname = 'public';
For each one, look at which columns it exposes:
select column_name from information_schema.columns
where table_schema = 'public' and table_name = 'public_profiles';
And the honest test — ask your API the way a stranger would, with only the anon key:
curl "https://YOURPROJECT.supabase.co/rest/v1/public_profiles?select=*" \
-H "apikey: YOUR_ANON_KEY"
If emails, phone numbers, or anything else private comes back, you have my three-week problem.
The fix
Pick whichever fits your case:
Drop the sensitive columns from the view. A public profile needs a username and an avatar. It doesn't need an email.
On Postgres 15+, make the view respect the caller's RLS:
alter view public_profiles set (security_invoker = true);
Now reads through the view run as the person asking, and your policies apply again.
If the view was never meant to be public, revoke API access:
revoke select on public_profiles from anon;
I did the first two. Belt and suspenders felt right after three weeks of not noticing.
I build with AI every day and that's not changing. But I've stopped treating "it works" as the end of the review, because working code can expose more than it renders. I ended up building a small tool that scans for this pattern and a few related ones automatically. If you're on Supabase and want me to take a look at your repo for free, leave a comment and I'll run it.
Top comments (16)
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.
You are right that standard flattening still respects the policy WHERE clause, and the classic security_barrier failure is usually leaky functions evaluating before the RLS qual. However, that exact planner-pushdown behavior is what makes the AI dismissing this as mere partial data so dangerous, since a leaky function can still exfiltrate those rows via side effects before the barrier applies. We really need static analysis tools that flag these specific evaluation-order risks before they reach production.
On the tooling point, the mechanical checks today are the blunt ones, like a view that never got security_invoker set. That's what I've been building, and I've had it running over public Supabase repos cloned into a sandbox so nothing live gets touched, 830 of them so far, 309 handed rows straight to a plain anon key. Evaluation-order risk is a harder target, since it depends on the plan the planner picks for that policy against that data shape, and a static pass has no view of the plan. So my bias is toward the tool that shouts about the blunt end before it goes hunting the subtle one, because the blunt end is where email addresses are leaking right now. Standing offer to anyone who lands here from the article: send a schema and I'll put it through the same run, no charge.
Running a sandbox against 830 public Supabase repos to catch missing security_invoker flags is incredibly valuable, especially finding 309 that leak directly to the anon key. You are spot on about evaluation-order risk being the next frontier, since the query planner can easily execute a vulnerable function before the RLS policy filters the rows. Have you found any reliable heuristics for statically analyzing the planner's execution order without actually running the queries in your sandbox?
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.