Security
Can users make themselves admin? Protecting role columns in Supabase
Row Level Security decides which rows a user may change, not which columns. So if role or is_admin sits on the profiles table and users may edit their own profile, they can also edit their own role - hiding the admin page in the app does not stop that. Below: how to check your project in the SQL Editor, and two fixes: a separate roles table users cannot write to (what Supabase recommends), or column-level grants as a quick patch.
What you are seeing
Like most access-control bugs, this one rarely announces itself. The signals that should make you check:
- Your app has an admin area, staff accounts, paid tiers or credits, and the value that decides who gets them is stored on a table such as
profilesorusers. - The only thing between a normal user and the admin page is a check in the front-end code, such as
if (profile.role === 'admin')before showing a menu item. - The Security Advisor lists
Security policy relies on user-editable data(lint 0015,rls_references_user_metadata): a policy trustsuser_metadata, which end users can change (Database Advisors). - Someone you did not appoint shows up with admin rights, or a user reports that they could open a page meant only for you.
- You asked your AI tool to add roles and never looked at the SQL it wrote.
How to check it yourself
Run these against your own project only. Steps 1 to 3 only read. Step 4 makes a change inside a transaction and rolls it back, so nothing is saved. All of them run in the Supabase dashboard under SQL Editor.
1. Find the columns that hold power
This lists every column in the public schema whose name suggests it controls access or money, and whether the signed-in role (authenticated) and the anonymous role (anon) hold the privilege to write it. It uses Postgres's own `has_column_privilege` function, which is true whether the privilege comes from a table-wide grant or a grant on that one column.
select
c.table_name,
c.column_name,
has_column_privilege('authenticated', format('%I.%I', c.table_schema, c.table_name), c.column_name, 'UPDATE') as signed_in_can_update,
has_column_privilege('authenticated', format('%I.%I', c.table_schema, c.table_name), c.column_name, 'INSERT') as signed_in_can_insert,
has_column_privilege('anon', format('%I.%I', c.table_schema, c.table_name), c.column_name, 'INSERT, UPDATE') as anon_can_write
from information_schema.columns c
where c.table_schema = 'public'
and c.column_name ~* '(role|admin|staff|permission|plan|tier|credit|verified|approved)'
order by c.table_name, c.column_name;true means the grant allows the write - whether a user can actually do it on their own row is decided by the policies in step 2.true here is common and not yet a bug: on existing projects, every new table in public started with every privilege granted to anon and authenticated (Row Level Security). It becomes a bug when step 2 shows a policy that lets users update or insert their own row in that table.
2. Read the write policies on those tables
select tablename, policyname, cmd, roles, qual, with_check
from pg_policies
where schemaname = 'public'
and (
cmd in ('INSERT', 'UPDATE', 'ALL')
or qual ilike '%user_metadata%'
or with_check ilike '%user_metadata%'
)
order by tablename, cmd;- An
UPDATE(orALL) policy on the table from step 1 with a condition likeauth.uid() = id: the user may change their own row, and nothing in the policy limits which columns. Ifsigned_in_can_updatewastrueforrole, they can set their own role. - An
INSERTpolicy on that table: check whether a user could create their row withrole = 'admin'already filled in, for example if the app creates the profile itself instead of a trigger, if older users have no profile row yet, or if a user can delete their profile and create it again. - Any condition that reads
user_metadata(written asauth.jwt() -> 'user_metadata'): Supabase's docs say this data "can be modified by authenticated end users" and is "not a good place to store authorization data" (Row Level Security).
3. Check the sign-up trigger and your database functions
Many Supabase apps create the profile row with a trigger on auth.users, often called handle_new_user. The pattern in Supabase's docs copies fields from raw_user_meta_data into profiles (Managing user data). That column is filled from whatever the app sends at sign-up. If the trigger also copies a role from it, anyone who signs up can choose their own role. List the functions in public and look at the ones flagged:
select
p.proname as function_name,
p.prosecdef as security_definer,
p.prosrc ilike '%raw_user_meta_data%' as reads_signup_metadata,
p.prosrc ~* '\m(role|is_admin)\M' as mentions_role
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public'
order by security_definer desc, function_name;reads_signup_metadata or mentions_role is true (Database > Functions in the dashboard) and read what it does with the role.Pay extra attention to security_definer = true functions that change a role, such as set_user_role or make_admin. A security definer function runs with the privileges of its owner (usually postgres), so it bypasses RLS, and one that lives in public can be called over the API by any signed-in user (Security Advisor lint 0029, Database Advisors). Unless the function itself checks that the caller is already an admin, it is a promotion button for everyone.
4. Try it with a test account, then roll it back
Create or pick a normal test user, copy its User UID from Authentication > Users, and run the block below. It switches to the signed-in role and identity the same way Supabase's own policy tests do (set local role and set local request.jwt.claim.sub, Row Level Security), tries the promotion, and ends with rollback, so nothing is saved.
begin;
set local role authenticated;
set local request.jwt.claim.sub = 'YOUR-TEST-USER-UID';
update public.profiles
set role = 'admin'
where id = 'YOUR-TEST-USER-UID'
returning id, role;
rollback;is_admin = true instead of role = 'admin', or where user_id = ... if the profile is linked by a user_id column.- A row comes back showing the admin value: any signed-in user can make themselves admin. Fix it before anything else.
permission denied(code42501): the role has no update privilege on that column. That is what you want.- Zero rows and no error: RLS did not let this user update the row at all. Either there is no update policy, or the
whereclause does not match their row. Note that an update also needs a matching select policy to work (Row Level Security). - An error about an invalid value: your column uses another value for admins, or an enum type. Use the exact value your app uses.
5. Look at where the app checks the role
From the project root of the code your AI tool produced (for Lovable, the repository synced to GitHub):
grep -rnE 'is_admin|isAdmin|user_metadata|role.{0,6}admin' \
--exclude-dir=node_modules --exclude-dir=.git --exclude-dir=dist .Why it happens
Postgres runs two separate checks before a request touches a table. Grants decide whether a role may run an operation (such as update) on the table, or on specific columns. Policies decide which rows that operation applies to (Row Level Security). The typical generated policy, "users can update their own profile", answers the row question correctly and says nothing about columns. Supabase's docs show exactly this: an owner-only update policy still gives the owner "full access to update the row, including all of the columns" (Column Level Security).
- The role is stored next to data the user is meant to edit, so a single table-wide update grant covers both.
- The admin check lives only in the browser. Lovable's own guidance says client-side checks "can be inspected, modified, or bypassed by users" and that roles and permissions must be checked server-side (Lovable docs). Your Supabase URL and public key are in the front end, so anyone signed in can call the database API directly and skip your screens.
- The role is read from
user_metadata, which users can change themselves, or copied from sign-up metadata by a trigger. - A
security definerhelper that changes roles was created inpublic, where the API exposes it. - Postgres privileges do not subtract the way people expect. Revoking update on one column while the table-wide grant stays does nothing: in the PostgreSQL docs' words, "the table-level grant is unaffected by a column-level operation" (GRANT).
How to fix it
select id, role from public.profiles where role = 'admin';) and compare it with who should be. If the check above succeeded, someone may already have used it. Test the changes on a Supabase branch or a copy of the project before production, and keep them in a migration.Option A (recommended): move roles to a table users cannot write
Supabase's docs recommend "RLS policies in combination with a dedicated table for handling user roles" over column-level privileges (Column Level Security). Signed-in users may read their own role, so the app can still show or hide the admin menu, but they get no insert, update or delete privilege on the table at all:
create table public.user_roles (
user_id uuid primary key references auth.users (id) on delete cascade,
role text not null check (role in ('admin', 'staff'))
);
alter table public.user_roles enable row level security;
revoke all on table public.user_roles from anon, authenticated;
grant select on table public.user_roles to authenticated;
create policy "Users can read their own role"
on public.user_roles for select
to authenticated
using ( (select auth.uid()) = user_id );insert into public.user_roles (user_id, role) values ('USER-UID', 'admin');), which runs with full privileges.Copy the admins you confirmed in the warning above, not blindly every row that says admin:
insert into public.user_roles (user_id, role)
values
('CONFIRMED-ADMIN-UID', 'admin')
on conflict (user_id) do update set role = excluded.role;The new table does not close the old hole by itself. Until the role column on profiles is gone, stop users writing it, the same way as in option B (replace display_name, avatar_url with your real editable columns):
revoke insert, update on table public.profiles from anon, authenticated;
grant update (display_name, avatar_url) on table public.profiles to authenticated;
select tablename, policyname, cmd, qual, with_check
from pg_policies
where schemaname = 'public'
and (qual ilike '%role%' or with_check ilike '%role%');private.is_admin() (below). Permissive policies are combined with OR (PostgreSQL: CREATE POLICY), so an old admin policy left in place still grants access to anyone who can set their own profiles.role.Then give your policies one trustworthy way to ask "is this user an admin?". This follows the pattern in Supabase's docs: a security definer function with search_path set to empty and fully qualified names, kept in a schema that is not exposed through the API (Use security definer functions):
create schema if not exists private;
create or replace function private.is_admin()
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
select exists (
select 1
from public.user_roles
where user_id = (select auth.uid())
and role = 'admin'
);
$$;
revoke execute on function private.is_admin() from public, anon;
grant usage on schema private to authenticated;
grant execute on function private.is_admin() to authenticated;private to Exposed schemas in the API settings. Functions are executable by everyone by default in Postgres (Privileges), hence the revoke.Use it in the policies of every table the admin area reads or writes. Permissive policies on the same table and operation are combined with OR (PostgreSQL: CREATE POLICY), so this adds admin access next to the existing owner policy instead of replacing it:
create policy "Admins can read all orders"
on public.orders for select
to authenticated
using ( (select private.is_admin()) );
create policy "Admins can update all orders"
on public.orders for update
to authenticated
using ( (select private.is_admin()) )
with check ( (select private.is_admin()) );Finally, change the app to read the role from user_roles instead of profiles, and once nothing reads the old column, remove it (alter table public.profiles drop column role;) - after a backup, since that deletes the data in it. A role changed in user_roles takes effect on the user's next request.
Option B (quick patch): stop users writing the column
If you cannot restructure today, take away the table-wide write privileges and give back only the columns users are meant to edit. Revoking just the role column would not work, because the table-wide grant stays in force (GRANT). Replace display_name, avatar_url with your real editable columns:
alter table public.profiles alter column role set default 'user';
revoke insert, update on table public.profiles from anon, authenticated;
grant update (display_name, avatar_url) on table public.profiles to authenticated;
-- Only if the app creates the profile row itself instead of a trigger:
grant insert (id, display_name, avatar_url) on table public.profiles to authenticated;user.- A save that sends the whole profile, including
role, now fails withpermission denied. Change the app to send only the fields the user edited. - This only restricts writes. Supabase's warning that
select *fails applies to roles whose read access is restricted by column (Column Level Security); reads here are unchanged. - Every new column you add later is not editable by users until you add it to the grant. That is the safe default, but expect your AI tool to hit
42501and try to "fix" it with a table-wide grant. Do not accept that change.
In both cases
- Sign-up trigger: never take a role from
raw_user_meta_data. Insert the profile with fixed defaults and assign roles only yourself. - Policies that read
user_metadata: rewrite them to useprivate.is_admin()or another server-controlled source. If you prefer roles inside the token,app_metadatacannot be changed by the user, but a JWT is not updated until it is refreshed, so a removed admin keeps access until then (Row Level Security). Supabase's custom claims and RBAC guide shows the full token-based setup with a custom access token hook. security definerfunctions inpublicthat change roles: move them out of the exposed schema, or revokeexecutefromanonandauthenticatedand call them only from your own server code.- Edge Functions or API routes that do admin work: identify the caller from their access token, never from a user id or role sent in the request body, and look the role up in
user_roles. Lovable's guidance: every server function "must check who is calling and whether they may perform the action" (Lovable docs). - Keep hiding the admin menu in the UI for normal users - that is good design, it is just not security.
Verify
Rerun step 4 as the test user. With option B it should return 42501. With option A, run the same block with the promotion replaced by insert into public.user_roles (user_id, role) values ('YOUR-TEST-USER-UID', 'admin'); - it should also return 42501. Also rerun the original step 4 block on profiles until you have dropped the old column: it must fail too. Then rerun step 1, open the Security Advisor, and click through the app as a normal user and as an admin: a screen that broke usually means a policy or a column grant you have not added yet.
When to get help
One admin role on a small app is manageable with the steps above. Get a second pair of eyes when:
- Roles are more than admin and user: staff per organisation, team owners, invited members. Then role checks mix with multi-tenant policies, and the mistakes get quieter.
- Roles unlock money: credits, paid plans, payouts or refunds stored on a table users can reach.
- Step 4 already succeeded on a live app and you do not know whether anyone used it, or what an unexpected admin account did.
- Admin actions run in Edge Functions or API routes you did not write and cannot easily read.
Sources
Primary documentation this guide was checked against. Platforms change - if something here no longer matches, the source wins.
- Row Level SecuritySupabase docs
- Column Level SecuritySupabase docs
- Custom Claims and Role-based Access Control (RBAC)Supabase docs
- Managing user dataSupabase docs
- Database Advisors (lint reference)Supabase docs
- GRANTPostgreSQL docs
- PrivilegesPostgreSQL docs
- System information functions (has_column_privilege)PostgreSQL docs
- CREATE POLICYPostgreSQL docs
- Avoiding security pitfallsLovable docs
Frequently asked questions
- Is hiding the admin page in the app enough?
- No. Your Supabase URL and public key ship to the browser, so any signed-in user can call the database API directly and skip your screens. Hiding the menu is fine for design, but the database policies and your server functions must refuse admin actions on their own.
- Why doesn't my "users can update their own profile" policy protect the role column?
- Row Level Security filters rows, not columns. A policy that lets a user update their own row lets them change every column in it that their grants allow. Either keep the role in a separate table users cannot write to, or restrict the update grant to specific columns.
- Can I store the role in user_metadata?
- No. Supabase's docs say user_metadata can be updated by the signed-in user and is not a good place for authorization data, and the Security Advisor flags policies that rely on it (lint 0015). app_metadata cannot be changed by the user, but the value in a token only updates when the token is refreshed.
- I revoked update on the role column but users can still change it. Why?
- Because the table-wide update grant is still there, and Postgres does not subtract a column from it. Revoke update on the whole table first, then grant update on the specific columns users may edit.
- Someone may already have made themselves admin. What now?
- Close the hole first, then list everyone with an admin role and remove the ones you did not appoint. Review what those accounts could reach and changed. If personal data of other users may have been accessed, get advice on whether you have to report it.
Related guides
Fixed-price audit
Want someone to check the whole app, not just this?
A review of data security, production readiness and App Store readiness for apps built with AI tools. 900 USD, a ranked list of fixes within 5 business days.