Mixed Self-Check

How to use this self-check

Read each scenario, decide your answer and one reason before reading the walkthrough. If you cannot give a reason, revisit the named section. Being right for the wrong reason does not survive a follow-up question.

Scenario walkthroughs

Scenario 1: background jobs occasionally write data into the wrong tenant. Likely cause: the job message lacks tenant_id, or a worker thread reuses context from a previous job. Fix: put the tenant in every message and set or clear context per job (section 3). Scenario 2: after enabling RLS, developers still see all tenants in tests. Likely cause: tests connect as the table owner or a superuser. Fix: test as the restricted application role and add FORCE ROW LEVEL SECURITY (section 5). Scenario 3: a schema migration fails on 3 of 400 tenant databases. Fix: the wave rollout halts, the catalogue shows which tenants are on which version, the data issue is repaired, and the rollout resumes (section 7). Scenario 4: one tenant's nightly import slows every customer's dashboard. Fix: per-tenant limits and fair queues now, a replica or dedicated shard later (section 6).

Spot the bug

This handler runs in a pooled PostgreSQL setup. Identify the isolation bug before reading the self-check question.

def list_invoices(conn, tenant_id):
    with conn.cursor() as cur:
        cur.execute("SELECT set_config('app.tenant_id', %s, false)", (tenant_id,))
        cur.execute("SELECT id, total FROM invoices")
        return cur.fetchall()

Quick check: In the snippet above, the connection comes from a pool and the table uses RLS keyed on `app.tenant_id`. What is the main problem?

• RLS cannot read custom settings
• SELECT statements are never filtered by RLS
• With is_local set to false the setting lasts for the whole session, so a later request on the pooled connection that forgets to set the tenant runs with this tenant's id
• Python cannot pass parameters to SQL