3

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.

Improve this question

1 Answer 1

Reset to default
3

Your options are:

  • Use roles, and SET search_path on 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.

Improve this answer
7
  • Thanks for your answer. Your second bullet point was an idea I'd thought about but to complicate things further, there are a few "public" schemas that I'd want some staff (say admins) from all organisations to be able to access and other staff not to. Does this requirement mean I'd have to go with the "role for every user" approach that I wanted to avoid or is there a better solution?
    – ste
    Sep 9, 2014 at 1:13
  • @ste You should put those schemas on the search_path for all users and use permissions to control who can actually read the tables. That's why search_path is a path, not some sort of current_schema setting - so you can have multiple schemas in it. I'd have one role per organisation and, after my app authed the user, I'd SET ROLE or SET SESSION AUTHORIZATION to "become" that role, which would have permissions GRANTed only to its schema and would have a default search_path set by ALTER USER ... SET search_path.
    – Craig Ringer
    Sep 9, 2014 at 1:20
  • @ste For "admin" users I'd create a second role for each organisation that inherits the organisation role and a general purpose "admin" role that has access to the shared tables GRANTed to it. This user would have the search_path set to include the organisation schema and the admin schema(s).
    – Craig Ringer
    Sep 9, 2014 at 1:21
  • @ste If that's too complicated, just give every organisation rights to the admin schema. Don't run GRANTs for each new org user though, create a role 'admin' and GRANT that role to each new org user. Then manage who can do what to the shared admin schemas at the application level.
    – Craig Ringer
    Sep 9, 2014 at 1:22
  • The one thing that's confusing me is how the app will know if a particular user is an admin or not? One way I can think of is to have a "roles" table within that organisations schema, link each user to the appropriate role, then restrict access at the application level as you said or even edit the search_path of that user based on the role_id (if that's possible).Adding the allowed schemas to that users search path is the preferably approach but I'm not sure how to determine the type of user and therefore the appropriate search path.
    – ste
    Sep 9, 2014 at 1:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.