Multi-Tenant Data Isolation and Row Level Security

Over the past one and a half years, I was involved in designing and developing a multi-tenant treasury management system. In this article, I will share our approaches to the data isolation aspect of our multi-tenant solution and the learnings from it.

Background and Problem Regarding Data Isolation

Before going into the problem that I will focus on today, I must first give some background into our architecture for storage and data in our system. When it comes to data partitioning for SaaS systems, at the extreme far right end, we have the approach of using dedicated databases for each tenant (silo model), and on the other side of the spectrum is the shared database model (pool model).

Shared database model (Pool model)

Dedicated database model (Silo model)

For obvious reasons such as reduced management overhead and lower cost, it was decided that our solution would be in the shared database model, and with that comes the drawback of lower levels of inherent data isolation. 

In most cases with the shared database model, the data isolation depends on developers implementing the correct WHERE clauses in every SQL statement, but this is, of course, error-prone. We wanted an approach to enforce the data isolation from a separate layer and in comes the concept of Row Level Security (RLS).

interface TransactionRepository : JpaRepository<Transaction, Long> {

    // trivial approach
    fun findAllByTenantId(): List<Transaction>
    
    // what we want to achieve
    override fun findAll(): List<Transaction>
}

Solution: Row Level Security (RLS)

In general terms, Row Level Security (in RDBMS) refers to mechanisms that allow controlling access to rows in a database table based on some context (for example, tenant_id). To my knowledge, this feature has been around on PostgreSQL DB since 2020, and it is available in SQL Server, in Amazon Aurora for PostgreSQL, and RDS for PostgreSQL at the time of writing.

ALTER TABLE gtms_payment.TRANSACTION ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_policy ON gtms_payment.TRANSACTION
    USING (business_group_code = current_setting('app.current_tenant')::VARCHAR);

Other Solutions: Sping Post Filter, Hibernate Filters With Spring Aspects

To give more details from my analysis of the possible solutions, let me share some brief info on other possible approaches as well. One of the options was Spring PostFilter, but that would mean all data (across multiple tenants) would be returned to the backend from the db query before they are filtered and sent out, and this was not ideal for our use case.

Another option (which is specific to our backend tech stack — Spring, Hibernate, and Java/Kotlin) was to use Hibernate Filters with a Spring Aspect. This option was quite good but not as strong as having the enforcement done on the DB layer. (We did use this approach to solve a similar use case, and I hope to cover this in a later blog)

Going Ahead With RLS

Our standard database solution throughout the organization was PostgreSQL (for use cases like ours), and this helped in our decision to go ahead with RLS as the solution, but if you are trying to solve a similar problem with RLS, do take note that not all of the relational databases support Row Level Security yet so you might have to be aware of being locked into a subset of databases that support RLS. However, switching to another relational DB that supports RLS should not be too difficult as there is little dependency on the DB type on the code level itself when using RLS.

Implementation

Going into the implementation details, once RLS is enabled on the DB, we should set the parameters (in our case, app.current_tenant) for the DB to run the policy on for each query. In our case, we do that on the DataSource implementation as follows. In our flow, we maintain the Tenant information on Spring Security Context (again, which I hope to cover in a separate blog), and the getTenantId() which is abstracted out here is retrieving the Tenant Id from the Spring Security Context.

class TenantAwareHikariDataSource : HikariDataSource() {

    override fun getConnection(): Connection? {
        val connection = super.getConnection()
        return createConnection(connection)
    }

    override fun getConnection(username: String, password: String): Connection? {
        val connection = super.getConnection(username, password)
        return createConnection(connection)
    }

    private fun createConnection(connection: Connection): Connection? {
        connection.createStatement().use { sql ->
            sql.execute("SET app.current_tenant = '${getTenantId()}'")
        }
        return connection
    }
}

[2024-08-25 22:53:50] [42704] ERROR: unrecognized configuration parameter "app.current_tenant"

In all multi-tenant systems, there will always be some use cases, such as tenant onboarding, where you will have to run DB queries without a Tenant Id. So, can we support such use cases? The answer is yes! In PostgreSQL functionality, the schema owner bypasses RLS policies. Hence, in our solution we have, we maintain two sets of DB configs (Tenant DB Config and Non Tenant DB Config) and two sets of Spring repositories tied to each DB config with tenant config as primary.

Thus, in the few use cases that need to run queries without a Tenant Id, we use the Non-Tenant configs. We also have to use PostgreSQL schema owner user for the database change management tool (in our case, Liquibase).

Pros and Cons of the RLS Approach to Sum Things Up

Pros

• RLS provides data isolation on the DB layer. Hence, it provides a stronger isolation than that of the common WHERE clause approach.
• Most of the data isolation logic (Tenant-aware data source, Tenant and Non-Tenant DB configs, etc.) can be on a separate library and shared across different micro-services. Hence, developers can worry less about tenant data isolation while developing application-level business logic.

Cons

• Since not all relational databases support RLS yet, there will be a DB lock-in, as explained in previous sections.

I hope you all have fun with RLS! :-)

References

1. Multi-tenant data isolation with PostgreSQL Row Level Security
2. Architectural approaches for storage and data in multitenant solutions