Our system is fundamentally a multi-tenant setup, where each client maintains a separate database with its own data and users. The authentication system on login issues an authentication token which is returned to the user on login; the login request contains a unique client code identifying which tenant the user is attempting to access as well as the user's own username and password information. The auth system can use that information to validate that
- The client is a valid client code with an existing database to access
- The user is a valid user within that database
and then issue the auth token in the response.
The problem comes in with the fact that we have another set of users, administrative support users, who can access all databases. To implement this behaviour we've identified two potential ways to implement this behaviour: one, we have an additional database containing only support users, and when the user tries to log in we check that additional database's user list as part of authentication; or two, we add support users to the internal user lists of each unique database and somehow try and keep that in sync.
Normally the first option, the second database, would be my go-to solution here; however, because our database solution is Postgres, cross-database queries aren't really a tool we have available, instead having to maintain two separate connections for each authentication request.
Are there any other potential ways to implement this which we're missing? If not, are there any potential drawbacks/performance concerns to this methodology to be aware of?