# Multi-tenant: the error you can't see

> Isolating each company's data in a single database looks solved until it isn't. Why fixing the search_path isn't enough and where to really look.

Field note No. 02 · Engineering · September 2, 2026 · 2 min read
By: Juan Pedro Gachet, co-founder and director at Mithandir · https://mithandir.com/en/blog/multi-tenant-el-error-que-no-se-ve

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`:

```sql
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:

```sql
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:

```sql
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.

---

More field notes: https://mithandir.com/en/blog · Talk to the team: https://mithandir.com/en#contacto