transactions RLS restricts rows but not columns — a student can self-insert a 'successful' sale

[guillermoscript]

Found while reviewing #525 / working #512 (2026-07-25). Verified against a local database.

Problem

transactions grants ALL to authenticated:

-- 20260126190500_lms_complete.sql:3692
GRANT ALL ON TABLE "public"."transactions" TO "authenticated";

and its RLS policies restrict which rows a user may write, never which columns:

-- 20260313025318_rls_transactions.sql
CREATE POLICY "Users can create own transactions"
  ON public.transactions FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id AND tenant_id = get_tenant_id());

CREATE POLICY "Users can update own transactions"
  ON public.transactions FOR UPDATE TO authenticated
  USING (auth.uid() = user_id AND tenant_id = get_tenant_id())
  WITH CHECK (auth.uid() = user_id AND tenant_id = get_tenant_id());

So an authenticated student can POST /rest/v1/transactions (or PATCH a row they already own) with any column values they like, as long as user_id is theirs and tenant_id is their current tenant. status, amount, product_id, plan_id, payment_provider and currency are all in scope.

Impact

Two separate consequences, both reached without touching a payment provider:

1. Free enrolment. trigger_manage_transactions (AFTER INSERT/UPDATE) calls enroll_user(NEW.user_id, NEW.product_id) whenever product_id is set and status = 'successful', and handle_new_subscription(...) on the plan_id branch. A self-inserted row with status: 'successful' therefore grants entitlements — i.e. paid course access — with no money involved.
2. Fabricated payout liability. getPayoutsOwed() reads transactions where status IN ('successful','refunded') and payment_provider IN (paypal, lemonsqueezy, binance). A self-inserted row carrying one of those providers lands in grossOwed, so the platform's own payout dashboard reports it owes a school money for a sale that never happened.

Scope note

#512 (PR #527) closes the school_percentage_snapshot column specifically, by making the database compute and freeze that value regardless of what the caller sends. That is one column. The rest of the row is still caller-controlled, which is why this is filed separately rather than folded in.

Possible directions

Not a recommendation yet — these need weighing against the paths that legitimately insert transactions on the user-scoped client (app/api/payments/checkout/route.ts, app/[locale]/(public)/checkout/actions.ts):

• Column-level privileges: revoke table-level INSERT, UPDATE from authenticated and re-GRANT an explicit column list. Note a bare REVOKE ... (column) is a no-op while the table-level grant is held. The risk is silently breaking any column omitted from the new list.
• Tighten the policies to pin the columns that matter — e.g. WITH CHECK (... AND status = 'pending' AND payment_provider IS NULL) on INSERT, so only a webhook or a service-role path can produce a successful row.
• Move transaction creation behind a SECURITY DEFINER RPC and drop the direct grant entirely.

The second is probably the smallest change that closes the enrolment path; worth confirming no legitimate caller inserts a non-pending transaction on the user-scoped client first.

[guillermoscript]

An AI agent (Claude Code) is picking this up. Plan of attack below, verified against the live local database rather than migration files alone.

Root cause / current behavior

Confirmed on a local database — this is the real, current state, not an inference from migrations:

• authenticated holds table-level SELECT, INSERT, UPDATE on public.transactions (no DELETE, no TRUNCATE). Every one of the 20 columns is therefore writable.
• RLS is enabled (not FORCE), and the two write policies constrain rows only:

Users can create own transactions   INSERT  WITH CHECK (auth.uid() = user_id AND tenant_id = get_tenant_id())
Users can update own transactions   UPDATE  USING/WITH CHECK (auth.uid() = user_id AND tenant_id = get_tenant_id())

• status is transaction_status NOT NULL DEFAULT 'pending', but nothing stops a caller from sending a different value.
• Both triggers are live and both reach the enrollment path:

after_transaction_insert  AFTER INSERT ON transactions FOR EACH ROW EXECUTE FUNCTION trigger_manage_transactions()
after_transaction_update  AFTER UPDATE ON transactions FOR EACH ROW EXECUTE FUNCTION trigger_manage_transactions()

