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

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

Cause 1

RLS denies by default and a per-operation policy is missing

When RLS is enabled and no policy applies, Postgres denies by default. No rows are visible or updatable, so a select returns an empty result instead of an error. Policies are per operation. An insert policy alone is not enough for insert().select(): rows returned by RETURNING must also pass a SELECT policy, and Postgres throws an error if they do not. Supabase's docs add that an UPDATE also needs a matching SELECT policy.

Cause 2

The profile row is written from the client before a session exists

With Confirm email switched on, Supabase's signUp returns a user but a null session. If the client then inserts into a profiles table, the request is anonymous and auth.uid() returns null. A comparison such as null = user_id is never true, so the WITH CHECK fails and sign-up breaks.

Cause 3

The policy compares the wrong column

A policy may compare auth.uid() against the row's own id instead of its owner column, or against a column that holds a different identifier. A policy may also trust raw_user_meta_data, which signed-in users can update themselves. Supabase's docs say that field is not a good place for authorization data. A role stored there lets users promote themselves.

Cause 4

Claims set on the server never reach the rule

In Firebase, custom claims set with the Admin SDK only appear in a user's ID token after one of three things: the user signs in again, the old token expires and is refreshed, or the client forces a refresh with getIdToken(true). Until then, a rule checking request.auth.token.admin sees the old token and denies the request. In Supabase, authorization data belongs in raw_app_meta_data, which users cannot change.

Cause 5

Something bypasses the rules and nobody checks

Tables in the public schema with RLS switched off are readable by anyone holding the publishable key. Views created by the postgres user bypass RLS by default. The service_role key has the bypassrls attribute, so a test or script run with it tells you nothing about what customers can reach.

How to fix it

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();

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);

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);
```
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.
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).

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.

Fix it with Gemmein

Each Gemmein collection has exactly one safety rule, such as private, where each signed-in user sees and edits only their own records. ownerUserId is server-derived and read-only, and touching a record your session can't see returns 404 not_found, never a 403 that confirms it exists.

Pick the rule that matches who may see the data
Use private for anything personal and direct when one user sends to another; shared is wrong for personal data because every signed-in user reads every record. In local dev a collection is born from the terminal's rule conversation (your human picks the rule), npx gemmein collection add <name> or a dropped declaration file; npx gemmein sync creates it in the cloud app's dev environment, and it reaches Live by go-live and promote.
```
// gemmein/collections/notes.json
{ "rule": "private", "means": "each person keeps their own notes" }
```
Finish sign-in before writing the profile
A missing sign-in comes back as denied (401 — sign in first). Make the profile a keyed create on user.userId (not user.id, which is undefined): one record per user, and your own retry gets your existing record back (existing: true). In a community collection everything is public, so keep record data minimal.
```
await g.auth.sendEmailCode(email);
await g.auth.verifyEmailCode({ email, code });
const user = await g.auth.currentUser();
await g.collection("profiles").create(data, { key: "profile:" + user.userId });
```
Leave ownership to the server
ownerUserId is server-derived and read-only, so never store your own userId, role or owner fields inside data, and there is no role to branch on. Under private, each signed-in user sees only their own records.
```
const { records } = await g.collection("notes").list();
```
Keep secret keys on the server and scope them narrowly
gemmeinServer() refuses to run in a browser or a phone, npx gemmein check flags a build that carries a secret key and npx gemmein go-live refuses it. A key that writes creates and updates records under any rule; a secret key can be scoped to particular collections (npx gemmein keys create --access, or the dashboard's API keys page), a call outside that scope answers 403 scope_denied, scopes are fixed when a key is created, and every server write is a Logs row naming the key and the record's person.

Questions

Why does my Supabase query return an empty array with no error?

With RLS enabled, rows that no SELECT policy allows are filtered out silently instead of raising an error. Check that a SELECT policy exists for the role making the request, and that it compares auth.uid() with the right owner column.

Can I use the service_role key to get past the error?

Only in server code. The service_role key bypasses RLS completely, so if it ships to the browser, every visitor gets full access. Supabase's docs say never to expose it to customers.

Is it safe to store a user's role in user metadata?

No. The signed-in user can update raw_user_meta_data, so a policy that trusts it lets users grant themselves access. Use raw_app_meta_data, which users cannot change.

I set a Firebase custom claim but my rule still denies the request. Why?

The client is still sending the old ID token. The new claim arrives on the next sign-in, on the refresh after the old token expires, or immediately if you call getIdToken(true).

Do Postgres views respect my RLS policies?

Not by default, because views are usually created by the postgres user. On Postgres 15 or later, create them with security_invoker = true. On older versions, revoke access from anon and authenticated, or move the view to a schema the API doesn't expose.