Tenant isolation belongs in Postgres, not your application code
Trademark Guard connects to merchants' Amazon SP-API and Walmart stores, imports their product catalogues, and monitors them continuously for conflicts against USPTO trademark registrations. Catalogues do not arrive politely. A new merchant connects a store and tens of thousands of items land in a burst, get normalised, and go through a matching engine. Meanwhile every other merchant's catalogue is sitting in the same database, being processed by the same workers, served by the same API.
The requirement that shaped the design was not "keep tenants separate". It was that every tenant's data stays invisible to every other tenant in a way I can point at: one enforcement point I can read, rather than a property that has to hold across every query anyone ever writes.
The failure mode nobody plans for
The default approach is to filter in the application. Every query carries a
WHERE tenant_id = ?, the tenant comes off the request context, and the ORM
threads it through. It is easy to write, easy to reason about, and it works.
It works until one query somewhere does not do it.
The problem is not that developers are careless. It is that the guarantee is unenforceable by construction. There is no compiler check for "this query is tenant-scoped". There is no type that a query returns only if it was filtered. The condition holds because everyone remembered, every time, in every code path, including the analytics endpoint written under time pressure, the background job that runs outside the request lifecycle and therefore has no request context to pull a tenant from, the raw SQL someone dropped in because the ORM generated something slow, and the admin tool.
And when it fails, nothing fails. A missing WHERE clause does not raise. The
query returns successfully. It just returns more rows than it should, and those
rows go into a response, or a search index, or an email. A cross-tenant leak
looks exactly like a working feature until someone notices their competitor's
SKUs in their dashboard.
That is the property I did not want: a security boundary whose failure mode is silent and whose enforcement is a convention.
What RLS actually changes
Row Level Security moves the predicate from the application into the table definition. Postgres appends the policy to every query against that table itself, so a query cannot be issued without it.
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE products FORCE ROW LEVEL SECURITY;
CREATE POLICY products_tenant_isolation ON products
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);
USING filters what a statement can read or touch. WITH CHECK constrains the
rows it can write. Writing both here is redundant, strictly speaking: this policy
has no FOR clause, so it covers every command, and such a policy with no
WITH CHECK falls back to applying its USING expression to new and updated
rows as well; writes are already constrained either way. I spell it
out because the write-side rule is then visible at the point of definition
rather than something you have to remember Postgres does for you, and because
the moment you want the read and write predicates to differ, the explicit clause
is the only way to say so.
current_setting reads a run-time configuration parameter. Postgres lets you
define your own namespaced parameters, so app.tenant_id is just a piece of
session state the policy can read. The application sets it once per unit of
work, and every statement in that unit is scoped:
BEGIN;
SELECT set_config('app.tenant_id', '9f4c8a1e-3d2b-4c77-9f1a-6b0d5e8c2f31', true);
SELECT id, title FROM products WHERE brand = 'Acme';
UPDATE products SET status = 'flagged' WHERE id = 41;
COMMIT;
The third argument to set_config is is_local. With true, the setting is
reverted at the end of the transaction. That flag is the whole design, and the
next section explains why.
The shape of the code changes too. The service layer stops passing a tenant into
every query and instead sets it once, at the boundary: in the request dependency
for API calls, at the top of the task for Celery workers. Queries go back to
being about the domain. If the tenant is never set, current_setting raises,
the statement fails, and nothing is returned. That is the behaviour I wanted:
fail closed and loudly, rather than open and quietly.
The parts that bite
RLS is not free, and most of what bit me is not in the introductory examples.
The table owner bypasses its own policies. By default, policies do not apply
to the role that owns the table, which, in most setups, is exactly the role the
application connects as, because it is the role that ran the migrations. You get
a table with RLS enabled, policies attached, and no isolation at all, and
nothing warns you. FORCE ROW LEVEL SECURITY is what closes that. Superusers
and roles with the BYPASSRLS attribute still ignore policies regardless, so
the application should not connect as either.
Connection poolers reuse connections. A pooler in transaction mode hands the
same physical connection to different tenants' requests in sequence. A plain
SET app.tenant_id persists on that connection after your request finishes, so
the next request to borrow it inherits your tenant, which is the leak you
adopted RLS to prevent, reintroduced one layer down. Scoping the setting to the
transaction (set_config(..., true), or SET LOCAL) is what makes this safe,
and it means every tenant-scoped operation has to run inside an explicit
transaction. Autocommit queries outside a transaction have no tenant to read.
Policies are predicates, and predicates cost. The policy expression is added
to every statement against the table, evaluated per row, on top of whatever the
query already does. On a large catalogue scan that changes the plan, so an index
on tenant_id (usually leading a composite index with the columns you actually
filter on) stops being an optimisation and becomes load-bearing:
CREATE INDEX products_tenant_brand_idx ON products (tenant_id, brand);
There is a subtler version of this. Postgres evaluates policy conditions before
user-supplied conditions that are not marked leakproof, precisely so a crafted
WHERE clause cannot infer the contents of rows it is not allowed to see. That
ordering is the correct security behaviour, but it does take a lever away from
the planner, and it is worth knowing about before you go looking for why a query
chose the plan it did.
Debugging gets more ambiguous. An empty result set now has two meanings: no matching data, or the wrong tenant context. Both look identical from the application. I found it worth logging the resolved tenant alongside queries during development, because otherwise you spend time reading the query when the bug is in the context.
Where it sits in the wider pipeline
The architecture around it is a Next.js frontend over a FastAPI service layer, with Celery workers consuming a Redpanda bus for the import and matching stages and Elasticsearch serving the matching engine.
RLS protects exactly one boundary: Postgres. Nothing else.
Messages on the bus are not rows. A Celery worker picking up an import event has
whatever tenant identifier the message carries and no ambient context, so the
worker has to set app.tenant_id from the message payload before it touches the
database. The correctness of the message payload itself is on the producer,
not on the database. The search index is the same story: Elasticsearch has no
idea what a policy is, so tenant scoping there is a filter I have to apply on
every query, plus a decision about whether tenants share an index at all.
Assuming RLS covers the system is a genuine failure mode. It closes the largest hole and it closes it well, but it draws a line around one datastore, and every component outside that line needs its own answer. The value is not that the whole system became safe. It is that one part of it stopped depending on being remembered, and the remaining parts became a list short enough to enumerate.
The argument in one line
Prefer guarantees that fail closed over conventions that rely on being remembered.
This came out of Trademark Guard.