考慮以下步驟以在我當前遵循的兩個表上應用行級安全性：
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');
這將只允許分別為用戶qualifier = 'CH'選擇要使用的行并按縮進方式作業。ueliFRjaques
但是，有沒有辦法只依賴parent.qualifier并讓策略對關系中的每一行生效？
像child1，child2，...，這樣我就不必a）為每個子表創建/維護一個額外的限定符，b）更重要的是可以為每個角色保留額外的策略嗎？
我面對 20 個需要 RLS 保護的表，其中包含大約 20 個不同的限定符。如果我的數學是正確的，我最終會得到 400 份保單。
uj5u.com熱心網友回復：
這樣我就不必為每個子表創建/維護額外的限定符
是的：
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');
或者更簡單（因為ch_reader_policyonparent也將應用于SELECTin 子策略）：
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
));
更重要的是：這樣我就可以為每個角色節省額外的政策？
不，您仍然需要每個表和角色的策略。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/464760.html
標籤：PostgreSQL 行级安全