DEV Community

Cover image for Our multitenant Postgres has tests that read the catalogue. Here is what they check, and what they miss
Viacheslav Bobok
Viacheslav Bobok

Posted on

Our multitenant Postgres has tests that read the catalogue. Here is what they check, and what they miss

This is the third post in a series about Reperline, a construction project control system where Claude can read project data over MCP but can only propose changes.

  • Part 1: the AI role can insert into exactly one table, enforced by grants.
  • Part 2: a reader found that a person could approve a stale proposal. Postgres refuses it now.

Both posts got comments that turned into migrations. Part 1 led to migration 057 (function privileges). Part 2 led to three more: 064 (stale proposals, from Mike Johnstone), 068 (a backfill that made old proposals look fresh, also Mike) and 069 (a proposal whose evidence is older than the newest fact, from Edward Izgorodin). That loop is the reason we write these posts: we ship, explain, and let people who know Postgres better than us look for holes.

So this time I want to show the part that is supposed to catch holes before readers do: the tests that read the Postgres catalogue. And then, honestly, the kind of bug they cannot catch.

Why tests that read the catalogue

A list of protected tables goes stale the day someone adds a table. So none of these tests keep a list of what to check. They ask pg_class, pg_proc and pg_roles what exists now, and fail on anything that does not follow the rules. A new table, view or function is covered the moment its migration runs.

CI applies all migrations to an empty Postgres and runs them on every pull request that touches the schema, and a git hook before each commit runs the schema guard on a scratch database whenever a migration is staged.

1. The tenant lives in the transaction, and its absence means no rows

Every query from the web app goes through one wrapper:

await client.query('begin');
await client.query(
  `select set_config('app.org_id', $1, true), set_config('app.user_id', $2, true)`,
  [s.orgId, s.userId]);
Enter fullscreen mode Exit fullscreen mode

The third argument true makes the setting local to the transaction. With a connection pool, that matters: a session level setting would ride along to the next request that borrows the same connection.

Policies compare org_id = current_org_id(), where

create function current_org_id() returns uuid language sql stable as
$$ select nullif(current_setting('app.org_id', true), '')::uuid $$;
Enter fullscreen mode Exit fullscreen mode

No setting means null, and org_id = null is never true. So a query that forgot the wrapper sees nothing instead of everything. The test for that walks every table with an org_id column and counts rows as the app role with no tenant set:

for (const t of tables) {
  const n = Number((await app.query(`select count(*) n from public."${t}"`)).rows[0].n);
  if (n && !GLOBAL_ROWS.has(t)) leaks.push(`${t}=${n}`);
}
assert.deepEqual(leaks, [], `visible without an org context: ${leaks.join(', ')}`);
Enter fullscreen mode Exit fullscreen mode

A second test does the same inside org A, counting rows of org B in every tenant table. A third suite (described in part 1) tries to read across organisations as both the app role and Claude's role, and to update, delete and insert across them as the app role.

2. Nobody can step around RLS

The cheapest way to lose row level security is a role that ignores it. The guard checks that the login roles the app and the MCP server actually connect with are not superusers and do not have BYPASSRLS:

for (const x of r) assert.ok(!x.rolsuper && !x.rolbypassrls, `${x.rolname} bypasses RLS`);
Enter fullscreen mode Exit fullscreen mode

It also checks that each login role is a member of exactly one group role, holds no direct grants of its own (everything goes through the group) and cannot create temporary tables. That last one comes up again in section 5.

3. A table without RLS must be unreachable

Enabling RLS is a per table decision, which means it is a per table chance to forget. The guard lists every table where relrowsecurity is false and fails if the app role or Claude's role can reach it, unless it is on a short list of global catalogues that both roles can only read (for example the list of permission areas):

select c.relname,
       has_table_privilege('app_rw',  c.oid, 'select,insert,update,delete') rw,
       has_table_privilege('app_mcp', c.oid, 'select,insert,update,delete') mcp
  from pg_class c
 where c.relnamespace = 'public'::regnamespace and c.relkind in ('r','p') and not c.relrowsecurity
Enter fullscreen mode Exit fullscreen mode

A table that holds sessions or one time codes is simply closed: no grant, no policy, reachable only through a function that checks what it needs.

4. Views run as the person asking

