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.