# Pattern 02 — SECURITY DEFINER helper function bypass

## The failure

AI generates a helper function to abstract away repeated policy logic:

```sql
CREATE OR REPLACE FUNCTION get_my_posts()
RETURNS SETOF posts
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT * FROM posts WHERE user_id = auth.uid();
$$;
```

The WHERE clause looks correct. The function still runs your query. But `SECURITY DEFINER` means the function executes with the privileges of its creator — typically the `postgres` role in Supabase — which is a superuser. Superusers bypass RLS. Your policies on `posts` never run.

The result: any caller of `get_my_posts()` can see all rows regardless of what your RLS policies say, because the actual query runs as `postgres`.

This is one of the hardest bugs to spot because:
- The function output looks correct in the happy path (WHERE clause limits rows)
- Your RLS policies still show as active in the Supabase dashboard
- The bypass only matters when someone finds a way to call the function without going through your app's auth

## The fix

Drop `SECURITY DEFINER`. The default is `SECURITY INVOKER`, meaning the function runs as the calling user — who is either `anon` or `authenticated` — and your RLS policies apply normally.

```sql
CREATE OR REPLACE FUNCTION get_my_posts()
RETURNS SETOF posts
LANGUAGE sql
STABLE
SET search_path = public    -- Always set this on functions that access your tables
AS $$
  SELECT * FROM posts WHERE user_id = auth.uid();
$$;
```

## When SECURITY DEFINER is legitimately needed

Only use `SECURITY DEFINER` when:
- You need to access a table that the calling role (`anon`/`authenticated`) has no direct SELECT grant on
- You're building an audit function that must bypass RLS by design

In those cases, add `REVOKE EXECUTE ... FROM PUBLIC` and `GRANT EXECUTE ... TO service_role` only, so the function can't be called from client-side code.

## Testing it

```sql
-- Check all SECURITY DEFINER functions in your schema:
SELECT routine_name, security_type
FROM information_schema.routines
WHERE routine_schema = 'public'
  AND security_type = 'DEFINER';
-- Any row here is a potential RLS bypass. Review each one.
```