Best strategy for a single, shared tenant-aware database and centrally-controlled data access?

[willnationsdev]

Related #7455

TLDR / Summary

I am taking an old homebrew proprietary multitenant CMS with a single database per environment and porting it to OC CMS. In the original, tenants' data is stored, tracked, managed, and controlled from a central admin UI. Each tenant sees users/content/etc. as though they were their own data. In reality, creating something stores it centrally and maps it as coming from the tenant (the central system has final authority). Each tenant/user combo then queries the central system that responds by dictating what data should be exposed/presented to the tenant/user. What is the safest and most maintainable way of doing this in OC CMS? I'm thinking it would involve wrapping the existing ISession instance with a custom class that controls how queries are done. Is this safe? If so, how likely is it that core WILL NOT break it with a future update? Does this warrant an Issue to modify core in any way to keep such a strategy secure/safe? (Questions re-iterated at bottom)

Problems

As much as possible, I want to minimize customization of the OrchardCMS/OrchardCore codebase's default behavior (to avoid needing to maintain alternate code). Thus far, I've been following the approach recommended in #7455 where each tenant has its own YesSql collection, and data is remotely accessed across tenants using ShellHost/ShellScope. This has presented a number of problems though (listed below).

The common thread is that this approach results in additional code complexity, long-term maintenance, inefficient scaling, and a bad developer experience. We are now looking into single-database alternatives that preserve the flexible configuration allowed for each OC tenant (features, theme, sub-routed admin, etc.).

Tenant-Scoped Data

Users

I use AzureAD for auth, but each user has to link it to a local user in each tenant. When moving from one tenant to another, users have to log in each time rather than already being authenticated. I'd like the user to already be authenticated everywhere at once and let authorization handle tenant-scoping of the UI (e.g. what users are displayed as part of a tenant, etc.).

Content

If a tenant marks that content is allowed to be shared/visible (read-only), I'd either have to constantly sync data between tenants' tables with BackgroundTasks/IContentManager.ImportAsync or use faux reference items that need their own custom logic and can become invalid "null" holes. Both strategies introduce unnecessary complexity and points of failure.

Other

For everything else, we will likely run into similar problems as Content, but without the built-in utility to migrate/import across YesSql containers (like WorkflowTypes). Would need to devise custom solutions for each type to individually choose data should be exposed and to where.

Omni-Tenant Operations

I'm worried about performance and complexity at scale when dealing with omni-tenant operations. If 1) I have hundreds of tenants, 2) I need to do many regular/recurring operations with all of them, and 3) each operation requires multiple SQL queries, then I will need to repeat all of these queries/operations in a loop through all tenants and aggregate/resolve the data. Even if I manually compose a single SQL query instead, I'd likely be joining together hundreds or thousands of tables. It would be much simpler to just grab all the data from a single source directly and do what's needed without any additional work.

