Dfyjkt

I have a django app has clients and multiple users that are linked to those clients, and I want to migrate the system to hierarchical multi-tenant architecture.

Hierarchical Part

Clients can include other clients recursively. For instance, Client A includes Client B and Client C. If a user Client A logs in the system, user will see Client B and Client C's data. If a user of Client B logs in the system, user will see only Client B's data.

Multi-Tenant Part

I want to store all clients' data in separate schemas. But there is some data that is not relevant to clients, so I want to store that data in "public" schema.

When I research Building Multi Tenant Applications with Django, I saw this part:

def set_tenant_schema_for_request(request):
 schema = tenant_schema_from_request(request)
 with connection.cursor() as cursor:
 cursor.execute(f"SET search_path to schema")

However, to apply my hierarchical example that I've mentioned above, I must reach multiple schemas at the same time. Can I do that or are there any other ways to implement my architecture?

You can list multiple schemas in the PostgreSQL search path. If the same table appears in several schemas only rows from the first schema in the search path will be returned. There's no automatic combining of rows from tables in different schemas. So that's probably not what you want.

You could rearchitect your application so that every query selects data from tables in different schemas and then use union clauses to join those results together. While that would provide the right results, it should be clear that structuring all of your queries this way will complicate the application greatly and makes it also not a good plan.

If you want the ability to see the data from several clients at once, a multi-tenant architecture with isolated schemas is not the right way to go. Having eliminated the alternatives, I think only one approach remains. You have to use shared schema multi-tenancy with rows from different clients in the same tables.

Having a shared schema means that there's less separation of data from different clients, but it provides a simple way to show data from multiple clients at once in cases where that's what's required. The normal approach to implementation is to add terms to the where clauses of your queries to ensure that only data that should be visible for the logged-in user is returned.

Another possibility you might consider would be to use row-level security within the database. With that approach each client would have their own Postgres user account with a policy that restricts access to only the rows that are applicable to them. That pushes some responsibility for ensuring correct security from the application layer to the database layer, which has pros and cons. A benefit is that security needs to be implemented only once at the database, as opposed to throughout the data access parts of the application. A possible drawback is that it may be more work for the database to do.

https://www.postgresql.org/docs/11/ddl-rowsecurity.html