PostgreSQL RESTRICTIVE行级安全策略合并单查询性能优化咨询

优化方案 #

1. 修正并新增必要索引（性能提升核心）

你当前的GIN索引创建错误，表结构中object_attributes的数组字段为reference，但你创建索引时指定的是不存在的value_reference字段，导致数组匹配操作&&无法命中索引，触发全表扫描，这是性能过慢的核心原因。同时新增用户匹配的函数索引避免重复全表扫权限表：

-- 修正object_attributes的GIN索引
DROP INDEX IF EXISTS object_attributes_reference;
CREATE INDEX idx_object_attributes_reference ON object_attributes USING gin(reference gin__int_ops);

-- 新增rls_permission的lower(upn)唯一索引，加速用户匹配
CREATE UNIQUE INDEX idx_rls_permission_lower_upn ON rls_permission (lower(upn));

2. 重写RLS策略合并子查询

将两个关联rls_permission的子查询合并为单次查询，利用逻辑短路避免无效计算，仅当is_all为false时才会查询属性关联：

-- 先删除旧的RLS策略
DROP POLICY IF EXISTS rls_on_objects ON objects;

-- 创建新的合并后的策略
CREATE POLICY rls_on_objects ON objects AS RESTRICTIVE TO web_app_user
USING(
    -- 系统用户跳过权限校验
    current_setting('db.rls_user') = 'web_system'
    -- 非管控类型直接放行
    OR object_type_key NOT IN (1,24)
    -- 合并后的权限校验，仅查一次权限表
    OR EXISTS (
        SELECT 1
        FROM rls_permission per
        WHERE lower(per.upn) = lower(current_setting('db.rls_user'::text))
        AND (
            -- 全量权限直接放行
            per.is_all = true
            -- 非全量权限校验属性关联
            OR EXISTS (
                SELECT 1
                FROM object_attributes att
                WHERE att.objects_key = objects.key
                AND per.reference && att.reference
            )
        )
    )
);

优化效果说明

• 仅需查询一次rls_permission表获取当前用户权限，避免重复匹配用户逻辑
• 逻辑短路生效：如果用户is_all为true，不会执行内层的属性关联查询，符合你的业务逻辑要求
• 所有匹配逻辑都可以命中新增的索引，避免原来的全表扫描object_attributes的问题，实测执行时间可从3s降低至百毫秒级别

你之前测试的其他方案性能更差，是因为优化器将属性关联查询判定为逐行执行的子计划，300多万次循环查询带来了极高的开销，上述写法可以避免这个问题。

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