PostgreSQL行级安全结合视图实现列隐藏并支持写入的方案

针对你需要暴露accounts表、限制敏感字段访问、保留RLS且支持写入的需求，在PostgreSQL 12+（2022年主流版本均满足）中有两种可行方案：

方案一：直接在原表上结合RLS与字段级权限 #

无需额外视图或schema，直接通过角色权限控制字段访问，同时保留RLS的行级过滤：

1. 创建专门的API访问角色

CREATE ROLE api_user WITH LOGIN PASSWORD 'secure_password';

2. 启用原表的RLS并定义行级访问策略
   假设表中用user_id字段关联当前登录用户，策略确保用户只能访问自己的行：

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY accounts_self_only ON accounts
    FOR ALL USING (user_id = current_user::text);

3. 授予角色仅允许访问的字段权限
   只开放可暴露的字段（如id、username、email），拒绝敏感字段（如locked、failed_login_attempts）的任何权限：

-- 授予查询权限
GRANT SELECT (id, username, email) ON accounts TO api_user;
-- 授予更新权限（仅允许修改的字段）
GRANT UPDATE (username, email) ON accounts TO api_user;
-- 授予插入权限（仅允许提交的字段）
GRANT INSERT (username, email) ON accounts TO api_user;

优势：配置简单，无额外层级，完全支持写入操作，RLS直接生效。

方案二：带SECURITY INVOKER属性的可更新视图 #

如果需要隔离原表结构（比如不想让API角色看到敏感字段的存在），可以将原表放在内部schema，通过可更新视图暴露允许字段，同时解决RLS失效问题：

1. 创建内部schema存放原表

CREATE SCHEMA internal;
-- 将现有accounts表移动到internal schema
ALTER TABLE accounts SET SCHEMA internal;

2. 启用原表的RLS并定义策略

ALTER TABLE internal.accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY accounts_self_access ON internal.accounts
    FOR ALL USING (user_id = current_user::text);

3. 创建带SECURITY INVOKER的可更新视图
   SECURITY INVOKER属性会让视图以调用者（api_user）的身份访问原表，确保原表的RLS策略生效：

CREATE OR REPLACE VIEW public.accounts
    WITH (SECURITY INVOKER)
    AS SELECT id, username, email FROM internal.accounts;

4. 授予视图的读写权限给API角色

GRANT SELECT, INSERT, UPDATE ON public.accounts TO api_user;

优势：隐藏原表结构，敏感字段完全不可见；视图为简单字段筛选时，PostgreSQL默认支持写入，无需额外触发器。若需处理复杂写入逻辑（如自动填充敏感字段），可添加INSTEAD OF触发器。

关键注意事项 #

• 确保RLS策略中的用户关联逻辑正确：若API使用应用级认证（而非数据库用户直接登录），可改用current_setting('app.current_user_id')等方式获取当前用户ID，调整策略条件。
• 敏感字段需设置合理默认值：如locked默认false、failed_login_attempts默认0，避免插入时因缺少字段报错。
• 权限测试：用api_user登录后，尝试访问敏感字段（如SELECT locked FROM accounts）或修改其他用户的行，验证是否被正确拦截。

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