Lesson 9 · Row-Level Security & the whole chain

RLS & multi-tenancy

The last gate — enforced by the database itself. Every row carries a tenant, and Postgres refuses to hand you another tenant's rows.

Your win: explain how resource_path travels from the JWT into a Postgres session variable, and how one function — permission_check — makes tenant isolation automatic on every query, even a raw one.

The idea: the database enforces tenancy

Tiers 1 and 2 (Parts 1–2) all ran in Go — the interceptor, the role gate, the service check. They work only if the application remembers to check. Row-Level Security is different: it's enforced by Postgres, on the table, so even a hand-written SELECT * FROM users returns only rows the caller is allowed to see.1 Here, the first thing RLS enforces is the tenant: your organization (resource_path).

Step 1 — the JWT's tenant becomes a session variable

Recall resource_path from the Manabie claim (Lesson 2). When the pgx pool hands a connection to a request, a BeforeAcquire hook stamps that claim onto the connection as a Postgres session variable — every acquire:

internal/golibs/database/pool_gcp.go:63,161 (setPostgres, condensed)
claims := interceptors.JWTClaimsFromContext(ctx)     // the verified JWT (from the interceptor)
conn.QueryRow(ctx,
  "SELECT set_config('permission.resource_path', $1, false)," +
  "       set_config('permission.user_group',   $2, false)," +
  "       set_config('app.user_id',            $3, false)",
  claims.Manabie.ResourcePath, claims.Manabie.UserGroup, claims.Manabie.UserID)
// verifies the echo; returns FALSE (refuses the connection) on any mismatch
Why on every acquire? Connections are pooled and reused across requests — and tenants. If the variable weren't reset per checkout, request B could inherit request A's tenant. So it's re-stamped from the current request's JWT every time, and the connection is refused if the values don't take. (This is the pooler gotcha every RLS guide warns about — solved here by BeforeAcquire.)2

Step 2 — permission_check filters every row

Now the database has the tenant in a session variable. Two tiny SQL functions turn that into enforcement:

migrations/auth/1001_migrate.up.sql:1,14
FUNCTION autofillresourcepath()  →  current_setting('permission.resource_path')   -- for INSERTs
FUNCTION permission_check(rp, t) →  ( rp = current_setting('permission.resource_path') )  -- the tenant test

And every tenant-scoped table gets a policy built from permission_check, plus ENABLE and FORCE ROW LEVEL SECURITY:

migrations/bob/1182_migrate.up.sql:12-14 (the pattern, repeated for every table)
CREATE POLICY rls_permission ON permission
    USING      ( permission_check(resource_path, 'permission') )   -- which rows I can READ
    WITH CHECK ( permission_check(resource_path, 'permission') );  -- which rows I can WRITE
ALTER TABLE permission ENABLE ROW LEVEL security;
ALTER TABLE permission FORCE  ROW LEVEL security;
Three pieces, and each matters USING filters reads — a row is visible only if its resource_path equals the session's tenant. WITH CHECK filters writes — you can't insert or update a row into another tenant. FORCE ROW LEVEL SECURITY is the crucial one: by default Postgres lets the table owner bypass RLS entirely — the single most common RLS footgun. FORCE binds even the owner, so the application's own DB user can't escape tenancy.2

And autofillresourcepath() is the DEFAULT for every resource_path column (you saw it on users in Lesson… well, in the migration): a new row auto-stamps the current tenant, so a developer can't forget to set it, and WITH CHECK would reject it anyway if it were wrong.

The golden rule, enforced resource_path is never taken from the request — only from the JWT (Lesson 2). RLS is what makes that rule unbreakable: even if a handler tried to query another tenant's data, Postgres wouldn't return it, because the session variable came from the verified token, not from anything the caller controls. Tenant isolation you can't code your way around.
Read this next

Postgres Row-Level Security for multi-tenancy

The mechanism itself, and the two gotchas this design handles — pooled-connection context and the owner bypass.

→ postgresql.org — Row Security Policies
→ AWS — multi-tenant RLS · Crunchy Data — RLS for tenants

Check yourself (from memory)

Q1. How does the tenant (resource_path) get into the database session?

setPostgres runs set_config(...) from JWTClaimsFromContext per acquire, refusing the connection on mismatch.

Q2. What does permission_check(resource_path, 'permission') evaluate?

It's the tenant test: the row's resource_path must equal the session's. That single equality is the isolation core.

Q3. Why does the schema use FORCE ROW LEVEL SECURITY, not just ENABLE?

By default the owner bypasses RLS — the classic footgun. The app's own DB user often is the owner, so FORCE is essential.
Recall: how RLS enforces tenant isolation.
the bridge + the functions + the gotchas, then reveal
Bridge: resource_path from the JWT → setPostgres BeforeAcquire (pool_gcp.go:63,161) runs set_config('permission.resource_path'/'permission.user_group'/'app.user_id', …) per connection acquire (refuses on mismatch — solves the pooler gotcha). Functions: permission_check(rp,t) = rp = current_setting('permission.resource_path') (tenant test); autofillresourcepath() = DEFAULT for resource_path cols (auto-stamp on insert). Policy: CREATE POLICY rls_<t> USING (permission_check(...)) WITH CHECK (permission_check(...)) — USING filters reads, WITH CHECK filters writes — plus ENABLE + FORCE ROW LEVEL SECURITY (binds the owner too — the #1 RLS footgun). resource_path is never from the request; RLS makes that unbreakable.
🎯 Interview one-liner "How do you guarantee tenant isolation?" → "Postgres row-level security. A pool hook stamps the tenant from the verified JWT onto each connection as a session variable; every table's policy checks the row's resource_path against it via permission_check. We FORCE RLS so even the table owner is bound, and default the column from the session so inserts auto-stamp. The tenant is never client-supplied — so it can't be coded around."
Next: RLS does more than tenant — some tables also check your granular permission on the row's location, mirroring Lesson 8 in the database. That's where app.user_id earns its keep. Ask me about the pooler or owner gotchas if they're new.

1. Postgres RLS. In-repo: migrations/auth/1001_migrate.up.sql:1,14, migrations/bob/1182_migrate.up.sql:12-14.

2. Pooler + owner gotchas: AWS multi-tenant RLS. In-repo: internal/golibs/database/pool_gcp.go:63,161-206.