postgres-rls

PostgreSQL 行级安全

概述

行级安全（RLS）为数据隔离提供纵深防御。正确实施时，即使应用代码遗漏了过滤条件，也能防止数据泄漏。错误实施时，则会造成虚假的安全感，同时让数据在不同租户之间泄漏。

核心原则： RLS 是最后一道防线，而非唯一防线。实施不当就会导致数据泄露。

开始时宣布：“我正在应用 postgres-rls 来验证行级安全的实施情况。”

此技能适用场景

触及以下任意模式时，必须使用此技能：

模式	示例
**/migrations/**/*tenant*	migrations/001_add_tenant_id.sql
**/migrations/**/*rls*	migrations/005_enable_rls.sql
**/migrations/**/*policy*	migrations/010_create_policies.sql
**/*policy*.sql	db/policies.sql
**/auth/**	src/auth/context.ts
**/*tenant*	lib/tenant.ts、services/tenantService.ts
**/*multi-tenant*	docs/multi-tenant-architecture.md

使用以下命令检查：

git diff --name-only HEAD~1 | grep -iE '(tenant|rls|policy|auth.*sql|multi.?tenant)'

关键漏洞

1. 超级用户绕过（严重）

超级用户和具有 BYPASSRLS 属性的角色会忽略所有策略。

-- DANGEROUS: Testing as superuser shows RLS "working" when it's bypassed
SET ROLE postgres;
SELECT * FROM orders;  -- Returns ALL rows, RLS ignored

-- CORRECT: Test as application role
SET ROLE app_user;
SELECT * FROM orders;  -- Returns only permitted rows

检查清单：

• 应用以非超级用户角色连接
• 没有角色具有 BYPASSRLS 属性
• 测试以应用角色运行，而非超级用户

2. 表所有者绕过（严重）

除非设置了 FORCE ROW LEVEL SECURITY，否则表所有者会绕过 RLS。

-- INCOMPLETE: Owners bypass this
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- COMPLETE: Everyone including owners must obey policies
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

检查清单：

• 所有启用 RLS 的表都同时设置了 ENABLE 和 FORCE
• 迁移中包含这两条语句

3. 视图绕过（严重）

视图默认以创建者的权限运行。由超级用户拥有的视图会完全绕过 RLS。

-- DANGEROUS: View owned by superuser bypasses RLS
CREATE VIEW all_orders AS SELECT * FROM orders;

-- SAFE (PostgreSQL 15+): Security invoker respects caller's RLS
CREATE VIEW user_orders
WITH (security_invoker = true)
AS SELECT * FROM orders;

检查清单：

• 所有基于启用 RLS 的表创建的视图都使用 security_invoker = true（PG15+）
• 视图不由超级用户角色拥有
• 将物化视图记录为会绕过 RLS

4. USING 与 WITH CHECK 不匹配（高）

USING 用于筛选读取；WITH CHECK 用于验证写入。缺少 WITH CHECK 会允许插入无法查看的数据。

-- INCOMPLETE: User can INSERT rows they can't SELECT
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- COMPLETE: Both read and write protected
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

检查清单：

• 所有策略都同时包含 USING 和 WITH CHECK
• WITH CHECK 逻辑符合安全意图

5. 线程本地上下文泄漏（高）

连接池可能导致租户上下文在请求之间泄漏。

-- DANGEROUS: Context persists across pooled connections
SET app.tenant_id = 'tenant-123';

-- SAFE: Use SET LOCAL inside transaction (auto-resets)
BEGIN;
SET LOCAL app.tenant_id = 'tenant-123';
-- ... queries ...
COMMIT;  -- Context automatically cleared

应用模式：

// DANGEROUS: Leaks between requests
await db.query(`SET app.tenant_id = '${tenantId}'`);

// SAFE: Transaction-scoped context
await db.transaction(async (trx) => {
  await trx.raw(`SET LOCAL app.tenant_id = ?`, [tenantId]);
  // ... queries ...
});

检查清单：

• 始终使用 SET LOCAL，而非 SET
• 在事务内设置上下文
• 请求处理后的处理程序重置上下文（纵深防御）

6. 策略函数中的 SQL 注入（高）

策略中使用的函数可能成为注入入口。

-- DANGEROUS: If current_tenant() uses user input unsafely
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_tenant());

-- The function itself must be injection-safe:
CREATE OR REPLACE FUNCTION current_tenant()
RETURNS uuid AS $$
BEGIN
  -- SAFE: Casts to UUID, not string concatenation
  RETURN current_setting('app.tenant_id')::uuid;
END;
$$ LANGUAGE plpgsql STABLE;

7. 物化视图与数据导出（中）

物化视图不会遵循源表的 RLS。数据导出也可能绕过策略。

-- DANGEROUS: Contains ALL tenants' data
CREATE MATERIALIZED VIEW order_stats AS
SELECT tenant_id, count(*) FROM orders GROUP BY tenant_id;

-- Background jobs with superuser access can export all data

检查清单：

• 将物化视图记录为安全敏感对象
• 导出任务以应用角色运行
• 对批量数据访问记录审计日志

性能注意事项

为策略列建立索引

-- Without index: Sequential scan on every query
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Add index for policy column
CREATE INDEX idx_orders_tenant_id ON orders(tenant_id);

将函数封装在子查询中

逐行调用函数的开销很大。将其封装在子查询中，使其只求值一次：

-- SLOW: Function called per row
CREATE POLICY access_check ON documents
  USING (user_has_access(auth.uid(), id));

-- FASTER: Evaluated once, cached
CREATE POLICY access_check ON documents
  USING ((SELECT auth.uid()) = owner_id);

对复杂检查使用 SECURITY DEFINER

使用 SECURITY DEFINER 函数，避免 RLS 策略链：

-- SLOW: RLS on permissions table also evaluated
CREATE POLICY access_check ON documents
  USING (id IN (SELECT document_id FROM permissions WHERE user_id = auth.uid()));

-- FASTER: Bypass RLS chain with SECURITY DEFINER
CREATE OR REPLACE FUNCTION user_document_ids(uid uuid)
RETURNS SETOF uuid AS $$
  SELECT document_id FROM permissions WHERE user_id = uid;
$$ LANGUAGE sql STABLE SECURITY DEFINER;

CREATE POLICY access_check ON documents
  USING (id IN (SELECT * FROM user_document_ids(auth.uid())));

通过反规范化提升性能

在每张表上都存储 tenant_id，即使通过联接“显而易见”也不例外：

-- SLOW: Must join to get tenant context
CREATE POLICY order_items_policy ON order_items
  USING (order_id IN (
    SELECT id FROM orders WHERE tenant_id = current_setting('app.tenant_id')::uuid
  ));

-- FAST: Direct column check
ALTER TABLE order_items ADD COLUMN tenant_id uuid;
CREATE POLICY order_items_policy ON order_items
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

迁移模式

安全的 RLS 迁移

-- Step 1: Add column (if needed)
ALTER TABLE orders ADD COLUMN IF NOT EXISTS tenant_id uuid;

-- Step 2: Backfill data (batched for large tables)
UPDATE orders SET tenant_id = (
  SELECT tenant_id FROM customers WHERE customers.id = orders.customer_id
) WHERE tenant_id IS NULL;

-- Step 3: Add NOT NULL constraint
ALTER TABLE orders ALTER COLUMN tenant_id SET NOT NULL;

-- Step 4: Create index
CREATE INDEX CONCURRENTLY idx_orders_tenant_id ON orders(tenant_id);

-- Step 5: Enable RLS (both statements!)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- Step 6: Create policies
CREATE POLICY tenant_isolation ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

-- Step 7: Grant appropriate permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_role;

测试 RLS

必需的测试

describe('RLS Policies', () => {
  it('tenant A cannot see tenant B data', async () => {
    // Insert as tenant A
    await setTenantContext('tenant-a');
    await db('orders').insert({ id: 1, tenant_id: 'tenant-a', amount: 100 });

    // Switch to tenant B
    await setTenantContext('tenant-b');

    // Should not see tenant A's data
    const orders = await db('orders').select();
    expect(orders).toHaveLength(0);
  });

  it('cannot insert data for other tenant', async () => {
    await setTenantContext('tenant-a');

    await expect(
      db('orders').insert({ tenant_id: 'tenant-b', amount: 100 })
    ).rejects.toThrow(/violates row-level security/);
  });

  it('superuser role is not used in application', async () => {
    const result = await db.raw('SELECT current_user');
    expect(result.rows[0].current_user).not.toBe('postgres');
  });
});

以非超级用户身份测试

# Create test role
CREATE ROLE test_app_user;
GRANT app_role TO test_app_user;

# Run tests as this role
psql -U test_app_user -d testdb -f tests/rls_tests.sql

RLS 策略产物

实施 RLS 时，将以下产物发布到议题中：

## Row Level Security Implementation

### Tables with RLS Enabled

| Table | ENABLE | FORCE | Policies | Index |
|-------|--------|-------|----------|-------|
| orders | ✅ | ✅ | tenant_isolation | idx_orders_tenant_id |
| order_items | ✅ | ✅ | tenant_isolation | idx_order_items_tenant_id |
| customers | ✅ | ✅ | tenant_isolation | idx_customers_tenant_id |

### Policy Details

| Table | Policy | USING | WITH CHECK |
|-------|--------|-------|------------|
| orders | tenant_isolation | tenant_id = current_tenant() | tenant_id = current_tenant() |

### Security Verification

- [ ] Application connects as non-superuser role
- [ ] All RLS tables have FORCE ROW LEVEL SECURITY
- [ ] All policies have WITH CHECK clause
- [ ] Context uses SET LOCAL (transaction-scoped)
- [ ] Views use security_invoker = true
- [ ] Policy columns are indexed
- [ ] Cross-tenant tests written and passing

### Application Role
- Role name: `app_service`
- BYPASSRLS: `false`
- Superuser: `false`

**Verified At:** [timestamp]

检查清单

完成 RLS 实施前：

• 所有表都设置了 ENABLE 和 FORCE ROW LEVEL SECURITY
• 所有策略都同时包含 USING 和 WITH CHECK
• 应用以非超级用户、非 BYPASSRLS 角色连接
• 在事务内使用 SET LOCAL 设置上下文
• 视图使用 security_invoker = true（PG15+）
• 策略列已建立索引
• 跨租户隔离测试通过
• RLS 产物已发布到议题中

集成

此技能会在以下情况下触发：

• 修改包含 tenant/rls/policy 模式的迁移文件
• 修改与身份验证相关的数据库代码
• 修改多租户架构

此技能与以下内容集成：

• security-review——RLS 是更广泛的安全审查的一部分
• database-architecture——RLS 决策属于架构层面
• local-service-testing——必须针对真实的 Postgres 测试 RLS

参考资料

• PostgreSQL RLS 文档
• 常见的 RLS 陷阱
• RLS 性能优化
• AWS 多租户 RLS