Back to Details

postgresql

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

PostgreSQL

PostgreSQL

When to use

适用场景

  • Designing or changing multi-tenant tables with Row-Level Security (RLS)
  • Debugging tenant isolation issues
  • Adding/changing Alembic migrations for schema, RLS policies, or indexes
  • Writing tests that validate RLS isolation
  • Configuring PostgreSQL authentication, replication, or tuning
  • 设计或修改带有行级安全（RLS）的多租户表
  • 调试租户隔离问题
  • 为schema、RLS策略或索引添加/修改Alembic迁移
  • 编写验证RLS隔离性的测试
  • 配置PostgreSQL认证、复制或调优

RLS Multi-tenancy Pattern

RLS多租户模式

Non-negotiables

必须遵循的规则

  • RLS context is mandatory for any tenant-scoped query
  • Context must be set inside the same transaction as the queries
  • No fallbacks for tenant ID (fail fast if missing)
  • Async-only DB access when using async frameworks
  • 必须设置RLS上下文：所有租户范围的查询都需要设置
  • 上下文必须与查询在同一事务内设置
  • 租户ID无fallback方案：如果缺失则直接失败
  • 异步框架下仅使用异步数据库访问

Setting RLS Context

设置RLS上下文

RLS works only if the current transaction has the context set:
sql
SET LOCAL app.current_tenant_id = '<tenant_uuid>';
Must run before the first tenant-scoped query in that transaction.
RLS仅在当前事务已设置上下文时生效：
sql
SET LOCAL app.current_tenant_id = '<tenant_uuid>';
必须在该事务中的第一个租户范围查询之前执行此操作。

Common Failure Modes

常见失败场景

  • Setting 
    SET LOCAL ...
    after the first 
    select()
  • Setting the context in one session, then querying in another
  • Running queries outside the expected transaction scope
  • 在第一个
    select()
    之后设置
    SET LOCAL ...
  • 在一个会话中设置上下文，却在另一个会话中查询
  • 在预期的事务范围外执行查询

Typical RLS Policy

典型RLS策略

sql
ALTER TABLE some_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY some_table_tenant_isolation
ON some_table
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
sql
ALTER TABLE some_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY some_table_tenant_isolation
ON some_table
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

Multi-tenant Table Checklist

多租户表检查清单

  • Tenant ID column is UUID
  • FK to tenants table with 
    ON DELETE CASCADE
  • Indexes aligned with access patterns (usually tenant_id first)
    • PostgreSQL does not auto-index FK columns — add explicit indexes
    • UNIQUE allows multiple NULLs unless using 
      NULLS NOT DISTINCT
      (PG15+)
  • RLS is enabled and policies exist
  • Application code sets RLS context at transaction start
  • 租户ID列类型为UUID
  • 与租户表建立外键关联，并设置
    ON DELETE CASCADE
  • 索引与访问模式匹配（通常以tenant_id作为首列）
    • PostgreSQL不会自动为外键列创建索引——需手动添加显式索引
    • 除非使用
      NULLS NOT DISTINCT
      （PG15+版本），否则UNIQUE约束允许多个NULL值
  • 已启用RLS并配置相应策略
  • 应用代码在事务开始时设置RLS上下文

Alembic Migrations Checklist

Alembic迁移检查清单

  1. Add/modify schema (columns, constraints, FKs)
  2. Create/update indexes
  3. Enable RLS and create/adjust policies
  4. Add verification (tests) for isolation
  5. Provide a real downgrade (no stubs)
  1. 添加/修改schema（列、约束、外键）
  2. 创建/更新索引
  3. 启用RLS并创建/调整策略
  4. 添加隔离性验证（测试）
  5. 提供真实的回滚方案（不允许使用占位符）

RLS Isolation Testing Recipe

RLS隔离性测试方案

—

测试目标

Goal:
  • Data for tenant A is visible to tenant A
  • Data for tenant A is NOT visible to tenant B
