RLS blocks your own users or leaks their data: how to fix it

Sign-up fails with a row-level security error, a signed-in user gets an empty list of their own records, or a shared screen shows someone else's data. Nine builder reports in our data describe this pattern. It appears on Supabase, Firebase, Lovable, Base44 and hand-rolled backends. It usually surfaces just before launch or just after the first real customers sign in.

What you'll see

• Sign-up throws "new row violates row-level security policy" when the app creates the user's profile row.
• A signed-in user's query returns [] with no error, even though the rows are visible in the dashboard table editor.
• An insert succeeds but the call that reads the row back fails, or an update silently changes nothing.
• File uploads to storage fail for every user, or one user can open another user's files.
• A user can read, edit or delete other customers' records, or can set a field on their own account that makes them an admin.
• You cannot tell whether the rules are right, because you have only ever tested as yourself.

Why it happens

How to fix it

1. Create per-user rows in a trigger, not from the client

   Let the database create the profile when the auth user is inserted, so no client session is needed. A failing trigger blocks sign-ups, so test it before deploying.

   create function public.handle_new_user()
   returns trigger language plpgsql
   security definer set search_path = ''
   as $$
   begin
     insert into public.profiles (id) values (new.id);
     return new;
   end;
   $$;
   
   create trigger on_auth_user_created
     after insert on auth.users
     for each row execute procedure public.handle_new_user();

2. Write one policy per operation against the owner column

   Give every operation the app uses its own policy. SELECT and DELETE use USING, INSERT uses WITH CHECK, and UPDATE uses both. If the app calls insert().select() or updates rows, it also needs a SELECT policy.

   alter table public.notes enable row level security;
   
   create policy "owner reads" on public.notes
     for select to authenticated
     using ((select auth.uid()) = user_id);
   
   create policy "owner inserts" on public.notes
     for insert to authenticated
     with check ((select auth.uid()) = user_id);

3. Log the error and don't trust an empty array

   Log the error's code and message instead of rendering an empty list. If a user gets an empty result for data they own, the usual cause is a missing or mismatched SELECT policy. The data is usually still there.

   const { data, error } = await supabase
     .from('notes')
     .insert({ body })
     .select();
   if (error) console.error(error.code, error.message);

4. Keep authorization data where users cannot write it

   In Supabase, store roles in raw_app_meta_data and never reference user_metadata in a policy. In Firebase, set claims with the Admin SDK, then call getIdToken(true) on the client before relying on the new claim.

5. Fix storage policies on storage.objects

   Storage policies live on the storage.objects table. An upload needs an INSERT policy, and an upsert also needs SELECT and UPDATE. Scope each policy to the bucket, and to a folder named after the user's id using storage.foldername(name).

6. Add a two-account test to CI

   Sign in as user A and run queries against user B's rows. Test both what must succeed and what must fail. Run supabase test db on every pull request and before every deploy.

   begin;
   select plan(2);
   set local role authenticated;
   set local request.jwt.claim.sub = '<user-a-uuid>';
   select is(
     (select count(*) from public.notes where user_id = '<user-b-uuid>'),
     0::bigint, 'A cannot read B');
   select throws_ok(
     $$insert into public.notes (user_id, body) values ('<user-b-uuid>', 'x')$$,
     '42501', null, 'A cannot write as B');
   select * from finish();
   rollback;

Check it's fixed

• Sign up a fresh account with Confirm email switched on and confirm it. Check that the profile row exists and that the user can read it.
• The two-account tests pass in CI. User A can read and write their own rows, and every attempt on user B's rows returns nothing or fails with an RLS error.
• Run supabase db advisors --type security (or open Security Advisor in the dashboard). Confirm there are no findings for rls_disabled_in_public, policy_exists_rls_disabled, rls_enabled_no_policy or rls_references_user_metadata.
• Call the API with only the publishable key and no session. Confirm it returns no customer rows or files.

Questions

Sources

• Supabase: Row Level Security
• Supabase: User management (profile trigger)
• Supabase JavaScript reference: signUp
• PostgreSQL: CREATE POLICY
• Supabase: Testing overview
• Supabase Storage: Access control
• Supabase: Database advisors
• Firebase: Control access with custom claims

Related guides

← Back to the full report: what breaks when AI apps go live