Inherit row level security policy to children/relations-CodePudding

Consider the following steps to apply row-level security on two tables that I currently follow:

create table parent
(
    parent_id bigint primary key,
    qualifier text not null,
    content   text
);

create table child1
(
    child1_id bigint primary key,
    parent_id bigint references parent on delete cascade,
    qualifier text not null,
    content   text
);

-- trying to boost rls performance
create index on parent (qualifier);
create index on child1 (qualifier);

-- insert test data
insert into parent values (1, 'CH', 'some secret values');
insert into parent values (2, 'FR', 'some secret values');
insert into parent values (3, 'MX', 'some secret values');
insert into child1 values (1, 1, 'CH', 'CH addendum');
insert into child1 values (2, 2, 'FR', 'FR addendum');
insert into child1 values (3, 3, 'MX', 'MX addendum');

-- create roles
create role readers;
create role ch_readers;
create role fr_readers;

-- Add roles to group role
grant readers to ch_readers, fr_readers;

-- grant basic read privileges
grant select on parent, child1 to readers;

-- create concrete users and grant corresponding roles
create user ueli with password 'pass123' role ch_readers;
create user jaques with password 'pass123' role fr_readers;

-- enable rls on tables
alter table parent enable row level security;
alter table child1 enable row level security;

-- create policies
create policy ch_reader_policy on parent for select to ch_readers using (qualifier = 'CH');
create policy ch_reader_policy on child1 for select to ch_readers using (qualifier = 'CH');
create policy fr_reader_policy on parent for select to fr_readers using (qualifier = 'FR');
create policy fr_reader_policy on child1 for select to fr_readers using (qualifier = 'FR');

This will only allow to select rows where qualifier = 'CH' to user ueli respectively FR for user jaques and works as indented.

However, is there a way to rely only on parent.qualifier and let the policy take effect on every row in the relation?

Like child1, child2, ..., so that I don't have to a) create/maintain an additional qualifier for each child table and b) more important can spare the additional policy for each role?

I am facing 20 tables that require RLS protection with roughly 20 different qualifiers. If my math is correct I would end up with 400 policies.

CodePudding user response：

so that I don't have to create/maintain an additional qualifier for each child table

Yes:

CREATE POLICY ch_reader_policy ON child1
  FOR SELECT 
  TO ch_readers
  USING ((
    SELECT qualifier
    FROM public.parent
    WHERE public.parent.parent_id = parent_id
  ) = 'CH');

Or simpler (since the ch_reader_policy on parent will also be applied to the SELECT in the child policy):

CREATE POLICY ch_reader_policy ON child1
  FOR SELECT 
  TO ch_readers
  USING (EXISTS(
    SELECT *
    FROM public.parent
    WHERE public.parent.parent_id = parent_id
  ));

more important: so that I can spare the additional policy for each role?

No. You still need a policy per table and role.