Supabase column security: lock role, plan, and credits

You asked Lovable, Bolt, or Cursor for user profiles with a plan and some free credits. It made a profiles table with role, plan, is_pro, and credits columns, turned on Row Level Security, and added the policy everyone recommends: users can update their own profile. The Security Advisor is quiet. The app works.

Any signed-in user can now give themselves role = 'admin', plan = 'pro', and a million credits, with one request to your Supabase API using the same publishable key your app already ships. RLS checked that the row was theirs. It never looked at which columns changed.

This guide shows why that happens, the fix Supabase documents and the trap in it, two alternatives, and SQL you can run in your own project to prove a user cannot touch the columns that cost you money. For the rest of your policies, keep the Supabase RLS checklist open next to this page.

Checked on October 20, 2026 against the Supabase column-level security, RLS, API security, and user data docs, the PostgreSQL 17 manual, and the PostgREST error reference linked below. We reproduced the hole and verified every fix on PostgreSQL 17.11 with Supabase-style anon, authenticated, and service_role roles and Supabase's own auth.uid() definition. VibeLocked is independent and not affiliated with Supabase.

The short version

• RLS is row security. An owner-only update policy lets the owner change every column in their row.
• Table-level grants beat column-level revokes. revoke update (credits) on profiles does nothing while authenticated still has update on the table.
• The working fix: revoke update and insert on the table from anon and authenticated, then grant them back only on safe columns.
• Supabase's preferred design: keep roles, plans, and credits in their own table that users can read and never write. Your server writes it with the secret key.
• Test it as the authenticated role in the SQL editor. The SQL editor's default postgres role proves nothing.

Why "users can update their own profile" is not enough

Supabase's column-level security page puts it plainly: RLS gives "granular control over who can access rows of data" but "doesn't give you control over which columns they can access within rows." Its own example policy, using ((select auth.uid()) = user_id), "gives the post owner full access to update the row, including all of the columns."

Here is the table shape AI tools generate most often:

create table public.profiles (
  id uuid primary key references auth.users on delete cascade,
  display_name text,
  avatar_url text,
  role text not null default 'user',
  plan text not null default 'free',
  credits int not null default 10
);

alter table public.profiles enable row level security;

create policy "Users can view own profile"
on public.profiles for select to authenticated
using ((select auth.uid()) = id);

create policy "Users can update own profile"
on public.profiles for update to authenticated
using ((select auth.uid()) = id)
with check ((select auth.uid()) = id);

On existing Supabase projects, a new table in public also receives select, insert, update, and delete for anon, authenticated, and service_role by default. Supabase's Securing your API guide explains those default grants, says the platform default is changing so exposure becomes opt-in, and shows how to grant explicitly. With that grant and the policy above, a signed-in user's request runs this, and Postgres accepts it:

update public.profiles
set role = 'admin', plan = 'pro', credits = 999999
where id = auth.uid();

In our test it returned admin | pro | 999999. Nothing in the policy is wrong for the row. The with check clause only proves the new row still belongs to the user. The PostgreSQL CREATE POLICY docs explain why it cannot do more: using runs against the existing row and with check against the proposed new row, and neither expression can compare the old credits value with the new one.

This is the database version of mass assignment, which OWASP calls Broken Object Property Level Authorization (API3:2023) and MITRE tracks as CWE-915. The OWASP Mass Assignment cheat sheet gives the usual example: a user adds isAdmin=true to a profile update. With Supabase, the client talks to the table directly, so there is no API route in between to strip the field. The common vulnerabilities guide covers the same bug in hand-written API routes.

Which columns to worry about

Anything that changes what a user may do or what you pay for:

• role, is_admin, permissions, team_role
• plan, is_pro, subscription_status, trial_ends_at, stripe_customer_id
• credits, tokens_remaining, usage_count, quota
• email_verified, banned, approved, referral_bonus
• foreign keys that move a row into someone else's team or organization

If a column decides access or money, the user must not be able to write it.

