Row-level security is not your only tenant boundary

Rechvix is multi-tenant. Many organisations share one PostgreSQL database, and the design says a bug in one layer must not become a cross-tenant data leak. The README states that every tenant table is protected by an organisation filter in application code and by row-level security.

In early September 2026 I checked whether that was true by reading the SQL of every repository, not the intent. It was true in one module. In thirteen other lookups across six modules the query was WHERE id = $1 and nothing else, relying entirely on row-level security for isolation.

Why that matters even though RLS works

Nothing had been exploited and the integration suite has tests that run raw SQL without an organisation filter to prove row-level security holds. But a single layer depends on correct plumbing at every call site forever, including call sites that do not exist yet. The blast radius of any misconfiguration was larger for those thirteen methods than my own design said it should be.

This was not hypothetical. Earlier in the project the compose file connected the application as the migrator role. That role owns the tables, and a table owner bypasses row-level security entirely, so the policies silently did nothing. A startup check, WarnIfRuntimeRoleOwnsTenantTables, caught it. At that point most tables had no application-layer filter either.

The change

Every repository lookup now takes the organisation and the identifier, and filters on both: WHERE organisation_id = $1 AND id = $2. The identifier is already the primary key, so the extra condition costs nothing measurable and cannot change which row comes back for a legitimate caller. It only changes the answer for a foreign identifier, which now returns not found without depending on the database having blocked it first.

There is one deliberate exception, the organisation lookup itself. Its only caller always passes the principal's own organisation as the identifier, never a value from the client, so there is no second identifier to filter by. A comment at the call site says so.

The two-role design

A migrator role owns the schema and runs migrations. A separate runtime role, which owns nothing, is what the application and the worker connect as. Row-level security applies to the runtime role and cannot be bypassed by ownership. Setting automatic migration on the application would need its connection to carry schema privileges and defeat the point, so I do not allow it as a shortcut.

This bit again when I packaged Rechvix for CasaOS. The runtime role was created by an init script mounted from the repository, and on CasaOS there is no repository, so the mounted directory was empty and the application crash-looped on a failed password for a user that was never created. The fix was a one-shot service that writes the role-creation SQL into that directory before PostgreSQL starts. I then checked that the runtime role still had no DDL privileges.

Method

Two independently verified mechanisms are better than one, because they fail differently. Row-level security depends on session settings reaching every connection. A WHERE clause depends only on the query text. I let the compiler find every call site of the changed signature, and did not rely on grep alone.

The general lesson is to audit the stated architecture against the code from time to time. The gap I found was not a clever attack. It was ordinary drift between what the design said and what thirteen small functions did.