多租户数据隔离（RLS与策略引擎）最佳实践

一、租户身份与令牌type Token = { sub: string; tenantId: string; scope: string[] } function hasScope(tok: Token, need: string) { return tok.scope.includes(need) } 二、策略映射与守卫type Policy = { route: string; method: string; scope: string } const policies: Policy[] = [ { route: '/orders', method: 'GET', scope: 'read:orders' }, { route: '/orders', method: 'POST', scope: 'write:orders' } ] function allowed(tok: Token, route: string, method: string): boolean { const p = policies.find(x => x.route === route && x.method === method) return !!p && hasScope(tok, p.scope) } type Req = { token: Token; path: string; method: string } type Res = { status: (n: number) => Res; end: (b?: string) => void } function guardRoute(req: Req, res: Res, next: Function) { if (!allowed(req.token, req.path, req.method)) return res.status(403).end('forbidden') next() } 三、参数化过滤器与查询构造type Q = { sql: string; params: any[] } function tenantFilter(tenantId: string): Q { return { sql: 'tenant_id = $1', params: [tenantId] } } function buildQuery(base: string, filter: Q, extra?: Q): Q { const parts = [filter.sql] const params = [...filter.params] if (extra) { parts.push(extra.sql); params.push(...extra.params) } const sql = `${base} WHERE ${parts.join(' AND ')} LIMIT 100` return { sql, params } } function listOrders(tok: Token): Q { return buildQuery('SELECT * FROM orders', tenantFilter(tok.tenantId)) } 四、写操作与幂等function createOrder(tok: Token, payload: { id: string; amount: number }): Q { const base = 'INSERT INTO orders(id, tenant_id, amount) VALUES($1,$2,$3)' return { sql: base, params: [payload.id, tok.tenantId, payload.amount] } } 五、验收清单路由与方法需匹配Scope；未授权拒绝并审计。查询始终包含`tenant_id`参数化过滤；限制`LIMIT`与额外条件参数化，防注入。写操作自动填充`tenant_id`；所有操作与`tenantId`绑定并可追踪。