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
user_vars.company_id) seems like a security bug. Anyone who can connect to the database could useSETto evade RSPs.