Single-database multi-tenancy is the cheapest kind: one schema, one connection, an organization_id column on every tenant-owned table. The whole design rests on one promise, and it is a promise about forgetting: no developer on the team will ever have to remember to write WHERE organization_id = ?, because forgetting it once leaks another customer's data.
Doctrine has had the tool for this for years. It is a SQLFilter, it is about thirty lines, and almost every article about it stops at the happy path. The interesting part is not the filter. It is the map of the places where it is simply not there, because that map is what you actually have to defend.
Everything below is read from Doctrine ORM 3.6.7 and from a suite that runs on every commit.
The filter
final class OrganizationFilter extends SQLFilter
{
public const string NAME = 'organization';
public const string PARAMETER = 'organization_id';
public function addFilterConstraint(ClassMetadata $targetEntity, string $targetTableAlias): string
{
if (!$targetEntity->getReflectionClass()->implementsInterface(OrganizationOwnedInterface::class)) {
return '';
}
return \sprintf('%s.organization_id = %s', $targetTableAlias, $this->getParameter(self::PARAMETER));
}
}
OrganizationOwnedInterface is a marker with one method, getOrganization(). An entity opts into tenancy by implementing it, and that is the entire public API of the mechanism. No attribute to remember, no base class to extend, no trait whose absence is invisible in a diff.
The filter is declared in doctrine.yaml with enabled: false. That is deliberate, and it is the first design decision worth arguing about: a filter that is on by default in the container is on in your fixtures, in your migrations, in your data-repair scripts, and it will bite you at three in the morning. It gets turned on by the layer that knows who is asking.
The layer that knows who is asking
public static function getSubscribedEvents(): array
{
// Right after the firewall (priority 8) so the user is available.
return [KernelEvents::REQUEST => ['onKernelRequest', 7]];
}
public function onKernelRequest(RequestEvent $event): void
{
if (!$event->isMainRequest()) {
return;
}
if (str_starts_with($event->getRequest()->getPathInfo(), '/admin')) {
return;
}
$organization = $this->organizationContext->getOrganization();
if (null === $organization) {
return;
}
$this->entityManager
->getFilters()
->enable(OrganizationFilter::NAME)
->setParameter(OrganizationFilter::PARAMETER, (string) $organization->getId());
}
Priority 7 is not a magic number. Symfony's Firewall listener subscribes to kernel.request at priority 8, so 7 is the first slot where Security::getUser() is populated. Higher and you get no user and therefore no filter at all, which is the worst possible failure mode, because the page still renders.
That is the whole mechanism. Now the useful part.
Place 1: the console, and every Messenger worker
There is no kernel.request in a CLI process. Your commands, your cron jobs and your Messenger consumers therefore run with the filter off, seeing every tenant's rows.
This is correct behaviour and you want it: a nightly billing command has to iterate over all organizations. But it means the guarantee "tenant data is invisible by default" is a guarantee about HTTP, not about your application. Every command that touches tenant-owned entities has to scope itself explicitly, and there is no compiler to remind you.
The honest framing is: the filter is a safety net under your controllers. Under your workers there is no net, and pretending otherwise is how a support script mails the wrong invoice to the wrong customer.
Place 2: your own back office, on purpose
An admin panel exists precisely to look across tenants. Filtering it would make the metrics wrong and the CRUD useless, so /admin is exempted by path.
The trade is that a path prefix now carries a security consequence, and path prefixes are easy to change. It only holds because the same prefix is locked to ROLE_ADMIN in access_control. If you copy this pattern, copy both halves, and treat the exemption list as security-critical code rather than as configuration.
Place 3: find() when the entity is already in the identity map
// EntityManager::find(), doctrine/orm 3.6.7
$entity = $unitOfWork->tryGetById($sortedId, $class->rootEntityName);
find() returns from the identity map before any SQL is generated. If an entity belonging to another organization was loaded earlier in the same request, by a fixture, by a cascade, by a getReference() that got initialized, then find() hands it back and no filter is consulted, because no query happens.
The same applies to getReference() itself, which builds a proxy from an id without touching the database at all. Passing a user-supplied id to getReference() and trusting the filter to reject it does nothing: the proxy is returned, and it only fails much later, when something initializes it.
Practical rule: a filter protects queries, not object identity. Authorization on an id that came from the outside is still authorization. Keep your voters.
Place 4: DBAL, and every line of native SQL
Filters are a DQL concern. $connection->executeQuery() and createNativeQuery() never see them. This is obvious once stated, and it is where the leaks actually happen, because native SQL is exactly what people reach for on the reporting and export screens, which are exactly the screens that show a lot of rows at once.
Place 5: joined inheritance, when the column is not on the root table
This one is silent, and it is the reason to read the ORM source rather than the documentation.
// SqlWalker::generateFilterConditionSQL(), doctrine/orm 3.6.7
case ClassMetadata::INHERITANCE_TYPE_JOINED:
// The classes in the inheritance will be added to the query one by one,
// but only the root node is getting filtered
if ($targetEntity->name !== $targetEntity->rootEntityName) {
return '';
}
With JOINED inheritance, Doctrine only ever offers the root entity to your filter. So if your abstract root does not implement the tenancy interface and each concrete subclass does, the subclass is skipped by Doctrine and the root is skipped by your own implementsInterface() check. Two correct-looking guards, and the result is no WHERE clause at all.
The fix is a modelling rule, not a code change: in a joined hierarchy, organization_id and the marker interface belong to the root entity. Worth an architecture test if your domain uses inheritance.
And one place it does run, where most people assume it does not
The folklore says Doctrine filters only apply to SELECT. That is false in ORM 3, and it is easy to check: walkUpdateStatement() and walkDeleteStatement() both call walkWhereClause(), which is exactly where filters are injected. Here is the SQL Doctrine actually generated for me, with the filter enabled:
-- DQL: DELETE FROM SampleNote n
DELETE FROM sample_notes WHERE (sample_notes.organization_id = '...')
-- DQL: UPDATE SampleNote n SET n.title = :t
UPDATE sample_notes SET title = ? WHERE (sample_notes.organization_id = '...')
Bulk DQL statements are scoped. Note the table name in place of an alias, since useSqlTableAliases is false for those statements. Good news, but do not let it lull you: this is the ORM's DQL path only, and place 4 still stands one line away.
Proving it, rather than believing it
An isolation guarantee that is not tested is a comment. The check is short, and the part that matters is the assertion on the filter itself, the one that fails loudly the day someone "simplifies" the subscriber:
$client->loginUser($user);
$client->request('GET', '/dashboard');
self::assertTrue($entityManager->getFilters()->isEnabled(OrganizationFilter::NAME));
$notes = $entityManager->getRepository(SampleNote::class)->findAll();
self::assertCount(1, $notes);
self::assertSame('Mine', $notes[0]->getTitle());
Plus its mirror image, that an anonymous request leaves the filter off, which is what catches a firewall-priority regression before your customers do.
Two organizations, one row each, one authenticated request. It runs in under a second, and it is the only reason anyone should believe the paragraph at the top of this article.
What I would keep
Three sentences, if you are building this today.
The filter belongs off in the container and on at the edge, because the layer that knows the tenant is the only layer entitled to turn it on. The marker interface belongs on the root entity, and an architecture test should say so. And the filter is a net under HTTP only, so every command, every consumer and every line of native SQL is code you have to read with tenancy in mind.
I maintain ShipAnvil, a Symfony 7.4 LTS SaaS starter, and this is the tenancy layer it ships, isolation tests included. The article stands on its own, though. If you find a sixth hole, I would genuinely like to hear about it.
Written by Eric Mollenthiel, freelance Symfony developer in Lyon, France.
More at mollenthiel.fr.
Top comments (7)
Places 1 and 4 both close if the predicate moves out of the ORM entirely. We do it in Postgres with RLS: the policy reads a
current_settingthat the per-request transaction sets, so a worker, a line of native SQL and an ad-hoc psql session all get the same WHERE clause whether anyone remembered it or not, and your /admin exemption becomes a database role instead of a path prefix.It buys a different failure mode rather than none: unfiltered becomes empty. Both bugs it has cost us were invisible in tests, because the test connection was a superuser and a superuser bypasses RLS, which is your "assert the filter is enabled" test one layer down.
Was keeping it in the ORM a portability call?
No, and I would rather say that plainly than invent a portability story after the fact. The kit is PostgreSQL and nothing else: a migration installs a PL/pgSQL function and a trigger for pg_notify, the setup check refuses to run without pdo_pgsql, and the README promises PostgreSQL 16+. Portability was never the reason.
The real reason is a deployment contract. It is a repo people buy and deploy wherever they like, so every requirement I put on their infrastructure is one I have to keep supporting.
And your superuser trap is wider than superusers, which is the part worth flagging to anyone reading this and reaching for RLS. A plain role that merely owns the table bypasses its own policies too: "Table owners normally bypass row security as well, though a table owner can choose to be subject to row security with ALTER TABLE ... FORCE ROW LEVEL SECURITY" (PostgreSQL 18, 5.9). I measured it this morning on 18.3, throwaway database: owner role, not superuser, no BYPASSRLS, policy in place, nothing set, and every row comes back with no error at all. Add FORCE and the same query returns zero, then the setting brings back the right one.
That matters more in production than in tests here, because Doctrine migrations create the tables under the application account: our own deploy guide does CREATE DATABASE app_db OWNER app, which is what most Symfony deployments look like. So it would be the buyer's database sitting there with RLS enabled and every policy inert, green suite, no error anywhere. Adopting it as a default means asking them for three things the filter never asks for: a migrating role separate from the serving role (or FORCE on every tenant table, and on the next one someone adds), a transaction around every request for SET LOCAL to live in, with a PgBouncer in transaction pooling turning a stray SET into a cross-tenant leak, and a CI that stops running as superuser. Inside a product where you own the infrastructure, your trade is the better one. As a default in a starter kit, I did not want to hand that bill over.
One correction in your favour, and it goes further than you claimed: RLS closes four of the five, not two. Place 5 goes too, since a policy sits on the table and does not care that the SqlWalker only ever offers the root entity of a JOINED hierarchy to the filter. The cost is that the list of things to cover moves into your migrations instead of your model, and it is a list either way. Place 2 is better in your version than in mine, exactly as you put it: an exemption that is a database role is a far better home for a security consequence than a path prefix. Place 3 is the one no predicate closes, yours or mine: find() from the identity map and getReference() emit no SQL at all, so there is no query for a policy to apply to, and it stays an authorization question in the application.
Do you run the suite under a non-owning role, or did FORCE make that question moot? And what happens to the per-request SET LOCAL if a PgBouncer in transaction pooling ends up in front?
Following up, because your comment changed the kit and not just my answer.
Place 2 told the reader to treat the exemption list as security-critical code, and nothing on my side actually held that line: the list was a private constant, and no test tied it to the access_control rule that is supposed to make it safe. It is public now, because a list no test can read is a list nobody can guard, and two tests hold it.
One reads access_control out of security.yaml, redoes Symfony's own resolution (first matching rule wins, preg_match on the rule path) and asserts ROLE_ADMIN alone for every path that escapes the filter. The roles of an access_control rule are an OR, so asserting membership guards nothing: one extra role reopens the path, and only equality catches that. It also covers /administration, which str_starts_with happily lets through and which nobody has in mind.
The other checks over HTTP that the filter really is off under /admin for an admin who does belong to an organisation, so the filter had something to do and only the path stopped it, and that it is still on for that same admin under /dashboard. The exemption is about the path, not the role, and that second assertion is the one that falls if someone later "simplifies" the subscriber into a role check.
Four mutations, run rather than assumed: unguarded exempt prefix, rule widened to ROLE_USER, exemption removed, exemption rewritten as a role test. All four fail the suite, with a message that says what to do.
Your version still puts that consequence somewhere better than a path prefix, and I said so. This was the part I could actually close.
You closed place 2 properly, and my own version of that test is where your first question stings. The suite runs throughout as a non-owning application role, which is RLS-subject under plain ENABLE, so dropping FORCE would have failed nothing in it. A FORCE no test can fail is a FORCE nobody can guard, your line one layer down. So one test becomes a NOSUPERUSER NOBYPASSRLS role that owns the base tables, with no step-down, carrying its mutation the way your four do: revert the FORCE migration and the owner reads every tenant. My eight oldest tables were ENABLE-only until then anyway, created under the app account, exactly the case you measured on 18.3.
No PgBouncer yet. The setting is already transaction-local (SET LOCAL ROLE, set_config with is_local true), which is the form transaction pooling survives, so a session SET is the leak. Would you take FORCE everywhere, or the separate migrating role?
Both, and I'd push back on the framing: they aren't two ways of doing the same thing, they fail differently.
FORCE is a property of a table. The separate role is a property of a connection. That decides which future mistake each one covers.
The role covers what you add. Adding
invoice_linenext month still needs ENABLE and a policy whichever you picked, so the role doesn't remove the per-table checklist, it removes the one item on it that fails silently. A missing ENABLE or a missing policy is caught by any isolation test that reads as another tenant. A missing FORCE is caught by nothing, unless the suite runs as the owner, which is the test you just wrote.FORCE covers how you connect. The role only holds while nothing serves as the owner, and that assumption erodes outside the HTTP request: a console command, a worker, a backfill run by hand, a psql session during an incident. Any of those opened as the migrating role has the bypass back, and none of them go through the request-scoped SET LOCAL your policy reads. FORCE holds whatever connects.
So: the role for what you'll add, FORCE for how you'll connect. If I had to take one, I'd pick on how the team actually errs. More new tables than out-of-band access paths, take the role. Lots of workers, backfills and incident sessions, take FORCE.
But the part of your comment that matters more than either answer is the test. Once an owner-role test fails on a missing FORCE, forgetting it stops being silent, and the choice stops being load-bearing. It's the same move as place 2: which answer you pick matters less than whether the answer can be checked. I had that backwards, and you closed it the way I closed place 2.
For the kit my answer hasn't moved, neither ships, because both are requirements on infrastructure I don't control. What moved is that I was treating "a FORCE nobody can guard" as a reason not to go near it at all. You've shown it's guardable.
The kit reason still holds, but I think it rules out one thing more than it needs to.
FORCE and the separate role are both demands on the buyer's infrastructure, agreed. Detecting that neither is in place is not. You already ship a setup check that refuses to run without pdo_pgsql, and the same check could ask whether the serving role owns the tenant tables with FORCE off, which is your 18.3 measurement turned into two queries. It imposes nothing. It just means the buyer who deploys with CREATE DATABASE app_db OWNER app finds out at setup instead of never.
That is your own move from place 2 rather than mine: not picking the answer for them, just making the silent case audible. Would that clear the deployment-contract bar, or does a warning you cannot action still count as a requirement?
You're right, and the refusal was wider than the reason behind it. Detecting is not imposing, and I don't have an argument against that distinction.
On your direct question, my answer is: a warning you cannot action is a requirement in disguise, but only when all it says is "this is wrong". It stops being one when it names what is still true without it. Here that sentence exists, and it is not a consolation prize: the Doctrine filter isolates tenants on its own. So the honest message is "you have the belt, you don't have the braces, and here is the case the braces catch", not "your deployment is broken". A buyer on shared hosting who cannot change ownership can read that and get on with his day. That clears the bar for me.
One correction, and it costs your proposal something. The check you're pointing at is a Makefile target, not an application command: it runs before .env.local exists and never opens a connection: PHP version, five extensions, Composer. So this isn't two queries added to something that already runs at the right moment; it's a second check that has to live somewhere after the database URL is set. Smaller than it sounds, the kit already ships console commands of that shape, but the cost isn't zero and I'd rather say so than let it look free.
And here is the part I haven't measured, which is where I'd push if I were you: what those two queries return for a serving role that isn't the owner and cannot see what it doesn't own. Catalog visibility is the whole trick, and a check that quietly answers "nothing to report" because the role can't see the tenant tables would be worse than no check at all. I don't have that number yet.