LazyDev_OH

Posted on Apr 30 • Originally published at gocodelab.com

Supabase RLS — 5 Common Mistakes I Broke and Fixed Myself

#supabase #postgres #security #webdev

I had RLS enabled on a Supabase project and data still leaked. A single anon API key read another user's entire notes table. No error message. The problem was that I'd only configured half of it. It took two hours to find what was missing.

RLS (Row Level Security) is PostgreSQL's row-level security feature. The simple way to picture it: a lock on every row of a table. Supabase ships it by default, but if the setup is half-done, it gets quietly broken. This is the 5 mistake patterns I found while reproducing real attack scenarios myself.

Block these five and you stop most data leaks. The difference is one line of code, one policy.

Quick Look

Mistake 1 — RLS itself wasn't enabled → table fully public. ALTER TABLE ... ENABLE ROW LEVEL SECURITY required
Mistake 2 — RLS on, no policies → returns 0 rows (silent failure). At least 1 policy required
Mistake 3 — auth.uid() called directly → re-runs per row, slow. Replace with (SELECT auth.uid()) pattern
Mistake 4 — UPDATE WITH CHECK missing → user_id can be tampered with. USING + WITH CHECK together always
Mistake 5 — INSERT with no role specified → anon can write. TO authenticated must be explicit
Bonus — service_role key exposed to client → RLS fully bypassed. Isolate to server-only env vars

What RLS Is — Why Mistakes Happen

RLS is PostgreSQL's row-level access control. Unlike GRANT, which controls read/write at the whole-table level, RLS attaches a condition to each row so only specific rows can be seen. The "you can only see your own data" rule is enforced at the DB layer, server-side. No matter how sloppy the app code is, the DB layer blocks access.

Supabase Auth puts the logged-in user's UUID into a JWT token and passes it along. Calling auth.uid() inside an RLS policy retrieves that UUID. Compare it to the table's user_id column to restrict access to your own rows. Break that link and data leaks anywhere.

Supabase has three roles. anon is the anonymous user accessing without login. authenticated is a user logged in via Supabase Auth. service_role is an admin key that bypasses RLS. If a policy doesn't specify a role, it applies to all three. Not knowing this means anon users become subject to the policy and end up with unintended access.

Why do mistakes happen so often? Because RLS setup is split across stages. First you enable RLS on the table, then create policies, then specify roles and conditions inside each policy. Drop any of these and the table is silently breached or silently locked. No error message — that makes it harder to find.

Mistake 1 — RLS Itself Wasn't Enabled

When you create a new table in Supabase, RLS is off by default. CREATE TABLE alone makes the entire table queryable with the anon API key. The Dashboard Table Editor shows a "RLS disabled" warning, but it gets ignored often. Deploy in this state and anyone can read the table.

I tested this directly. A curl with the anon key returned all the data as-is. No authentication, content field exposed too. One missing line of RLS does it. Some devs turn RLS off during development for convenience, but it must be on before deploy.

The safest pattern is to put the ENABLE line right next to CREATE TABLE in migration files.

-- Vulnerable: no RLS → fully public
CREATE TABLE notes (
  id      UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID,
  content TEXT
);

-- Fix: run immediately after CREATE TABLE
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

-- Find all tables with RLS off (result should be 0 rows)
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public' AND rowsecurity = false;

I made a query to find tables with RLS off. Adding this query to a CI/CD pipeline catches it automatically before deploy.

Mistake 2 — RLS Enabled but No Policies

Sometimes you enable RLS and no data shows up at all. No error. Just an empty array []. At first I couldn't tell whether it was a data bug or a security setup issue. After staring at the wrong query for a while, I finally realized there were no policies.

If RLS is enabled and zero policies exist, PostgreSQL blocks all access by default. This is called implicit deny. It returns 0 rows with no error message, so it looks like a bug. You need at least one allow policy for data to show up.

The default (PERMISSIVE) policies combine with OR when there are multiple on the same operation. Passing one is enough for access. RESTRICTIVE policies combine with AND and must all pass. Most cases use PERMISSIVE; use RESTRICTIVE only when you need additional restrictions.

