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 :
- 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;
}
- 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 :
- 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.
- 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.