An alternative we thought of is to use a "shared" tenant, push copies of data there, and then use that for aggregate tasks, but it has issues of its own (can't "write" to the central data for en masse changes, adds unnecessary points of failure with desyncs, more code complexity, and lots of custom code to maintain, etc.)

DB Complexity

Even with the few experimental tenants that have been made so far, looking at the database shows that it's become a gigantic mess of tables. Things are hard to find, data is spread out everywhere, etc. The old CMS database is much simpler, streamlined, and easier to maintain.

Possible Solution

What if I replace the service registration for IStore with a derived class that overrides the CreateSession() method so that it returns a custom implementation, e.g. a CentralizedSession?

Concept

The new session internally creates an instance of YesSql.Session and lets it do most of the work as normal. But, when creating IQuery instances via Query/QueryIndex, it could pre-emptively start with a ReduceIndex that groups any given document by tenant name (using the ShellScope.Current.ShellContext.Settings.Name) and then call the intended Query/QueryIndex before returning it.

Would also need to ensure that tenants don't activate features without first running their DataMigrations on the Default tenant, but that doesn't seem dangerous or too difficult.

Risks

The most dangerous/hacky aspect of this solution is the fact that OC modules have the potential to directly execute SQL statements against their respective databases (which could then violate the central session's control over things).

The only example of this I can find though in the OC source code is the IndexingTaskManager implementation. From what I can tell, it just keeps track of the latest "update"/"delete" status of ContentItemIds in the system. Seems like it wouldn't be harmful to this setup, but these index tasks are used by the LuceneIndexingService which could have other repercussions. It seems like a search engine API for data stored in ContentItems (I think?), so there might be a risk of one tenant's search accidentally pulling up links to content in other tenants inadvertently. Not sure yet.

Still, that doesn't mean that other OC features in the future won't rely on direct SQL statements.

Questions

1. Do you foresee any other risks with this approach and do you think it is safe to move forward this way?
2. Is there any way to guarantee such an approach could work long-term? For example, are there existing similar use cases that the OC CMS maintainers are making sure not to compromise with future updates?
3. If there is a concrete way this design intent could be integrated into the configuration of a SaaS OC CMS app in the core repo, then should an Issue be opened?

[Skrypt]

I personally think that each tenants should use their own table even if it's becoming "harder to manage" over time.
It's always harder to isolate a specific tenant data when you are using a single table for multiple tenants.
I think you can use the OpenId module to authenticate every tenants on a single main tenant. Though, the same role permissions will be applied on every tenants. As for Lucene we don't recommend using it in a SaaS scenario, you should look at ElasticSearch instead (scalable), we have a PR which is a work in progress that already works but needs some adjustments here and there.

An other approach you could consider is to use the Content API of each of these tenants and make cross API tenant calls. If you are using OpenId I believe it should work. It would then remove the need to move/sync data across different tenants.

Also of course using a Redis cache might be usefull in a SaaS context.

[willnationsdev]

I personally think that each tenants should use their own table even if it's becoming "harder to manage" over time.
It's always harder to isolate a specific tenant data when you are using a single table for multiple tenants.

That's a fair concern. It seems odd to me though that the recommended way of sharing/aggregating data and operations throughout tenants is to apply constant synchronization/importing/migration efforts of all of the different tenants' databases, etc.

A safer approach might be if we were to selectively store specific types of documents (e.g. Users, certain types of ContentItems, etc.) in a single "Shared" headless tenant database, but then keep all other documents in tenant-specific tables. However, to avoid duplicating/syncing the data constantly, we'd still be overriding the ISession implementation to redirect things with centralized control. We'd just be doing it for specific subsets of tenants' Documents rather than for all documents.

Would that be something Orchard Core intentionally supports long-term? Or is any attempt to override ISession a no-go for Orchard Core users that don't want to break compatibility with the main repository?

I think you can use the OpenId module to authenticate every tenants on a single main tenant. Though, the same role permissions will be applied on every tenants.

We already use AzureAD for centralized authentication and have a separate role management app / database for defining combinations of users/sites/roles between different environments (used for our existing sites/platforms). The problem we have with users is that every time a user logs into a tenant, there is a tenant-specific local user that has to be externally authenticated to AzureAD and all their role info is duplicated to the tenant. Every time a user moves from one tenant to another (which will happen), they have to 1) ensure they have a user on that tenant, and 2) re-log-in to that tenant. This creates a very bad user experience overall. Instead, we want our OC apps and/or tenants to store and track all Users/Roles in a singular tenant database that can just run background tasks to import from the other campus database.

My concern is only whether attempting to do this is something that can be assured from OC maintainers or if we will need to acknowledge that every update of OC will require us to do a thorough review of the OC source code to see if any security leaks occur (i.e. are core OCCMS modules running direct SQL queries against the database, or are there new problems with ISession replacement).

An other approach you could consider is to use the Content API of each of these tenants and make cross API tenant calls.

If I understand correctly, isn't this the synchronization strategy? You'd have to wait until the local tenant actually attempts to save the ContentItem and trigger the IContentHandler methods. We'd then implement a handler that additionally saves the content to a different tenant database. That's exactly the kind of synchronization I'd like to avoid where we have multiple copies of the same ContentItem/YesSql.Document in different places.

If you are using OpenId I believe it should work. It would then remove the need to move/sync data across different tenants.

I don't really understand this bit. Isn't OpenId just a means of having users authenticate? I thought the OpenId server module is like setting up our own "AzureAD"-like authentication service and then other tenants with OpenId client modules could validate themselves against the tenant with the OpenId server module. I wouldn't think we need this if we already have external authentication.

Also of course using a Redis cache might be usefull in a SaaS context.

Didn't consider this, but then we'd lose out on having indexes (MapIndex/ReduceIndex) that keep track of info about anything shared between tenants, right? Unless we built a completely custom third-party API around Redis that mimics YesSql features.

[willnationsdev]

Just realized a significant issue with the approach of using a centralized ISession for only targeted Documents that limits its relevance. If you attempt to redirect the storage of Documents based on information within the Content portion of the saved Document, then you won't be able to reliably return data isolated to a particular tenant whenever a user queries an index because the index information for the saved object(s) will be in a different database, and you can't know which subset of the index table the user needs until after you've already returned the IQuery and they use a separate Where statement or similar (nor can you decipher it since it'll be straight-up SQL or a C# expression).