-- Vulnerable: RLS on, no policies → returns 0 rows (silent failure)
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
-- No policies → all blocked, returns [] with no error

-- Correct: RLS + policy together
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users see own notes"
  ON notes FOR SELECT TO authenticated
  USING ((SELECT auth.uid()) = user_id);

-- List policies on the current table
SELECT policyname, cmd, roles, qual
FROM pg_policies
WHERE tablename = 'notes';

Mistake 3 — auth.uid() Called Directly

When writing policies, many people put auth.uid() directly in the USING clause. It works. But there's a performance trap. This pattern calls auth.uid() once per row of the table.

10,000 rows means 10,000 auth.uid() calls. Wrap it in a subquery as (SELECT auth.uid()) and it runs once per query. The Supabase official docs recommend this pattern. The bigger the table, the wider the gap.

I compared the two with EXPLAIN ANALYZE. On a 50,000-row table, the direct call did a Seq Scan with 50,000 function executions. The (SELECT auth.uid()) version had 1 function execution and used an index scan. Query time differed by more than 4x.

-- Slow: re-calls auth.uid() per row
CREATE POLICY "slow policy"
  ON notes FOR SELECT TO authenticated
  USING (auth.uid() = user_id);

-- Fast: called once per query (recommended)
CREATE POLICY "fast policy"
  ON notes FOR SELECT TO authenticated
  USING ((SELECT auth.uid()) = user_id);

-- Replace existing policy
DROP POLICY IF EXISTS "slow policy" ON notes;
CREATE POLICY "fast policy"
  ON notes FOR SELECT TO authenticated
  USING ((SELECT auth.uid()) = user_id);

-- Add user_id index (if missing, add it)
CREATE INDEX IF NOT EXISTS notes_user_id_idx ON notes(user_id);

Mistake 4 — UPDATE WITH CHECK Missing

UPDATE policies have two clauses: USING and WITH CHECK. USING applies when picking which rows to modify. WITH CHECK validates that the post-modification result still satisfies the condition. Drop WITH CHECK and a row's ownership can be transferred to another user.

The scenario is UPDATE-ing my note's user_id to another user's UUID. With USING only, the pre-modification row is mine, so the condition passes. With no post-modification check, it saves as-is. My note is now owned by another user.

This attack is real and reproducible. From the JavaScript client, send .update({ user_id: 'other-user-uuid' }). With a USING-only policy, the request succeeds. The target then SELECTs their own data and gets the tampered note. Data integrity broken.

-- Vulnerable: no WITH CHECK → user_id can be tampered
CREATE POLICY "update own notes (vulnerable)"
  ON notes FOR UPDATE TO authenticated
  USING ((SELECT auth.uid()) = user_id);

-- Correct: USING + WITH CHECK both specified
CREATE POLICY "update own notes"
  ON notes FOR UPDATE TO authenticated
  USING      ((SELECT auth.uid()) = user_id)
  WITH CHECK ((SELECT auth.uid()) = user_id);

UPDATE policies must always include both USING and WITH CHECK. Even if the two conditions are identical expressions, both must be specified. PostgreSQL's docs explicitly state this behavior.

Mistake 5 — INSERT Policy with No Role Specified

If an INSERT policy doesn't include a TO clause, PostgreSQL applies the policy to all roles by default. That includes anon. If WITH CHECK is loose, data can be written without login.

Especially when WITH CHECK (true) is used as a fully permissive condition with no role specified, anyone can INSERT. Spam data piles up and the table grows fast. Just specifying TO authenticated blocks it.

Even WITH CHECK (auth.uid() = user_id) isn't fully safe. In the anon role, auth.uid() returns NULL. NULL = UUID comparison is FALSE, so it looks blocked. But if user_id has no NOT NULL constraint and the client sends user_id as NULL, you get a NULL = NULL comparison. Depending on DB version or settings, that can pass.

-- Vulnerable: no role + loose condition → anon can INSERT
CREATE POLICY "insert notes (vulnerable)"
  ON notes FOR INSERT
  WITH CHECK (true);

