One Missing WHERE Clause Can Expose Another Customer’s Data

A customer opens their dashboard and sees another company’s invoices.

Nobody needed to break in. An endpoint returned data it should never have returned.

That’s one of the mistakes I want to make harder to introduce when building a B2B SaaS. Authentication alone doesn’t solve it: a user can be correctly logged in and still receive another customer’s data.

For BootSaaS, I chose a schema-per-tenant architecture with Spring Boot, PostgreSQL, and Liquibase. I want separate business tables for each customer, a shared connection budget, and deployments a small team can operate. For that combination, schema-per-tenant is my clear choice.

This is an architecture walkthrough. The examples illustrate the design rather than provide a complete implementation.

First, what counts as a tenant?

In BootSaaS, a tenant is a customer organization or workspace. Each user belongs to exactly one tenant, and a tenant can have several users. This is the model used throughout this article.

Tenant isolation answers one question: which organization’s data can this request access?

Permissions inside that organization answer another: can this member view invoices, invite teammates, or change settings? You need both.

How one missing filter can expose customer data

Consider a shared invoices table:

Invoice 101 belongs to Acme (tenant_id=acme) and has an amount of 1200.
Invoice 102 belongs to Globex (tenant_id=globex) and has an amount of 850.

To retrieve Acme’s invoices, an application might execute:

SELECT id, amount
FROM invoices
WHERE tenant_id = :current_tenant;

Now imagine a new export endpoint uses this query:

SELECT id, amount
FROM invoices;

If application-level filtering is the only isolation mechanism, this returns both customers’ invoices. The SQL is valid. The endpoint may even pass a test suite containing only one tenant.

The same mistake can affect a lookup by ID, an update, or a delete. A globally unique invoice ID prevents collisions; it doesn’t establish that the current user may access that invoice.

I wouldn’t build tenant isolation around everyone remembering a filter. ORM support can centralize filtering, while PostgreSQL’s row-level security can enforce policies in the database. With RLS, the application role matters: superusers, roles with BYPASSRLS, and normally table owners bypass those policies. PostgreSQL documents these rules here.

The weak point in the example is relying entirely on every application query remembering the tenant restriction.

The three common storage approaches

My target here is a B2B SaaS with many workspaces, broadly the same data model, and a small engineering team. For that workload, I don’t consider these three approaches equally attractive.

Shared tables: simple operations, isolation through row scoping

Every customer shares the same tables, with a tenant identifier on tenant-owned rows. One structure to migrate and a shared connection pool make this operationally attractive.

But the tenant boundary depends on correctly enforced row scoping. If I chose this design, centralized enforcement would be a requirement. For BootSaaS, I want each customer’s business records in separate tables, so I chose a different boundary.

Database per tenant: too much operational machinery for my default

For the SaaS I’m building, I would reject database-per-tenant as the default. The operational cost starts inside the Spring Boot application.

Spring Boot normally uses HikariCP when you include the JDBC or JPA starter. With a straightforward setup using one HikariDataSourceper database, 1,000 tenant databases means managing 1,000 separate pools if you keep them all active. Spring Boot documentation

Each pool brings connection objects, housekeeping, and a lifecycle to manage. Retaining idle connections across hundreds of mostly inactive tenants wastes resources. For example, configuring a minimum of five idle connections in each of 1,000 active pools means asking those pools to maintain 5,000 idle connections in total. That is a configuration example, not an unavoidable connection count. HikariCP configuration

You can create pools on demand and close unused ones. But now your application needs to manage pool creation, eviction, and concurrent access. I don’t want that additional machinery to be the default cost of creating a workspace.

I would reconsider separate databases for a concrete customer requirement that justifies their operational cost, but I wouldn’t build that complexity into a standard SaaS by default.

Schema per tenant: the sweet spot for BootSaaS

Schema-per-tenant means using one PostgreSQL database with a separate schema for each customer organization. A schema is a named container for database objects, including tables. Each tenant gets its own set of business tables inside that container.

For example, Acme’s invoices live in acme.invoices, while Globex’s invoices live in globex.invoices. These are two distinct tables inside the same database.

