Forum Discussion
How to block RLS from being applied in a one-to-many relationship ?
Hi,
I have a table "sensitive_attributes" connected to a table "aggregates" with a one-to-many relationship configured (one sensitive_attribute has many aggregates). aggregates.sensitive_attribute_id is linked to sensitive_attributes.id
I want a viewer to see only its "sensitive_attributes" but access to all "aggregates" through RLS.
I added role, configured it to filter the "sensitive_attributes", and it works. But this user then only see the aggregates linked to these sensitive attributes. Just like the RLS propagate from the parent to the children. Is there a way to have RLS on the first model, NOT propagating on the second ?
The relationship is one-to-many, it is "single direction", bi-directional filtering is OFF.
Hello,sybic_2001
I am glad to help you.According to your description, when there is a relationship between two tables, if you set the Row level security on the table field above the relationship pass, the RLS will work with the table data downstream of the relationship as well as with the relationship between the tables, and you want to remove the restriction on the pass.
Unfortunately ,RLS is designed to restrict access to data at the row level, and its filters naturally propagate down through the relationships in the model as a design feature.
So if you set up RLS for the parent table on the filter side, then the accessible data for the child table will also be restricted
like this:When I set RLS on SalesFather, the other two tables are also limited by the passing of the relationship
So if you want to achieve a similar effect: limiting user access to the table on the top of the relationship while displaying the data from the table on the bottom of the relationship, you can choose to make a copy of the "sensitive_attributes" table.
You can choose to make a copy of the "sensitive_attributes" table.
The copied table has no relationship with the other tables (the calculate column or measure in the original "sensitive_attributes" table needs to be recreated), and you can set up RLS for this new copied table without setting up RLS for the "sensitive_attributes" table. attributes" table and set up RLS for this new replica table without configuring RLS for the "sensitive_attributes" table.
like this.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hello,sybic_2001
I am glad to help you.According to your description, when there is a relationship between two tables, if you set the Row level security on the table field above the relationship pass, the RLS will work with the table data downstream of the relationship as well as with the relationship between the tables, and you want to remove the restriction on the pass.
Unfortunately ,RLS is designed to restrict access to data at the row level, and its filters naturally propagate down through the relationships in the model as a design feature.
So if you set up RLS for the parent table on the filter side, then the accessible data for the child table will also be restricted
like this:When I set RLS on SalesFather, the other two tables are also limited by the passing of the relationship
So if you want to achieve a similar effect: limiting user access to the table on the top of the relationship while displaying the data from the table on the bottom of the relationship, you can choose to make a copy of the "sensitive_attributes" table.
You can choose to make a copy of the "sensitive_attributes" table.
The copied table has no relationship with the other tables (the calculate column or measure in the original "sensitive_attributes" table needs to be recreated), and you can set up RLS for this new copied table without setting up RLS for the "sensitive_attributes" table. attributes" table and set up RLS for this new replica table without configuring RLS for the "sensitive_attributes" table.
like this.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.