基于行级安全策略实现用户数据隔离解决方案

策略创建语法如下：

CREATE [OR REPLACE] POLICY name ON table_name
    [AS { PERMISSIVE | RESTRICTIVE }]
    [FOR { ALL | SELECT | INSERT | UPDATE | DELETE }]
    [TO { role_spec | PUBLIC } [, ...] ]
    [USING ( using_expression ) ]
    [WITH CHECK ( check_expression ) ];

参数说明：

• AS PERMISSIVE（默认）：宽松策略，多策略之间以OR合并。
• AS RESTRICTIVE：限制策略，与所有宽松策略以AND合并，常用于做“黑名单”式强制过滤。
• FOR子句：限定策略生效的命令类型，ALL表示对所有DML生效。
• TO子句：指定策略适用的角色，PUBLIC表示所有角色。
• USING：对现有行（SELECT/UPDATE/DELETE时的旧行）的可见性谓词。
• WITH CHECK：对新增或更新后的新行（INSERT/UPDATE新值）的合法性谓词，不满足则报错。

策略表达式中通常引用如下所示的上下文函数来确定当前访问者的身份与用户归属：

• 当前会话执行SQL所使用的角色（受SET ROLE影响）。

  SELECT current_user;

• 当前会话登录时使用的原始角色（不受SET ROLE影响）。

  SELECT session_user;

• 自定义GUC参数，常用于在事务内传递用户ID。

  SELECT current_setting('app.user_id');

在方案中，应用层在每个事务开始时通过SET LOCAL app.user_id = xxx将用户ID注入会话上下文，策略表达式再通过current_setting('app.user_id')取出，作为行级过滤的依据。

RLS涉及两类关键属性：

• BYPASSRLS：表owner与拥有BYPASSRLS属性的角色默认不应用RLS策略，可为应用角色显式授予或撤销该属性。
• FORCE RLS：对表owner强制启用RLS策略，防止owner通过自身身份绕过隔离。

命令示例：

• 授予应用角色绕过RLS的能力（生产环境慎用）。

  ALTER ROLE app_user BYPASSRLS;

• 撤销绕过RLS的能力。

  ALTER ROLE app_user NOBYPASSRLS;

• 对表owner强制启用RLS。

  ALTER TABLE orders FORCE ROW LEVEL SECURITY;

RLS不是对标准GRANT/REVOKE权限的替代，而是补充。

• 标准权限决定“能否执行某类操作”，RLS决定“能操作哪些具体的行”。
• 必须先通过标准权限校验，才进入RLS策略校验；两者同时满足才能执行。
• 即使某用户拥有表的SELECT权限，若无对应RLS策略许可，也查不到任何行（不是报错，而是返回0行）。
• 对于INSERT/UPDATE，RLS既校验旧行（USING）也校验新行（WITH CHECK），防止越权写入。

具体步骤如下：

1. 环境准备及账号权限配置。

   环境准备包括：规划数据库账号、创建用户表和业务表、配置权限等。

   1. 创建应用连接角色。

      CREATE ROLE app_user LOGIN PASSWORD 'your_password';

   2. 创建用户主表。

      CREATE TABLE users (
          user_id   BIGINT PRIMARY KEY,
          user_name VARCHAR(128) NOT NULL,
          created_at TIMESTAMPTZ DEFAULT NOW()
      );

   3. 创建业务表 orders（含 user_id 列）。

      CREATE TABLE orders (
          id         BIGSERIAL PRIMARY KEY,
          user_id    BIGINT NOT NULL,
          order_no   VARCHAR(64) NOT NULL,
          amount     NUMERIC(12,2) NOT NULL,
          status     INT NOT NULL DEFAULT 0,
          created_at TIMESTAMPTZ DEFAULT NOW()
      );

   4. 在user_id上创建索引（提升性能必须的操作）。

      CREATE INDEX idx_orders_user_id ON orders(user_id);
      CREATE INDEX idx_orders_user_status ON orders(user_id, status);

   5. 授予app_user权限。

      GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;
      GRANT SELECT ON users TO app_user;

2. 启用行级安全策略并创建隔离策略。

   对业务表启用行级安全，并创建基于user_id的隔离策略。

   1. 启用行级安全。

      ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

   2. 创建隔离策略。

      CREATE POLICY user_isolation ON orders
          FOR ALL
          TO app_user
          USING (user_id = current_setting('app.user_id')::bigint)
          WITH CHECK (user_id = current_setting('app.user_id')::bigint);

   3. 强制表owner也遵守RLS（防止绕过）。

      ALTER TABLE orders FORCE ROW LEVEL SECURITY;

3. 测试用户数据隔离效果。

   使用不同用户上下文测试数据隔离，验证每个用户只能访问自己的数据。

   1. 准备测试数据（以管理员身份）。

      INSERT INTO orders (user_id, order_no, amount) VALUES
          (1001, 'ORD001', 100.00),
          (1001, 'ORD002', 200.00),
          (1002, 'ORD003', 300.00);

   2. 模拟用户1001的连接。

      SET ROLE app_user;
      SET app.user_id = '1001';

   3. 用户1001查询（只能看到自己的数据）。

      SELECT * FROM orders;

      预期返回2条记录（user_id=1001）。

   4. 尝试越权插入（会被拒绝）。

      INSERT INTO orders (user_id, order_no, amount) VALUES (1002, 'ORD004', 400.00);

      预期返回：

      ERROR: permission denied for sequence orders_id_seq

   5. 切换到用户1002。

      RESET app.user_id;
      SET app.user_id = '1002';

   6. 用户1002查询。

      SELECT * FROM orders;

      预期返回1条记录（user_id=1002）。

PostgreSQL的自定义GUC参数（Custom GUC）允许应用层在事务级别注入任意的命名参数。SET LOCAL设置只在当前事务中生效，事务结束自动RESET，能天然避免连接复用导致的用户串号问题。

BEGIN;
SET LOCAL app.user_id = '10086';
SELECT * FROM orders WHERE status = 1;  -- RLS自动拼接 AND user_id = 10086
COMMIT;

每张业务表都必须包含一个user_id列，并建议满足以下要求：

• 数据类型：建议使用BIGINT以容纳大规模用户号；小型也可使用INT或UUID。
• 非空约束：user_id应设为NOT NULL，避免出现“无用户”行被策略过滤后变成幽灵数据。
• 索引：必须在user_id上建索引，避免RLS谓词导致全表扫描；联合查询高频列建议建复合索引（user_id, 列名）。
• 外键：业务表之间的外键列也应携带user_id，并在外键中包含user_id，防止跨用户引用。

推荐的通用策略模板如下（适用于绝大多数业务表）：

CREATE POLICY user_isolation ON schema.table_name
    USING (user_id = current_setting('app.user_id')::bigint)
    WITH CHECK (user_id = current_setting('app.user_id')::bigint);

USING与WITH CHECK一致，保证查询、修改、删除的可见行同新增/更新后的新行属于同一用户。若某场景允许只读跨用户但不能写，可省略WITH CHECK，场景建议两者均配置，实现真正的行级强隔离。

推荐划分以下角色类别：

• 应用角色（app_user）：仅授予业务表的DML权限、NOBYPASSRLS、FORCE RLS对表owner生效，避免被绕过。
• DBA角色（dba_role）：授予DDL与运维权限；可由安全管理员在有审计、有审批的情况下临时BYPASSRLS处理故障。
• 只读角色（ro_user）：数据中台、BI报表角色，授予SELECT权限，并配置与app_user相同的RLS策略，确保只看本用户数据。