100 Tenants, One Database, Zero Data Leaks — How We Use PostgreSQL Row-Level Security

Every multi-tenant SaaS has the same nightmare scenario: a bug in the application layer lets one customer see another customer’s data. It’s not theoretical. It happens. And when it happens on a platform that handles cloud billing data, it’s not just embarrassing — it’s a compliance incident, potentially a contract breach, and exactly the kind of story that ends early-stage companies.

We built Fintropy to handle sensitive financial data for cloud accounts across multiple enterprises. We could not afford to get this wrong.

Here’s how we designed multi-tenancy at the database layer, why we chose the approach we did, and what we’d warn you about before you do the same.

The Three Standard Approaches (and Their Problems)

Separate databases per tenant

One database per customer. Airtight isolation. Also: a migration nightmare at scale, connection pool explosion, and infrastructure costs that don’t make sense until you have large customers.

Separate schemas per tenant

One schema per customer in the same database. Better than separate databases, but migrations still require running changes N times, schema routing in your ORM is fiddly, and you lose cross-tenant querying for your own analytics.

Shared tables with application-layer filtering

Everyone in the same tables. The application adds WHERE tenant_id = :current_tenant to every query. Simple to implement, and every ORM supports it.

The problem: it relies entirely on discipline. One forgotten filter, one raw SQL query without a WHERE clause, one junior developer who didn’t know the convention — and you have a data leak. The database will happily return every row you ask for.

What We Chose: Row-Level Security

PostgreSQL Row-Level Security (RLS) enforces data isolation at the database engine level, not the application level.

Every table in Fintropy has a tenant_id column. Every query runs inside a database session where we set the current tenant:

SET app.current_tenant_id = '3f7b9c2a-...';

And every table has an RLS policy:

ALTER TABLE cloud_subscriptions ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON cloud_subscriptions
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

After that, any query on cloud_subscriptions will only ever return rows belonging to the current tenant — regardless of what the application code does. The database engine applies the filter before returning results.

Even if a developer writes this:

# Forgot to filter by tenant — catastrophic bug potential
subscriptions = db.query(CloudSubscription).all()

They get back only the current tenant’s subscriptions. The database enforced the boundary.

The Setup in SQLAlchemy

In FastAPI, we wire the tenant into the database session on every request:

# In our database session factory
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# In our auth dependency
def set_tenant_context(
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    db.execute(
        text("SET app.current_tenant_id = :tid"),
        {"tid": str(current_user.tenant_id)}
    )
    return db

Every endpoint that needs tenant isolation depends on set_tenant_context. The session variable is set once per request. Every query on that session runs with the RLS policy applied.

What It Actually Costs

Migration complexity

You can’t run a migration with a superuser role that bypasses RLS without explicitly setting the tenant. We write migrations carefully — some DDL operations need RLS bypassed (for the migration itself), some don’t.

We have a BYPASSRLS flag on our migration runner that we use deliberately and sparingly.

Testing setup

Every test that touches the database needs to set up the tenant session variable. We have a pytest fixture that handles this:

@pytest.fixture
def db_with_tenant(db, test_tenant):
    db.execute(text("SET app.current_tenant_id = :tid"),
               {"tid": str(test_tenant.id)})
    yield db

Forgetting this fixture means your test queries return zero rows, which is a confusing failure mode until you understand the system.

Performance

RLS policies add a filter to every query. In practice, since tenant_id is indexed on every table, the overhead is negligible — the planner uses the index either way. But you need to verify this with EXPLAIN ANALYZE on your critical queries.

Superuser access

Database superusers bypass RLS by default. Your application never connects as superuser. Your migration tooling should be careful about when it uses elevated privileges.

35 Tables, Zero Cross-Tenant Leaks

Fintropy has 35+ data models — cloud subscriptions, scan results, SLA breaches, billing records, budgets, recommendations. Every single one is protected by the same RLS pattern.

The thing we value most isn’t the security guarantee in isolation — it’s the forcing function it creates. No developer can accidentally write a query that leaks data across tenants. The database physically won’t return those rows. The application can have bugs; the boundary holds anyway.

For a platform handling enterprise cloud billing data, that’s the right place to enforce the invariant.

Fintropy is a multi-cloud FinOps platform in private beta. Learn more at nuvikatech.com