The trap in the first fix: revoking one column

The natural fix, and the one AI tools tend to write, is to revoke the dangerous columns:

-- Looks right. Does nothing while the table-level grant exists.
revoke update (role, plan, credits) on public.profiles from authenticated;

We ran exactly that and then the same attack. It still returned 999999. The PostgreSQL GRANT docs explain why: "Granting the privilege at the table level and then revoking it for one column will not do what one might wish: the table-level grant is unaffected by a column-level operation." Supabase's page repeats it: if a role has both, and you revoke the column-level privilege, "the table-level privilege will still be in effect."

Even the migration example lower on Supabase's own page revokes only update (title). That only works because the earlier example on the page already revoked the table-level privilege. Copy the order, not just the last snippet.

Fix 1: grant update only on the columns users may edit

Revoke the whole table privilege, then grant back the safe columns:

-- Stop the client roles from writing any column...
revoke update on table public.profiles from anon, authenticated;
revoke insert on table public.profiles from anon, authenticated;

-- ...then allow only what the profile form edits.
grant update (display_name, avatar_url) on table public.profiles to authenticated;
grant insert (id, display_name, avatar_url) on table public.profiles to authenticated;

Results from our run as authenticated, with the owner policy unchanged:

Request	Result
update ... set display_name = 'Alice B'	Works
update ... set credits = 999999	ERROR: permission denied for table profiles
Upsert that sets credits in on conflict do update	Denied
Upsert that only sets display_name	Works
insert of a new profile with credits = 999999	Denied
insert with only id and display_name	Works, role, plan, credits take the defaults
Same update as service_role	Works

Do not forget insert

This is the hole most fixes miss. With update locked down but the table-level insert grant left in place, a user whose profile row does not exist yet can insert one with role = 'admin', plan = 'pro', credits = 999999, and our test accepted it. That matters when your app creates the profile from the browser after signup, or when a user can delete their own row and insert it again. Supabase's user data guide creates the profile with a trigger on auth.users instead. That trigger function runs as its owner, so it is not affected by the client grants, and it should set only id and the safe fields.

What changes in your app

The API answers a blocked write with Postgres error 42501. The PostgREST error table maps it to HTTP 403 for a signed-in user (401 for an anonymous one), and supabase-js returns it in error. Your profile form keeps working as long as it sends only the allowed columns, both for update and upsert. If a form spreads the whole profile object into .update(profile), it now fails, which is the point: send { display_name, avatar_url } and nothing else. With column grants, an upsert also needs select on the conflict column and on every column it sends. Supabase upsert new row violates row-level security has the tested grant table.

Leave select at the table level unless you also need to hide columns from reads. Supabase warns that restricted roles "cannot use the wildcard operator (*)." We confirmed both sides of that: with column-level select, select * and update ... returning * failed with the same 42501 error, so .select() with no column list breaks. If you need hidden columns, move them to another table rather than fighting *.

What stays safe and what can reopen it

Two behaviors from our test are worth knowing:

1. A column added later, such as is_pro boolean, was not updatable by authenticated. Column grants list what is allowed, so new columns start locked. That is the safe default.
2. Running grant update on table public.profiles to authenticated again, which an AI tool may do while "fixing" a permission error, reopened every column. Our next attack set credits to 777. Put the grants in a migration, and add the test below to CI so a regression fails the build.

Supabase also has a dashboard screen for column privileges under Feature Preview, which it keeps there on purpose because the feature is "quite advanced." Migrations are easier to review.

Fix 2: a trigger that rejects changes to protected columns

If you cannot change grants yet, a before update trigger can compare the old and new row, which a policy cannot do:

create or replace function private.protect_profile_columns()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
  if current_user in ('anon', 'authenticated') and (
       new.role    is distinct from old.role
    or new.plan    is distinct from old.plan
    or new.credits is distinct from old.credits
  ) then
    raise exception 'role, plan and credits are read only'
      using errcode = '42501';
  end if;
  return new;
end;
$$;

