Multi-tenant: the error you can't see

In this note · 3 sections

1. The function that skips the rules
2. Where the real access control is
3. What we learned

When a system serves several companies from a single database, there is a question that gets answered on day one and never looked at again: how do we guarantee that company A can’t see company B’s data?

In Postgres the short answer is Row Level Security. You enable RLS on the table, write a policy that compares the row’s company against the logged-in user’s, and that’s it. It is correct, it works, and it is what we use.

The problem shows up later, when the system grows and someone needs to do something the policy doesn’t allow.

The function that skips the rules

Say you need to compute a total that spans several tables, or run a nightly job. Writing that while respecting RLS is awkward, so the easy way out appears: a SECURITY DEFINER function. That function runs with the permissions of whoever created it —normally the database owner— and RLS policies don’t stop it. That is exactly what it exists for.

So far, so good. The problem is what gets reviewed afterward.

The advice everyone repeats about SECURITY DEFINER is to set its search_path:

CREATE FUNCTION calcular_total(p_empresa uuid)
RETURNS numeric
LANGUAGE sql
SECURITY DEFINER
SET search_path = public   -- this is what everyone checks
AS $$ ... $$;

That prevents a real attack: someone creating a table with the same name in a schema that comes earlier in the search_path and getting the function to read theirs. It is a necessary defense.

But it has nothing to do with who can call the function.

Where the real access control is

A SECURITY DEFINER function sidesteps RLS by design. So the only thing that decides who can run it is the GRANT EXECUTE. That is the access control, not the search_path.

And that’s the trap: in Postgres, when you create a function, execute permission is granted to PUBLIC by default. You don’t have to do anything wrong for it to be left open — you have to remember to close it.

In one of our projects we found three SECURITY DEFINER functions that had the search_path correctly set, passed every check we ran, and were executable by the anonymous role. The automated check came back green because it looked at what everyone looks at.

Here is how to see it:

SELECT p.proname,
  p.prosecdef AS es_security_definer,
  pg_get_function_identity_arguments(p.oid) AS args,
  array_to_string(p.proacl, ', ') AS permisos
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public' AND p.prosecdef
ORDER BY p.proname;

If permisos shows =X/ with no role in front of it, or the value is NULL, anyone can call that function. Closing it is explicit:

REVOKE EXECUTE ON FUNCTION calcular_total(uuid) FROM PUBLIC;
GRANT  EXECUTE ON FUNCTION calcular_total(uuid) TO authenticated;

What we learned

The invariant we were checking was incomplete. It verified that RLS was enabled on every table and that SECURITY DEFINER functions had a fixed search_path. It was missing the third question, the one that matters: who can execute each of those functions.

It is a textbook case of why an automated check that comes back green can be worse than having none: it creates confidence without giving the guarantee. The check now looks at all three things, and it runs on every deploy.

If you have a multi-tenant system on Postgres, the query above takes two seconds. It’s worth running today.