如何为Web应用配置Postgres RLS：会话与告警权限管控

一、基础准备：角色规划与RLS启用 #

先按权限层级创建核心角色：

-- DB管理员角色（最高权限，负责数据库维护）
CREATE ROLE db_admin WITH LOGIN PASSWORD 'secure_db_pass';
-- 站点管理员角色（管理站点全局数据，不含DB级操作）
CREATE ROLE site_admin WITH LOGIN PASSWORD 'secure_site_pass';
-- 普通应用用户角色（网站用户的统一操作角色，无需直接登录）
CREATE ROLE app_user NOLOGIN;

所有需要行级安全控制的表，必须先启用RLS：

ALTER TABLE [目标表名] ENABLE ROW LEVEL SECURITY;

二、UserSessions表配置 #

1. 表结构（示例） #

CREATE TABLE UserSessions (
    session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INT NOT NULL, -- 关联用户表ID
    login_timestamp TIMESTAMPTZ DEFAULT NOW(),
    ip_address INET,
    user_agent TEXT
);

-- 启用RLS
ALTER TABLE UserSessions ENABLE ROW LEVEL SECURITY;

2. RLS策略 #

-- 允许DB/站点管理员查看所有会话记录
CREATE POLICY allow_admin_view_sessions ON UserSessions
    FOR SELECT
    TO db_admin, site_admin
    USING (true);

-- 允许普通用户插入会话记录（登录时写入）
CREATE POLICY allow_user_insert_sessions ON UserSessions
    FOR INSERT
    TO app_user
    WITH CHECK (true); -- 若需校验user_id与当前用户匹配，可在WITH CHECK中添加对应逻辑

3. 权限赋值 #

GRANT INSERT ON UserSessions TO app_user;
GRANT SELECT ON UserSessions TO db_admin, site_admin;

三、UserAlerts表（用户专属告警）配置 #

1. 表结构（示例） #

CREATE TABLE UserAlerts (
    alert_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL, -- 关联用户表ID
    alert_content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    is_read BOOLEAN DEFAULT false
);

-- 启用RLS
ALTER TABLE UserAlerts ENABLE ROW LEVEL SECURITY;

2. RLS策略（用户仅操作自身数据） #

通过会话变量传递当前网站用户ID，策略基于此变量过滤行：

-- 查看自身告警
CREATE POLICY allow_user_view_own_alerts ON UserAlerts
    FOR SELECT
    TO app_user
    USING (user_id = current_setting('app.current_user_id')::INT);

-- 插入自身告警（系统推送或用户创建）
CREATE POLICY allow_user_insert_own_alerts ON UserAlerts
    FOR INSERT
    TO app_user
    WITH CHECK (user_id = current_setting('app.current_user_id')::INT);

-- 更新自身告警（如标记已读）
CREATE POLICY allow_user_update_own_alerts ON UserAlerts
    FOR UPDATE
    TO app_user
    USING (user_id = current_setting('app.current_user_id')::INT)
    WITH CHECK (user_id = current_setting('app.current_user_id')::INT);

-- 删除自身告警
CREATE POLICY allow_user_delete_own_alerts ON UserAlerts
    FOR DELETE
    TO app_user
    USING (user_id = current_setting('app.current_user_id')::INT);

3. 权限赋值 #

GRANT SELECT, INSERT, UPDATE, DELETE ON UserAlerts TO app_user;
-- 若需管理员查看所有告警，可添加：
GRANT ALL ON UserAlerts TO db_admin, site_admin;

四、角色权限绑定与传递 #

推荐方案：连接池+会话变量（适配数千用户） #

1. 应用端使用通用连接角色（如app_connector）连接数据库，切换到app_user角色：

SET ROLE app_user;

2. 设置会话变量传递当前网站用户ID（应用层注入实际用户ID）：

SET app.current_user_id = '123'; -- 123为当前登录用户的ID

3. 允许app_user设置该变量：

ALTER ROLE app_user SET app.current_user_id = '0'; -- 默认值
GRANT SET ON CONFIGURATION PARAMETER app.current_user_id TO app_user;

不推荐方案：每个网站用户对应Postgres角色 #

数千用户会导致Postgres角色数量过载，管理维护成本极高，仅适用于用户量极小的场景。

五、测试与优化 #

• 权限测试：用db_admin/site_admin登录验证是否能查看所有会话；用app_user角色登录，设置不同current_user_id验证是否只能操作自身告警。
• 性能优化：给UserAlerts.user_id、UserSessions.user_id创建索引，降低RLS过滤的开销：

CREATE INDEX idx_user_alerts_user_id ON UserAlerts(user_id);
CREATE INDEX idx_user_sessions_user_id ON UserSessions(user_id);

• 权限最小化：避免给app_user赋予DROP、ALTER等危险权限，仅保留必要的CRUD权限。

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