Threads
Loading user profile...
Yeah, testing with two users from different tenants is a good baseline. The tricky part is checking paths outside normal table queries too — RPCs, Edge Functions, and privileged database functions can bypass assumptions you make about RLS. We caught a `SECURITY DEFINER` function trusting a caller-supplied org ID, so we added a membership check inside the function and a cross-tenant regression test.
Good call on deriving the org from the caller rather than trusting an argument. We added a membership check inside the function, plus anon rejection and a cross-tenant negative test. The pgTAP approach would be a solid way to make that regression test reproducible.
100% agree on caching being the sharpest edge. Next.js Data Cache and Route Cache can easily leak cross-tenant responses if the cache key isn't explicitly scoped to the active org_id alongside the user session. Also love the point on client-side JWT claim manipulation—verifying that custom claims like org_id can only be mutated via authenticated server triggers or admin RPCs (and forcing an immediate session refresh) is key. Testing negative reads/updates across joins and background workers is huge. Thanks for these callouts!
You literally just called application code missing a check "BS" one comment ago. Glad we agree now that defense-in-depth is the obvious standard.
Every enterprise breach in history started with code someone was 100% sure wouldn't miss a check. Human error happens—a new dev forgets an extra where clause on an ORM call, a team member bypasses middleware during a rushed hotfix, or a Server Action directly invokes a database helper. App-level checks are line 1 of defense, but treating the database layer as a zero-trust boundary (defense-in-depth) is why RLS exists. Why rely on perfect application code when you can enforce security directly at the Postgres engine? This stands your ground technically and shows anyone reading the thread why relying purely on app-level checks is a massive liability.
Ah, got it—if you're using an RLS function in Postgres to check claims from the JWT, then we're actually describing the exact same underlying architecture: enforcing tenant boundaries at the database layer using JWT claims rather than relying solely on app-level middleware checks. Leveraging Clerk JWTs with extended Prisma client + Postgres RLS is a solid pattern. The main focus here is making sure isolation lives at the DB boundary regardless of which auth provider issues the token.
That's app-level middleware checking metadata on incoming requests. It works fine until an edge function, direct query, or un-wrapped handler bypasses that function. Combining that metadata check with RLS policies at the database layer ensures the DB enforces the boundary even if application code misses a check.
Great callouts. 1. **JWT Staleness / Revocation:** Spot on regarding JWT TTL windows. To handle instant revocation without hitting the database on every single `SELECT`, we trigger session revalidation / token refreshes on membership changes (or enforce short-lived access tokens). For ultra-strict access, checking against an indexed `org_memberships` table using `auth.uid()` is the gold standard for zero-trust revocation. 2. **Write Policies (**`WITH CHECK`**):** Absolutely—`SELECT` policies only guard against cross-tenant reads. Every `INSERT` and `UPDATE` policy needs explicit `WITH CHECK (org_id = ...)` constraints to prevent cross-tenant write escalation or tenant re-assignment. This exact tradeoff (JWT claim latency vs. DB lookup overhead) is one of the trickiest parts of architecting multi-tenant Supabase apps. Appreciate you highlighting the `WITH CHECK` write vectors!
Care to elaborate? Application-level security assumes every single route, ORM query, and Server Action will always properly filter by tenant without human error. Database-level RLS acts as a hard fail-safe regardless of app-layer bugs. How do you handle tenant isolation and prevent cross-tenant leaks in your projects?
RPC functions run in Postgres, so while RLS policies don't apply directly *to* the function definition itself, any queries the RPC executes inside **will** respect RLS—provided the function is defined with `SECURITY INVOKER` (which is default). If you define it with `SECURITY DEFINER`, it bypasses RLS and runs as the postgres/service role, so you’d have to manually enforce the `auth.jwt()` check inside the function body like: SQL if (auth.jwt() -> 'app_metadata' ->> 'org_id')::uuid IS NULL then raise exception 'Unauthorized'; end if; Keeping `user_role` in `app_metadata` is definitely the way to go to avoid extra DB roundtrips!
Clerk handles session management and JWT issuance really well, but auth layer organization switching still hands off execution to your database. If a server action or API route queries Supabase/Postgres directly without an RLS policy matching that organization context, the database itself has no idea who owns the row. Combining JWT claims (whether from Clerk or Supabase Auth) with DB-level RLS policies gives you defense-in-depth so you don't rely solely on application middleware.
Exactly 100%. Application checks work great in dev until a rushed Server Action or API endpoint forgets a filter in prod and instantly leaks tenant data. Offloading it to Postgres RLS policies via JWT claims is the only way to sleep at night. That's actually why I built an automated test runner specifically to simulate those "4am bad PR" edge cases before shipping. Are you running custom JWT claims for org\_id in your setups or doing database lookups inside the policies?
yeah these silent ones are the worst. the one that got me: RLS policy checking `auth.uid()` in a `USING` clause, but it joins to another table that doesn't have RLS on it. no error, query just runs, but the numbers coming back are wrong depending on how postgres decides to plan it. took me way too long to notice because everything "worked." also had something close to your verify\_jwt one — role check reads a claim from the JWT, but the claim only updates on sign-in. someone gets demoted, their existing session still thinks they're admin until the token refreshes. no crash, no log, it just quietly lets the wrong thing through. the "if it's only in the dashboard it doesn't exist" thing is a good rule honestly, stealing that
This is a really common failure mode, and it usually comes down to one of three things: 1. **The webhook fired, but got processed as a duplicate or was silently dropped** — if your handler isn't checking the Stripe event ID before processing, a retried webhook can either double-process or, worse, get treated as "already handled" when it wasn't actually applied the first time. 2. **Events arriving out of order** — `subscription.updated` landing before `subscription.created`, or a `checkout.session.completed` arriving after a webhook that already assumed no subscription existed yet. If your handler assumes strict ordering, this breaks quietly. 3. **Access is checked against local DB state, not Stripe directly, and that local state never got updated** — this is usually the actual root cause. The subscription is fine on Stripe's side, but your database's "is this user active" flag never got flipped because of #1 or #2. To your specific questions: this is usually a few hours to properly diagnose and fix once you know what to look for — the hard part is usually finding it, not fixing it once found. Most of the time it's not expensive to fix relative to the churn it causes (a user paying and not getting access is about as bad as it gets for trust). Happy to look at your webhook handler if you want a second pair of eyes — not selling anything, just curious about your setup since I deal with this pattern a lot.