0Pricing
FastAPI Backend Development Bootcamp · 课时

行级安全与数据分区

使用 PostgreSQL 行级安全和租户作用域查询守卫，强制实施严格的数据边界。

行级安全与数据分区 是 CoddyKit 上的免费 FastAPI Backend Development Bootcamp 课时。 这是第 3 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 FastAPI Backend Development Bootcamp 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 FastAPI Backend Development Bootcamp 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

Why Hard Tenant Boundaries Matter

In a multi-tenant SaaS, the most catastrophic bug class is cross-tenant data leakage: tenant A reading tenant B's rows. A single missing WHERE tenant_id = ? in one endpoint is enough to leak everything.

There are three layers of defense:

  • Application guards — every query scopes by tenant_id.
  • Database row-level security (RLS) — PostgreSQL refuses to return rows that don't match a session policy, even if the app forgets.
  • Partitioning — physically separate tenant data for performance and blast-radius control.

This lesson combines all three on FastAPI + PostgreSQL. The golden rule: never trust the application alone. RLS is the safety net that turns a leak into an empty result.

The Shared-Schema Tenant Model

We use the shared-schema model: one set of tables, every row carries a tenant_id column. It is the cheapest to operate and the easiest to migrate, but it has zero isolation by default — isolation is entirely your responsibility.

Every tenant-owned table follows the same shape: a tenant_id foreign key, indexed, and ideally part of a composite primary key so cross-tenant joins are impossible by construction.

from sqlalchemy import Column, BigInteger, String, ForeignKey, Index
from sqlalchemy.orm import declarative_base

Base = declarative_base()


class Invoice(Base):
    __tablename__ = "invoices"

    # Composite PK (tenant_id, id) makes a row globally addressable
    # only WITH its tenant -> no accidental cross-tenant lookups.
    tenant_id = Column(BigInteger, ForeignKey("tenants.id"), primary_key=True)
    id = Column(BigInteger, primary_key=True)
    customer = Column(String, nullable=False)
    amount_cents = Column(BigInteger, nullable=False)

    __table_args__ = (
        Index("ix_invoices_tenant", "tenant_id"),
    )

Resolving the Tenant From the Request

Every request must resolve to exactly one tenant before any query runs. Common sources: a subdomain (acme.app.com), a header (X-Tenant-ID), or — most securely — a claim baked into the authenticated JWT so the client cannot forge it.

Prefer the JWT claim. A header or subdomain is attacker-controlled; a signed token claim is not. Expose the resolved tenant through a FastAPI dependency so every endpoint shares one trusted source.

from fastapi import Depends, HTTPException, Request


async def get_current_tenant(request: Request) -> int:
    # Set earlier by the auth dependency after verifying the JWT signature.
    tenant_id = getattr(request.state, "tenant_id", None)
    if tenant_id is None:
        # Fail closed: no tenant context => refuse, never default to "all".
        raise HTTPException(status_code=401, detail="No tenant context")
    return tenant_id

Application-Level Query Guards

The first layer is disciplined query scoping. Centralize it so no developer has to remember the WHERE tenant_id = ? clause manually. A tenant-scoped session or repository injects the filter automatically.

Here a small repository wraps SQLAlchemy and forces the tenant filter on every read. The point is to make the safe path the default path.

from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession


class InvoiceRepository:
    def __init__(self, session: AsyncSession, tenant_id: int):
        self.session = session
        self.tenant_id = tenant_id

    async def list(self):
        stmt = select(Invoice).where(Invoice.tenant_id == self.tenant_id)
        result = await self.session.execute(stmt)
        return result.scalars().all()

    async def get(self, invoice_id: int):
        stmt = select(Invoice).where(
            Invoice.tenant_id == self.tenant_id,
            Invoice.id == invoice_id,
        )
        return (await self.session.execute(stmt)).scalar_one_or_none()

Why App Guards Alone Are Not Enough

Application guards fail silently. A raw SQL report, a forgotten filter in an admin endpoint, an ORM relationship that loads across tenants, or a junior dev's hotfix — any of these bypasses the repository.

You want a layer that the application cannot forget. That is PostgreSQL Row-Level Security: the database itself attaches an invisible predicate to every query against a protected table. Even SELECT * FROM invoices returns only the current tenant's rows.

Enabling Row-Level Security in PostgreSQL

RLS works by binding policies to a session variable. The app sets app.current_tenant at the start of each request; policies compare each row's tenant_id against it.

Steps: enable RLS on the table, then create a USING policy (controls visibility for reads/updates/deletes) and a WITH CHECK policy (controls what inserts/updates are allowed to write).

-- Run once per protected table (migration)
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;  -- applies even to table owner

CREATE POLICY tenant_isolation ON invoices
    USING (tenant_id = current_setting('app.current_tenant')::bigint)
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::bigint);

Setting the Tenant Context Per Request

For RLS to work, every connection must carry the right app.current_tenant before any query. With a connection pool this is delicate: a pooled connection is reused, so you must set the variable at request start and reset it at request end, otherwise a leaked variable causes cross-tenant reads.

Use set_config(key, value, true) — the true flag makes it transaction-local, so it auto-resets when the transaction ends. This is the safest pattern with pooled connections.

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession


async def scoped_session(session: AsyncSession, tenant_id: int):
    # Transaction-local: third arg `true` ties the setting to the current tx,
    # so it cannot leak to the next request reusing this pooled connection.
    await session.execute(
        text("SELECT set_config('app.current_tenant', :tid, true)"),
        {"tid": str(tenant_id)},
    )
    return session

