Supabase RLS Linter Flaw: SECURITY DEFINER Risk

And just like that, a little piece of your meticulously crafted Supabase security falls apart. You thought you were clever, wrapping <a href="/tag/authuid/">auth.uid()</a> in a scalar subselect to supercharge your RLS policies. The auth_rls_initplan linter pats you on the back, chirping about efficiency. But here’s the kicker: it’s blind.

This linter, bless its cotton socks, only squints at the policy expression itself. It doesn’t dare peek inside the shadowy depths of any function that policy might call. So, you’re happily deploying, thinking your million-row table will breeze through queries, when in reality, you’ve just reintroduced a performance killer. A slow query disguised as a quick fix.

What does this look like in the wild? Take this innocent-looking setup:

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)));

The linter sees the (SELECT ...) wrapper around is_owner and thinks, “Aha! InitPlan optimization, coming right up!” It’s all green lights. Until the query runs, and that is_owner function — which is marked SECURITY DEFINER, mind you — gets called for every single row. The auth.uid() inside it? Back to per-row evaluation. The optimization never even shows up for its shift.

It’s like hiring a bodyguard to stand at your front door, only for him to duck out for a smoke break every time someone walks in. Pointless.

So, how do you fix this insidious problem? You have to move the optimization inside the function. Wrap the auth.uid() call within the function itself:

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, sure, but the crucial auth.uid() call within it benefits from that single InitPlan execution. The linter is still technically happy because the policy is wrapped, but now the actual performance bottleneck is addressed. It’s a workaround, but a necessary one.

The Developer’s Double Take: Why This Matters

This isn’t just about a minor performance tweak. It’s about understanding the subtle nuances of how PostgreSQL and tools like Supabase interpret security and execution plans. SECURITY DEFINER functions are powerful, but they come with responsibilities. They run with the privileges of the function owner, not the calling user. If you’re not careful, this can lead to unintended access or, as we’re seeing, a performance nightmare that’s hard to spot.

The lesson here? Don’t blindly trust your linters. They’re guides, not gospel. Especially when SECURITY DEFINER is involved. A quick pg_dump and a search might be your new best friend:

pg_dump --schema-only your_db \n| rg -B 5 'auth\.(uid|jwt)\(\)' \n| rg -A 2 'SECURITY DEFINER'

Or, if you’re already wrestling with a slow query, run EXPLAIN ANALYZE and look for that tell-tale subplan that scales with the number of rows scanned. If it feels like a row-by-row operation, it probably is. This is the second, more painful pass you need to take after the linter gives you its false sense of security.

Is This a Supabase Problem or a PostgreSQL Problem?

This vulnerability is fundamentally a PostgreSQL behavior amplified by how Supabase, and by extension its RLS policies, interact with SECURITY DEFINER functions. The linter is a Supabase tool, but the underlying execution mechanics are pure PostgreSQL. The flaw lies in the linter’s limited scope – it’s not designed to trace execution into functions, especially those with elevated privileges. It’s a classic case of a tool being good at its specific job, but missing the bigger picture when complex interactions are at play.

So, yes, it surfaces in Supabase, but the root cause is understanding how SECURITY DEFINER interacts with function calls and query planning. It’s a reminder that even seemingly simple database operations can hide surprising depths of complexity, particularly when security and performance intertwine.

🧬 Related Insights

• Read more: Friday’s Linux Patch Onslaught: OpenSSH, Kernels, and Grafana Under Fire Across Distros
• Read more: AI’s ‘Via’: Connecting Your Tools, or Just Another Layer?

Frequently Asked Questions

What does SECURITY DEFINER actually do? SECURITY DEFINER functions execute with the privileges of the user who created the function, not the user who calls it. This is powerful but requires careful handling to avoid security risks.

How can I check for this linter blind spot? Use the provided pg_dump and rg commands to search your schema for auth.uid() or auth.jwt() calls within SECURITY DEFINER functions. Alternatively, use EXPLAIN ANALYZE on slow queries and look for row-by-row subplans.

Will this slow down my Supabase application? Potentially, yes. If your RLS policies call SECURITY DEFINER functions that directly use auth.uid() without an inner subselect, queries on large tables can become significantly slower.