跳到正文
DEV Community· Solvion·· 15 小時前AI 評分58

研究分析 844 個 Supabase 項目的 SQL,列出常見權限設定問題

We read the SQL of 844 Supabase apps. Here's what we found

AI 導讀

GuardLayer 分析 844 個公開 Supabase 項目已提交的 SQL,發現 51.3% 至少符合一項所列設定模式,不包括只按登入狀態授權的政策。最常見的模式是允許任何已登入用戶讀取或更改所有資料列(41.7%);樣本來自提交 SQL 的公開倉庫,掃描命中不代表問題必然可被利用。

正文

Originally published on guardlayer.io.

We read the committed SQL of 844 public Supabase projects in November 2026. 17% create at least one table without turning on row level security. 28% have a policy whose condition is simply true on a table that is written to or holds personal data. 19% expose a SECURITY DEFINER function that the API roles can call and that never checks who is calling. 12% let a user give themselves more rights, most often by editing their own role or plan. Half the projects have at least one of these patterns.

None of this is a flaw in Supabase. Every pattern below is a project setting, and each has a fix of a few lines. We publish the numbers because the same few mistakes keep coming back, and they are easier to avoid once you know what they look like.

How we did it

  • What we read: public GitHub repositories of Supabase apps, found through GitHub searches across app categories (bookings, clinics, shops, schools, dating, internal tools) and languages. We kept the 844 that commit SQL: migrations, a schema.sql or a pg_dump snapshot.
  • How we read it: each project's SQL was run through GuardLayer's static scanner (the same engine as the product), with all files read together in order. A table that gets RLS in a later migration, or a function revoked further down, doesn't count against the project.
  • What counts: a project counts once per pattern, however many tables or functions match. We count warning and critical findings only. Informational ones, such as a public read on a table of blog posts, aren't counted.
  • What we left out: public storage buckets (22% of projects). Most are avatars and product images that are meant to be public, and the SQL alone can't tell which ones aren't.

Separately, between November 7 and 18 we checked 50 projects by hand (43 of them are in this sample), traced each problem through the app's own code, and reported it privately to the owners. That's the high-confidence half of this study. The rest of it is what a careful read of the SQL shows, not proof that every match can be exploited.

The results

Pattern Projects Share 95% CI
Policy lets any signed-in user read or change every row 352 41.7% 38.4–45.1%
Policy condition is just true (write or sensitive table) 232 27.5% 24.6–30.6%
SECURITY DEFINER function the API can call, no caller check 158 18.7% 16.2–21.5%
Table created without RLS 142 16.8% 14.5–19.5%
Users can raise their own rights 99 11.7% 9.7–14.1%
Table granted directly to anon 62 7.3% 5.8–9.3%
At least one of the above, apart from the signed-in row 433 51.3% 47.9–54.7%

Size doesn't help. Among the 685 projects with three or more SQL files, the share with at least one of these patterns is slightly higher, 54.6%, than across all 844.

1. "Signed in" treated as "allowed"

The most common pattern, in 42% of projects, is a policy like using (auth.role() = 'authenticated') or to authenticated using (true) on a table that holds other people's data. It reads as "only logged-in users", but sign-ups on Supabase are open by default, so "logged in" means anyone who has made an account. Some of these are deliberate, like a shared team table in an app with closed sign-ups. Many are not. The fix is to scope the rows: using (user_id = auth.uid()), or a membership check for team data. More on this in the Supabase RLS guide.

2. Policies that say true

create policy ... using (true) with check (true) lets everyone the policy applies to do what it allows, on every row: anyone holding the public anon key, or every signed-in user when it's written to authenticated. We only count it on tables that are written to or that hold personal data: a profiles table readable by anyone, emails included, or a table any visitor can update. Not every match is a leak. A public waitlist form is meant to accept inserts from anyone. But this is the pattern behind more of our reports than any other. Why using (true) is a trap covers when it's fine.

3. Definer functions anyone can call

A SECURITY DEFINER function runs with its owner's rights and skips RLS. On Supabase, new functions in public can be executed by anon and authenticated by default, so a definer function that takes an id from the caller and never checks auth.uid() hands that id's data, or that id's write access, to anyone. 19% of projects have at least one. The impact varies a lot: a function that bumps a view counter is a nuisance, one that changes another user's plan is not. A common reason they stay open is that revoke execute ... from public alone doesn't remove the direct grants to anon and authenticated; the Kagelin case study shows how that plays out.

