PostgreSQL：为客户创建个性化角色并实现数据权限控制

1. 先搞定基础角色（避免递归错误） #

先建一个通用的customer角色当权限模板，这个角色不用登录，只给它两张表的查询权限就行，别搞继承循环：

-- 创建基础权限角色，禁止登录
CREATE ROLE customer NOLOGIN;
-- 赋予table_1和table_2的查询权限
GRANT SELECT ON table_1, table_2 TO customer;

之前的无限递归错误，大概率是你不小心让新角色继承了自己，或者customer角色继承了包含它的角色，用\du命令能查看角色继承关系，排查下就行。

2. 创建ALICE的专属角色 #

直接建client_alice_stewart角色，让它继承customer的权限，需要登录的话加上LOGIN和密码：

-- 创建专属角色，设置登录密码（不需要登录就去掉LOGIN）
CREATE ROLE client_alice_stewart LOGIN PASSWORD '自定义密码';
-- 让专属角色继承基础权限
GRANT customer TO client_alice_stewart;

3. 关键：用行级安全锁死数据访问 #

光给查询权限还不够，得限制只能看到自己的id（51）对应的数据，这就得开行级安全（RLS）：

-- 给两张表开启行级安全
ALTER TABLE table_1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE table_2 ENABLE ROW LEVEL SECURITY;

-- 给ALICE的角色建table_1的访问策略，只允许看id=51的记录
CREATE POLICY alice_access_table1 ON table_1
FOR SELECT TO client_alice_stewart
USING (id = 51);

-- 同理给table_2建策略
CREATE POLICY alice_access_table2 ON table_2
FOR SELECT TO client_alice_stewart
USING (id = 51);

4. 验证效果 #

切换到ALICE的角色测试下：

SET ROLE client_alice_stewart;
SELECT * FROM table_1; -- 只会返回id=51的记录
SELECT * FROM table_2; -- 一样的效果
RESET ROLE;

内容的提问来源于stack exchange，提问作者Kris