Postgres授予角色权限后查询返回空表问题排查

1. customer角色查询返回空的原因 #

切换到customer角色时，策略中的current_role值为'customer'，但策略的WHERE条件是匹配'client_' || lower(...)格式的角色名，显然'customer'不符合该格式，子查询返回NULL，而customer_id = NULL的判断永远不成立，因此返回空结果。

2. client_julie_sanchez角色查询返回空的潜在问题 #

问题1：public.customer表未配置行级安全策略 #

你给payment表启用了行级安全，但customer表没有。当client_julie_sanchez通过customer角色查询customer表时，默认行级安全规则是拒绝所有访问（即使已授予SELECT权限），导致策略内的子查询无法获取对应customer_id，最终customer_id = NULL的条件不成立，返回空。

问题2：public.customer表中无匹配记录 #

如果customer表里不存在first_name='Julie'且last_name='Sanchez'的记录，子查询同样返回NULL，导致条件不成立。

问题3：角色名与拼接逻辑的匹配问题 #

拼接逻辑lower(c.first_name || '_' || c.last_name)要求表里的名字转换后和角色名后缀完全一致，若名字包含空格、特殊字符或大小写不匹配，会导致拼接结果与角色名无法对应。

修复步骤 #

1. 给public.customer表添加行级安全策略，允许customer角色查询自身对应记录：

ALTER TABLE public.customer ENABLE ROW LEVEL SECURITY;

CREATE POLICY policy_customers
ON public.customer TO customer
USING (
  'client_' || lower(first_name || '_' || last_name) = current_role
);

2. 确保public.customer表存在匹配client_julie_sanchez的记录，示例插入语句：

INSERT INTO public.customer (customer_id, first_name, last_name)
VALUES (1, 'Julie', 'Sanchez');

3. （可选）调整customer角色的查询权限：若需要让customer角色能查看所有payment记录，可添加宽松策略：

CREATE POLICY policy_payments_all
ON public.payment TO customer
USING (true)
WITH CHECK (true);

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