Case study: why REVOKE FROM PUBLIC didn't lock Kagelin's functions
On Supabase, REVOKE EXECUTE ON FUNCTION … FROM PUBLIC does not stop anonymous users from calling a function. Supabase gives the anon and authenticated roles their own EXECUTE grant on every new function in the public schema, and a revoke from PUBLIC leaves those grants in place. Kagelin had locked four internal SECURITY DEFINER functions this way, and all four were still callable with the public anon key. The fix is to revoke from public, anon, authenticated and grant back only to the role that needs it.
We reported this privately to Kagelin on November 12, 2026. Achyuth, who builds it, shipped a fix the next day in v1.51.1 and went further than the report asked. This write-up is published with his permission. Kagelin is at kagelin.app.
What Kagelin does
Kagelin is a task, focus-timer and habit app. It sends reminders for tasks that are due, a morning briefing and an evening plan, and the timer tells you when a focus session or a break ends. Those reminders go through a notification queue in Postgres: rows are added when something is due, and an edge function picks them up and sends the pushes.
Kagelin takes privacy seriously. Notification bodies are encrypted, and older payloads had already been scrubbed in an earlier migration. That matters for what this bug could and couldn't expose.
How did the bug work?
The queue is worked by a few database functions. They run as SECURITY DEFINER, so they can touch every user's rows, which is what a background worker needs. Simplified:
create or replace function public.claim_due_notifications(p_limit int default 50)
returns setof public.notification_queue
language plpgsql security definer set search_path = '' as $$
begin
return query
update public.notification_queue q
set status = 'processing',
claimed_at = now(),
retry_count = case when q.status = 'processing' then q.retry_count + 1 else q.retry_count end
where q.id in (
select c.id from public.notification_queue c
where (c.status = 'pending' and c.scheduled_at <= now())
-- reclaim rows a crashed worker left behind
or (c.status = 'processing' and c.claimed_at < now() - interval '30 seconds')
limit p_limit
)
returning q.*;
-- (a separate step marks rows failed once retry_count reaches 3)
end; $$;
revoke execute on function public.claim_due_notifications(int) from public;
grant execute on function public.claim_due_notifications(int) to service_role;
That looks locked down: revoked from PUBLIC, granted to the service role. The July 12 advisor-fix migration used the same pattern for the briefing helpers, and later migrations copied it for the queue and scheduler functions. It was written to clear the Supabase Security Advisor warnings for functions that anonymous and signed-in users can execute.
The catch is how Supabase sets up privileges. New functions in public are granted to anon, authenticated and service_role directly, through default privileges. PUBLIC is a separate pseudo-role that every role belongs to. Revoking from PUBLIC removes that one grant and leaves the direct grants to anon and authenticated alone. You can see it on a default Supabase project:
create function public.demo() returns int language sql security definer as $$ select 1 $$;
revoke execute on function public.demo() from public;
select has_function_privilege('anon', 'public.demo()', 'execute'); -- true
Four functions in Kagelin had this pattern:
claim_due_notifications(int): claims due reminders from the queueinvoke_edge_function(text): calls an edge function with the service role key, which it reads from Vaultget_users_for_morning_briefing()andget_users_for_evening_plan(): list who should get a briefing now
What did it expose?
With only the anon key, which every web app ships in its JavaScript, anyone could call these over /rest/v1/rpc:
- Stop everyone's reminders.
claim_due_notificationsmarks the rows it returns asprocessing. Called every half minute, it would keep reclaiming them until they passed the retry limit and were markedfailed, so reminders would silently never arrive. It also returned the queue rows. Because the bodies were encrypted, that was mostly metadata: user ids, types and times. - Make the database call edge functions.
invoke_edge_functionposted to/functions/v1/<name>with the service role key, and the name was whatever the caller passed in. The response never came back to the caller, so the key itself didn't leak, but anyone could triggerprocess-queueordaily-briefingas often as they liked, or point the call at another function. - List a subset of users and their time zones. The two briefing helpers returned user ids and time zones for users in the current morning or evening briefing window.
We found this by reading the public migrations and never tested it against Kagelin's project.
How was it fixed?
Kagelin's fix is migration 20261113053500_revoke_security_definer_anon_authenticated.sql, shipped in v1.51.1. It does four things, and the last two go beyond the report:
-- 1. Revoke from the API roles, not just PUBLIC, and grant back only what's needed.
revoke execute on function public.claim_due_notifications(int) from public, anon, authenticated;
grant execute on function public.claim_due_notifications(int) to service_role;
-- 2. Only two edge functions can be invoked, so the name can't be abused.
-- (inside invoke_edge_function)
if p_function is null or p_function not in ('process-queue', 'daily-briefing') then
raise exception 'invoke_edge_function: unauthorized function name "%"', p_function;
end if;
-- 3. New functions start locked instead of open.
alter default privileges for role postgres
revoke execute on functions from public;
alter default privileges for role postgres in schema public
revoke execute on functions from anon, authenticated;
The same migration also tightened a few helper functions we hadn't flagged. The fourth part is a regression test that fails if a SECURITY DEFINER function or a client RPC grant opens up again. Kagelin also turned on GitHub private vulnerability reporting, so the next report has a private channel.
The cron jobs keep working unchanged: pg_cron runs as the postgres owner, and the edge functions use the service role, so neither needs the grants that were removed.
Does my app have the same problem?
This query lists every SECURITY DEFINER function in public and whether the API roles can run it. Run it in the Supabase SQL editor:
select p.proname as function,
pg_get_function_identity_arguments(p.oid) as args,
has_function_privilege('anon', p.oid, 'execute') as anon_can_run,
has_function_privilege('authenticated', p.oid, 'execute') as signed_in_can_run
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.prosecdef
order by 1;
A true isn't automatically a bug. Some functions are meant to be called from the app, like a function that returns the current user's own data. For each true, ask two questions:
- Should the browser call this at all? If only your server or a cron job does, revoke it from
public, anon, authenticatedand grant it toservice_role. - If the browser does call it, does it check who is calling? A
SECURITY DEFINERfunction ignores RLS, so it must useauth.uid()itself, not trust a user id passed in as a parameter.
To stop new functions from repeating this, add the default-privileges change from Kagelin's fix to a migration. More on the Advisor warning itself: Public can execute SECURITY DEFINER function.
Does GuardLayer catch this?
Yes, in both places it looks. Live check reads the real grants from your Supabase project and flags any SECURITY DEFINER function that anon can execute. The code scan reads your migrations together and follows the grants in order, including REVOKE … FROM PUBLIC, revokes in later migrations and ALTER DEFAULT PRIVILEGES, so it catches the same mistake before it ships.
It flags a function the API roles can still run when the function changes data, touches secrets or trusts an id from the caller and never checks who is calling. When the only revoke is FROM PUBLIC, the finding explains why that isn't enough, and the suggested fix is the one Kagelin shipped: revoke from public, anon, authenticated and grant back to service_role.
FAQ
Isn't PUBLIC everyone?
In plain Postgres, a function's default grant goes to PUBLIC, so revoking from PUBLIC is enough. Supabase adds direct grants to anon and authenticated on top. Those are separate privileges, and each needs its own revoke.
Does revoke … from anon alone fix it?
It stops visitors who aren't signed in. Signed-in users still have their own grant, and sign-ups are open by default on Supabase, so in practice that is still anyone. Revoke from authenticated too unless the app calls the function.
Will revoking break my cron jobs?
Not if they run in the database (pg_cron runs as the owner) or use the service role key. Check anything that calls the function from the browser with the anon key, because that call will now fail.
Does ALTER DEFAULT PRIVILEGES fix existing functions?
No. It only changes the grants on functions created after it runs. Revoke the existing ones explicitly, as Kagelin did.
See which of your Supabase tables anyone can read, free
Connect your Supabase project read-only. GuardLayer checks RLS, policies, grants and functions as they are right now, and gives you the SQL to fix each problem. It never reads your rows and can't change anything.
Check my Supabase projectPrefer to check code instead? Scan your repo, no signup.