Canonical flow:
  1. Setup data through an admin session (RLS bypass) for tenant A and B
  2. Assert via an RLS session:
    • set context to tenant A → sees only tenant A data
    • set context to tenant B → does not see tenant A data
  • 租户A的数据仅对租户A可见
  • 租户A的数据对租户B不可见

Destructive Operations Safety

标准流程

Hard rules:
  • Never run 
    DELETE
    without a narrow 
    WHERE
    targeting specific data
  • Never run 
    TRUNCATE
    /
    DROP
    without explicit confirmation
Pre-flight before destructive actions:
  1. Confirm exact target (tables / IDs / date range)
  2. Run a 
    SELECT
    /row count first and show results
  3. Ask for final confirmation, then execute
  1. 通过管理员会话（绕过RLS）为租户A和B准备测试数据
  2. 通过RLS会话验证：
    • 设置上下文为租户A → 仅能看到租户A的数据
    • 设置上下文为租户B → 无法看到租户A的数据

References

破坏性操作安全规范

Schema & Design

硬性规则

  • table-design.md — Data types, constraints, indexing, partitioning, JSONB, safe schema evolution
  • charset-encoding.md — Character sets, encoding, collation, ICU, locale settings
  • 禁止在没有精准
    WHERE
    条件定位特定数据的情况下执行
    DELETE
  • 禁止在没有显式确认的情况下执行
    TRUNCATE
    /
    DROP

Authentication

破坏性操作前的预检步骤

  • authentication.md — pg_hba.conf, SCRAM-SHA-256, md5, peer, cert, LDAP, GSSAPI
  • authentication-oauth.md — OAuth 2.0 (PostgreSQL 18+), SASL OAUTHBEARER, validators
  • user-management.md — CREATE/ALTER/DROP ROLE, membership, GRANT/REVOKE, predefined roles
  1. 确认精准的操作目标（表/ID/日期范围）
  2. 先执行
    SELECT
    /行计数并查看结果
  3. 请求最终确认后再执行操作

Runtime Configuration

参考资料

—

Schema与设计

  • connection-settings.md — listen_addresses, max_connections, SSL, TCP keepalives
  • query-tuning.md — Planner settings, work_mem, parallel query, cost constants
  • replication.md — Streaming replication, WAL, synchronous commit, logical replication
  • vacuum.md — Autovacuum, vacuum cost model, freeze ages, per-table tuning
  • error-handling.md — exit_on_error, restart_after_crash, data_sync_retry
  • table-design.md — 数据类型、约束、索引、分区、JSONB、安全的schema演进
  • charset-encoding.md — 字符集、编码、排序规则、ICU、区域设置

Internals

认证

  • internals.md — Query processing pipeline, parser/rewriter/planner/executor, system catalogs, wire protocol, access methods
  • protocol.md — Wire protocol v3.2: message format, startup, auth, query, COPY, replication
  • authentication.md — pg_hba.conf、SCRAM-SHA-256、md5、peer、cert、LDAP、GSSAPI
  • authentication-oauth.md — OAuth 2.0（PostgreSQL 18+）、SASL OAUTHBEARER、验证器
  • user-management.md — CREATE/ALTER/DROP ROLE、角色成员、GRANT/REVOKE、预定义角色

See also

运行时配置

  • sql-expert — Query patterns, EXPLAIN workflow, optimization
  • connection-settings.md — listen_addresses、max_connections、SSL、TCP keepalives
  • query-tuning.md — 规划器设置、work_mem、并行查询、成本常数
  • replication.md — 流复制、WAL、同步提交、逻辑复制
  • vacuum.md — 自动清理、清理成本模型、冻结年龄、按表调优
  • error-handling.md — exit_on_error、restart_after_crash、data_sync_retry
—

内部实现

—
  • internals.md — 查询处理流水线、解析器/重写器/规划器/执行器、系统目录、有线协议、访问方法
  • protocol.md — 有线协议v3.2：消息格式、启动、认证、查询、COPY、复制
—

相关链接

—
  • sql-expert — 查询模式、EXPLAIN工作流、优化