Row-level from the first commit

The worst bug in a multi-tenant system is the one nobody sees in testing. The page loads, the query returns, and the numbers look right. Then one customer opens a record that belongs to another, because the system trusted the request instead of checking who sent it. There is no stack trace and no alert. One tenant reads another tenant’s data in production, and the logs record a 200.

Nothing about this failure is exotic. It is the most common serious flaw on the web, and the people who catalogue web vulnerabilities for a living rank it first. In the OWASP Top 10:2021, Broken Access Control moved up to the number-one position after ranking fifth in 2017, and it carried the most occurrences of any category in the contributed dataset: 94% of applications were tested for some form of broken access control, the average incidence rate was 3.81%, and the category accounted for over 318,000 occurrences across 34 mapped CWEs.¹

The everyday shape of this failure has a name: the insecure direct object reference, or IDOR. OWASP lists it explicitly under Broken Access Control as permitting a user to view or edit someone else’s account by supplying its unique identifier.² You have seen the pattern even if you have never named it. A URL ends in /invoice/4815, a request body carries tenant_id, an API accepts a record’s primary key. The application looks up the row, renders it, and never asks whether this caller was entitled to that row. The identifier was the only key the system checked, and identifiers are cheap to guess and trivial to enumerate.

Trust is a decision, and most systems make it by accident

IDOR looks like a coding mistake. Behind it sits an architectural assumption: that the application layer will always remember to ask the access-control question. A team can get that right a thousand times, and the one miss is still a breach. The assumption taxes every handler, every query, every background job, and every hastily added admin endpoint, each of which must independently re-derive who is asking and what they may touch.

This is why isolation cannot live on a backlog. A backlog item ships once and is done, but a boundary has to hold for every query the system will ever run. Before asking how to add tenant isolation, ask where the boundary lives and who is forced to honour it. If the honest answer is “the application, by convention,” there is no boundary yet.

Two tenants separated by nothing but a WHERE clause stay separated until someone forgets to type it.

A boundary the database enforces

There is a better place to put the boundary, and it has existed in PostgreSQL for years. Row-level security lets the database itself decide which rows a given role may see or change. Once you enable it on a table with ALTER TABLE … ENABLE ROW LEVEL SECURITY, all access to select or modify rows must be allowed by a row security policy; if no policy exists, a default-deny policy applies and no rows are visible or modifiable.³ That default-deny is the whole point. The table starts closed. You open it deliberately, with a policy that encodes the rule, rather than leaving it open and hoping every query remembers to narrow itself.

A sharp edge here catches teams who think they have turned RLS on. In PostgreSQL, table owners normally bypass row-level security, and superusers and roles with the BYPASSRLS attribute always bypass it; a table owner only becomes subject to its own policies if you set FORCE ROW LEVEL SECURITY.⁴ So the migration that creates the table and the connection your application uses can be precisely the contexts that ignore the policy you so carefully wrote. We run ENABLE and FORCE together, in the same migration that creates the table, every time.

On our stack the concrete tool is Supabase, which is PostgreSQL underneath. Supabase uses row-level security policies (which add an implicit WHERE clause to every query) as its primary fine-grained authorisation mechanism, requires RLS to be enabled on any table in an exposed schema (by default, the public schema), and combines it with Supabase Auth to provide end-to-end per-user and multi-tenant isolation from the browser to the database.⁵ The implicit WHERE clause is the elegant part. The filter stops being something a developer types into each query and might forget. It attaches to the table and travels with every statement, including the ones written next year by an engineer who has never seen the policy.

Why retrofitting is the expensive path

The objection is predictable: we will ship now and add isolation when we have paying tenants. This is the most expensive sequence available, and the security engineering literature has said so for years. NIST’s Secure Software Development Framework, SP 800-218 version 1.1 from February 2022, recommends integrating secure development practices into every SDLC model so that software is well-secured, with the explicit goal of reducing the number of vulnerabilities in released software and addressing their root causes rather than their symptoms.⁶ That is the substance behind the phrase “shift left.” The root cause of IDOR is a missing boundary. Put the policy in the first migration and the cause is gone. Retrofit it after launch and you are treating symptoms, one endpoint at a time.

Retrofitting is dangerous for a specific structural reason. A system that assumed trust has scattered that assumption everywhere: into queries, joins, caches, exports, reports, and integrations. Turning on FORCE ROW LEVEL SECURITY in a mature codebase does not quietly tighten things; it surfaces every place that silently relied on seeing all rows. That means walking every call site written under the old assumption, and each one is a chance to break a working path or, worse, to widen a policy until it no longer isolates anything.

The cost is not only engineering time. The IBM Cost of a Data Breach Report 2024 found the global average cost of a breach reached USD 4.88 million, a 10% rise over the prior year, and that organisations using security AI and automation extensively across prevention workflows incurred on average USD 2.2 million less in breach costs while detecting and containing incidents 98 days faster.⁷ Read past the headline figure and the shape of the argument is clear: prevention and early detection are where the money is saved. A boundary that exists from the first commit is prevention in its cheapest form.

One migration up front, or incident response for the life of the system. The bill arrives either way.

Defence in depth ends at the database

The database is the floor of this design, not the whole building. NIST defines defence in depth as an information security strategy integrating people, technology, and operations to establish variable barriers across multiple layers of the organisation: the layering of multiple, heterogeneous countermeasures so that attacks missed by one are caught by another.⁸ Isolation should appear at more than one layer; the discipline is choosing which layer is authoritative.

It is not the proxy. Request routing can fail open, headers can be spoofed, and an edge check is an optimistic convenience that guarantees nothing. We do a session-presence check at the edge to keep unauthenticated traffic out early, then authorise close to the data (in the data access layer, server-side), and we let the database have the final, non-bypassable word through RLS. The layers reinforce each other. If the DAL forgets a filter, FORCE ROW LEVEL SECURITY still denies the rows. If a query is written by hand against the database, the policy still applies. No single forgotten check becomes a breach.

1. 01

   The edge runs a presence check. It keeps anonymous traffic out cheaply, and because the check is optimistic and spoofable it never serves as the authorisation boundary.

2. 02

   Authorisation happens in the DAL. Server-side code resolves the caller, applies the access rule, and returns shaped objects rather than raw rows. This is the boundary the application sets on purpose.

3. 03

   RLS backstops everything above it. ENABLE plus FORCE ROW LEVEL SECURITY with explicit policies, so the rows stay closed even when another layer slips.⁴

The ordering matters. Each layer is written to survive a slip in the one above it, and given enough years there will be a slip. The database is the exception. It starts closed, and it opens only where a policy says so.

From the first commit

We mean “from the first commit” literally. The first migration that creates a tenant-scoped table also enables RLS, forces it for the owner, and ships the policy that encodes the rule, which leaves no window in which the table exists without its boundary.

This costs almost nothing on day one and a great deal to recover on day four hundred. The default-deny posture already ships with PostgreSQL; a migration switches it on.³ Supabase attaches the filter to every query once a policy exists,⁵ NIST tells you to build security in rather than add it later,⁶ and the breach economics favour prevention over response.⁷ A client who eventually owns and runs the system without us inherits the boundary with the schema. The only decision left is whether you draw it before you have data to lose, or after. We draw it first.