Wiring RLS Into a FastAPI Dependency

Tie it together: a dependency yields a session that already has the tenant context set inside an open transaction. Endpoints then run normal queries and RLS quietly enforces isolation.

Note the connection must not run as a PostgreSQL superuser or table owner without FORCE ROW LEVEL SECURITY — superusers and owners bypass RLS by default. Use a dedicated, low-privilege application role.

from fastapi import Depends
from sqlalchemy.ext.asyncio import AsyncSession


async def get_tenant_session(
    tenant_id: int = Depends(get_current_tenant),
    session: AsyncSession = Depends(get_session),
):
    async with session.begin():  # one transaction => set_config stays scoped
        await session.execute(
            text("SELECT set_config('app.current_tenant', :tid, true)"),
            {"tid": str(tenant_id)},
        )
        yield session  # every query inside is RLS-filtered automatically

Partitioning by Tenant for Scale

Once you have many tenants, a single giant table hurts: vacuum, index bloat, and noisy-neighbor query plans. Declarative partitioning by tenant_id (LIST or HASH) splits the table into physical partitions.

Benefits: queries that include tenant_id get partition pruning (only the relevant partition is scanned), maintenance runs per-partition, and a tenant offboarding becomes a fast DETACH/DROP instead of a slow bulk delete.

-- Hash partitioning spreads tenants across N partitions evenly
CREATE TABLE invoices (
    tenant_id  bigint NOT NULL,
    id         bigint NOT NULL,
    customer   text   NOT NULL,
    amount_cents bigint NOT NULL,
    PRIMARY KEY (tenant_id, id)
) PARTITION BY HASH (tenant_id);

CREATE TABLE invoices_p0 PARTITION OF invoices
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE invoices_p1 PARTITION OF invoices
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- ... p2, p3

RLS and Partitioning Together

RLS policies on a partitioned parent table are inherited by all partitions automatically in modern PostgreSQL, so you define the policy once on the parent. Partition pruning and the RLS predicate compose: the planner prunes to one partition, then RLS filters rows within it.

Choosing LIST vs HASH:

  • HASH — even distribution, no hotspots, but you can't isolate one big tenant.
  • LIST — pin specific large tenants to dedicated partitions; great for noisy-neighbor isolation and per-tenant backups.

Verifying Isolation in Tests

Isolation must be tested like a security control, not assumed. Write a test that sets tenant A's context, inserts rows, switches to tenant B, and asserts B sees nothing. This pure-Python model demonstrates the exact invariant your RLS policy enforces.

class RLSSimulator:
    """Mimics how a USING policy filters rows by session tenant."""

    def __init__(self):
        self.rows = []
        self.current_tenant = None

    def set_tenant(self, tenant_id):
        self.current_tenant = tenant_id

    def insert(self, row):
        # WITH CHECK: can only write rows for the active tenant.
        if row["tenant_id"] != self.current_tenant:
            raise PermissionError("WITH CHECK violation")
        self.rows.append(row)

    def select_all(self):
        # USING: only rows matching the active tenant are visible.
        return [r for r in self.rows if r["tenant_id"] == self.current_tenant]


db = RLSSimulator()
db.set_tenant(1)
db.insert({"tenant_id": 1, "id": 10, "customer": "Acme"})

db.set_tenant(2)
db.insert({"tenant_id": 2, "id": 11, "customer": "Globex"})

print("Tenant 2 sees:", db.select_all())
db.set_tenant(1)
print("Tenant 1 sees:", db.select_all())

Quick Check: Pooled Connections and RLS

You set app.current_tenant with set_config on a connection drawn from a pool, but you used the session-level form (third argument false) instead of the transaction-local form. What is the most likely production consequence?

Recap: Defense in Depth for Tenant Data

You now have a layered isolation strategy for multi-tenant FastAPI:

  • App guards — a tenant-scoped repository makes the safe query the default; but it can be forgotten.
  • Row-Level Security — ENABLE + FORCE RLS, a USING/WITH CHECK policy keyed on app.current_tenant, and a low-privilege role so the DB cannot return foreign rows.
  • Transaction-local context — set the tenant with set_config(..., true) inside the request transaction so pooled connections never leak.
  • Partitioning — LIST/HASH by tenant_id for pruning, per-tenant maintenance, and fast offboarding; RLS policies inherit to all partitions.

The mindset: treat isolation as a security control with overlapping layers, and verify it with tests. Never trust the application alone.

常见问题解答

「行级安全与数据分区」课时是免费的吗？

是的 — 「行级安全与数据分区」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 FastAPI Backend Development Bootcamp 课程的其余内容，请升级到 CoddyKit PRO。 FastAPI Backend Development Bootcamp 课程共包含 4 节课。

「行级安全与数据分区」这节课中我会学到什么？

使用 PostgreSQL 行级安全和租户作用域查询守卫，强制实施严格的数据边界。 你通过在浏览器中直接运行的动手代码来练习 FastAPI Backend Development Bootcamp，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 FastAPI Backend Development Bootcamp 需要有经验吗？

无需任何先前经验。CoddyKit 上的 FastAPI Backend Development Bootcamp 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。

「行级安全与数据分区」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 FastAPI Backend Development Bootcamp 课中编写并运行代码吗？

能。每节 FastAPI Backend Development Bootcamp 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 租户隔离策略与权衡
  2. 租户上下文解析与中间件
  3. 行级安全与数据分区
  4. 用量计量、配额与计费钩子
← 返回 FastAPI Backend Development Bootcamp