sql - How to apply RLS on table using value from same table

Question

I have a table like below and two users Soni and Garve. I want to show few orders only to users based on their role and using value in string_to_match column.

user Soni can see order_id like below, all product_keys of an order_id if order_id has even one string_to_match=pending rows/orders.

similarly role Garve can see all product_keys of an order_id if order_id has string_to_match=waiting orders.

Is it possible in Postgres?

question from:https://stackoverflow.com/questions/66053005/how-to-apply-rls-on-table-using-value-from-same-table

Answer 1

If everybody has a roleid and based on you can decide ,so

-- roleid  1 = access to 'pending'
-- roleid 2 = access to 'waiting'

declare roleid integer;
select * from 
yourordertable t1
where  exists ( select 1 from yourordertable t2 
                where t2.order_id = t1.order_id
                and case roleid 
                   when 1 then t2.string_to_match = 'pending'
                   when 2 then t2.string_to_match = 'waiting'                         
              ) 
and case roleid when 2 then t2.string_to_match <> 'pending' 

not sure about last condition but based on your last output screenshot, it filters out 'pending' status for 'waiting' status .if It is not needed you can remove it.