With a shared application database role and deliberate schema selection, connections can be reused across tenants. The team can size a common pool for actual concurrent work. Requiring a separate database role for every tenant would change that pooling calculation.

This gives me the combination I want: explicit separation of business tables, centralized tenant routing, and a common migration definition applied through one database connection infrastructure. Access checks, privileges, and isolation tests make that separation enforceable.

That’s the sweet spot for BootSaaS. I’m willing to manage versioned tenant schemas to get this boundary, and I don’t want the extra database topology as the default price of onboarding a customer.

Here’s what that separation changes for an application query. With separate schemas, the query can stay simple:

SELECT id, amount
FROM invoices;

If the connection resolves invoices to acme.invoices, that query reads Acme’s table. Globex’s rows are in another table. Omitting a tenant filter no longer combines the two datasets in this example.

PostgreSQL uses search_path to resolve unqualified table names. However, schemas are namespaces, not automatic security walls: a database role with sufficient privileges can explicitly query another schema. PostgreSQL’s schema documentation explains both mechanisms.

This is the boundary I want to enforce: establish the authorized tenant context before business queries run. I can concentrate that responsibility in the connection and persistence infrastructure, then test it explicitly.

That infrastructure must be correct. An Acme request routed to Globex’s schema can still read the wrong invoices. Tenant authorization and connection handling are requirements of this design, and I treat them as such.

Persistence, migrations, and tenant provisioning have different jobs

Hibernate handles entity persistence and querying. Database migrations describe and version changes to the database structure. The application coordinates tenant creation and decides which tenant a request belongs to.

For BootSaaS, I use Liquibase to run and track those migrations. Flyway is another option for that role; schema-per-tenant does not depend on Liquibase. The following implementation details reflect the tool I chose.

Adding Liquibase doesn’t automatically create a tenant onboarding system. Spring Boot’s standard integration runs migrations at startup; provisioning schemas when workspaces are created requires additional application logic. Spring Boot documents the startup integration here.

Liquibase changelogs contain changesets and can use XML, YAML, JSON, or formatted SQL. XML and YAML can describe changes such as creating a table; they can also reference SQL files. They aren’t simply containers you must put SQL into. Liquibase’s changelog documentation includes examples.

Creating or changing a JPA entity doesn’t, by itself, write the corresponding migration. The mapping and the migration both need to describe the intended database structure.

About ddl-auto=none

When Liquibase owns schema changes, Hibernate shouldn’t also create or update those tables. One configuration choice is:

spring.jpa.hibernate.ddl-auto=none

But validate is different from update or create: it checks the schema without changing it. It isn’t inherently incompatible with Liquibase. With dynamically provisioned tenants, you need to decide which schema exists at startup and what Hibernate can actually validate. A successful startup check is not evidence that every tenant schema is up to date. Hibernate defines these actions here.

Creating a workspace: provision first, activate second

I use asynchronous tenant provisioning in BootSaaS. The rule is straightforward: a workspace must not accept business requests until its schema is ready. The lifecycle should enforce that:

• Register the new tenant in a shared tenant registry, with a provisioning status.
• Allocate an internal schema name and create the schema.
• Run the tenant changelog against that schema.
• Mark the tenant active only after provisioning succeeds.
• Record failures so provisioning can be investigated and retried safely.

A shared schema can hold the tenant registry and other platform-wide records. Tenant schemas hold the business data that belongs to each workspace. Keep platform migrations and tenant migrations separate so a new workspace doesn’t receive copies of tables intended to be global.

Schema names should come from an application-controlled mapping, not raw user input inserted into SQL.

The migration definition is reusable, but its execution must target each tenant separately. A straightforward design is to keep each tenant’s migration history and lock tables in its own schema. Liquibase distinguishes the default target schema from the schema holding DATABASECHANGELOG and DATABASECHANGELOGLOCK; configure both deliberately. See the default schema parameter and the Liquibase metadata schema parameter.

This matters because Liquibase identifies applied changesets using their ID, author, and file path. Reusing one undifferentiated migration history for every tenant can make a changeset applied to one schema appear already applied for the next. Liquibase describes that tracking here.

Retries also deserve attention. If an attempt created the schema but failed later, restarting provisioning must account for that partial state. Running work asynchronously doesn’t make it automatically recoverable.