-- Incomplete: NULL comparison may accidentally pass
CREATE POLICY "insert notes (incomplete)"
  ON notes FOR INSERT
  WITH CHECK (auth.uid() = user_id);
-- auth.uid() is NULL in anon → NULL = NULL comparison risk

-- Correct: TO authenticated + ownership check
CREATE POLICY "insert own notes"
  ON notes FOR INSERT TO authenticated
  WITH CHECK ((SELECT auth.uid()) = user_id);

The principle is to not depend on accidental condition pass-through. Specifying TO authenticated blocks anon-role requests before policy evaluation. Specifying the role makes intent explicit and prevents unexpected bugs.

Bonus — service_role Key in the Client

The service_role key bypasses RLS. Requests with this key can access every row. Put this key in a browser or mobile app and the entire RLS setup becomes meaningless. Anyone can open DevTools, extract the key, and access all data.

It must be managed only as a server-side environment variable. In Next.js terms, manage it as a server-side variable without the NEXT_PUBLIC_ prefix. The client should only get the anon key. Any name like NEXT_PUBLIC_SUPABASE_SERVICE_ROLE_KEY in code must be fixed immediately.

If the key is already exposed, rotate it immediately. Project Settings → API → API Keys. The old key expires the moment a new one is issued. Same applies if the key was committed to GitHub. Even in git history, rotate immediately.

The recommended Next.js pattern is to split the Supabase client into two. One for the browser (anon key) and one for the server (service_role key), each created separately. createBrowserClient and createServerClient handle that role.

Key	Where	RLS	Public?
`anon`	browser, app client	Applied	Safe to expose
`service_role`	server-only (Edge Functions, API Routes)	Bypassed	Never expose

Real-World Scenario — Full RLS Setup for a Multi-User Notes App

I built the full CRUD-protected RLS setup for a notes app from scratch. SELECT, INSERT, UPDATE, DELETE — all four needed. The structure ends up with 4 policies on one table.

Order of setup matters. Run as: create table → enable RLS → policies. Naming policies clearly with intent helps when debugging later. "notes: select own" — table name + operation + target — is readable even six months later.

Skip the DELETE policy and other users can delete your notes. DELETE also needs a USING condition. SELECT and DELETE need only USING, INSERT needs only WITH CHECK, UPDATE needs both.

-- Full RLS setup example for a multi-user notes app

