Multi Tenancy with Multiple Databases

I'm struggling to overcome some multi-tenancy problems. I've implemented multi-tenancy applications before, but haven't been 100% happy with the process. With this application, I wanted to get some second opinions on the best way to approach it.

With a single database implementation, it's relatively straight forward. All clients run on one database that's shared. Each 'account' will be assigned a tenant_id and each user created within that 'account' will have a relationship bound by the tenant_id.

My only concern with this approach is overly developing an authorisation layer and future scalability. Which leads me to multiple databases.

So, the ideal workflow:

Upon account creation, a separate client database is created and all data is contained within. There are a few approaches to identifying that account; either by a subdomain or an identifier in the URL:

Route::get('accounts/{account_id}/dashboard');

My concern with this; what would be the best approach for user authentication? Ideally, you'd want a user to login through http://mydomain.com and let the application work out a redirect to a subdomain or create a URL identifier (as shown above). With the multiple database approach, this wouldn't be as straight forward. The only way I could think of doing it would be to have a separate user database which would store all user accounts. When a user logs in and is authenticated, it would switch to the correct DB and redirect to their domain (let's say http://client1.mydomain.com).

This doesn’t sound like an ideal solution as there would be a lot of DB connection switching and user relationships would be rather difficult.

How do other multi client database applications overcome this issue? Is a multi client database even the way to go? (Ignoring application success or use - purely from a development practice).

Any advice on this topic would be greatly appreciated.

Thanks for reading.

stefanbauer

10 years ago

Level 26

Why not authenticating a user already on the account database (directly on the subdomain)? In our SaaS Solution we did it that way.. Later we migrated only to one global login domain without any subdomains.

Personally, i would go with subdomain solution. If a user creates a new account, create the database, create the subdomain (the real nginx/apache subdomain, create the real vhost and enter there (in the config) the database credentials as environment). I mean automatically :-) ... This is the only way how it is secure in my eyes, because the database credentials should never touch your database or codebase.

1 like

mattbartlett

10 years ago

Level 1

Hi @stefanbauer, thanks for your response.

In a live application I've already developed, direct authentication on the subdomain is the method I went with. I never knew about setting VHost env variables in the config file though, I must try that out.

However, the problem remains for user authentication. I'm porting a web application to iOS (using http://ionicframework.com/ - looks pretty neat), and want a single authentication point. From a mobile apps perspective, you'd ideally want one login portal, and then the app can seamlessly direct the authenticated user to the correct database behind the scenes.

Any suggestions on how this could be done would be appreciated.

gregac

6 years ago

Level 1

@mattbartlett We are facing the same issue. We have multiple-tenancy app. Each client can login on their own sub domain.

But now we are building iOS/Android app. And we want only one app / login that works for all clients.

Have you maybe found any solution to that since your last post?

Please or to participate in this conversation.