Villads Østergaard, VOJO · 2026-11-02
Between July and October 2026 we ran ten rounds of security review on VOJO Recruit, our multi-tenant recruitment platform built on Supabase (Postgres, PostgREST and Edge Functions). The most serious findings were not exotic. They were lines of SQL or configuration that look locked down and are not. Each class below was confirmed against our live system, fixed, and is now guarded by an automated check. None of them depends on a flaw in Supabase itself: each follows from documented behaviour.
We publish this because every one of these traps is easy to repeat and hard to see in review. The examples are simplified and use invented table and function names.
Supabase sets default privileges so that functions the postgres role creates in the public schema receive explicit EXECUTE grants for anon, authenticated and service_role. The familiar hardening line only removes the grant to the PUBLIC pseudo-role. The explicit grants stay, and PostgREST exposes the function at /rest/v1/rpc/ to anyone holding the public client key. For a SECURITY DEFINER function that was meant for the backend only, that is an unauthenticated entry point that runs with the owner's rights.
-- Looks backend-only. Is still callable with the public key.
revoke all on function public.apply_credit(bigint, int) from public;
grant execute on function public.apply_credit(bigint, int) to service_role;
-- What it takes:
revoke all on function public.apply_credit(bigint, int) from public, anon, authenticated;
grant execute on function public.apply_credit(bigint, int) to service_role;A migration that looks right proves nothing. Ask the database which SECURITY DEFINER functions the anon role can execute:
select p.oid::regprocedure as fn
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public'
and p.prosecdef
and has_function_privilege('anon', p.oid, 'EXECUTE');Then confirm from the outside. A POST to /rest/v1/rpc/ with only the public key answers PGRST202 if the function does not exist and 42501 (permission denied) if it is blocked. Anything else, including an error raised by the function body, means the function is callable.
Backend jobs call database functions with the service key, where auth.uid() is NULL. It is tempting to read NULL as “trusted caller” and skip the tenant check. The anon role also has a NULL uid, so the guard below waves through exactly the caller it should stop. In our case the function queued work for a worker that runs with the service key.
-- Intended: skip the membership check for the service role.
if auth.uid() is not null then
perform assert_member(p_tenant_id);
end if;
insert into job_queue (...) values (...); -- anon gets here tooDecide on the role claim and fail closed. Inside a SECURITY DEFINER function current_user is the owner, so it cannot tell you who is calling either.
if coalesce(auth.jwt() ->> 'role', '') = 'service_role' then
null; -- trusted backend path
elsif auth.uid() is null then
raise exception 'not_authenticated' using errcode = '42501';
else
perform assert_member(p_tenant_id);
end if;Better still, a function that only the backend should call is not granted to anon or authenticated at all (trap 1), so the question never reaches the function body.
Edge Functions verify the bearer token by default. The legacy anon key that ships in every browser bundle is itself a JWT signed for the project, so it passes. A function that relies on verify_jwt and then builds a client with the service key is an open endpoint. We confirmed this with nothing but the public key: several functions reached their body and answered “not found” for a made-up id. With a real id they would have sent another tenant's email or returned a file from private storage.
Projects that have moved to the newer publishable keys should check the behaviour for their own setup rather than assume this applies unchanged.
If anyone can sign up, the authenticated role means “anyone on the internet with an email address”. A policy with using (true) for authenticated is a public table with an extra step. Three earlier review rounds missed this in our system because each one asked “can anon do this?”, and anon was correctly denied.
Test policies as a real signed-in user with no memberships, in a transaction you roll back:
begin;
set local role authenticated;
select set_config('request.jwt.claims',
'{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}', true);
select count(*) from public.leads; -- expect 0
rollback;The same applies to tests of functions that call auth.uid(): without claims set, the function returns at its first “not authenticated” branch, and a before/after comparison passes on both sides without measuring anything.
Storage policies usually check the object path against a membership table inside EXISTS. An unqualified name inside the subquery binds to the nearest table that has such a column. If the tenants table also has a name column, the policy compares the tenant's display name with the folder, not the object path. Postgres does not warn.
create policy "members upload" on storage.objects for insert to authenticated
with check (exists (
select 1
from tenant_members m
join tenants t on t.id = m.tenant_id
where m.user_id = auth.uid()
and t.id::text = (storage.foldername(name))[1] -- t.name, not objects.name
));
-- Fix: say which name you mean.
and t.id::text = (storage.foldername(objects.name))[1]Ours failed closed and denied every upload. That is still a trap: the obvious fix for a failing upload is to loosen the predicate. Other shapes of the same mistake fail open. We now flag any storage.foldername(.name) that is not objects.name, and fix every CRUD variant of a policy together, because they are usually written as copies of each other.