We plan to have several databases or schemas for our clients, one per company. And a user may have to connect to different databases if he works for several companies (e.g some sales).
We are currently thinking about what would be the best solution : one physical database per client, or one schema per client in the same database. Both have advantages and drawbacks.
In the one database per client case, would it be possible to select the database to connect to in the Login screen ? I have looked through the code and it seems that being connected to a database at this point is mandatory. Which yields the need of having some kind of intermediate database or external mechanism to login first, before login to the actual database.
This is, among other reasons, why I prefer having one database and different schemas. In that case CUBA schema needs to be changed dynamically. I know from the doc that you can define it statically by configuration (Connecting to a Non-Default Database Schema - CUBA Platform. Developer’s Manual), but would it be possible to change it dynamically ?
Unfortunately, I’m not aware of how people use this concept (if they really do). So please let us know if you have any practical results in this direction.
I read that, but it adds a lot of complexity and there are too many limitations. Worse, tenant db are then strongly dependent on the central database (sequences, dynamic attributes, entity log - not options in our case…) which defeats IMHO the purpose of isolating tenant in their database.
Which is why we prefer a solution of routing once and once only, at login time. Then the user is connected to a standard CUBA database with no drawbacks at all.
That lets the routing problem to solve at login time, whatever the solution you need a login tiers/module, a CUBA app or not. I was thinking about a solution along these lines :
central schema/db where some process replicates credentials from other schemas/db, mapping user to tenant along the way
user connects to the CUBA app in central schema/db
user submits logging, app validates against replicated credentials
if ok CUBA resets its connection to the correct tenand schema/db
There is still a central schema/db needed like in the proposed solution but the complexity and the risk is largely reduced, and restricted to login time. However this needs CUBA to be able to reset its main connection without restarting the container, I do not measure the difficulty of that.
A variation of this solution could use routing, instead of switching main connection into a single CUBA app, we could have 2 apps : one for login and one for all tenants. Once the login app has validated the login against the tenant app (could be a REST call for instance), it routes the user to the tenant app url with a session ID (no additional login) and with an url parameter defining the tenant schema/db. In that case the central schema only needs to know which user for which tenant, no replicated credentials. That also needs CUBA app initial controller to use this parameter to initialise its database connection.
That would be nice if CUBA platform supports something like this login model at some point in time, as this is a recurrent problem for SaaS applications.
I strongly agree with you.
Although the solution Konstantin and the CUBA team provided works fine, it has some limitations which make the decision to use it or not in a long-term project difficult. The main limitations are, as you mentioned, the dependences on the main db, which results in not using CUBA features to it’s full capabilities: user roles and security, custom screen filters, reports, etc., which all need custom implementation classes (every internal table like sec_filter needs an additional field for the tenant-db and a custom implementation class which takes this into account), so that it’s quite complicated to be implemented.
I hope for a more practical solution (like the one you mentioned above) because more and more users require this type of applications.
Hi
I want to add that, at least IMHO, the last solution proposed by Michael is the way to go.
And I want to add that this should be implemented in a “standard” and future-proof way.
Such way could involve delegating the actual login to a central IDP, supporting OpenID Connect or SAML (my preference goes to the former, as it is the direction where most of the modern IDPs are going nowadays).
Once the IDP has authenticated the user centrally, and obtained claims about the user, it will redirect the user using a standard and secure authentication redirect flow (one documented in OpenID connect specs), passing the encrypted SSO token (a JWT is the preferred option speaking of OpenID connect).
The target app/tenant can then maintain working data inside its private container, but general info about user identities must be maintained on the IDP itself (only the base infos, like google and others do in their SSO mechanism).
If one wants to let edit users inside a tenant/app, without leaving the main interface and opening a separate window on the IDP, we could use REST apis and OAuth 2 to interact with the IDP, but this opens up some problems regarding distributed transactions if the interaction with the IDP involves more than one API call.
In the end, I’m against custom-made solutions when security is involved, only proven and certified solutions works when dealing with corporate apps.
EDIT: obviously, the tenant configuration is maintained centrally on the IDP, so that it can decide dynamically what target URL to call, and it will encrypt the needed info in the token payload. Like Michael said, the receiver controller will extract the payload and use that info to setup the container dynamically)
I was not able to test redirection between both applications as described in OP, the git source is incomplete.
This is an interesting step towards SaaS model, but with current CUBA limitations 1 application = 1 port = 1 database. So if you want a true database per tenant, you have to setup 1 app/port/database per tenant, I think nobody will do that.
And there are still the current limitations of CUBA db-per-tenant approach discussed in this thread. I really think that additional store features of CUBA (which is great) is more suited for integrating legacy databases for instance.
This is close though : currently CUBA IdpServiceBean depends on an already open database connection to the main store, notably through its dependency on LoginWorker. We simply need a way to pass the database parameter to this service, have it open the connection and proceed to login.
Even more simple (but probably complex for CUBA) would be to be able to change CUBA connection to its main store through an API at any point in time. Then we simply need a hook into the login process for our code to get the database from the user, connect CUBA to it, then proceed with login.
