I'm creating a web application (in PHP and PostreSQL) that will be used by organisations and their staff and I want to use multiple schemas to support multi-tenancy. There will be one database and I want all data related to organisation A to be stored in a schema named A and all data for organisation B to be stored in schema named B etc..
What's confusing me is how to handle user logins. Let's say Amy is a member of Organisation A and Ben is a member of Organisation B. When Amy goes to example.com to access my web application she's presented with a login form, which asks for her username and password. Now, Amy's username and password is stored in the users table of schema A but how does the web application know where to look to authenticate her?
I've never used multiple schemas in a web application before so in the past I would create a fixed username, i.e. webapp for the application, which would log in and check the credentials supplied by Amy against the data stored in the public.users table (where public is the default schema created by PostgreSQL) but with the design I'm thinking of using, there is no way to tell which schema Amy belongs to and as a result, it wouldn't be easy to find the correct users table to authenticate her.
One approach would be to stop using a users table for authentication and create a role with the LOGIN privilege for the database for every user, then only give that user permission to interact with the appropriate schema. That's a solution I've seen but not one I really want to use. I assume there's a better way but I just haven't been able to find it.
So, to summarise, all I want is for users to be able to log in to my app via the web interface and be restricted to the schema of the organisation they belong to. What's the best way to accomplish this, bearing in mind I intend to have several organisations and users in the long run.
All help is appreciated. Thanks in advance.
Best Answer
Your options are:
Use roles, and
SET search_pathon each role so it looks in the appropriate organization's schema. While in many ways the cleanest approach from a database perspective, this is a pain when giving orgs the right to manage their own users.Require users to specify their organisation at login time, probably with user@domain style logins, or using mycompany.myhostedapp.com style URLs. Then look up the org by @domain suffix before authing the user against that org. That's probably what I'd do.
Create a view that's a union of all company user tables and look users up in this. Likely to be a performance nightmare, and how will you handle duplicates?
Create a parent table for all user tables, make all the user tables inherit it, and query the parent table. Just a slightly more efficient version of the view approach, not recommended.
I strongly suggest an authentication design that carries the company identity of the user along with their login request.