Handling a request: resolve, authorize, then query

Creating separate schemas is only half the implementation. Every request also needs to reach the correct one.

In BootSaaS, the source of the tenant identifier depends on whether the request is authenticated.

For public routes such as login or registration, the tenantId comes directly from the request URL. It identifies the tenant the request is targeting. That value is routing information: putting a tenant ID in a URL does not grant access to its data.

Once the user is authenticated, the tenantId is included directly in the JWT payload alongside the other authentication claims. The token’s signature protects those claims against undetected modification; it does not make the payload confidential. The JWT must be validated before its claims are trusted. Spring Security documents JWT validation here.

For each authenticated request, a filter in the Spring Security chain extracts the tenantId from the validated JWT and places it in the tenant context. From there:

• CurrentTenantIdentifierResolver supplies the current tenant identifier to Hibernate.
• MultiTenantConnectionProvider provides a connection configured for the corresponding PostgreSQL schema.
• Business queries execute against that tenant’s tables.

The tenant context must be established before Hibernate opens the tenant-scoped session. It must also be cleared after the request so that a reused application thread cannot carry the previous request’s tenant into another one.

That’s what I like about this design: tenant routing follows the authenticated identity through Spring Security and into Hibernate. The authentication flow stays stateless, with no server-side authentication session required to remember the selected tenant.

For BootSaaS, this is a clean fit. I establish the tenant context at the request boundary, and the persistence infrastructure uses it consistently to select the right schema.

Existing tenants need migrations too

Creating the first tables is only the beginning. When a release adds a column, every existing tenant eventually needs that migration.

Whether each tenant has a separate schema or a separate database, the change must be applied to each tenant’s tables and its outcome tracked. Choosing schema-per-tenant simplifies the connection infrastructure; it does not eliminate the repeated migration work.

If migrating one tenant took five seconds, updating 2,000 tenants sequentially would take approximately 2 hours 47 minutes. That’s illustrative arithmetic, not a benchmark for a particular migration tool. It applies to either layout under that assumption. Put that sequence in your deployment pipeline, and your release waits for it.

That requires orchestration: enumerate tenants, track outcomes, limit concurrent migrations, and handle failures. If schemas are upgraded gradually, the application needs to tolerate the supported versions during that window, or access must be coordinated with the upgrade.

Keep the tenant structure consistent by default. Separate schemas allow customization, but maintaining a different set of columns for each customer introduces migration branches, mapping differences, and more combinations to test. For a standard SaaS, that undermines the maintainability this design is meant to give you.

At large tenant counts, schema and table growth still need measurement, and the instance remains a shared resource and failure domain. I accept those constraints for BootSaaS. A common pool and separate business tables are useful benefits now; the migration strategy must remain explicit as the product grows.

The isolation tests I would include

A useful test suite needs at least two tenants with different data. I would include:

• A list or export request for Acme that must never return Globex’s records.
• A request using a resource ID belonging only to Globex, which Acme must not read or modify.
• The same resource ID in both schemas, verifying that each tenant gets its own record.
• An unauthorized workspace selection, rejected before business data is accessed.
• Alternating requests through a deliberately small connection pool, including an exception path, to exercise connection reuse.
• A failed provisioning attempt, verifying that the workspace stays unavailable and recovery doesn’t corrupt its state.

These tests verify the boundaries that the architecture depends on.

Why this is my default for BootSaaS

For BootSaaS, schema-per-tenant is a deliberate engineering choice. I want separate business tables per workspace, reusable database connections, and a common set of versioned migrations that the application can apply as tenants are provisioned and upgraded.

I consider the extra database topology of database-per-tenant a poor default for that workload. Schema-per-tenant gives the team a concrete boundary to enforce while keeping the connection infrastructure shared.

Authorized tenant selection, correct connection handling, recoverable provisioning, and isolation tests are the implementation work that makes this choice deliver. That’s where I want to invest the engineering effort.

I’m building BootSaaS, a Spring Boot and Angular boilerplate that includes schema-per-tenant PostgreSQL storage and asynchronous provisioning with Liquibase. If you’re considering the same foundation for a project, you can find more details there.