Java Spring Postgresql Changeset Example

Multi-Tenancy Support With Spring Boot, Liquibase, and PostgreSQL

A step-by-step guide on how to implement multi-tenancy

Multi-Tenancy Models

There are multiple models to achieve multi-tenancy in a microservice:

Database per Tenant: Each tenant has its own database and is isolated from other tenants.
Shared database, Separate Schema: All tenants share a database, but have their own database schemas and their own tables.
Shared Database, Shared Schema: All tenants share the same database and schema. Shared tables have a column with the tenant identifier, which shows the owner of the row.

Each model has its pros and cons. For this story, we are going to focus on the third model, shared database, shared schema, shared tables having a discriminator column with the tenant identifier. See diagram below.

Multi-Tenancy Implementation in Database

Data Isolation: PostgreSQL Row Level Security (RLS)

For data isolation, PostgreSQL RLS is preferred over Hibernate filters. Hibernate's support for discriminator-based multi-tenancy is not mature, still questionable. There is a known issue HHH-6054 still in OPEN status and has had no progress since 2017. For that reason, we are not going to use Hibernate Filters. Instead, we look to PostgreSQL RLS to achieve data isolation.

RLS is a PostgreSQL security feature which allows database administrators to define policies to control how specific rows of data display and operate for one or more roles. RLS is, in essence, an additional filter we can apply to a PostgreSQL database table. When a user tries to perform an action on a table, this filter is applied before the query criteria or other filtering, and the data is narrowed or rejected according to the security policy. We can create row level security policies for specific commands like SELECT, INSERT, UPDATE, and DELETE.

Add a discriminator column

A discriminator column, say tenant_id, needs to be added in tables where multi-tenancy support is needed. We are going to use a demo Spring Boot microservice named customer-service as example. It's a simple microservice which handles customer CRUD operations. Let's assume we need multi-tenancy support for Customer entity. We need to add a new Liquibase changeSet to add the tenant_id column to customer table, non nullable, see below:

Define row level policies

Row level policies need to be defined for the tables which need to have multi-tenancy support. Two steps:

Enabling Row Level Security for the table
Define a Policy for the table, referencing the tenant_id discriminator column

Both steps can be wrapped in one Liquibase changeSet, looks like the following:

Add a app level database user

With Row Level Security, in addition to the table owner database user (in customer-service's case, it's database user postgres), we need to introduce an app level database user who can access the tenant specific operations. Why? Row Level Security policies are by default not applied for the table owner, as table owner must be able to access all rows for administrative purposes. So let's add a app level database user, say "customerservice" by adding a new Liquibase changeSet. See below. The second changeSet in the sample script below is to ensure the SELECT, INSERT, UPDATE, DELETE permissions are properly granted to "customerservice" user for all existing tables. The "ALTER DEFAULT PRIVILEGES..." in the first changeSet ensures all future new tables will have those permissions properly granted to "customerservice". Both changeSets are needed.

Multi-Tenancy Implementation in Application

High level class diagram

See below the class diagram for multi-tenancy implementation in customer-service microservice. We will drill down class by class in the sections below.

Data Source Configuration

We need two data sources:

master data source: Liquibase looks for master data source for database migration needs.
tenant data source: this data source is the default data source for our microservice. See code below. Notice the @Primary annotation on tenantDataSource bean definition.

DataSourceConfiguration class looks like this:

Accordingly, data sources definition in application.yml:

Tenant Aware Database Connection

With the Row Level Security policy in place, every time the app asks the data source for a connection, we need to set the PostgreSQL session variable to the tenant_id to enforce data isolation. When the connection is closed, clear tenant_id from PostgreSQL session. Since we don't have separate database users per tenant, we can utilize a custom session parameter e.g. app.tenant_id to associate current tenant with a database connection. Setting a session parameter is done using a Postgres-specific SQL statement.

"SET app.tenant_id TO '" + tenantId + "'"

See below TenantAwareDataSource class.

TenantAware Interface

This is the interface where multi-tenant entity classes need to implement.

TenantContext

Using InheritableThreadLocal to store/retrieve/clear the current tenant id.

JPA EntityListener

Now how do we set the tenant_id on each entity class which needs multi-tenancy support? Look to JPA EntityListener! EntityListener allows a listener to be attached to the lifecycle of a JPA entity. It allows us to to populate the discriminator column with the current tenant id. See TenantListener class below. The tenantId is captured from TenantContext and set at the entity right before an update, delete, and save.

@PreUpdate: before the update operation
@PreRemove: before an entity is removed
@PrePersis: before persist is called for a new entity

BaseEntity

Adding this class BaseEntity, which implements TenantAware interface, to be extended by entities which need multi-tenancy support. Notice @EntityListeners(TenantListener.class), enabling TenantListener to attach to the life cycle of the particular entity classes which extends BaseEntity.

Entity classes which need multi-tenancy support now need to extend BaseEntity. Sample entity class Customer as follows:

Tenant_id capture from client app

Now let's think about how best to extract the tenancy identifier from an incoming request. A popular option is to have client app send a HTTP Header with the name X-Tenant-ID to pass in existing tenantId. Our microservice then reads the X-Tenant-ID header and stores its value in the InheritableThreadLocal CURRENT_TENANT in TenantContext. We can add a TenantInterceptor like below. in preHandle method, we set the tenantId based on the HTTP Header passed in, if none is passed in, then tenantId holds default value "0".

Next we add WebConfiguration to inject TenantInterceptor to it.

Cucumber BDD test scenarios

To validate the implementation of multi-tenancy feature, the following test scenarios have been developed. As we can see, tenant data is isolated to the particular tenant only.

Sample step definition as follows:

For the complete source code for this story, check out my GitHub repo.

Happy Coding! Happy Crafting!

chambersbobegir.blogspot.com

Source: https://betterprogramming.pub/multi-tenancy-support-with-spring-boot-liquibase-and-postgresql-d41942dc0639