Why do we use SECURITY DEFINER functions when RLS would otherwise block a needed operation?

Row Level Security is deliberately restrictive by default — a user typically can't see or modify rows outside their own scope. That's correct most of the time, but some operations legitimately need to cross that boundary in a controlled way: incrementing a shared counter, writing an audit log entry tied to another user's action, or checking whether a username is already taken across all users (something a user-scoped SELECT policy would never allow).

A SECURITY DEFINER function lets you carve out exactly that one operation as a privileged, narrowly-scoped exception: the function runs with the owner's elevated privileges regardless of who calls it, so it can perform the cross-boundary action, while the RLS policies on the underlying tables remain untouched and still apply to every other query. The key discipline is keeping the function narrow — validating its inputs carefully and doing only the one privileged thing it's meant to do — rather than exposing a general-purpose bypass, since anyone who can call the function inherits whatever access it grants.

This pattern shows up often for things like a "check availability" RPC, a moderation action performed by a non-admin trigger, or aggregating counts across all users for a public leaderboard that individual RLS policies would otherwise hide.