CODEWITHSUNDEEP
postgresqlspring-bootmulti-tenantin-memorydatabase-security
Subhajit
Subhajit

Reputation: 894

Multitenancy with Database connection using credentials to achieve pure isolation and increases security

Using "single database multi-schema" strategy of multitenancy I want to make purely isolated connections to database for each tenant.

I thought to reach it in small phases :

  1. Phase 1 : Created multiple schema in a database and per request based on the tenant identifier performed the datasource switching. Internally I had separate schema for each tenant, and based on the tenant identifier I was executing the sql queries on a particular schema.
getConnection(tenantIdentifier){
    set search_path to "tenantIdentifier"; 
    execute sql queries;
}
  1. Phase 2 : Earlier the connection was created by superuser, basically it was not purely isolated as the user has the permission to access different schema. So I want to create a credential based approach to make the connection purely isolated for which I need to create users with login credentials with granted permissions to access a particular schema only.

To address this either :

  1. Approach 1: I can create a map of tenant and user, using which I can create database connection for each request .

Con - For each request if we want to create a datasource using the user credentials it will take more time to access the DB, hence the next approach.

  1. Approach 2 : I can create an in-memory map of datasources created from using the tenant and user credentials. Everytime a new user comes we can create the datasource object once and keep it in memory for future requests.

Con : With the increase in number of tenants the in-memory list will also increase.

Is there any better solution than this to have a connection for each tenant or any improvement in approach ? Any guidance will help me.

Upvotes: 0

Views: 177

Answers (0)

Related Questions