Postgres Row-Level Security for Multi-Tenant SaaS: When It's Enough and Where It Quietly Breaks

Postgres row-level security is one of those features that sounds like the answer to a question SaaS teams have been asking for years: what if the database itself enforced tenant isolation, so a missing WHERE clause could never leak one customer's data to another?

The appeal is real. Postgres row-level security for multi-tenant architectures pushes isolation down a layer, from application code—which developers can forget to update—to the database engine, which doesn't forget. But teams that adopt RLS without understanding its limits often discover the gaps in production, and the gaps are subtle enough that standard test suites rarely catch them.

This post maps where RLS holds up, where it quietly fails, and how to build a multi-tenant Symfony/Doctrine application on top of it without getting burned.

What Postgres RLS Actually Does

Row-level security lets you attach policies to tables that Postgres evaluates before returning or modifying any row. A policy is a predicate—a SQL expression—that must evaluate to true for a given row and a given database session. If no policy matches, the row is invisible.

A basic tenant isolation policy looks like this:

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Create a policy for tenant isolation
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Every query against orders now implicitly filters by tenant_id = current_setting('app.tenant_id'). A developer writing a careless SELECT * FROM orders gets only their tenant's rows, not everyone's. If the app.tenant_id session variable is not set, Postgres returns nothing—which is safer than returning everything.

The mechanic that makes this work is SET LOCAL or SET SESSION to write the current tenant into the session configuration before each request:

SET LOCAL app.tenant_id = '3f7d2a19-cb12-4e8b-9f00-a1b2c3d4e5f6';

From the application side, this needs to happen once per request, before the first query runs.

Wiring RLS Into a Symfony/Doctrine Application

The cleanest integration point in Symfony is a Doctrine event listener that fires on postConnect and on each request's first query. A RequestStack-aware listener can set the tenant on each new connection:

// src/Doctrine/TenantConnectionSubscriber.php
use Doctrine\DBAL\Event\ConnectionEventArgs;
use Doctrine\DBAL\Events;
use Doctrine\Common\EventSubscriber;
use Symfony\Component\HttpFoundation\RequestStack;

class TenantConnectionSubscriber implements EventSubscriber
{
    public function __construct(
        private readonly RequestStack $requestStack,
        private readonly TenantContext $tenantContext,
    ) {}

    public function getSubscribedEvents(): array
    {
        return [Events::postConnect];
    }

    public function postConnect(ConnectionEventArgs $args): void
    {
        $tenantId = $this->tenantContext->getId();
        if ($tenantId === null) {
            return;
        }
        $args->getConnection()->executeStatement(
            "SET LOCAL app.tenant_id = :id",
            ['id' => $tenantId]
        );
    }
}

This approach feels elegant, but postConnect fires once per physical connection, not once per request. When connections are reused—which they always are in production—the tenant variable from the previous request is still set on that connection. The subscriber needs to run before each request's first query, not just after connection establishment.

The practical solution is a Symfony event listener on kernel.request that calls SET LOCAL explicitly:

// src/EventListener/TenantSessionListener.php
use Doctrine\DBAL\Connection;
use Symfony\Component\HttpKernel\Event\RequestEvent;

class TenantSessionListener
{
    public function __construct(
        private readonly Connection $connection,
        private readonly TenantContext $tenantContext,
    ) {}

    public function onKernelRequest(RequestEvent $event): void
    {
        if (!$event->isMainRequest()) {
            return;
        }
        $tenantId = $this->tenantContext->getId();
        if ($tenantId === null) {
            return;
        }
        $this->connection->executeStatement(
            "SET LOCAL app.tenant_id = :id",
            ['id' => $tenantId]
        );
    }
}

SET LOCAL scopes the setting to the current transaction. If you're using SET SESSION, the value persists for the life of the connection—which is exactly the wrong behavior when connections are shared. Always use SET LOCAL inside a transaction, or SET (session-level) only if you can guarantee the connection is not reused before the next request sets it again.

