Multi Tenant applications using PostgreSQL Row Level Security

 PostgreSQL supports Row Level Security (RLS) so that applications can restrict who can see which records in a table either based in DB user name or based on Connection session variable. This feature can be used to implement discriminator based multi tenant where records for multiple customers are present in single table. This blog explains steps for implementation https://www.crunchydata.com/blog/row-level-security-for-tenants-in-postgres

Advantage of this approach is that its easy to implement and day to day application development activities dont have to bother about breaking multi tenancy because code for applying discriminator is implemented at a framework that obtains connection from connection pool. Application developers dont have to modify individual SQL statement or even pass around tenant_id as a parameter in method calls.

Good thing about RLS is that RLS policy can be applied to specific Roles in Postgres and these roles should be used by application connections where multi tenancy needs to be applied. For application code where where multi tenancy should not be applied, use a role where RLS policy is not applied. eg. batch jobs that prunes history tables.

But RLS has a limitation and this comes from PostgreSQL inability to unset session variables. Since most applications uses connection pools to manage database connections, any session variables set on Postgres connection will remain and carry forward to next connection. So application framework code that releases connection should set RLS session variable to some invalid value.

Somehow I wasnt able to use JDBC PreparedStatement to set session variable. Only Statement worked.

Another peculiar feature is that by default RLS does not apply to owner of table. This behaviour can be changed by use of ENFORCE key word. 

Code to set session variable in Spring Boot 3.2 - https://gist.github.com/antopaul/d821816dee6f10a7dea91bb9c3e9b2e0