Everyone who’s wrestled with Supabase’s Row Level Security (RLS) has likely encountered the auth_rls_initplan linter. Its job is ostensibly to catch a common performance anti-pattern: using auth.uid() directly within a policy. The linter nudges you towards wrapping it in a scalar subselect, like (SELECT auth.uid()). This seemingly minor change flips a per-row function evaluation into a single, optimized InitPlan, saving milliseconds—or potentially minutes—on large datasets. We thought we were good. We were wrong.
Here’s the kicker: that linter, while clever, only inspects the RLS policy expression itself. It doesn’t peek inside the bodies of any functions those policies might be calling. So, you can craft perfectly innocent-looking SQL like this:
CREATE OR REPLACE FUNCTION public.is_owner(target_user uuid) RETURNS boolean LANGUAGE sql SECURITY DEFINER STABLE AS $$ SELECT auth.uid() = target_user; $$;
CREATE POLICY rows_owner ON public.things FOR SELECT USING ((SELECT public.is_owner(user_id)));
This code sails past the linter. The auth_rls_initplan gives it a clean bill of health. But underneath that tidy policy definition, a performance bomb is ticking. The scalar subselect at the policy level does indeed cache the result of is_owner(user_id). However, that function itself—is_owner—contains a direct call to auth.uid(). This inner call is evaluated on a per-row basis. The whole point of the (SELECT auth.uid()) optimization, the move from per-row to a single InitPlan, is completely negated because the function it’s calling doesn’t use that optimization internally.
It’s like putting a high-performance engine in a car with a severely restricted fuel line. You’ve got the potential for speed, but the bottleneck kills it. The result? Your queries, which you thought were optimized, crawl. A query that should be lighting fast becomes agonizingly slow, especially on tables with millions of rows.
So, how do you fix this? The solution is surprisingly straightforward, mirroring the linter’s original advice but applying it within the function’s body. You need to ensure the auth.uid() call inside the SECURITY DEFINER function is also wrapped as a scalar subselect.
CREATE OR REPLACE FUNCTION public.is_owner(target_user uuid) RETURNS boolean LANGUAGE sql SECURITY DEFINER STABLE AS $$ SELECT (SELECT auth.uid()) = target_user; $$;
Now, the function still executes per row—that’s inherent to its purpose of checking ownership against a specific row’s user_id. But the critical auth.uid() lookup within that function is now a single InitPlan call. The optimization finally clicks. The linter, in its current form, would miss this entirely, but the performance gain is undeniable.
Why does this matter so much? It highlights a fundamental challenge in building intelligent development tools: the depth of analysis. Linters and static analysis tools are invaluable, but they often operate on a surface level. True performance optimization, especially in complex systems like database security policies, requires understanding execution context and function call chains—a much harder problem to solve automatically.
So, what’s the practical takeaway? Don’t just trust the linter’s green light. If you’re using SECURITY DEFINER functions within your RLS policies, especially those that might interact with authentication context, perform a second pass. You need to actively search your schema for auth.uid() or auth.jwt() calls within the bodies of these functions, not just in the top-level policy expressions. A simple grep through your schema dump can help:
pg_dump --schema-only your_db \
| rg -B 5 'auth\.(uid|jwt)\(\)' \
| rg -A 2 'SECURITY DEFINER'
This command pipes the schema output to rg (ripgrep), first looking for auth.uid() or auth.jwt() calls, and then filtering those results to show lines surrounding definitions marked with SECURITY DEFINER. It’s a blunt instrument, but effective.
Alternatively, if you suspect a query is dragging its feet, fire up EXPLAIN ANALYZE on the problematic SQL. Look for subplans that execute once per row when you expect them to execute only once per query. If the execution count scales directly with the number of rows scanned, you’ve likely found one of these hidden performance traps.
This isn’t a critique of the Supabase team; they build fantastic tools. It’s a reminder that even with sophisticated tooling, the human element of understanding your system’s architecture and potential pitfalls remains indispensable. The auth_rls_initplan is a great starting point, but this manual check is the crucial second step to ensure your database security isn’t silently sabotaging your application’s performance.
It strikes me that this problem is a microcosm of a larger trend. As we delegate more and more architectural decisions to frameworks and tooling, we risk losing a deep understanding of the underlying mechanisms. This creates blind spots, just like the linter’s inability to see into function bodies. We become reliant on the tool to tell us what’s optimal, forgetting that the tool is only as smart as its programming, and sometimes, the real world is more nuanced.
How to Find the Hidden Performance Leaks
Beyond the grep command, consider the nature of your SECURITY DEFINER functions. If they’re designed to impersonate a trusted user (which SECURITY DEFINER implies) and they need to access current authentication information to make decisions, they are prime candidates for this issue. The linter is good at catching the obvious policy-level optimization, but it can’t infer intent or execution flow across function boundaries.
Think of it as a security audit for your performance. You’ve secured your perimeter (the RLS policies), but you need to check the internal security protocols (the function bodies) to ensure no unauthorized access—or in this case, inefficient execution—is occurring.
Why Does This Matter for Developers?
For developers, this means adopting a more skeptical and thorough approach to performance tuning, especially in backend and database contexts. It’s easy to assume that built-in tools have covered all the bases. When they haven’t, the performance consequences can be severe, leading to frustrated users and overloaded infrastructure. This isn’t about blame; it’s about diligence. The difference between a query that takes 30ms and one that takes 30 seconds isn’t magic; it’s often a subtle architectural oversight like this one.
🧬 Related Insights
- Read more: WireGuard’s Windows Revival: Bugs Fixed, Microsoft Drama Resolved
- Read more: [PyPI Supply Chain]: The ‘Hidden’ Threat on Your ML Stack
Frequently Asked Questions
What is SECURITY DEFINER in PostgreSQL?
SECURITY DEFINER means a function executes with the privileges of the user who defined it, rather than the user who is currently calling it. This is powerful but requires careful handling, as shown by its impact on RLS performance.
Will this issue affect all Supabase projects?
It primarily affects Supabase projects that use Row Level Security (RLS) policies in conjunction with SECURITY DEFINER functions, particularly if those functions call auth.uid() or auth.jwt() directly within their body without the scalar subselect wrapper.
Is there an automatic fix for this?
Currently, no automatic fix exists. Developers need to manually audit their SECURITY DEFINER functions or use tools like pg_dump and text search (like rg) to identify potential vulnerabilities. EXPLAIN ANALYZE is also a key diagnostic tool.