The Connection Pooler Problem

This is where most RLS implementations fail in production.

PgBouncer in transaction mode—which is the default recommended mode for most SaaS applications because it gives you better connection utilization—does not guarantee that session-level variables persist. In transaction mode, each transaction may run on a different backend connection. SET LOCAL only persists for the duration of a single transaction, so in transaction pooling mode, you can only safely use SET LOCAL inside an explicit transaction.

The failure mode: if your application runs a query outside a transaction—a simple SELECT with no wrapping BEGIN/COMMIT—and uses SET SESSION to configure the tenant, that setting may not be present when PgBouncer assigns a backend connection to the next query in the same request. The result is an RLS policy that evaluates with a NULL or wrong tenant ID.

The fix is to wrap the tenant configuration SET and the subsequent query in a single transaction, always:

BEGIN;
SET LOCAL app.tenant_id = '3f7d2a19-cb12-4e8b-9f00-a1b2c3d4e5f6';
SELECT * FROM orders; -- RLS policy evaluates correctly
COMMIT;

In Doctrine, this means your kernel.request listener should wrap the SET LOCAL call inside $connection->beginTransaction() and commit it after the response—or more practically, you ensure all data access happens inside Doctrine's transaction management. If you're using PgBouncer in session mode (lower connection utilization, but simpler semantics), session variables persist across queries. The tradeoff is that you need more backend connections, which costs more.

The general rule: if you're using a connection pooler, test your RLS policies under the pooler, not with direct connections. They behave differently.

SECURITY DEFINER Functions: The Invisible Backdoor

Postgres functions defined with SECURITY DEFINER run with the privileges of the function's owner, not the caller. This is frequently used for utility functions, migrations, and background jobs where you want elevated privileges for a specific operation.

The problem is that RLS is bypassed for the function owner if that owner is a superuser or the table owner. A SECURITY DEFINER function that queries a tenant table bypasses RLS entirely—regardless of what app.tenant_id is set to in the calling session.

-- This function bypasses RLS — dangerous if the owner is the table owner
CREATE OR REPLACE FUNCTION get_tenant_orders(p_tenant_id uuid)
RETURNS SETOF orders
SECURITY DEFINER
AS $$
  SELECT * FROM orders WHERE tenant_id = p_tenant_id;
$$ LANGUAGE sql;

The function above looks like it's manually applying the tenant filter, so you might assume it's safe. But because it runs as the table owner and bypasses RLS, any caller can pass any p_tenant_id—including one they don't own. The manual WHERE clause becomes the only protection, and you're back to application-layer isolation.

The mitigation: audit every SECURITY DEFINER function in your schema. Where possible, replace them with SECURITY INVOKER functions that run as the caller and are subject to RLS. Where you genuinely need elevated privileges, ensure the function manually enforces tenant isolation and add a comment explaining why RLS is not the protection mechanism.

Where RLS Is Not Enough

RLS is a strong defense, but there are categories of operations where it doesn't apply or applies imperfectly.

Aggregate queries across tenants. If your application runs queries like SELECT COUNT(*) FROM users for internal analytics or billing, RLS will correctly scope those to the current tenant. But if your analytics pipeline connects with a database role that bypasses RLS—a role with BYPASSRLS or superuser privileges—those queries see all tenants. Billing and analytics pipelines that connect directly to the database need their own isolation strategy.

Background jobs. Workers processing queue items often connect to the database before they know which tenant they're serving. If a job processor sets the tenant at the start of a job, that's correct. If it forgets—or if the tenant context is ambiguous because the job was enqueued without tenant metadata—the RLS policy may filter out all rows or silently operate on the wrong tenant's data.

Cross-tenant reporting. Some SaaS products offer cross-tenant aggregates to operators or resellers—"all accounts under your organization." This requires RLS bypass, which means the application layer needs to enforce the correct scope. RLS cannot express "show rows for all tenants in this set" without a more sophisticated policy that joins to an authorization table—doable, but complex.

