这是一个典型的PostgreSQL行级安全（RLS）应用场景，我帮你一步步拆解实现步骤，确保每个角色的权限完全符合需求：

postgres默认是超级用户，一般已经存在，我们只需要创建另外两个角色：

-- 创建认证用户角色（NOLOGIN表示不能直接登录，通常应用用户会继承这个角色）
CREATE ROLE authenticated_user NOLOGIN;
-- 创建访客角色
CREATE ROLE visitor NOLOGIN;

-- 补充：如果需要让具体用户登录，可以创建带LOGIN的用户并继承角色
-- 比如创建一个名为alice的认证用户：
-- CREATE USER alice WITH PASSWORD 'your_secure_password';
-- GRANT authenticated_user TO alice;

必须添加user_id列，用来关联当前登录用户，这是实现“仅访问自身private_data”的核心依据：

CREATE TABLE users (
    user_id TEXT PRIMARY KEY, -- 建议和角色/用户名保持一致，方便关联
    public_data TEXT NOT NULL,
    private_data TEXT NOT NULL,
    system_data TEXT NOT NULL
);

首先撤销public角色对users表的默认权限，避免不必要的访问：

REVOKE ALL ON TABLE users FROM public;

然后给postgres授予全权限（超级用户本来就有，这里明确配置更清晰）：

GRANT ALL ON TABLE users TO postgres;

这是实现细粒度权限控制的核心，先开启users表的RLS：

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

4.1 访客（visitor）权限策略 #

访客只能读取所有用户的public_data：

-- 授予访客仅读取public_data列的权限
GRANT SELECT (public_data) ON TABLE users TO visitor;
-- 允许访客访问所有行（因为要看到所有用户的public_data）
CREATE POLICY visitor_access_policy ON users
    FOR SELECT TO visitor
    USING (true);

4.2 认证用户（authenticated_user）权限策略 #

认证用户可以读取所有用户的public_data，仅能读取自身的private_data，无法访问system_data：

-- 授予认证用户读取public_data和private_data列的权限
GRANT SELECT (public_data, private_data) ON TABLE users TO authenticated_user;

-- 策略1：允许认证用户访问所有行的public_data
CREATE POLICY authenticated_public_access ON users
    FOR SELECT TO authenticated_user
    USING (true);

-- 策略2：仅允许认证用户访问自身行的private_data
CREATE POLICY authenticated_private_access ON users
    FOR SELECT TO authenticated_user
    USING (user_id = current_user);

这里的逻辑是：当认证用户查询public_data时，所有行都可见；当查询private_data时，只有user_id等于当前登录用户的行才会被返回。

可以通过切换角色来测试每个角色的权限是否符合预期：

-- 测试访客角色
SET ROLE visitor;
-- 可以正常读取所有public_data
SELECT public_data FROM users;
-- 尝试读取private_data或system_data会报错（权限不足）
SELECT private_data FROM users; -- 报错：permission denied for table users

-- 测试认证用户角色（假设已创建alice用户并继承authenticated_user）
SET ROLE alice;
-- 可以读取所有用户的public_data
SELECT public_data FROM users;
-- 只能读取自己的private_data
SELECT private_data FROM users WHERE user_id = 'alice'; -- 返回数据
SELECT private_data FROM users WHERE user_id != 'alice'; -- 返回空结果
-- 尝试读取system_data报错
SELECT system_data FROM users; -- 报错：permission denied for table users

-- 测试超级用户postgres
SET ROLE postgres;
-- 可以读取所有列所有行
SELECT * FROM users;

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