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
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.