Currently have RLS working excellently! Trying to reduce the dataset size for it as every report that external people see, uses it. I want RLS to work as is for external users but want to modify the security table so I don't have to have every single combination of internal employees and SecurityGroups. We have a lot! So, I added a column to the SecurityTable to flag if they are our employee or not. The table includes all external people and every security group they have. It also include a single record for every one of our employees with 1 for InternalEmployee.
TestTable
| FirstName | SecurityGroup |
| Bob | Group1 |
| Tom | Group2 |
SecurityTable
| Username | SecurityGroup | InternalEmployee |
| susan@outside.org | Group1 | 0 |
| tiffany@mycompany.com | NULL | 1 |
The relationship between these two tables is preventing me from doing this as the internal employee doesn't have a securitygroup. I have SecurityTable filtering TestTable joining on SecurityGroup. Is there a way to make RLS work this way listing tiffany just once or do I need to add a record in the security table for Group1, Group2, Group3 and so on?
For this example, Tiffany would see all the FirstNames and Susan would only see Bob.