trigger_manage_transactions() calls enroll_user(NEW.user_id, NEW.product_id) when product_id is set and status = 'successful', and handle_new_subscription(...) on the plan_id branch. So both impacts in the report reproduce exactly as described, and the UPDATE trigger means owning a legitimate pending row is a second route to the same place.

Approach

Two layers, because the two GitHub-visible impacts have different mechanics (self-insert vs. self-escalate), and one app fix to keep a legitimate caller working.

1. Pin status on INSERT (closes the self-insert path).

Recreate the INSERT policy with status = 'pending' added to the WITH CHECK. Permissive policies OR together, so this must be DROP POLICY + CREATE POLICY, not an additive policy.

An untrusted caller can then still create a row, but only a pending one — which fires no trigger branch and is excluded from getPayoutsOwed() (app/actions/platform/payouts.ts:52 filters status IN ('successful','refunded')).

2. Restrict UPDATE to the three columns a legitimate user-scoped caller actually writes (closes the self-escalate path, and a third issue found while surveying).

REVOKE UPDATE ON transactions FROM authenticated, then re-GRANT UPDATE (status, provider_subscription_id, stripe_payment_intent_id). A bare column-level REVOKE is a no-op while the table-level grant is held, so the table-level revoke has to come first.

Recreate the UPDATE policy as USING (... AND status = 'pending') and WITH CHECK (... AND status IN ('pending','failed')) — a user may only touch a still-pending row of their own, and may only move it to failed.

This second layer is not redundant with the first. RLS WITH CHECK cannot compare OLD to NEW, so a policy alone cannot make a column immutable. Without the column grant, a student can create a legitimate pending transaction for a cheap product, PATCH its product_id to an expensive one, pay the cheap price, and let the provider webhook flip the row to successful — the trigger then enrols them in the expensive course. The column grant closes that mechanically.

3. Move the two status: 'successful' inserts in app/[locale]/(public)/checkout/actions.ts (enrollUser, lines 52 and 100) to createAdminClient().

A full inventory of application-side writes found 7 user-scoped write sites; five of them already write status: 'pending' or 'failed' and are unaffected. These two are the only legitimate callers that need 'successful' on the user-scoped client, so step 1 would break them. Both already validate tenant ownership before writing (the product/plan lookups are .eq('tenant_id', tenantId)) and take user_id from getCurrentUserId(), which satisfies the createAdminClient() rule in CLAUDE.md.

Files:

