Users can see other users' data: fixing RLS and access rules

Everything works when you test with one account. Then someone finds they can read, change or delete other customers' rows through the public API, or give themselves admin at sign-up. This came up in 10 of the verified builder reports in our data, across Supabase, Firebase and hand-rolled backends.

What you'll see

• A signed-in user, or an anonymous visitor with only the public key, can list every row in a table instead of just their own.
• Changing an id in a request lets one user update or delete another customer's record.
• Anonymous requests can insert rows, or overwrite rows that belong to paying customers.
• A function or view the client can call returns other users' emails, tokens or password hashes.
• Someone signs up with a role field in their user metadata and gets admin access.
• Supabase Security Advisor reports 'Table publicly accessible' or 'Security policy relies on user-editable data'.

Why it happens

How to fix it

1. Turn on RLS for every table in an exposed schema

   With RLS on and no policies, the publishable key can reach no rows. Access stays denied until a policy allows it. Do this for every table in an exposed schema, including lookup and join tables.

   alter table public.orders enable row level security;

2. Write one policy per operation, tied to ownership

   Postgres accepts only one operation per `for` clause, so write separate select, insert, update and delete policies. Name the role with `to`, and put `with check` on every write. Updates also need a matching select policy, or they match no rows.

   create policy "orders_select_own" on public.orders
     for select to authenticated
     using ((select auth.uid()) = user_id);
   
   create policy "orders_insert_own" on public.orders
     for insert to authenticated
     with check ((select auth.uid()) = user_id);
   
   create policy "orders_update_own" on public.orders
     for update to authenticated
     using ((select auth.uid()) = user_id)
     with check ((select auth.uid()) = user_id);
   
   create policy "orders_delete_own" on public.orders
     for delete to authenticated
     using ((select auth.uid()) = user_id);

3. Keep roles where users cannot write

   Store roles in `raw_app_meta_data`, which the user cannot update, or in a roles table with access revoked from `anon` and `authenticated`. Add them to the token with a Custom Access Token Hook. Never read roles from `user_metadata`.

   create policy "reports_admin_read" on public.reports
     for select to authenticated
     using ((select auth.jwt() -> 'app_metadata' ->> 'role') = 'admin');

4. Close the paths that skip policies

   Move `security definer` functions out of every schema listed under Exposed schemas. On Postgres 15 and later, set `security_invoker` on views so they follow the RLS of their underlying tables. Never use the secret or `service_role` key in the browser, because it bypasses RLS.

   alter view public.order_summaries set (security_invoker = true);

5. On Firebase, check ownership in your rules

   Replace `if true` and `if request.auth != null` with ownership checks such as `request.auth.uid == resource.data.author_uid` for reads and deletes. For creates, check `request.resource.data` so a user cannot write a document that names someone else as its owner.

6. Test with two accounts and an anonymous client

   Sign in as user A and create a row. Then, as user B and as an anonymous client, try to read, update and delete it. When RLS blocks an update or delete, the call returns no rows instead of an error, so assert on the rows that come back.

   const { data: row } = await asA.from('orders')
     .insert({ user_id: idA, total: 10 }).select().single();
   
   const { data: seen } = await asB.from('orders').select().eq('id', row.id);
   const { data: edited } = await asB.from('orders')
     .update({ total: 0 }).eq('id', row.id).select();
   
   assert(seen.length === 0 && edited.length === 0);

Check it's fixed

• Supabase Security Advisor shows no 'Table publicly accessible', 'Security policy not enforced', 'Security policy relies on user-editable data' or 'View bypasses row-level security' warnings.
• As user B and as an anonymous client, reading, updating and deleting user A's row each returns zero rows, and an insert with A's `user_id` is refused.
• Signing up with `role: 'admin'` in `options.data` gives the new account no extra access.
• pgTAP tests created with `supabase test new` pass under `supabase test db`, and they assert on returned rows instead of using `lives_ok` for allowed writes.

Questions

Sources

• Supabase: Row Level Security
• Supabase: Securing your API
• Supabase: Performance and Security Advisors
• Supabase: Custom Claims and Role-based Access Control
• Firebase: Avoid insecure rules
• OWASP Authorization Cheat Sheet

Related guides

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