So, even with my hybrid approach, you wouldn't be able to isolate particular ContentTypes or something. You end up doing all-or-nothing redirection based on the requested Index type or the requested Document Type. For example, I could redirect Users and the RolesDocument along with the various User/Role indexes, but I wouldn't be able to only redirect the ContentItemIndex queries related to a particular ContentType, etc. since that would be unsafe.

[willnationsdev]

I'm going to mark Skrypt's answer correct since they were the most helpful (though, lots of their advice is also present in the other discussion answers).

For people who see this later:

• Summary: Subverting the natural behavior of Store/Session to try and keep everything in a single database is risky and violates the conventions of the core repository, so it would likely lead to a need to maintain a fork.
• Concern 1: You cannot reasonably operate any feature that performs direct SQL statements against the database (things like the Indexing module), and core isn't going to be concerned with making such changes regularly even if it means breaking compatibility with such a system since it's all custom stuff anyway (expected).
• Concern 2: If you chose, for security purposes, to only store certain documents in certain databases, you'd have to keep in mind that users who query an Index table will be able to fetch data before they've actually specified which subset of those documents they care about. This means that anyone overriding Session behavior can't reliably use that sort of information to redirect data access calls. You are essentially limited to whatever data exists outside of the saved document's Content column, i.e. its Id, Type, Version, and any other unrelated constant data accessible statically or via DI within a tenant pipeline (e.g. ShellHost info).
  For example, it is impossible to safely store some ContentItems of different ContentTypes in different places via data access redirection; any given tenant could query the ContentItemIndex and get back an IQuery instance before they've actually given a Where statement to constrain the ContentType they care about (so the Session doesn't even know yet). Furthermore, even if a Where statement was given, it would be straight SQL or a C# lambda expression, neither of which can be easily parsed and broken down without unreliable/hacky/complex code.
• Concern 3: Many documents are expected to be singletons within the database based on their data type (basically any IDocument-implementing class). Difficult to simulate these being stored separately while they are in a single database since Session normally just grabs whatever the first instance there is and reads/overwrites it.
• Recommendation: Suck it up and deal with tenant scopes. Avoid centralized operation of the data access as much as possible since it is largely unsafe to tinker with it.
  If mass, aggregated reads are necessary, then you'll need to have a custom management admin in the Default tenant you write that probably uses UNION ALL SQL statements to merge together query results from all of the different tenants.
  For any sort of mass update/insert or YesSql session save, you'd have to just manually loop through all available tenant scopes, fetch their scoped session instance, and do what you need to do (linear time). Due to YesSql's index system, there isn't really any other suitable way of handling it, unless you fall into the special case of your updated document not having indexes associated with it, in which case, it might be possible to update, but not insert, globally with a mass SQL statement, procedurally generated by getting all of the tenant's table prefixes, similar to the mass aggregated read operations.
• Post Remarks: It might be possible to centralize just the Users and RolesDocument if the Session override exclusively redirected those documents. This then opens the possibility of having a single login for the entire Orchard instance, assuming you can get browser cookies generated in a tenant-agnostic way.

I really appreciate everyone's help!

[ns8482e]

OrchardCore multi-tenant is designed to be an isolated container per each tenant. The one shared services are those that were added to host. I would suggest to design your orchard solution that go with the orchard core concept and not against it.

You could create a module that could perform operations for all tenants that can be enabled/disabled in host tenant. You can also use Commands

[Skrypt]

When I say using the Content API I meant for displaying data across tenant. It would keep the actual data in every tenant where they've been created. Then, from a REST API call you can retrieve data from a tenant in a different tenant. This can be done securely by using the OpenAPI module.

You will need to also set the CORS policies to allow this if you are using a different domain per tenant. Though in the scenario where your tenants are only separated by url prefixes on the same domain then it's not an issue. This is mainly why each tenants have their own REST, GraphQL API endpoints and so on.

I'm not sure about the ISession, I'd need to investigate further. Though, basically it is a Dapper ISession so it allows doing pretty much what you want. Though, everything customized is not supported by OC so yes it will surely eventually require more maintenance. This is mainly why we say, use OC the way it's designed to be used. Else, if some feature can be added in OC to make your work easier let's see what would be missing. But we can't refactor entirely how it's designed.

I'm thinking that what you probably want is a main application that allows to manage every tenants and that can execute SQL Queries on each of them. It could be a module activated in the main tenant which would add services around the Tenant features of OC. Something like a OC SaaS management tool. This could intercept Content events from each tenants and apply migration of data across tenants. It's a cross tenant data replication strategy tool that you need to develop if you want to go that path.

[willnationsdev]

@Skrypt