Schema migrations. Running ALTER TABLE or CREATE INDEX on a large tenant-scoped table requires a database role that bypasses RLS or you'll see unexpected behavior. Migration tooling should connect as the migration role and explicitly document which tables have RLS enabled, so operators know what they're bypassing.

Layering RLS With the Doctrine Filter

For teams using Symfony with Doctrine, the most resilient approach is to use both RLS and the Doctrine tenant filter. RLS provides database-layer enforcement that survives rogue queries, ORM bypasses, or direct psql connections from a developer. The Doctrine filter provides a second layer that catches ORM-level oversights and makes the tenant scope visible in the PHP code.

The Doctrine filter won't protect against raw SQL queries executed with $connection->executeQuery(), but it covers all Doctrine entity queries. RLS covers everything at the database level. Together, they reduce the blast radius of any single failure.

// Enable both layers in your Symfony bootstrap
// 1. Doctrine filter (ORM layer)
$em->getFilters()->enable('tenant_filter')->setParameter('tenantId', $tenantId, 'uuid');

// 2. RLS session variable (database layer)
$connection->executeStatement("SET LOCAL app.tenant_id = :id", ['id' => $tenantId]);

The combination is verbose but explicit. Every developer reading the request bootstrap understands that tenant isolation happens at two levels—and that both need to be correct.

A Practical Checklist Before Shipping RLS to Production

Running through this checklist before your first production deployment will save a painful post-incident review:

Test under your actual connection pooler mode. Write an integration test that connects through PgBouncer in transaction mode, does not wrap in an explicit transaction, and verifies that RLS applies correctly. This test will fail if your configuration is wrong.

Audit SECURITY DEFINER functions. Run SELECT proname, prosecdef FROM pg_proc WHERE prosecdef = true; and review each result against tenant-scoped tables.

Verify your background worker sets tenant context before the first query. Review every job class for TenantContext initialization.

Test the "no tenant set" path. What does your application do when app.tenant_id is NULL? The correct answer is: return no rows and log a warning. The wrong answer is: return all rows (which can happen if your RLS policy uses current_setting('app.tenant_id', true) with the missing_ok flag and doesn't handle the empty-string case).

Check your migration role. Confirm that your migration tooling's database role has BYPASSRLS and that this is intentional, documented, and the role has no other elevated privileges.

When to Choose RLS Over Schema-Per-Tenant

RLS is the right default for most SaaS products at the startup and scale-up stage. Schema-per-tenant isolation offers stronger guarantees but at significant operational cost: more complex migrations, higher Postgres memory usage per schema, and harder cross-tenant analytics. RLS gives you strong isolation at the database layer without that overhead.

The exceptions where schema-per-tenant becomes worth the complexity: enterprise clients with contractual data residency requirements, regulated industries where an auditor needs to verify data separation at the schema level, and products where per-tenant backup and restore is a core feature that clients actually use.

For the shared-schema path at Wolf-Tech's SaaS development services, we recommend starting with RLS-backed shared schema and deferring the schema-per-tenant migration until a specific enterprise requirement forces it. Most SaaS businesses never need to make that migration if they get the RLS implementation right from the start.

Summary

Postgres row-level security is a genuine improvement over pure application-layer tenant isolation. It survives developer oversights, ORM bypasses, and direct database connections—as long as you understand where it doesn't reach. The three places it reliably fails teams are connection pooling misconfiguration, SECURITY DEFINER function blind spots, and background jobs that connect without tenant context.

The patterns in this post—using SET LOCAL inside transactions, auditing SECURITY DEFINER functions, layering RLS with the Doctrine filter, and testing under the actual pooler configuration—close most of those gaps without requiring a schema-per-tenant rewrite.

If you're designing multi-tenant isolation for a new SaaS product, or auditing an existing shared-schema application for tenant data leakage risks, reach out at hello@wolf-tech.io or visit wolf-tech.io to discuss what the right architecture looks like for your specific situation.