create table user_table(
idx text,
_section text[]
);
create table section_table(
_section text,
sub_section text[]
);
alter table user_table enable row level security;
create policy user_p on user_table for select
using (section @>
(select _section from user_table
intersect
(select _section::text[]
from section_table
where current_setting('my.sub_section',true)::text[]
<@ (select sub_section
from section_table)
)
)
);
insert into user_table values
(1,'{"section1"}'),(2,'{"section2"}'),
(2,'{"section2","section1"}');
insert into section_table values
('section1','{"s","b"}'),
('section2','{"c","d"}');
expect output:
set my.sub_section ='{"c"}';
select * from user_table;
should only show (2,'{"section2"}'), but it shows everything.
When sub_section is entered, it should go to section_table and find the
corresponding section and intersect with user_table, once the value of
intersect is not null, then should return the corresponding value of
user_table.
I am confused why it doesn't show the correct expect result.
