Solana settlement columns are caller-controlled at INSERT — a pending row can under-quote what it owes (#528 follow-up)

[guillermoscript]

Found while implementing #528 (PR #537), 2026-07-25. Verified against a local database.

Problem

#528 closed the two impacts that depended on an untrusted caller producing a successful transaction: the INSERT policy on transactions now pins status = 'pending', and table-level UPDATE is revoked from authenticated in favour of a three-column grant.

That invariant is "an untrusted caller may open a transaction, but may not declare it paid". It deliberately says nothing about what a pending row claims it is owed — and the Solana settlement columns are exactly that:

settlement_currency  text     -- 'sol' | 'usdc'
settlement_base      bigint   -- amount in lamports / USDC base units
settlement_mint      text
settlement_sol_usd   numeric(20,8)

All four are still writable by authenticated on INSERT, because app/api/payments/checkout/route.ts:233 legitimately writes them on the user-scoped client and the fix in #528 did not narrow INSERT columns.

Impact

The on-chain payment is verified against the row's own settlement_base, in lib/payments/solana-reconcile.ts:103-106:

if (tx.settlement_base != null && tx.settlement_currency) {
  vTotalBase = Number(tx.settlement_base)
  vSplTokenStr = tx.settlement_mint
  vDecimals = tx.settlement_currency === 'usdc' ? 6 : 9
}

vTotalBase becomes the totalBase handed to verifySplitTransfer. Both callers reach it — app/api/payments/solana/verify/route.ts:27 (reconcileSolanaOneTimeTransaction) and the /api/cron/solana-reconcile backstop. A student can therefore skip the checkout route and POST /rest/v1/transactions directly:

{
  "user_id": "<self>", "tenant_id": "<own tenant>",
  "product_id": <a $100 course>, "amount": 100, "currency": "usd",
  "status": "pending", "payment_provider": "solana",
  "settlement_currency": "sol", "settlement_base": 1
}

The row is pending, so it passes the #528 policy cleanly. They then pay 1 lamport, the verify endpoint compares the on-chain transfer against settlement_base: 1, finds it satisfied, and flips the row to successful on the service-role client — which fires trigger_manage_transactions and grants the entitlement. Full course access for a fraction of a cent, through the legitimate payment path.

amount is caller-controlled at INSERT for the same reason, which is the same shape of problem for any provider that reconciles against the stored amount rather than against the product's price.

Why it wasn't folded into #528

#528's two filed impacts (free enrolment via a self-inserted successful row, and fabricated payout liability) are both gated on the row reaching successful, and both are closed by pinning status. This one is reached through a legitimately pending row, so it needs a different control, and mixing the two would have made that PR unreviewable.

Possible directions

The straightforward fixes for status do not transfer here, because these columns must hold caller-supplied values at insert time:

• Column-level INSERT grants don't help on their own: the checkout route needs to write these columns, so they'd stay in the allow-list.
• A BEFORE INSERT trigger that recomputes the values — the pattern Split snapshot is written at a single call site with no DB backstop (#496 follow-up) #512 used for school_percentage_snapshot — can't fully work: settlement_sol_usd comes from a live SOL/USD quote fetched off-platform (getSolUsdPrice()), which a trigger cannot obtain. A trigger could still recompute amount from products.price / plans.price and reject a settlement_base inconsistent with it, which would close most of the gap.
• Move transaction creation behind a SECURITY DEFINER RPC (direction 3 in transactions RLS restricts rows but not columns — a student can self-insert a 'successful' sale #528) and revoke the direct INSERT grant. The RPC would take product_id/plan_id plus the quoted rate and derive amount, currency and the settlement figures server-side. This is the thorough fix and probably the right one, but it is a real change to the checkout path.
• Or verify against the product price rather than the row in solana/verify — narrower, and it leaves the column writable for anything else that reads it.

Worth confirming which of settlement_* and amount are actually load-bearing in verification before choosing, rather than hardening all of them by reflex.

[guillermoscript]

Plan of attack

Verified against a local database and the call sites below.

Which columns are actually load-bearing

The issue asks this before choosing a control, so taking it first:

Column	Read where	Load-bearing?
settlement_base	lib/payments/solana-reconcile.ts:104 → verifySplitTransfer({ totalBase }); app/api/payments/solana/tx/route.ts:119 → buildSplitTransaction({ totalBase })	Yes — it is the figure the chain is checked against, and it also sets what the QR asks the wallet for.
settlement_currency	same two sites, selects decimals (6 for usdc, 9 otherwise)	Yes — wrong decimals move the verified amount by 10³.
settlement_mint	same two sites, splToken (NULL ⇒ native SOL)	Yes — a caller-chosen mint means verification counts a token the attacker can mint freely.
settlement_sol_usd	nothing reads it	No. It is the audit record of the locked quote, nothing else.
amount	the legacy fallback in both sites (amountMajor: Number(tx.amount) when settlement_base IS NULL), and getPayoutsOwed()	Yes, on both counts. Note no webhook compares it against what the provider actually charged — lib/payments/webhook-dispatch.ts never reads amount — so for Lemon Squeezy / PayPal / Binance the under-quote does not buy access (those charge from their own catalog), it understates the school's payout instead.

So four of the five need to be trusted, and settlement_sol_usd is the only one that is purely informational.

Root cause

#528's invariant is "an untrusted caller may open a transaction, but may not declare it paid", enforced by pinning status on INSERT and revoking table-level UPDATE. It says nothing about the rest of the inserted row, because the table-level INSERT grant to authenticated had to stay — app/api/payments/checkout/route.ts:233 and app/api/stripe/create-payment-intent/route.ts:140 both insert on the user-scoped client.

That grant is the actual root cause. Nothing in the browser inserts into transactions; every legitimate insert already happens on the server:

• app/api/payments/checkout/route.ts — user-scoped client, price read from tenant-scoped products/plans
• app/api/stripe/create-payment-intent/route.ts — user-scoped client, same
• app/[locale]/(public)/checkout/actions.ts (enrollUser) — admin client since transactions RLS restricts rows but not columns — a student can self-insert a 'successful' sale #528
• app/actions/payment-requests.ts:359 — admin client
• webhooks / reconcilers / crons / scripts / Playwright specs — service role
• grant_free_subscription() — SECURITY DEFINER

The two API routes are the only reason the grant exists, and both already derive every financial field server-side from trusted sources (products.price / plans.price, and getSolUsdPrice() for the SOL quote).

Approach

Direction 3 from the issue — revoke the direct INSERT grant and derive everything server-side — implemented against the routes rather than behind a new RPC.

1. Move both user-scoped inserts to the admin client. Same change transactions RLS restricts rows but not columns — a student can self-insert a 'successful' sale #528 made to enrollUser. user_id comes from the session and tenant_id from getCurrentTenantId(), and the product/plan lookups that produce amount/currency/payment_provider are tenant-scoped reads on the user-scoped client — so the tenant validation CLAUDE.md requires for an admin-client write is already in place, and a caller still cannot reference another tenant's catalogue.
2. REVOKE INSERT ON public.transactions FROM authenticated, anon. POST /rest/v1/transactions then fails with permission denied for table transactions. amount, all four settlement_* columns, product_id, plan_id and payment_provider become server-owned at insert time; transactions RLS restricts rows but not columns — a student can self-insert a 'successful' sale #528's column grant already made them unwritable on UPDATE, so after this they are unreachable by an untrusted caller entirely.
3. Keep the transactions RLS restricts rows but not columns — a student can self-insert a 'successful' sale #528 INSERT policy and tighten it to pin the four settlement_* columns NULL alongside status = 'pending'. It is unreachable while the grant is revoked; it is there so the specific under-quote in this issue stays impossible if the grant ever comes back — a rollback, or someone resolving a permission denied error the direct way.

Two directions considered and rejected:

• A BEFORE INSERT trigger recomputing amount from products.price / plans.price (the Split snapshot is written at a single call site with no DB backstop (#496 follow-up) #512 pattern). It would overwrite legitimate values: payment_requests completion inserts the admin-confirmed payment_amount, and grant_free_subscription() inserts a zero-amount row for its plan. Rejecting or rewriting those turns a quiet mispricing into a broken payment path.
• A SECURITY DEFINER RPC. It cannot fetch the SOL/USD quote, so it would have to accept the rate as a caller parameter — and the rate is exactly the value that must not be caller-supplied, since settlement_base is derived from it. It would move the vulnerability rather than close it. Inserting on the admin client keeps the live quote from crossing the client boundary in either direction.

Risks

• create_transaction_for_renewal(sub_id, usr_id, pln_id) (20260126190500_lms_complete.sql:402) is SECURITY INVOKER, granted to anon/authenticated, and inserts into transactions. It is already unreachable for a user caller — it sets no tenant_id, so transactions RLS restricts rows but not columns — a student can self-insert a 'successful' sale #528's INSERT policy rejects it — and no application code calls it. After the revoke it fails one step earlier, at the grant. Flagged, not changed.
• handle_new_subscription's EXECUTE grant to authenticated (20260704190000) was justified by enrollUser inserting as the user. That has been the admin client since transactions RLS restricts rows but not columns — a student can self-insert a 'successful' sale #528, so nothing in this change depends on that grant.
• Any future user-scoped insert will fail loudly with permission denied for table transactions. That is the intended trade-off on a payments table: a silent success is the worse outcome.
• The two routes now require SUPABASE_SERVICE_ROLE_KEY. checkout/route.ts already calls createAdminClient() for the revenue-split snapshot, so only create-payment-intent gains a new dependency on it.

Test plan

1. Grant/policy state — a supabase/snippets/verify_*.sql asserting the final state (INSERT not held by authenticated/anon, policy pins status and the settlement columns), mirroring transactions RLS restricts rows but not columns — a student can self-insert a 'successful' sale #528's snippet so a later migration re-widening things is caught.
2. Local repro, before and after — the exact under-quoted insert from the issue, run against a local database with a student JWT: succeeds today, permission denied after the migration. The same insert as service role still succeeds, so the legitimate paths are provably untouched.
3. Playwright regression — a signed-in student's direct PostgREST insert into transactions is rejected, for both a plain pending row and the under-quoted settlement row.
4. The checkout path still works — drive /api/payments/checkout for a Solana product against the local database and confirm the pending row lands with the correct settlement_base/currency/mint written by the admin client, plus npm run build, npm run lint, npm run typecheck.