การรักษาความปลอดภัยระดับแถวและการแบ่งพาร์ทิชันข้อมูล

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.

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.

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.

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.

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.

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).

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.

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.

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.

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.

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.

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.