OrchardCore: Multi-tenancy with different schema write in wrong schema for indexes

Describe the bug

We discovered a bug when using a multi-tenant version of OrchardCore with the same database for each tenant but different schema. For now, I don’t know if the issue is only on postgresql or on other kind of database too.

To Reproduce

Steps to reproduce the behavior:

Create a SaaS tenant (so it will fall on default schema, public for us because we used a postgresql database)
Create a second tenant, let’s say tenant1, on the same database but in the schema “tenant1”
Create a third tenant, let’s say tenant2, on the same database but in the schema “tenant2”
Try to create a content item on tenant1 and on tenant2. Depend on the last launched tenant, one of them will not act as wanted.
The content item will be successfully created on one of the tenant (let’s say tenant1).
On the other one (let’s say tenant2), an error will occurs as the content item will be created in tenant2.Document but the index will fail as it try to insert it in tenant1’s indexes. For example, one of the error we got : PostgresException: 23503: insert or update on table "ContentItemIndex" violates foreign key constraint "fk_contentitemindex" DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
We discovered that, if we force the tenant that failed to create the content item to reload (by activating a new feature for exemple), we will not face the issue anymore on this tenant but now, the issue will be it on the other tenant.

We think that, somewhere, there is a mismatch between the schema, maybe with “OrchardCore.Data.IndexServiceCollectionExtensions.AddIndexProvider” but we’re not sure that’s the rootcause for now.

Expected behavior

Each tenant successfully save the Content Item in Document and in Indexes in the correct schema.

Screenshots

If applicable, add screenshots to help explain your problem.

About this issue

Original URL
State: closed
Created a year ago
Comments: 18 (16 by maintainers)

Most upvoted comments

With Sql Server but by using different schemas I could repro the same kind of issues.

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_ContentItemIndex”. The conflict occurred in database “oc-dev”, table “tenant1.Document”, column ‘Id’. The INSERT statement conflicted with the FOREIGN KEY constraint “FK_AutoroutePartIndex”. The conflict occurred in database “oc-dev”, table “tenant1.Document”, column ‘Id’. The statement has been terminated. The statement has been terminated.

I will look at it this night

jtkech on Jul 28, 2023

The solution would be to use

        var key = new CompoundKey(
            dialect.Name,
            type.FullName,
            _store.Configuration.Schema,
            _store.Configuration.TablePrefix,
            Collection);

By allowing new CompoundKey() to accept 5 parameters.

jtkech on Aug 2, 2023