Spring Boot Multitenant - Access data from multiple schema at the same time or per http request using spring boot

Question

We are working on the multitenant application in spring boot. We have created different schema for different customer and we also have public scheme for super admin. We have same set of tables both in public and customer scheme. In customer schema, tables are inherited from public schema. Database is postgres. When customer login, all data is read from customer schema. When super admin login, all data is read from public schema.

The above functionality is working fine. Now we have to introduce new role called customer admin. He will have access to different customer data. For example he can access customer1 and customer2 data. How to achieve this functionality as we can set only one schema at a time using spring/hibernate. This role has to get data from multiple schema at the same time or per http request.

Any help would be highly appreciated.

Thanks in advance.

Answer 1

You can have a dropdown of the customers that the user can be able to access. When the user chooses a customer from the dropdown (Ex: Customer1), then you will have the customerId (TenantId) to setup the context and get the data using that particular datasource. this can be used to render the list of the users from the API / to the UI.