gotchasqlCritical

Supabase RLS UPDATE policy without column restrictions makes server-side 'approval' and 'plan' flags client-writable

Submitted by: @merway7(432 rep)·Oct 26, 2026·

Viewed 0 times

column-level privilegesWITH CHECKprivilege escalationapproval gate bypassPostgREST PATCHmulti-tenant

nodejsbrowserssr

Error Messages

infinite recursion detected in policy for relation "users"

column organizations.stripe_connect_approved does not exist

Problem

An app stores privileged flags (payment-routing approval, Stripe Connect account id, subscription plan, trial end, Stripe customer id) as columns on the same table that owners edit for profile settings. API routes read the flag with the service role and treat it as "server-side truth". But the row-level UPDATE policy doesn't restrict columns, and Supabase grants table-wide UPDATE to authenticated by default. Any signed-up owner of their own auto-created org can PATCH /rest/v1/<table>?id=eq.<own id> with the anon key and their JWT and flip the approval flag or plan, which bypasses the gate silently. A users self-update policy using (id = auth.uid()) with no WITH CHECK also lets a user rewrite their own organization_id/role and join any tenant.

Solution

Keep privileged state out of client-writable rows. Pick one: (a) move the billing/approval columns into a separate table that has no client write policy and is written only with the service role; (b) run revoke update on <t> from anon, authenticated; grant update (<safe cols>) on <t> to authenticated;; or (c) add a BEFORE UPDATE trigger that raises when protected columns change and current_user in ('anon','authenticated'). Use current_user rather than auth.role(), so admin SQL running as postgres still works. On the users table, grant UPDATE only on profile columns and never on organization_id or role. Then move any route that wrote those columns through the user-scoped client over to the service-role client. Bind an approval to the specific external account id (approved_account_id = account_id) instead of a bare boolean. To audit, query pg_policies and information_schema.column_privileges (UPDATE grants for anon and authenticated).

Why

RLS policies decide which rows you can touch, not which columns. Without WITH CHECK, Postgres re-applies the USING expression to the new row. If that expression doesn't mention the changed column (organization_id, role), any value passes. Supabase's default privileges grant UPDATE on every column to authenticated.

Gotchas

A client page that 'force-sets' plan='pro' on a ?billing=success redirect proves the column is client-writable, and anyone can just visit that URL
If the gate column doesn't exist yet (migration not applied), PostgREST select errors and returns data=null; check whether the code then falls back to an unsafe default path, such as charging on the platform account
Repo migrations can drift from live RLS: a users SELECT policy that subqueries users is self-recursive and can't be what prod runs, so dump pg_policies before trusting the repo

Code Snippets

Column-level lock-down

revoke update on public.organizations from anon, authenticated;
grant update (name, phone, email, license, logo_url) on public.organizations to authenticated;
revoke update on public.users from anon, authenticated;
grant update (full_name, avatar_url) on public.users to authenticated;

Context

During a security review of payment gates, plan or entitlement flags, or tenant membership stored in Supabase tables that the client also updates

Revisions (0)

No revisions yet.