Following the the info in posts like these i would like to use the role system and policies, i.e. each user in my system would have a db role. I want to model the following properties but i can't come up with a role hierarchy.

Consider the following tables: companies(id, name) users(id, name) projects(id, name, company_id) users_companies(user_id, company_id, type) users_projects(user_id, project_id)

each user can have a different type within a company (admin/employee/customer) and depending on that the policy should change. So when a user is authenticated, we know his user_id and company_id Those can be provided id needed in policies with things like set local user_vars.user_id = 10 set local user_vars.company_id = 20 What would be the roles and policies needed in order to get this

  • you can see only your company row in the companies table
  • admins can see all the projects for their company
  • employees can see only the projects they are assigned to
  • no one can see/alter entries in users_companies (that is done somehow on signup and with triggers)
  • admins can see the users_projects entries but only related to the users and project in their company
  • employees can see users_projects entries only for projects that they are assigned to
  • admins can see all the users in their company
  • employees can see the users in their company that participate in the same projects as they do

I am looking for a base case that i can build on. My main problem is the fact that a user can have different roles/types depending on the current company

  • I think the problem is simplified a bit if i think that each user could have separate db roles depending in which company he wants to authenticat, so for for user joe there could be 2 roles joe_company_1 and joe_company_2 and you could do grant admin to joe_company_1 and grant employee to john_company_2 Commented Oct 9, 2015 at 12:27
  • 1
    Relying on session variables (user_vars.company_id) seems like a security bug. Anyone who can connect to the database could use SET to evade RSPs.
    – filiprem
    Commented Apr 5, 2019 at 9:38


Your Answer

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