Question posted in 
I’m working on a project where we aim to have a separate database for each tenant. In our setup, there is a central database (and API) containing a "users" table that stores usernames and passwords for all users. Additionally, this central database includes a "tenants" table to track all tenants and a "tenants_users" table to associate users with specific tenants. Notably, a user can belong to multiple tenants.
My goal is to allow users to log in at auth.example.com and then choose the tenant they want to access. Each tenant has its own {tenant}.example.com domain and we manage sessions server-side with a samesite cookie at example.com to track the login state across tenant domains.
Furthermore, each tenant maintains a "members" table to store additional member information, with membership roles stored in this table for authorization management. Before a user is added to this table we utilize an invitation system that is managed by the central server.
Now, here comes the challenge: we need the "tenant_users" table to display a list of tenants to which the user belongs, facilitating easy switching between them. Simultaneously, we want to store the membership role in the "members" table of the tenant database. The issue arises when the admin of a tenant wants to remove a member; in that case we also have to remove the user from the "tenant_users" table in the central database. This seems cumbersome and susceptible to synchronization issues.
Additionally, we have a central admin where a superadmin can add/remove users to any tenant, requiring data synchronization from the central server to all tenant databases.
The central server offers a REST API with various endpoints enabling a tenant to add a member to the central "tenant_users" table on the central server. In this case the tenant server communicates directly with the central server. Currently, I’m implementing JWT with RS256, where the server retains the public key of each tenant. The tenant signs the data, which is then utilized to enact the necessary changes in the database.
For authorization of a request from a tenant, we perform a database query to ascertain the role of the logged-in user who initiated the request. The logged-in state is maintained on the server using Redis.
I’m uncertain if I’m overthinking this or if this is a genuine concern. Should I reconsider the architecture, or is there a better way to address these challenges? My main concern is around keeping the "user_tenants" and "members" data synchronized. I also imagine that using oauth2 for server-to-server communication is another option, however I’m not certain if the current approach with JWT and public/private keys is incorrect, mainly because I’m aware people are opinionated when it comes to JWT.
2
Answers
When using Single-Sign-On in your organization, you should let each tenant to manage its own user sessions. A user should be able to sign into different tenants at will so there is no need to set a session cookie at parent domain. If a user wants to sign into tenant Alice, he will directly visit alice.example.com and sign into it. The session cookies set by alice.example.com will not be shared with other subdomains so the user will be remain logged out from other tenants. Similarly, the user registration and termination should be individually handled by tenants.
You can use OIDC for it so that tenants can identify their users. Set up an OIDC Provider and each tenant should allow sign in or sign up with the configured OIDC Provider. This flexibility will allow tenants to configure OIDC tokens based on their own policies. For example, currently you are making each tenant to store user information in members table. This is not required as that information can be stored in an OIDC ID token. Every tenant will only have to maintain its own table of registered users so that only registered users can sign in. When removing the user, the tenant can just revoke OIDC tokens of that user and remove its entry from its own registered users table.
Which tenants a user is a member of can be viewed at OIDC Provider’s endpoint which lists connected web apps so you don’t need tenant_users table. To allow the superadmin to terminate a user across tenants, you can use OIDC Back-Channel Logout.
How To Add A New User Into SSO Org?
To add a new user, the superadmin can simply create a user account at OIDC Provider. But this only registers the user with the OIDC Provider, not with the tenants. How do you want to register the user with tenants depends on you.
You can use two methods:
What if a tenant wants to add a user whose account at OIDC Provider does not exist?
In that case, Sign up with {OIDC Provider} will first ask the user to register with the provider before being redirected back to the tenant. If you don’t allow user registration like that, the tenant should send a request to superadmin to create a user account at OIDC Provider who will then send a follow-up requests to tenants to create an entry of the new user in their registered users table.
In the Microsoft Azure Enterprise Scale Architecture, it almost the same architecture, you have one identify hub which has all the identity resources, and all landing zones only have a connection to that identify hub. There is also a network hub with Azure Firewall Premium and the traffic of each landing zone goes through that Azure Firewall Premium which does TLS inspection and IDPS.
Here is a visio file
https://raw.githubusercontent.com/microsoft/CloudAdoptionFramework/master/ready/enterprise-scale-architecture.vsdx