A view runs with its owner's rights unless it says otherwise. Our views and tables are owned by the migration role, and a table owner is not subject to the table's RLS, so a plain view over a tenant table would show every organisation to anyone who can select from it. Since Postgres 15 a view can be created with (security_invoker = true), and the guard requires it for every view the app can read:

select c.relname from pg_class c join pg_namespace n on n.oid = c.relnamespace
 where n.nspname = 'public' and c.relkind = 'v'
   and not coalesce((select option_value = 'true' from pg_options_to_table(c.reloptions)
                      where option_name = 'security_invoker'), false)
   and has_table_privilege('app_rw', c.oid, 'select')
Enter fullscreen mode Exit fullscreen mode

All our calculations (forecasts, budget variance, risk signals) are views, so this one check covers most of what the product computes.

5. Functions: the part readers taught us

Part 1 was about one function privilege mistake: revoking EXECUTE from a role does nothing while PUBLIC still has it. The comments went further. One reader, X1Y1, pointed out that ALTER DEFAULT PRIVILEGES ... IN SCHEMA cannot take away what the global default grants, so the obvious fix does nothing. Reviewers also pointed at search_path on SECURITY DEFINER functions: if pg_temp is not listed last, Postgres looks in the session's temporary schema first, and a session that can create temp tables can shadow a table the function trusts.

Migration 057 fixed all of it in one pass over the catalogue, and the tests now check it the same way:

  • every SECURITY DEFINER function has search_path = public, pg_temp;
  • no SECURITY DEFINER function is executable by PUBLIC;
  • a newly created function is executable by nobody but its owner (the test creates a probe function in a transaction and asks has_function_privilege for every app role);
  • Claude's login role reaches exactly an allow list of nine SECURITY DEFINER functions, the ones its RLS policies and views call. Adding one to the list means writing down why.
for (const f of await definers())
  if ((await one(`select has_function_privilege($1, $2::oid, 'EXECUTE') x`, [MCP_LOGIN, f.oid])).x)
    reach.push(f.sig);
assert.deepEqual(reach.sort(), [...MCP_DEFINER].sort());
Enter fullscreen mode Exit fullscreen mode

The regression it caught inside the fix

Migration 058 was written the same morning, for an unrelated feature, and recreated one definer function with create or replace ... set search_path = public. Migrations run in number order, so on a fresh database 058 ran after 057, and create or replace replaces the SET clause too. The pg_temp gap came back for that one function. Nobody noticed by reading two correct looking files. The catalogue test failed, and migration 059 is a single alter function ... set search_path = public, pg_temp, shipped in the same commit as 057. That is the whole argument for these tests in one example: the fix was right, and a change next to it undid part of it without touching it.

6. One door to the database

The last guard is not SQL at all. A test scans the source for direct pool access (pool.query, new pg.Client) and fails on any file that is not on a short allow list with a reason next to it. The allowed files may only call functions, and a second check reads their SQL strings and fails on from <table>. Everything else goes through the wrapper from section 1, so there is no code path that talks to Postgres without a tenant.

What none of this catches

Every bug readers found in this series passed all of the checks above.

  • A stale proposal in the inbox (part 2) was a correct row, in the right organisation, approved by a person with the right role. The bug was in time, not in access.
  • The backfill in 064 stamped the current state on proposals that had never seen it. Correct tenant, correct grants, wrong history.
  • A proposal built from older photos could overwrite a newer measurement, because the trigger that keeps progress in sync takes the latest insert, not the latest observation.

The guards answer "can this role see or change this row". They cannot answer "is this change right". That still takes a person in the inbox, tests written from a scenario, and people outside the team reading the code with fresh eyes.

There are also gaps we know about and have not closed. The app role still holds DELETE on the fact journal, even though no code deletes a fact; the stale check counts journal rows partly because of that. And a policy that exists but is too generous (using (true) on a tenant table) would pass the "has RLS" checks. The isolation tests would catch it as a leak only if the test data has rows in two organisations for that table.

Your turn

If you run multitenant Postgres with RLS: what does your guard check that ours does not? I am especially curious about two things:

  1. How do you test that a policy is correct, not just present, without writing a test per table?
  2. Has anyone found a clean way to make journal tables append only for the app role while keeping migrations and admin repair possible?

Comments on the first two posts turned into four migrations. I would be glad to write the next one.

Top comments (0)