Postgres Functions and RPC

Call SQL functions from the client

When logic needs several statements, a transaction or aggregation, write a Postgres function and call it with supabase.rpc('name', args). Functions run with the caller's privileges by default (security invoker), so RLS still applies. security definer runs as the function owner and bypasses RLS; use it sparingly, set a fixed search_path, and validate inputs, because it is a common source of privilege escalation.

A function and its call

SQL plus supabase-js.

create or replace function public.project_progress(p_project_id bigint)
returns table (total int, done int)
language sql
security invoker
set search_path = ''
as $$
  select count(*)::int, count(*) filter (where t.done)::int
  from public.tasks t
  where t.project_id = p_project_id;
$$;

-- client side (TypeScript):
-- const { data, error } = await supabase.rpc('project_progress', { p_project_id: 1 })

Prefer invoker functions

Keep functions security invoker unless you truly need elevated rights, so the same RLS rules protect both table queries and RPC.

Quick check: What is the risk of a security definer function?

• It disables the anon key
• It cannot read any tables
• It only runs on the client
• It runs with the owner's rights and can bypass RLS