• supabase/migrations/20260725170000_transactions_column_hardening.sql (new)
• supabase/migrations/rollback/20260725170000_transactions_column_hardening.down.sql (new)
• supabase/snippets/verify_transactions_column_hardening.sql (new, matching the pattern from Split snapshot is written at a single call site with no DB backstop (#496 follow-up) #512/PR fix(db): back the revenue-split snapshot with a trigger, not one call site (#512) #525)
• app/[locale]/(public)/checkout/actions.ts (edit)

Risks / blast radius

• Breaking a legitimate writer is the main risk, and it is a silent one — a rejected write surfaces as a failed checkout, not a build error. Mitigated by having enumerated every write site first. The 7 user-scoped sites are checkout/actions.ts:52,100; app/api/payments/checkout/route.ts:233,320,337; app/api/stripe/create-payment-intent/route.ts:140,187,203,241. All admin-client and service-role paths (Stripe webhook, webhook-dispatch.ts, the Solana and Binance reconcilers, the two cron routes, payment-requests.ts, binance-personal.ts) bypass RLS and are untouched.
• The three granted UPDATE columns are exactly what those callers write — status (rollback to 'failed'), provider_subscription_id, stripe_payment_intent_id. Any future user-scoped update of another column will fail with permission denied for column. That is the intended trade-off; it fails loudly.
• USING (status = 'pending') on UPDATE narrows the policy further than the current behavior. Every legitimate user-scoped update runs against a row created moments earlier in the same request, so it is still pending.
• Interacts with the before_transaction_split_snapshot_* triggers from Split snapshot is written at a single call site with no DB backstop (#496 follow-up) #512 / PR fix(db): back the revenue-split snapshot with a trigger, not one call site (#512) #525, which already own school_percentage_snapshot. That column is a BEFORE-trigger override, so revoking UPDATE on it costs nothing.
• No cross-tenant or data-migration risk: this is grant/policy DDL only, no rows are read or rewritten.

Out of scope — filing separately

Two adjacent problems surfaced during the survey. Both are real, neither is what this issue describes, and folding them in would make the change unreviewable:

1. Settlement columns are caller-controlled at INSERT. settlement_base, settlement_currency, settlement_mint and settlement_sol_usd are writable by an untrusted caller, and app/api/payments/solana/verify/route.ts prices the on-chain payment against them. A self-inserted pending row carrying settlement_base: 1 is a pay-1-lamport-for-a-$100-course path — and it survives this fix, because the row is legitimately pending. Same root cause, different mechanism, and closing it properly needs the checkout RPC (direction 3 in the report), since the SOL/USD rate is fetched off-platform and cannot be recomputed in a trigger.
2. enrollUser grants access with no payment for any provider that is not Lemon Squeezy / PayPal / Binance / Solana. components/public/checkout-form.tsx:364 falls through to enrollUser(..., 'mock_test') whenever paymentMethod === 'card' and the provider is not one of those four — stripe and an unset provider both land there. That is an application-logic hole, not an RLS one; this change relocates that call to the admin client without altering the behavior.

Test plan

Automated:

• npm run db:reset — migration applies cleanly from scratch.
• npm run typecheck and npm run lint — the actions.ts edit.
• npm run build.
• psql -f supabase/snippets/verify_transactions_column_hardening.sql — asserts the final grant and policy state (table-level UPDATE gone from authenticated, exactly three column grants present, both WITH CHECK expressions carrying the status predicate).

Manual, against the local stack with a real student JWT (alice@student.com, Code Academy) on the anon key — this is the part that actually proves it, since RLS is invisible to the type checker:

1. Before the migration: POST /rest/v1/transactions with {user_id, tenant_id, product_id, amount, status: 'successful'} → 201, and entitlements gains a row for that course. Record it.
2. After: the same request → rejected by the policy; no entitlement is created.
3. After: insert a legitimate {... status: 'pending'} row → still 201 (the paid checkout must keep working).
4. After: PATCH that pending row with {status: 'successful'} → rejected.
5. After: PATCH that pending row with {product_id: <other product>} → permission denied for column product_id.
6. After: PATCH that pending row with {status: 'failed'} → 204 (the rollback path in payments/checkout/route.ts:337 must keep working).
7. Free-course and free-plan checkout (enrollFree / subscribeFree, both already on SECURITY DEFINER RPCs) still enrol.

[guillermoscript]

Correction to the plan above, so it doesn't stand as the record.

The plan's second "out of scope" item claimed enrollUser() grants course access with no payment for any provider that is not Lemon Squeezy / PayPal / Binance / Solana, and named stripe and an unset provider as reaching it. That is wrong. I checked the call site (components/public/checkout-form.tsx:364) but not the gate on the button that reaches it.

enrollUser() is only reachable from the "Pay & Enroll (Test)" sandbox button, and components/public/checkout-form.tsx:678 gates that button behind process.env.NODE_ENV !== 'production':

const isStripe = paymentProvider === 'stripe';
const allowSandbox = process.env.NODE_ENV !== 'production' && paymentProvider !== 'manual';

In production a Stripe product renders StripePaymentForm (the webhook grants enrollment) and a provider-less product renders a "card unavailable" notice. Neither reaches the action. So there is no production hole there and nothing to file — it is a development-only sandbox escape hatch, working as designed.

This does not change the fix: enrollUser() still writes status: 'successful' on the user-scoped client, so it still had to move to the admin client for local development and the E2E sandbox flow to keep working once the INSERT policy pins pending.

The plan's first out-of-scope item (settlement columns being caller-controlled at INSERT) stands and has been filed separately.

Everything else in the plan was implemented as written. PR: #537.