create trigger protect_profile_columns
before update on public.profiles
for each row execute function private.protect_profile_columns();

In our run, a user's set credits = 999999 failed with that message, set display_name = 'ok', credits = credits still worked (the value did not change), and the same update as service_role went through. The function is not security definer, so current_user is the caller's role. See the PostgreSQL docs on PL/pgSQL trigger functions and Supabase's triggers guide.

Keep the function in a schema that is not exposed over the API, here private (create it with create schema if not exists private;), so nobody can call it as an RPC. The weakness: the list of columns lives in code. A new is_pro column is writable until someone adds it to the trigger. You also need the same protection on insert, or a column default the user cannot override. Treat this as a stopgap.

Fix 3: move money and permissions out of the profile

Supabase's column page says column privileges are not recommended "for most users" and suggests "RLS policies in combination with a dedicated table for handling user roles." The same idea fits plans and credits:

create table public.entitlements (
  user_id uuid primary key references auth.users on delete cascade,
  plan text not null default 'free',
  credits int not null default 10,
  updated_at timestamptz not null default now()
);

alter table public.entitlements enable row level security;

revoke all on table public.entitlements from anon, authenticated;
grant select on table public.entitlements to authenticated;

create policy "Users can read own entitlements"
on public.entitlements for select to authenticated
using ((select auth.uid()) = user_id);
-- No insert, update, or delete policy for users. On purpose.

The profile keeps the fields users really own. The entitlements row is written only by your server: the Stripe webhook after a verified payment, an admin tool, or the route that spends a credit. Those use the Supabase secret key (or the legacy service_role key), which bypasses RLS, as the API keys docs explain. Keep that key on the server only. The anon vs service role guide covers where each key belongs.

For roles, Supabase's custom claims and RBAC guide puts them in a user_roles table read by an access token hook. Never put a role in user_metadata; users can change it with supabase.auth.updateUser(), which is why the Security Advisor flags policies that trust it. The client-side auth guide shows the role setups, and the RLS infinite recursion guide shows how to check a role from a policy without looping.

Credits: spend them where the work happens

Credits have a second problem. Even if users cannot add credits, an app that decrements them from the browser can simply skip the decrement. Do the check and the decrement on the server, in the route that calls the paid API, in one statement so two requests cannot spend the same credit:

update public.entitlements
set credits = credits - 1, updated_at = now()
where user_id = $1 and credits > 0
returning credits;

No row back means no credits left, so return an error before calling the model. We raced this against the usual read-then-write code in Stripe price tampering and credit race conditions: 20 parallel requests on 3 credits all succeeded with the naive code and exactly 3 with this statement. That guide also has a Supabase rpc version with a row lock and unique constraints for one-time bonuses. The OpenAI route guide covers the rest of that route, and the Stripe success page guide explains why the plan should change in a verified webhook, not on the page the user lands on after checkout.

The same bug in Firebase

Firestore rules have the same trap: allow update: if request.auth.uid == userId lets the owner write every field. Firebase's field access docs show the fix, request.resource.data.diff(resource.data).affectedKeys().hasOnly([...]), which limits an update to named fields. The Firebase security rules checklist shows hasOnly in full rules.

The wrong fixes AI tools suggest

• "Check in the frontend that the user cannot edit those fields." Hiding inputs changes nothing. The publishable key and project URL are in your page, and the API accepts any column the role can write.
• "Revoke update on the credits column." Without revoking the table-level grant first, this is a no-op, as shown above.
• "Add credits = credits to the WITH CHECK clause." Inside a policy, both sides refer to the new row, so it is always true.
• "Use the service role key in the client so the update works." That skips RLS for everyone holding your page source. See the anon vs service role guide.
• "Grant all on the table to authenticated" to clear a permission error. That undoes Fix 1 in one line.

How to test the fix

Run this in your project's SQL editor, or better, on a branch or local copy, with a real test user's id. It impersonates the API role the way Supabase's testing overview does, and rolls back so nothing changes:

