Sample Header Ad - 728x90

Roles and policies in Postgresql 9.5 row-level security

6 votes
0 answers
449 views
Following the the info in posts like [these](https://dba.stackexchange.com/questions/25357/choice-of-authentication-approach-for-financial-app-on-postgresql) 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
Asked by Ruslan Talpa (211 rep)
Oct 9, 2015, 08:13 AM
Last activity: Mar 29, 2020, 01:02 AM