Postmortem · Bug 04 of 03

Subscription forgery: when the subscriptions table trusts the client

Bug 04 from our own security review — the subscriptions table was client-writable.

The one-sentence version: the subscriptions table — the table that decides who is a paying customer — was writable by authenticated users. An organization owner could insert a row saying plan = pro through the REST API, and the app would believe it. No Stripe, no checkout, no payment.

How a policy becomes a vulnerability

RLS policies are usually read as protection, but every policy has two directions: what clients may read, and what clients may write. While the billing flow was being built, the subscriptions table carried write policies (insert, update, delete) tied to organization membership — the kind of permissive grant that feels harmless in development and is anything but once the table holds entitlement.

The bug

The write policies were the bug. The Stripe flow itself was honest: checkout, portal, webhooks all behaved. But the database never required Stripe. A signed-in organization owner could bypass the entire billing flow with one request: insert a subscriptions row for their own organization with the plan and status fields of a paying customer. Entitlement checks read this table, so a forged row was a forged subscription — self-issued Pro, for free, with no code changes required.

RLS does not ask whether a write makes business sense. It only asks whether the policy allows it. If your subscriptions table is client-writable, your billing is a suggestion.

How we caught it

Not by the suite — the billing-enforcement tests that now guard this table were written as part of the fix, and a test cannot catch a bug before it exists. This one surfaced in a manual security review: walking every RLS policy on every tenant-owned table with attacker eyes, asking of each policy not whether it lets the app work, but what it lets a signed-in user do that they should not. The subscriptions table's write policies failed that question on sight.

The fix: entitlement is a service role's job

Migration 000028_security_fixes.sql made the subscriptions table read-only to clients. Subscription state is now written exclusively through the service role — by the webhook handler after a real Stripe event, never by a user session — and client policies dropped to SELECT-only. The same migration tightened the adjacent paths a forged-entitlement review naturally touches: direct inserts into usage_records were removed (metered usage is recorded only through the privileged RPC, with a positive-amount constraint), audit-log inserts were restricted to the acting user's own actions, and the usage RPC's balance updates were serialized against concurrent calls.

-- the shape of the fix (simplified -- migration 000028 is the source of truth)

-- before: organization members could write entitlement
--   insert / update / delete policies on subscriptions for authenticated users

-- after: clients get read-only access to their own subscription state
--   SELECT-only policies on subscriptions for authenticated users
--   writes flow exclusively through the service role (webhook handler)

The regression coverage ships in the hardening suite's billing-enforcement file: it attempts subscription writes as an authenticated organization member and asserts they fail, then drives the same state change through the service-role path the webhook uses and asserts it succeeds. Entitlement becomes a property you can prove instead of a hope: no client role can write a paid plan. It runs in CI on every push.

The rule

One line: subscription writes are webhook/service-role only. Ask it about your own schema: which tables hold paid-entitlement state, and which roles can INSERT into them? If an authenticated role can — even one you consider trusted — your billing is one forged request away from free.

The honest summary

This isn't a claim that our app is secure or that these were the only bugs that could exist. It's the record of four real bugs we found in our own codebase: three by running executable attacks against the live database, one by reading the RLS policies the way an attacker would. It records the migrations that fixed them, and the executable tests that now guard them — tests that ship with the product, so you can run the same attacks against your own copy before your customers run them against you.