Row Level Security with different types of permissions

[Mumin192]

Hello

I work in a company where we have clients that have access to our website with a dashboard. In this dashboard each user have specific permission access of course

Each client is a company, and each company has site

We have three types of users that will use this dashboard:

1- a user who has access to a specific site in one company

2- a user who has access to all sites inside one company

3- a user who has access to everything (admin)

For the 1st type of user, it's easy to arrange RLS

For the 2nd and 3rd type it's not clear how to arrange it

in the DB, we have 3 tables:

1- dashboard_users_sites: it has all users with the 1st type and sites that are allowed. 2nd and 3rd types are not mentioned inside it

2- dashboard_users_companies: it has all users with 1st and 2nd types and companies that are allowed. 3rd type is not mentioned inside it

3- dashboard_users: it has all users from the 3 types 

Can you help me designing the table or set of tables that RLS should control it?
Thanks

[Mumin192]

Hello @collinq 

Unfortunately it didn't work. It's showing data only from the 1st type user

Any thoughts?

Thanks

[collinq]

Hey @Mumin192 ,

When you say it doesn't work, do you mean in Desktop when you test it or in the Service?  Is there a difference in the test results?

[collinq]

HI @Mumin192 ,

I think that RLS is the solution to all 3 scenarios.  I would set up the RLS for site AND company.  That way you have two criteria - which company and which site and you can mix and match as you see fit.  If you leave some names out (like the admin) then they wil lbe able to see everything.  

The site and the company do not have to come from the same table because you can select from multiple tables in the RLS setup.