为每个用户组合多个策略

Amazon Redshift 中的 RLS 支持为每个用户和对象附加多个策略。当为一个用户定义了多个策略时，Amazon Redshift 会根据表的 RLS CONJUNCTION TYPE 设置，使用 AND 或 OR 语法应用所有策略。有关联接类型的更多信息，请参阅ALTER TABLE。

一个表上的多个策略可以与您关联。将多个策略直接附加到您，或是您属于多个角色，并且这些角色附加了不同策略。

当多个策略应限制给定关系中的行访问权限时，可以将该关系的 RLS CONJUNCTION TYPE 设置为 AND。考虑以下示例。Alice 只能看到策略指定的“catname”为 NBA 的体育赛事。

-- Create an analyst role and grant it to a user named Alice.
CREATE ROLE analyst;
CREATE USER alice WITH PASSWORD 'Name_is_alice_1';
GRANT ROLE analyst TO alice;

-- Create an RLS policy that only lets the user see sports.
CREATE RLS POLICY policy_sports
WITH (catgroup VARCHAR(10))
USING (catgroup = 'Sports');

-- Create an RLS policy that only lets the user see NBA.
CREATE RLS POLICY policy_nba
WITH (catname VARCHAR(10))
USING (catname = 'NBA');

-- Attach both to the analyst role.
ATTACH RLS POLICY policy_sports ON category TO ROLE analyst;
ATTACH RLS POLICY policy_nba ON category TO ROLE analyst;

-- Activate RLS on the category table with AND CONJUNCTION TYPE. 
ALTER TABLE category ROW LEVEL SECURITY ON CONJUNCTION TYPE AND;

-- Change session to Alice.
SET SESSION AUTHORIZATION alice;

-- Select all from the category table.
SELECT catgroup, catname
FROM category;

 catgroup | catname 
---------+---------
 Sports   | NBA
(1 row)

当多个策略应允许用户查看给定关系中的更多行时，用户可以将该关系的 RLS CONJUNCTION TYPE 设置为 OR。考虑以下示例。Alice 只能看到策略指定的“Concerts”和“Sports”。

-- Create an analyst role and grant it to a user named Alice.
CREATE ROLE analyst;
CREATE USER alice WITH PASSWORD 'Name_is_alice_1';
GRANT ROLE analyst TO alice;

-- Create an RLS policy that only lets the user see concerts.
CREATE RLS POLICY policy_concerts
WITH (catgroup VARCHAR(10))
USING (catgroup = 'Concerts');

-- Create an RLS policy that only lets the user see sports.
CREATE RLS POLICY policy_sports
WITH (catgroup VARCHAR(10))
USING (catgroup = 'Sports');

-- Attach both to the analyst role.
ATTACH RLS POLICY policy_concerts ON category TO ROLE analyst;
ATTACH RLS POLICY policy_sports ON category TO ROLE analyst;

-- Activate RLS on the category table with OR CONJUNCTION TYPE. 
ALTER TABLE category ROW LEVEL SECURITY ON CONJUNCTION TYPE OR;

-- Change session to Alice.
SET SESSION AUTHORIZATION alice;

-- Select all from the category table.
SELECT catgroup, count(*)
FROM category
GROUP BY catgroup ORDER BY catgroup;

 catgroup | count 
---------+-------
 Concerts |  3
 Sports   |  5
(2 rows)