rls-demo-item-tags.sql

create table tag (
  id int generated always as identity primary key,
  name text
);

insert into tag (name)
select * from unnest(array[
    'alpha', 'beta', 'gamma', 'delta', 'epsilon', 'zeta', 'eta', 'iota', 'kappa', 'lambda', 'mu',
    'nu', 'xi', 'omicron', 'pi', 'rho', 'sigma', 'tau', 'upsilon', 'phi', 'chi', 'psi', 'omega'
]);

create table item (
  id int generated always as identity primary key,
  value text,
  tags int[]
);

insert into item (value, tags)
select
  md5(random()::text),
  array_sample((select array_agg(id) from tag), trunc(random() * 4)::int + 1)
from generate_series(1, 1000000);

create index on item using gin (tags);

alter table tag enable row level security;
alter table item enable row level security;

create role item_reader;
grant select on item to item_reader;
grant select on tag to item_reader;

create policy item_reader_tag_policy on tag
for select to item_reader
using (
    current_setting('item_reader.allowed_tags') is not null and
    current_setting('item_reader.allowed_tags')::text[] @> array[name]
);

create policy item_reader_item_policy on item
for select to item_reader
using (
    exists (
        select 1 from tag
        where item.tags @> array[tag.id]
    )
);