FastAPI Backend Development Bootcamp · 강의

행 수준 보안과 데이터 분할

PostgreSQL 행 수준 보안과 테넌트 범위 쿼리 가드로 강력한 데이터 경계를 적용합니다.

행 수준 보안과 데이터 분할은(는) CoddyKit의 무료 FastAPI Backend Development Bootcamp 강의입니다. 이것은 4개 중 3번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 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.

자주 묻는 질문

“행 수준 보안과 데이터 분할” 강의는 무료인가요?

네 — “행 수준 보안과 데이터 분할” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 FastAPI Backend Development Bootcamp 강의 전체를 잠금 해제할 수 있습니다. FastAPI Backend Development Bootcamp 강의에는 총 4개의 강의가 포함되어 있습니다.

“행 수준 보안과 데이터 분할”에서 뭘 배우나요?

PostgreSQL 행 수준 보안과 테넌트 범위 쿼리 가드로 강력한 데이터 경계를 적용합니다. 브라우저에서 직접 실행하는 실습 코드로 FastAPI Backend Development Bootcamp을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

FastAPI Backend Development Bootcamp을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 FastAPI Backend Development Bootcamp은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 3번째 강의입니다.

“행 수준 보안과 데이터 분할” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 FastAPI Backend Development Bootcamp 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 FastAPI Backend Development Bootcamp 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

← FastAPI Backend Development Bootcamp(으)로 돌아가기