Loading challenges
Nobody has started this yet — be first.
Business impact
POST /api/marketing/login -- the apex-domain global login every user hits before they have picked a tenant subdomain -- currently returns a 500 for any user who belongs to at least one tenant, instead of the list of tenants they can access. It is worse than it first looks: the identical crash also hits POST /api/auth/login, the ordinary tenant-subdomain login every user depends on for everyday access. Once this bug is present, logging in anywhere in the product, by anyone, is broken. This is a live example of exactly the class of RLS/connection- pooling gotcha this whole project exists to teach and guard against, reappearing because a single NULLIF(...) got dropped from the policy SQL.
Problem
Every RLS policy in this migration compares tenant_id/user_id against a raw current_setting('app.current_tenant_id', true)::uuid expression with the NULLIF(...) wrapper removed, so a session with nothing legitimately bound raises a database error instead of comparing as unmatched.
Current behavior
The first time any transaction on a pooled connection sets (or ) to anything at all, Postgres registers that custom GUC for the connection's remaining lifetime with a reset value of (empty string), not . Without guarding the cast, raises , uncaught, on every later transaction on that connection that legitimately has nothing bound -- and because Postgres does not skip evaluating the right-hand side of an OR once the left side already matched, (user id bound, no tenant id) and (tenant id bound, no user id yet -- the session does not exist until after this check succeeds) both crash on the memberships lookup.
Expected behavior
A request with no tenant bound querying a tenant-scoped table returns zero rows (or, for memberships, only rows matching the bound app.current_user_id, per its self-lookup policy) -- it never raises a database-level type error, regardless of what any earlier transaction on the same pooled connection happened to set.
Steps to reproduce
Why this matters
This is the exact bug documented at length in app/rls.py's module docstring and in README.md's "sharper-edged gotcha" section -- this ticket is that bug, reintroduced by deleting the one thing that guards against it. The migration's own docstring still describes the NULLIF wrapper in detail; the code below it no longer has one. A comment that used to be true does not stay true just because nobody updated it when the code changed -- trust what the SQL actually says.
Suggested approach
Compare the TENANT_ID_EXPR/USER_ID_EXPR constants near the top of this migration against what the migration's own docstring says they should be. After changing them, remember that Alembic migrations are not re-run automatically just because the file changed on disk -- re-apply the migration (flask db downgrade then flask db upgrade, or drop and recreate the test database) for the fix to take effect in any database you are testing against.
Acceptance criteria
Verification
pytest practice_tickets/tests/test_ticket_12_rls_nullif_regression.py -v
Hints (0/2)
Try it without hints first — the reading is the exercise.
Working on this ticket
Work on a branch named for the ticket — that's what you'll submit.
Branch off your fork
Sign in to create it automatically, or do it yourself:
$git checkout -b fix/rls-nullif-regressionFix it and commit
Meet every acceptance criterion, and add a test that would have caught this.
Push the branch
$git push -u origin fix/rls-nullif-regressionSubmit it below
Paste your fork URL and the branch name, with a short write-up of the root cause.
Questions
Ask about anything unclear in the ticket — the maintainer and anyone who has solved it can answer. Please don't post full solutions.
Sign in to ask a question or reply.
Sign inapp.current_tenant_idapp.current_user_id''NULLNULLIF(current_setting(...), '')''::uuidInvalidTextRepresentationPOST /api/marketing/loginPOST /api/auth/loginacmeglobexmake seed
*.localhost subdomains resolve to 127.0.0.1 out of the box in curl and every modern browser -- no /etc/hosts editing needed:
curl -s -X POST http://acme.localhost:5000/api/auth/login \
-H "Content-Type: application/json" \
--cookie-jar /tmp/acme.cookies --cookie /tmp/acme.cookies \
-d '{"email": "owner@acme.test", "password": "owner-acme"}'
curl -s http://acme.localhost:5000/api/tenant/settings --cookie /tmp/acme.cookies
For tooling that cannot fake subdomains (some HTTP clients, CI), send X-Tenant-Slug instead -- honored only when TRUST_TENANT_HEADER=true (the Docker dev setup enables it; production does not):
curl -s http://localhost:5000/api/tenant/settings -H "X-Tenant-Slug: acme"
You do not strictly need Docker to work the tickets, but -- unlike a project that can fake its backing services -- this one genuinely needs a real Postgres and Redis, because the whole point is proving isolation against the real database, not a mock. Without Docker: cp .env.example .env, point DATABASE_URL/ADMIN_DATABASE_URL/TEST_DATABASE_URL/TEST_ADMIN_DATABASE_URL/REDIS_URL at a local Postgres/Redis (docker/init-db.sql documents the exact roles/databases the app expects -- notably the saas_admin/BYPASSRLS role migrations need and the plain saas role every request uses), then:
python3 -m venv .venv && .venv/bin/pip install -r requirements-dev.txt
.venv/bin/flask db upgrade
.venv/bin/pytest practice_tickets/tests/test_ticket_07_webhook_signature_raw_body.py -v # a single ticket
./practice_tickets/run_tickets.sh # all 15, clean pass/fail summary
./practice_tickets/run_tickets.sh 07 12 13 # just a few, by number
All 15 tests fail at the start; as you fix each ticket in app/ (or, for TICKET-12, in migrations/versions/0002_row_level_security.py, plus re-applying it), its test -- and only its test -- should flip to passing. make test runs the project's own main suite; several of its tests (RBAC, tenant-spoofing, and billing-webhook tests among them) fail at the start for the same underlying reasons as specific tickets -- fixing those tickets fixes those failures too, not a separate task.
Level 1
Fix a bug
Read existing behaviour, correct it.