4. Tables without RLS

17% of projects create at least one table and never enable RLS on it anywhere in their SQL. Tables made in the Supabase dashboard get RLS by default; tables made with SQL, which is how AI coding tools make them, don't. Since October 30, 2026 Supabase no longer grants brand-new tables to the API roles automatically, which closes this for new projects. Older projects keep the automatic grants, and the usual fix for the resulting permission denied error is a grant, which reopens the table. See what changed with the Data API grants.

5. Users who can raise their own rights

This is the most damaging pattern, in 12% of projects, and the hardest to see, because the policy looks right. Three variants:

  • Editing their own role or plan (8.6%). create policy ... for update using (auth.uid() = id) on profiles, where the row also holds role, is_admin, plan, credits or balance. RLS picks rows, not columns, so the user can set any column on their own row. The VivTrack case study is a real example: any user could give themselves Pro.
  • Choosing their role at sign-up (1.8%). A handle_new_user trigger that copies raw_user_meta_data->>'role' into profiles. The browser sets that metadata.
  • Adding themselves to a team (3.0%). An insert policy on a *_members table that only checks user_id = auth.uid(), so anyone can join any team, often with any role.

The fix is a column grant (revoke update on public.profiles from authenticated; grant update (display_name, avatar_url) on public.profiles to authenticated;), a guard trigger, or moving the column to a table only the server writes.

What happened when we told people

We reported 50 of these projects privately, with the exact file, line and fix, between November 7 and 18. As of November 21:

  • 5 have shipped a fix, the fastest about an hour and a half after the report.
  • 2 have confirmed and are fixing.
  • 2 have replied and plan to fix.
  • 2 made their repository private.
  • The rest haven't replied yet. Most reports are less than two weeks old.

One reply is worth passing on. A team had already fixed the problem in their live database in August, but never committed the migration. Their production database was safe, while a rebuild from the repository would have reopened the hole. If you fix something in the SQL editor, put the same SQL in a migration the same day.

Three teams let us write up their cases: LabStock, Kagelin and VivTrack.

Check your own project

Run these in the SQL Editor. Each returns nothing on a project without the pattern.

Tables in public with RLS off:

select c.relname as table_name
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r' and not c.relrowsecurity;

Policies whose condition is just true, or that only check for a signed-in user:

select tablename, policyname, cmd, roles, qual, with_check
from pg_policies
where schemaname = 'public'
  and (qual = 'true' or with_check = 'true'
       or qual ilike '%auth.role()%authenticated%');

Definer functions the API roles can run:

select p.oid::regprocedure as function,
       has_function_privilege('anon', p.oid, 'execute') as anon_can_run,
       has_function_privilege('authenticated', p.oid, 'execute') as authenticated_can_run
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.prosecdef;

Update policies on tables that hold a role, plan or balance are in the self-check of the VivTrack case study.

Does GuardLayer catch this?

Yes. Every number in this study comes from GuardLayer's scanner, so the code scan flags each of these patterns in your migrations on every push, with the file, the line and the fix. Live check connects to the running project read-only and checks the real policies, grants and functions, which also covers anything you changed in the dashboard and never committed.

Limits

  • It's a sample, not a census. Public repositories that commit their SQL lean towards solo developers, side projects and AI-built apps. The rates are not a measure of all Supabase projects.
  • SQL isn't the whole picture. A project can enable RLS or revoke a grant in the dashboard without committing it, which would make a finding here a false alarm, and the reverse is just as possible.
  • A pattern isn't always a leak. Some matches are deliberate. We counted them anyway, because the SQL can't tell intent, and said where that matters most.

FAQ

Is Supabase insecure?
No. Supabase gives you RLS, grants and per-function permissions. Every pattern here is a project setting. See is Supabase secure?

What's the most common Supabase security mistake?
In this sample, a policy that treats any signed-in user as allowed (42% of projects). The most damaging is letting users edit their own role or plan.

Why did you leave public buckets out?
Most are meant to be public (avatars, product images), and the SQL can't tell which aren't.

Can I get the data?
The aggregated numbers are in this post. We don't publish per-project results, because many of the issues are still open.

來源:DEV Community · dev.to