CREATE TABLE notes (
  id         UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id    UUID REFERENCES auth.users(id) NOT NULL,
  content    TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY "notes: select own"
  ON notes FOR SELECT TO authenticated
  USING ((SELECT auth.uid()) = user_id);

CREATE POLICY "notes: insert own"
  ON notes FOR INSERT TO authenticated
  WITH CHECK ((SELECT auth.uid()) = user_id);

CREATE POLICY "notes: update own"
  ON notes FOR UPDATE TO authenticated
  USING      ((SELECT auth.uid()) = user_id)
  WITH CHECK ((SELECT auth.uid()) = user_id);

CREATE POLICY "notes: delete own"
  ON notes FOR DELETE TO authenticated
  USING ((SELECT auth.uid()) = user_id);

-- Index that directly affects RLS performance
CREATE INDEX notes_user_id_idx ON notes(user_id);

After setup I tested three scenarios. Do my notes show up under my UUID? Are my notes hidden under a different UUID? Does the anon key see nothing? All three behaved as expected.

Mixed Public/Private Posts

For blog-like data where public and private posts share a table with an is_public column. Two PERMISSIVE policies combine with OR — anon users pass only the public post policy, authenticated users pass either the public post policy or the own-post policy.

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy 1: public posts readable by anyone (including anon)
CREATE POLICY "posts: select public"
  ON posts FOR SELECT TO anon, authenticated
  USING (is_public = true);

-- Policy 2: own posts readable regardless of public/private status
CREATE POLICY "posts: select own"
  ON posts FOR SELECT TO authenticated
  USING ((SELECT auth.uid()) = user_id);

-- Two policies combined with OR:
-- anon: sees only public posts
-- owner: sees all own posts (public + private)
-- other users: see only that user's public posts

RLS Policy Checklist

Mistake Pattern	Symptom	Risk Level	Fix
RLS not enabled	All data exposed	Critical	`ENABLE ROW LEVEL SECURITY`
No policies	Returns 0 rows (no error)	High	Add at least 1 SELECT policy
`auth.uid()` called directly	Slow queries	Medium	Replace with `(SELECT auth.uid())` pattern
UPDATE WITH CHECK missing	user_id can be tampered	High	Add WITH CHECK
INSERT with no role	anon can write	High	Specify `TO authenticated`
service_role exposed to client	RLS completely bypassed	Critical	Move to server-only env var

Operation	USING	WITH CHECK	Role (TO)
SELECT	Required	Not required	authenticated (anon too for public content)
INSERT	Not required	Required	authenticated
UPDATE	Required	Required (often missed)	authenticated
DELETE	Required	Not required	authenticated

Adding RLS to an Existing Project

Order matters when adding RLS late. Either create the policies before enabling RLS, or wrap them in a transaction and run them together. Enabling RLS alone without policies puts the service in a 0-row state, even if only briefly. Wrapping with BEGIN/COMMIT applies it atomically.

-- Migrating an existing project: wrap RLS + policies in a transaction
BEGIN;

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY "notes: select own" ON notes
  FOR SELECT TO authenticated USING ((SELECT auth.uid()) = user_id);

CREATE POLICY "notes: insert own" ON notes
  FOR INSERT TO authenticated WITH CHECK ((SELECT auth.uid()) = user_id);

CREATE POLICY "notes: update own" ON notes
  FOR UPDATE TO authenticated
  USING ((SELECT auth.uid()) = user_id)
  WITH CHECK ((SELECT auth.uid()) = user_id);

CREATE POLICY "notes: delete own" ON notes
  FOR DELETE TO authenticated USING ((SELECT auth.uid()) = user_id);

COMMIT;
-- RLS enablement and policy creation applied atomically

How to Properly Verify RLS

After creating policies, actual testing is necessary. The Supabase SQL Editor lets you set the role and JWT claims directly.

Two things must be confirmed. Does my data appear with my UUID? Does my data not appear with another UUID? The second is more important. In many cases own data shows up fine, but blocking access to others' data is missing.

-- Testing RLS in Supabase SQL Editor

-- 1. Test authenticated role with my UUID
SET LOCAL role TO 'authenticated';
SET LOCAL "request.jwt.claims" TO '{"sub": "my-user-uuid-here"}';
SELECT * FROM notes;
-- Only my notes should appear

-- 2. Access with another UUID (should return 0 rows)
SET LOCAL "request.jwt.claims" TO '{"sub": "other-user-uuid"}';
SELECT * FROM notes;
-- Should return 0 rows

-- 3. Test anon role
SET LOCAL role TO 'anon';
SELECT * FROM notes;
-- Should return 0 rows

Firing a curl with the anon key directly is also essential. Hitting the actual API endpoint, not the SQL Editor, gives the real picture.

# Direct API call test with anon key
curl 'https://<project-ref>.supabase.co/rest/v1/notes?select=*' \
  -H 'apikey: <anon-key>' \
  -H 'Authorization: Bearer <anon-key>'

# Result should be []
# If data is visible, RLS configuration error

Testing with the JavaScript client is also possible. Log in a test account with supabase.auth.signInWithPassword and SELECT a row that belongs to a different account's UUID. An empty array result is correct. This approach has the advantage of testing through the exact same path as the actual app code.

Closing

Half-configured RLS gets breached. Enabling it matters, and writing policies correctly matters too. The most common of the five mistakes are not enabling RLS and missing policies. Fixing just those two prevents most data leaks.

Run through the checklist above every time a new table is created. It does not have to be perfect. One SELECT policy and one INSERT policy with WITH CHECK is enough to cover the basics. The DELETE policy and UPDATE WITH CHECK can be added as the next step.

Adding service_role key management to the code review checklist is a good idea. If any NEXT_PUBLIC_-prefixed environment variable holds a service_role key, move it immediately. RLS configuration is never a one-time task.

DEV Community