When I say using the Content API I meant for displaying data across tenant. It would keep the actual data in every tenant where they've been created.

From the sound of it, for the vast majority of Documents, this is an absolute necessity. Too many risks otherwise. Especially for anything subtypes (like ContentItem) or singleton documents (like IDocument instances).

Then, from a REST API call you can retrieve data from a tenant in a different tenant. This can be done securely by using the OpenAPI module.
Though in the scenario where your tenants are only separated by url prefixes on the same domain then it's not an issue.

Yeah, since we're all going off the same database, I can just open ShellScopes and fetch data access services like ISession as needed.

[the ISession] allows doing pretty much what you want.

That was the security/maintenance concern with hijacking it. Indexing/Lucene was the main concern since it does direct SQL statements, but nothing else in core really appears to do that, so if Lucene isn't appropriate for SaaS anyway, then it's a "this is kinda safe?" concept. The core has a convention of usually not having tenants violate the "work off of ISession" rule.

Though, everything customized is not supported by OC so yes it will surely eventually require more maintenance.

Right. I want to build on top of OC as much as possible without changing it if I can help it. But not sure if I'll be able to help it when it comes to the users/roles, even if I keep content/workflows/etc. isolated.

Else, if some feature can be added in OC to make your work easier let's see what would be missing. But we can't refactor entirely how it's designed.

Well, our use cases are...

1. have Users/Roles be managed/drawn from one tenant database (so that users don't have to log in repeatedly in each tenant).
2. have Content owned and exposed by tenant, observed/displayed by other tenants, rescindable by the original tenant, and ultimately overrideable from the Default tenant.

As far as I can tell, core already supports the ability to implement all of these things with custom code if developers are fine with tracking/synchronizing references across database collections and dynamically opening up N tenants' shells to use their services for N queries across the ecosystem. But if you do anything that is inherently operating on a macroscopic scale, then you have to have all kinds of details about what data is centralized, under what conditions, and how it is hooked back up to relevant contexts. And that is extremely implementation-specific that core can't define a singular solution for, so it likely just isn't appropriate for core.

I'm thinking that what you probably want is a main application that allows to manage every tenants and that can execute SQL Queries on each of them. It could be a module activated in the main tenant which would add services around the Tenant features of OC. Something like a OC SaaS management tool.

Yes, that's what I'm thinking I might have to do.

1. Replace ISession and make it so that User and RolesDocument are kept in the Default tenant. For everything else, typical behavior (unless you can think of another way to not require constant logging in?). Ensure that all tenants are using this Session.
2. Save ContentItems locally and create any necessary indexes for the local tenant. Ensure that any readable "mass operation" data is part of the indexes.
3. For macro operations...
   • Allow replacement ISession, a set of extension methods on ISession, or both to abstract/simplify mass operations across all shell contexts.
   • To read data, get all table prefixes and compose a custom SQL statement that UNION ALLs each of the index tables needed, keeping track of the originating tenant for each row along the way. Then filter down based on relevant permissions.
   • To write data...
     • worst-case scenario (seems to be the only option), have to apply an Action to a ShellScope and just iterate over all scopes to invoke it where the Action does some set of database changes with services, etc. (this just seems slow and not scalable).
     • ideally, use ISession to compose a query, convert to SQL, and somehow generate a single large query that can apply to all table prefixed Document tables in a single execution. Except that wouldn't trigger YesSql indexes to update, so you can't do that.

That strategy seems like it might be safe long-term since the only thing that significantly deviates from core is the User/RolesDocument centralization which is a comparatively minimal change from completely centralizing everything.

[Skrypt]

have Users/Roles be managed/drawn from one tenant database (so that users don't have to log in repeatedly in each tenant).

I think the issue comes from the fact that we create a different cookie name per tenant. Else, this should definitely work. Need to test this.

[ns8482e]

@willnationsdev Store and Session are tenant aware and are defined in tenant pipeline not in Host Services.

[willnationsdev]

@ns8482e Sorry if it was unclear, but by tenant-aware, I meant having a single Session that is aware of the context of all tenants. The way it currently works, every creation of the tenant pipeline instantiates a Store that is scoped to a particular tenant's TablePrefix. My original goal was to see if it was at all safe/possible to basically have everything in one database (so, I suppose, no TablePrefix basically), but track the context/usage of all normal operations' originating tenant with the ability to override the session's behavior with custom logic depending on what operation is being performed, for which tenant, and by which user, etc.

Skrypt has made it pretty clear though that doing anything like that essentially amounts to a gigantic, unreliable hack that will result in me needing to do a ton of extra maintenance since core could pretty readily/frequently violate the requirements of such an arrangement and I'd need to maintain a fork. So, not an option.