Chris van Eijk：PostgreSQL 中按租户唯一：软删除与 RLS 泄露
内容提要
多租户 PostgreSQL 中，全局唯一约束会跨租户泄露数据：租户 2 插入已属租户 1 的邮箱会报重复键错误，即使启用了行级安全。修复方法是将 tenant_id 加入约束。软删除仍占用唯一值，需使用部分唯一索引（WHERE deleted_at IS NULL）并配合 lower() 忽略大小写。ON CONFLICT 必须匹配索引条件。NULL 默认互不相等，可用 NULLS NOT DISTINCT 改变。
延伸解读
全局唯一约束的跨租户泄露风险
在启用行级安全（RLS）的多租户数据库中，全局唯一约束（如unique(email)）会绕过RLS检查，导致租户2插入已属租户1的邮箱时收到重复键错误，即使租户2看不到该行。这泄露了其他租户的数据存在性。修复方法是将tenant_id加入约束，如unique(tenant_id, email)，这样租户间互不干扰。若必须全局唯一（如登录邮箱），应放在非租户数据表中，并明确产品层面的冲突处理。
软删除下的唯一性处理
软删除的行仍占用唯一值，导致已删除客户的邮箱无法被重新添加。解决方案是创建部分唯一索引，仅覆盖未删除行，例如：create unique index on customers (tenant_id, lower(email)) where deleted_at is null。同时用lower()忽略大小写，避免Anna@Example.nl与anna@example.nl冲突。但恢复已删除行时，若存在活跃重复，数据库会拒绝，产品需决定合并或拒绝，并避免向用户暴露原始错误。
ON CONFLICT与部分索引的匹配
使用部分唯一索引时，ON CONFLICT子句必须包含索引的谓词条件，否则会报错“no unique or exclusion constraint matching the ON CONFLICT specification”。例如，对于上述部分索引，应写为on conflict (tenant_id, lower(email)) where deleted_at is null do nothing。若沿用旧的on conflict (tenant_id, email)，在替换约束后导入操作会立即失败。因此，迁
NULL值唯一性与NULLS NOT DISTINCT
默认情况下，PostgreSQL将每个NULL视为不同值，因此unique(tenant_id, external_ref)允许同一租户的多行NULL。从PostgreSQL 15起，可使用NULLS NOT DISTINCT使NULL也参与唯一性检查，从而拒绝第二个NULL。选择取决于业务语义：若“未关联”状态应允许多次，则保持默认；若必须唯一，则显式声明NULLS NOT DISTINCT。需按列逐一评估，避免意外约束。
Q&A
在启用行级安全（RLS）的多租户 PostgreSQL 中，全局唯一约束会导致跨租户数据泄露吗？
会。如果唯一约束是全局的（如 unique(email)），租户 2 插入一个已属于租户 1 的邮箱时，即使租户 2 看不到该行，也会收到重复键错误（ERROR: duplicate key value violates unique constraint）。使用 ON CONFLICT DO NOTHING 时虽无错误，但返回 INSERT 0 0 而非 INSERT 0 1，同样泄露了该地址在其他租户已存在的信息。PostgreSQL 文档明确指出，唯一约束等引用完整性检查总是绕过行安全。
如何修复多租户 PostgreSQL 中唯一约束跨租户泄露的问题？
将 tenant_id 加入唯一约束，例如 unique(tenant_id, email)。这样租户 2 可以添加相同邮箱而不会冲突，也不会泄露租户 1 的任何信息。如果某个约束确实需要全局唯一（如跨租户共享的登录地址），应将其放在非租户数据表中，此时“地址已被使用”的消息应作为产品决策而非意外泄露。
软删除后，唯一约束还会阻止重新添加相同值吗？如何解决？
会。软删除的行仍占用唯一值，导致重新添加相同地址时出现重复键错误。解决方案是使用部分唯一索引，只覆盖未删除的行，例如：create unique index customers_email_live on customers (tenant_id, lower(email)) where deleted_at is null; 这样已删除的行不再计入唯一性检查。同时需考虑恢复已删除行时若存在活跃重复项，数据库会拒绝恢复，产品需决定是合并还是拒绝。
在部分唯一索引上使用 ON CONFLICT 时为什么报错？如何正确写？
因为 ON CONFLICT 的冲突目标必须包含索引的条件（谓词）。例如，对于部分索引 create unique index ... where deleted_at is null，必须写成 on conflict (tenant_id, lower(email)) where deleted_at is null do nothing。如果只写 on conflict (tenant_id, lower(email))，会报错：there is no unique or exclusion constraint matching the ON CONFLICT specification。
PostgreSQL 中 NULL 值在唯一约束下默认是唯一的吗？如何改变？
默认情况下，PostgreSQL 将每个 NULL 视为互不相同，因此唯一约束允许多个 NULL 值。从 PostgreSQL 15 开始，可以使用 NULLS NOT DISTINCT 改变此行为，例如：create unique index ... on customers (tenant_id, external_ref) nulls not distinct。这样第二个 NULL 会被拒绝。具体选择取决于列语义，通常“尚未关联”应允许多次出现。
多租户 PostgreSQL 中设计唯一约束的检查清单有哪些？
1. 每个租户数据的唯一约束都应包含 tenant_id（最好放在首位）。2. 软删除时使用部分唯一索引（where deleted_at is null），并决定恢复已删除行时如何处理活跃重复项。3. 使用 lower() 在索引中忽略大小写，但注意 lower() 不是 leakproof，在 RLS 下通过它查找无法使用该索引。4. 对于部分索引，在 ON CONFLICT 中命名谓词。5. 对可空列决定是否使用 NULLS NOT DISTINCT。
