多租户数据隔离（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`绑定并可追踪。