Auth gate + RLS on the six app tables (the Dad gate) (#21)
* Auth gate + service-role DB access (RLS migration staged, not yet applied) Two holes, both verified live on 2026-08-19 before any change:
- Auth gate + service-role DB access (RLS migration staged, not yet applied)
Two holes, both verified live on 2026-08-19 before any change:
- No app authentication. /api/holdings returned 200 with the real holdings
JSON to an unauthenticated caller; /positions, /sell, /paper-trades, /api/positions, /api/copilot and /api/paper-trades all served data the same way, over plain HTTP as well as HTTPS.
- RLS was off on all six of this app's tables with zero policies, and the anon
key was shipped as a NEXT_PUBLIC_* build arg. Demonstrated with that public key: INSERT into public.trades -> 201, DELETE -> 204. Anyone could inject a fake position, delete a real one, or write monitor heartbeats. The probe row was removed and its absence verified.
Auth is a default-deny proxy.ts in front of every route, with a signed HttpOnly session cookie. Supabase Auth was not worth its complexity for two people who will never self-serve a signup; the whole mechanism is ~120 lines of Web Crypto with no runtime dependency, and it fails closed when SESSION_SECRET is missing. Carve-outs are /api/cron/* (bearer, already correct) and the login surface.
The database client now authenticates as service_role from a runtime env var. service_role has BYPASSRLS on this instance, so the RLS migration needs no policy bodies at all. Nothing reads Supabase from the browser -- the two client components that mention it import row types, which are erased at compile time -- so the anon key is gone from the image entirely rather than kept alive behind anon-read policies.
migrations/004_rls_six_tables.sql is committed but deliberately NOT applied yet. Applying it before every consumer holds the service-role key would turn all 16 crons red at once; the ordering is written into the migration's header.
Three things this caught locally that would have been outages in production: - nextUrl does not carry the Host header, so an unauthenticated user would have been redirected to http://127.0.0.1:3111/login. Redirects are built from the Host header instead. - a relative Location makes Next's proxy layer throw ERR_INVALID_URL and 500 every protected page. - next start sets x-forwarded-proto: http itself, so an unguarded HTTPS force breaks local development. Loopback is carved out.
Verified: next build green; 341 pytest pass; 16/16 auth primitive checks; 34/34 end-to-end gate checks against a local build, including forged-cookie rejection, rate limiting (10 failures then 429) and the cron bearer path.
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
- Security review fixes: open redirect, CSRF, rate-limit bypass, deploy check
A security-review subagent went over the auth diff adversarially. It could not break the session gate itself (34 bypass attempts -- traversal, encoded traversal, double slashes, case, RSC and /_next/data paths -- all denied), but it found real defects around it. Every one below was reproduced before fixing and is now covered by a test.
BLOCKER -- the deploy verification would have failed this very deploy. The old loop slept 10s, then asserted the auth gate the first time a "checks" body appeared. At t=10s Coolify is still pulling, so the PREVIOUS container answers: it already serves that body and has no auth gate, so the gate assertion saw 200 and exit 1'd. CI would have gone red and fired "production may be serving a broken build" on a perfectly good deploy. Worse generally: nothing in the check proved the NEW image was serving, so any future deploy could pass entirely against the old container. Both conditions must now hold in the same iteration and neither exits the loop early, so it simply waits for the new image. A 200 on unauthenticated /api/positions is now called out as a live data leak rather than a generic failure.
HIGH -- open redirect on the login page. safeNext() rejected //evil.com but browsers normalise a backslash to a slash in the authority position and strip raw TAB/LF/CR before parsing. Demonstrated: /\evil.com, /<TAB>/evil.com and /<LF>/evil.com all survived the old check and resolve to https://evil.com/. That is the highest-value redirect available here -- send Dad a link on the real domain, he sees the real login page, types the real password, lands on a clone. Replaced with origin comparison via the URL parser. Nine cases added to check_auth.mjs, and the old implementation was re-run against them to confirm the tests are not vacuous.
HIGH -- CSRF from a sibling subdomain. SameSite=Lax is scoped to the site (imprevista.com), not the origin, so any other *.imprevista.com app is same-site. request.json() parses a text/plain body, and text/plain is a CORS simple request -- no preflight -- so a cross-origin POST would have landed a fake trade. proxy.ts now requires a matching Origin on authenticated state-changing requests. Kept Lax rather than Strict deliberately: Strict would drop the cookie when Dad opens a link from a text message.
MEDIUM -- rate limiter was bypassable AND a lockout weapon. Traefik appends to X-Forwarded-For, so xff.split(',')[0] was attacker-controlled: 8 failures with a rotating spoofed IP never tripped the limiter, and spoofing a victim's IP locked that victim out using their correct password. Now keyed on the LAST hop, which is the one the proxy appended -- and which is also correct if a proxy replaces rather than appends. Kept per-address rather than global: a global counter is unspoofable too, but hands any stranger a way to lock both users out of the tool for 15 minutes, and that bites hardest exactly when a position needs managing. Added sweeping and a key ceiling; the old Map never pruned.
MEDIUM -- no session revocation. A valid signature was enough, so deleting AUTH_PASSWORD_DAD did not log Dad out; his cookie stayed good for 30 days and the only lever was rotating SESSION_SECRET, which signs everyone out. proxy.ts now also requires the account to still be configured.
MEDIUM -- cron secret accepted as ?secret=, which puts it in Traefik logs, container logs, browser history and Referer. Verified no consumer used it (the workflows, both Hetzner scripts, Kuma and the Cloudflare worker all send the header), so it is gone. Compare is now constant-time.
MEDIUM -- /login had no trailing slash in PUBLIC_PREFIXES, so startsWith made /login* blanket-public. Harmless today, silent leak the day someone adds /login-history. Exact matches and prefixes are now separate.
Also: added /how-it-works, /api/status and /api/graveyard as deliberate public paths (Charles's call -- an evidence page that requires a login is a contradiction, and it exposes only liveness timestamps and hypothesis verdicts).
Migration hardening, all verified against the live instance first: REVOKE now includes PUBLIC; confirmed zero views/matviews are built on the six tables and that none of the six SECURITY DEFINER functions in public reference them (both would have routed around RLS); recorded that anon currently holds full DELETE/INSERT/UPDATE on all six, so the REVOKE is load-bearing, not decoration.
Recorded but NOT fixed, because it is outside this session's scope: this app also writes predictions, iv_snapshots, overrides, signal_graveyard and four more tables that keep full anon grants. Those feed the recommendation engine, so it is an integrity exposure on trade inputs and needs its own scoped pass.
Redacted the cron secret literal from tasks/todo.md. Writing it there published it -- this repo is public. Options' value is rotated; DayScore's and PLY's were exposed on the same line and are NOT.
Verified: next build green; 341 pytest pass; 25/25 auth primitive checks; 41/41 end-to-end gate checks including the three CSRF variants, the XFF spoof, cross-user lockout, and /loginfoo. Confirmed the app's own writes still pass the Origin check (POST /api/holdings 201, DELETE 204); test rows removed and the 7 real holdings rows verified intact.
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
Co-authored-by: Claude Opus 5 (1M context) <noreply@anthropic.com>