Expo + Supabase 應用程式 7 個會洩漏資料的 RLS 錯誤及 SQL 修正方法

TL;DR

• In an Expo + Supabase app, the client talks to Postgres directly. Row Level Security is the only thing between your users and every row.
• The same 7 mistakes keep showing up: RLS off, using (true), unchecked inserts, user_metadata roles, views that skip RLS, the secret key in the bundle, and open Storage.
• Each one has a short SQL fix below, plus a way to actually test your policies.

Supabase is great because your Expo app can skip the API server and query Postgres directly. The project URL and publishable key ship inside your bundle, and that's fine, by design.

The catch: that only works if RLS is doing its job. And most RLS bugs aren't clever exploits. They're the same handful of mistakes, often copy-pasted from a quickstart, or written by an AI agent that just wanted the query to stop returning [].

Here's the table we'll use:

create table public.todos (
  id bigint generated always as identity primary key,
  user_id uuid not null references auth.users (id),
  title text not null,
  done boolean not null default false
);

1. RLS was never turned on

Postgres doesn't enable RLS on new tables by default. Table created in a migration? It may be wide open through the REST API to anyone with your publishable key.

Find the offenders:

select tablename
from pg_tables
where schemaname = 'public'
  and rowsecurity = false;

Fix:

alter table public.todos enable row level security;

RLS on with zero policies means the client sees nothing. That's the right starting point. Supabase's Security Advisor also flags this, so check it after every migration.

2. using (true) everywhere

The classic "it works now" policy:

create policy "Enable read access for all users"
on public.todos for select
using (true);

That's every row, for everyone, including signed-out users. Fine for a public catalogue. For user data:

create policy "Users can read their own todos"
on public.todos for select
to authenticated
using ((select auth.uid()) = user_id);

to authenticated skips anonymous requests entirely. Wrapping auth.uid() in a select lets Postgres evaluate it once per statement instead of once per row. The Supabase RLS docs recommend this.

3. Inserts and updates that don't check the owner

create policy "Users can create todos"
on public.todos for insert
to authenticated
with check (true);

Any signed-in user can now insert rows with someone else's user_id. Fix it on both write paths:

create policy "Users can create their own todos"
on public.todos for insert
to authenticated
with check ((select auth.uid()) = user_id);

create policy "Users can update their own todos"
on public.todos for update
to authenticated
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

On updates, using decides which rows you can touch. with check decides what the row can look like afterwards, so nobody can hand their row off to another user.

4. Roles stored in user_metadata

-- don't
using ((auth.jwt() -> 'user_metadata' ->> 'role') = 'admin')

Users can write their own user_metadata straight from the client:

await supabase.auth.updateUser({ data: { role: 'admin' } });

Congrats, everyone's an admin. Use app_metadata (server-side only) or a roles table with its own RLS:

using ((auth.jwt() -> 'app_metadata' ->> 'role') = 'admin')

5. Views and security definer functions

Views created by postgres run with their creator's permissions by default, so they bypass RLS. On Postgres 15+:

create view public.todo_stats
with (security_invoker = true) as
select user_id, count(*) as total
from public.todos
group by user_id;

security definer functions have the same problem. If one lives in an exposed schema, any client can call it over RPC. Move it to a schema that isn't exposed, set search_path = '', and check auth.uid() inside it.

6. The secret key is in your bundle

The secret key (formerly service_role) ignores RLS completely. Here's the easy way to ship it:

# .env
EXPO_PUBLIC_SUPABASE_URL=https://xyz.supabase.co
EXPO_PUBLIC_SUPABASE_KEY=sb_publishable_...   # fine
EXPO_PUBLIC_SUPABASE_SECRET=sb_secret_...     # leaked to every user

Every EXPO_PUBLIC_ variable is inlined into the JS bundle, and the Expo docs say it's visible in plain text in the compiled app. Unzip the APK and there it is. If it ever shipped, rotate it. Anything privileged (email, paid AI APIs, payments) goes in an Edge Function that holds the secret.

If you'd rather not retrofit all of this, the AppLighter Expo + Supabase starters ship with RLS on every table and secrets kept in Edge Functions. They also include an /audit-security command your coding agent can run against these exact mistakes.

7. Storage is wide open

Storage files live in storage.objects, which is also covered by RLS, and it's easy to forget. Public buckets serve every file to anyone with the URL. Scope access per user folder:

create policy "Users can read their own avatar files"
on storage.objects for select
to authenticated
using (
  bucket_id = 'avatars'
  and (storage.foldername(name))[1] = (select auth.uid())::text
);

Actually test it

A policy you haven't tried to break is a policy you're hoping works. Impersonate a user in the SQL editor:

begin;
set local role authenticated;
set local request.jwt.claim.sub = '00000000-0000-0000-0000-000000000001';

select * from public.todos;  -- only this user's rows

insert into public.todos (user_id, title)
values ('00000000-0000-0000-0000-000000000002', 'not mine');  -- should fail

rollback;

Then repeat it from the app: sign in as user B and fetch user A's row by id. You should get nothing back. Move the important checks into pgTAP and run supabase test db in CI so a future migration can't undo them.

Quick checklist

• RLS enabled on every exposed table
• No using (true) / with check (true) on user data
• Insert and update policies compare auth.uid() to the owner column
• Roles come from app_metadata or a table, never user_metadata
• Views use security_invoker = true
• Only the publishable key lives in EXPO_PUBLIC_ vars
• Storage policies are scoped per user
• Policies are tested by impersonation

Which of these have you shipped by accident? 👀 Drop your RLS horror story (or your favourite policy pattern) in the comments.