AskMeAnything
Ask
Ask
152
@soy_pour_sasha ·

jwt custom claims or a memberships join in every policy at 200 orgs Supabase Auth

Every policy currently does:

using ( org_id in (select org_id from memberships where user_id = (select auth.uid())) )

That adds 6-9ms per query and a dashboard page runs 11 queries, so it is roughly 80ms of pure policy overhead on a page that should be 150ms. 200 organisations, average 8 members.

The alternative is putting org_id and role into the access token as custom claims and comparing against those. My worry is revocation - if someone is removed from an org, their token is still valid until it expires.

9 answers Share
Report

9 answers

BestTopNewOldControversial

Answering anonymously — a moderator will review it first.

  • @pgpolicy_nadia · 4w ago · 3 replies
    @pgpolicy_nadia ·

    Claims are a cache. Treat the staleness the way you would treat any cache: bound it and be explicit about what you are willing to serve stale.

    Concretely - put membership in the token, drop the access token lifetime to 10 or 15 minutes, and force a token refresh at the moment membership changes so the person doing the removal sees it take effect immediately. Your exposure is then "a removed member can read for up to 15 minutes", which for most B2B products is the same order as "we deactivate accounts within the hour" and nobody blinks.

    Where I would not do this: anything where removal is an emergency - a compromised account, a fired employee with payroll access. For those tables keep the join and pay the 8ms, because 8ms is cheap and a 15-minute window is not.

    43
    Share
    Reply

    Answering anonymously — a moderator will review it first.

    Report
    • @soy_pour_sasha · 3w ago
      @soy_pour_sasha OP ·

      Splitting it by table is the answer I did not consider. Invoices and member management keep the join, everything read-heavy moves to claims.

      12
      Share
      Reply

      Answering anonymously — a moderator will review it first.

      Report
    • @supa_okonkwo · 4w ago
      @supa_okonkwo ·

      If you do split, write down the rule somewhere the next person will find it. A mixed policy set where some tables trust the token and some do not is genuinely confusing six months later.

      8
      Share
      Reply

      Answering anonymously — a moderator will review it first.

      Report
  • @costbasis_carl · 3w ago
    @costbasis_carl ·

    Watch token size if you go the claims route. A user in one org is a tiny token. We have a support account that belongs to 90 orgs; embedding all of them produced a JWT that pushed the cookie past a limit some proxies enforce, and the failure mode is a 431 that is very hard to trace. Put the current org in the token, not the full list.

    20
    Share
    Reply

    Answering anonymously — a moderator will review it first.

    Report
  • @wren_oyelaran · 4w ago · 3 replies
    @wren_oyelaran ·

    Before you rearchitect, check whether your 8ms is inherent or self-inflicted. Two things usually account for most of it:

    1. (select auth.uid()) inside the subquery - which you have, good.
    2. An index on memberships(user_id, org_id). Without it that subquery is a scan, and at 1,600 rows it is a fast scan that still costs you a plan node per query.

    Then wrap the whole thing in a stable security definer function user_orgs() and call it as org_id in (select org_id from user_orgs()). On our data that took the per-query overhead from 7ms to under 1ms without touching tokens at all.

    35
    Share
    Reply

    Answering anonymously — a moderator will review it first.

    Report
    • @soy_pour_sasha · 4w ago
      @soy_pour_sasha OP ·

      The index was there but on (user_id) alone, so the subquery still had to visit the heap for org_id. Adding the second column took it to 2.1ms before I changed anything else.

      12
      Share
      Reply

      Answering anonymously — a moderator will review it first.

      Report
    • @p99_hana · 3w ago
      @p99_hana ·

      One caution on security definer: set an explicit search_path on the function. A definer function without one is the classic Postgres privilege-escalation footgun, and it is two words to prevent.

      10
      Share
      Reply

      Answering anonymously — a moderator will review it first.

      Report
  • @supa_okonkwo · 3w ago
    @supa_okonkwo ·

    The 11 queries are more interesting than the 8ms. A dashboard doing 11 round trips is going to be your latency problem long after the policy is optimised - each one pays connection, planning and policy overhead separately. Batch them and the policy cost drops proportionally for free.

    24
    Share
    Reply

    Answering anonymously — a moderator will review it first.

    Report
  • @keg_line_ken · 3w ago
    @keg_line_ken ·

    6ms per query is not a performance problem, it is a number you measured. Come back when it is 60.

    9
    Share
    Reply

    Answering anonymously — a moderator will review it first.

    Report