-- 1. Attack: expect "permission denied for table profiles"
begin;
set local role authenticated;
set local request.jwt.claims = '{"sub":"<test-user-uuid>","role":"authenticated"}';
update public.profiles
set role = 'admin', plan = 'pro', credits = 999999
where id = '<test-user-uuid>';
rollback;

-- 2. Normal edit: expect one row back
begin;
set local role authenticated;
set local request.jwt.claims = '{"sub":"<test-user-uuid>","role":"authenticated"}';
update public.profiles
set display_name = 'Test'
where id = '<test-user-uuid>'
returning id, display_name;
rollback;

-- 3. What can the client roles write? Expect only the safe columns
select grantee, privilege_type, column_name
from information_schema.column_privileges
where table_schema = 'public' and table_name = 'profiles'
  and grantee in ('anon', 'authenticated')
  and privilege_type in ('INSERT', 'UPDATE')
order by grantee, privilege_type, column_name;

-- 4. One-line check per column: expect false
select has_column_privilege('authenticated', 'public.profiles', 'credits', 'UPDATE');

information_schema.column_privileges lists column privileges and, per the PostgreSQL docs, also shows table-level privileges as if granted on each column, so a leftover table grant shows up here too. has_column_privilege answers true if the role holds the privilege on the column or on the whole table. Run steps 1 and 4 against entitlements too if you use Fix 3, and put them in your test suite as described in testing Supabase RLS in CI.

Checklist

• Listed every column that decides access or money (role, plan, is_pro, credits, team ids)
• Table-level update and insert revoked from anon and authenticated on tables with such columns
• Column grants list only the fields your forms edit
• Profiles created by a trigger on auth.users, or client inserts limited to safe columns
• Plans and credits live in a table users can read but not write, updated by a verified webhook or server route
• Credits checked and decremented on the server in one statement
• No role in user_metadata, no secret key in the browser
• The attack test fails and the normal edit works, as authenticated, in CI

Copy-paste prompt for your AI coding tool

Paste this into Cursor, Claude Code, Lovable, or Bolt. Share table and column names only, never keys, passwords, or production data.

Review my Supabase schema for columns users can write but should not. For every table in public with an owner-only update or insert policy, list columns that control access or money (role, is_admin, plan, is_pro, subscription status, credits, quotas, team or org ids). Then write a migration that: revokes table-level insert and update from anon and authenticated on those tables; grants insert and update back only on the columns the app's forms edit; moves plan and credits into an entitlements table with an owner select policy and no user write policies, written only from server code with the secret key; and does not grant all on any table. Change my supabase-js calls to send only allowed columns and to name columns in select instead of using *. Add SQL tests that run as the authenticated role with set local request.jwt.claims and prove that setting role, plan, or credits fails with 42501 while editing display_name works.

Read the migration before it runs, test it on a branch, and run the tests above. More review prompts are in the security review prompt guide.

Sources

• Supabase: Column Level Security
• Supabase: Row Level Security
• Supabase: Securing your API (default grants, explicit grants, RLS)
• Supabase: User management (profiles table and trigger)
• Supabase: Postgres triggers
• Supabase: Custom claims and role-based access control
• Supabase: Understanding API keys
• Supabase: Testing overview (impersonating roles)
• supabase-js reference: update
• supabase-js reference: upsert
• PostgREST: Errors (42501 to 401 or 403)
• PostgreSQL: GRANT (table-level vs column-level privileges)
• PostgreSQL: Privileges
• PostgreSQL: CREATE POLICY (USING and WITH CHECK)
• PostgreSQL: PL/pgSQL trigger functions
• PostgreSQL: information_schema.column_privileges
• PostgreSQL: System information functions (has_column_privilege)
• Firebase: Control access to specific fields
• OWASP API Security Top 10 2023: API3 Broken Object Property Level Authorization
• OWASP Mass Assignment Cheat Sheet
• MITRE CWE-915: Improperly Controlled Modification of Dynamically-Determined Object Attributes