Supabase RLS: Find and Fix Database Security Risks
Краткое содержание
Pixel shares a practical guide to interpreting Supabase Security Advisor warnings about Row-Level Security. It explains four table states—RLS disabled, missing policies, unrestricted policies, and restricted access—and provides a read-only SQL query to identify risks before changing settings.
DEV Community
"RLS Disabled in Public": anyone with your project URL can read your whole database
Your Supabase Security Advisor has gone red. The wording is some of these: Table publicly accessible. "Anyone with your project URL can read, edit, and delete all data in this table because Row-Level Security is not enabled." Security policy not enforced. "A security policy exists but has no effect because Row-Level Security hasn't been turned on for the table." No access rules defined. "Row-Level Security is enabled but no policies exist, so no data can be read or written through the API." Security policy allows unrestricted access. "An RLS policy uses an always-true condition like USING (true), which defeats the purpose of having Row-Level Security enabled." Those are four separate warnings for what is really one question: who can reach each table. And they are the normal state of an app that an AI built for you, because the builder's job was to make the app work, and RLS off is the configuration where everything works. This note tells you which of four states each of your tables is in, and what to do about each. The first thing is one query. Do not start clicking through the Table Editor. Run this first Supabase dashboard, SQL Editor, new query, paste this, run it. It reads catalogue tables only. It changes nothing. select
t.tablename,
t.rowsecurity as rls_on,
count(p.policyname) as policies,
case
when not t.rowsecurity and count(p.policyname) > 0
then 'OPEN: policies exist but RLS is OFF, so they do nothing'
when not t.rowsecurity
then 'OPEN: no RLS at all, anyone can read and write this table'
when count(p.policyname) = 0
then 'LOCKED: RLS on with no policies, your app can read nothing'
when bool_or(p.qual = 'true')
then 'OPEN TO READS: a policy uses USING (true)'
when bool_or(p.with_check = 'true')
then 'OPEN TO WRITES: a policy uses WITH CHECK (true)'
else 'restricted'
end as verdict
from pg_tables t
left join pg_policies p
on p.schemaname = t.schemaname and p.tablename = t.tablename
where t.schemaname = 'public'
group by t.tablename, t.rowsecurity
order by 2, 1;
You get one row per table with a plain verdict. I wrote this query and ran it on a real PostgreSQL 16.15 server against eight tables built to be one of each case, and it labelled all eight correctly. Anything that says restricted is fine as far as this check goes. Everything else is below. Why a query and not the dashboard: the Table Editor shows you whether RLS is on. It does not tell you that the three policies you can see are doing nothing, which is the state that fools people, and it does not tell you that your one policy says true. State 1. RLS off. The table is open to the internet This is the advisor's only ERROR, and it deserves to be. I tested what it means rather than restating it. On PostgreSQL 16.15 I made a table with two users' rows in it, granted the table to a role standing in for anon, and left RLS off. As that role I read both rows including both users' card numbers, then changed another user's email address (UPDATE 1) and deleted their row (DELETE 1). Checking as the table owner afterwards, one row was left. No error at any point. Full create, read, update and delete, by a role with no password, holding only the key that is already sitting in your deployed JavaScript. If you are not sure whether your key is public, it is: Your Supabase key is in your deployed JavaScript. The publishable key is meant to be public. RLS is the only thing standing between it and your data. The fix is one line per table: alter table public.your_table enable row level security;
Then read State 3, because that line is also what breaks your app. If a table holds nothing private and nothing you would mind being rewritten by a stranger, it still needs RLS on with a read-only policy, not RLS off. "Public" and "writable by anyone" are not the same thing, and RLS off gives you both. State 2. The policies exist, but RLS was never switched on This is the one to check even if you think you did this prope...

Powered by Readybot.io