DEV Community

Vadym Arnaut
Vadym Arnaut

Posted on

The auth_rls_initplan linter has a blind spot: SECURITY DEFINER bodies

If you've ever migrated a Supabase project to the wrapped (SELECT auth.uid()) pattern, you know the linter that flags the bare call. auth_rls_initplan reads every RLS policy expression and warns whenever auth.uid() is used directly instead of inside a scalar subselect. The wrap turns the call from a per-row evaluation into a single InitPlan. On a million-row table that is the difference between a 30ms read and a 30-second read.

The linter is good. It is also incomplete.

What it doesn't see

The linter introspects the policy expression itself. It does not walk into the body of any function the policy calls. So this passes the linter cleanly:

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)));
Enter fullscreen mode Exit fullscreen mode

Green linter. Slow query. The (SELECT ...) at the policy level caches the return value of is_owner(user_id), which itself depends on the row's user_id and runs every row. The auth call inside the function is back to per-row evaluation, and the InitPlan optimization the wrap was supposed to enable never kicks in.

The fix is the same wrap, one level deeper

Move the scalar subselect inside the function body:

CREATE OR REPLACE FUNCTION public.is_owner(target_user uuid)
    RETURNS boolean
    LANGUAGE sql
    SECURITY DEFINER
    STABLE
AS $$
    SELECT (SELECT auth.uid()) = target_user;
$$;
Enter fullscreen mode Exit fullscreen mode

The function still runs per row, but the auth lookup inside it is the single InitPlan call the optimization was designed for.

How to find them

Grep the schema for auth.uid() inside function bodies, not just policies:

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

Or EXPLAIN ANALYZE the slow query and look for a subplan that fires once per row instead of once per query. If it scales with rows scanned, you have one of these.

The linter is a good first pass. This is the second pass to run before you call your migration done.

Top comments (0)