Row Level Security (RLS) Policies Explained for Beginners
Row-Level Security (RLS) is the most important security feature for any app using Supabase or PostgreSQL - and the most commonly misconfigured. This beginner-friendly guide explains what RLS is, how it works, and how to implement it correctly.
By Paula C · Kraftwire Software
· 9 min readWhat Is Row-Level Security?
Row-Level Security (RLS) is a database feature that controls which rows a user can see and modify based on their identity. Instead of building access control in your application code, you define rules directly in the database - and the database enforces them on every query, no exceptions.
Think of it like this: without RLS, your database is a filing cabinet with no locks. Anyone with access to the cabinet can open any drawer and read any file. **With RLS, each drawer only opens for the person whose name is on it.**
RLS is built into PostgreSQL (and therefore Supabase, which uses PostgreSQL under the hood). It's the single most important security feature for any application that stores user data - and it's the most commonly misconfigured feature we find when scanning AI-built apps.
---
Why RLS Matters (Especially for AI-Built Apps)
The Architecture of Modern Web Apps
When you build an app with Lovable, Bolt.new, or any Supabase-based tool, your frontend communicates directly with the database through the Supabase JavaScript client. There's no traditional backend server sitting between them.
This architecture is powerful - it eliminates the need to build API endpoints for every database operation. But it also means **your database is directly exposed to the internet** through the Supabase API. The only thing protecting your data is RLS.
What Happens Without RLS
Without RLS policies, anyone who knows your Supabase URL and anon key (both are public and visible in your frontend code) can:
// An attacker opens browser console and runs:
const { data } = await supabase.from("users").select("*");
// → Returns ALL user records, including emails, profile data, etc.
const { data } = await supabase.from("orders").select("*");
// → Returns ALL orders from ALL users
await supabase.from("users").delete().neq("id", "");
// → Deletes ALL user records (if RLS is disabled)
This isn't hypothetical. We see this vulnerability in **38% of AI-built apps** we scan. The app works perfectly for legitimate users - but the data is completely unprotected from anyone who looks.
---
How RLS Works: The Basics
Step 1: Enable RLS on a Table
ALTER TABLE public.user_profiles ENABLE ROW LEVEL SECURITY;
When you enable RLS, the default behavior is to **deny all access**. No one can read, write, or delete any rows - not even authenticated users. This is secure but not useful until you add policies.
Step 2: Create Policies
Policies are rules that define who can do what. Each policy has:
A **name** (for documentation)
A **command** (SELECT, INSERT, UPDATE, DELETE, or ALL)
A **USING clause** (which existing rows the user can see/modify)
A **WITH CHECK clause** (what data the user can insert/update)
**Example: Users can only read their own profile:**
CREATE POLICY "Users can read own profile"
ON public.user_profiles
FOR SELECT
USING (auth.uid() = user_id);
This policy says: when anyone tries to SELECT from `user_profiles`, only return rows where the `user_id` column matches the currently authenticated user's ID.
**Example: Users can only insert their own records:**
CREATE POLICY "Users can insert own profile"
ON public.user_profiles
FOR INSERT
WITH CHECK (auth.uid() = user_id);
**Example: Users can update only their own profile:**
CREATE POLICY "Users can update own profile"
ON public.user_profiles
FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
Note: UPDATE policies need both USING (which rows can be targeted) and WITH CHECK (what the updated data must satisfy).
Step 3: Test Your Policies
The most important step - and the one most developers skip. Log in as User A and try to access User B's data:
// Logged in as User A
const { data } = await supabase
.from("user_profiles")
.select("*")
.eq("user_id", "user-b-id");
// Should return empty array, not User B's data
console.log(data); // → []
---
Common RLS Mistakes (And How to Fix Them)
Mistake 1: RLS Enabled But No Policies
This is the most confusing mistake. You enable RLS (good!), but don't add any policies. Result: **nobody can access any data**, including your app. The app breaks, and in frustration, you disable RLS entirely.
**The fix:** Always add policies immediately after enabling RLS. At minimum, add a SELECT policy for authenticated users.
Mistake 2: Overly Permissive Policies
-- ❌ BAD: Lets any authenticated user read ALL rows
CREATE POLICY "Authenticated can read"
ON public.messages
FOR SELECT
TO authenticated
USING (true);
This policy checks that the user is authenticated but doesn't scope access to their own data. Any logged-in user can read every message in the system.
**The fix:** Always scope policies to the current user:
-- ✅ GOOD: Users can only read their own messages
CREATE POLICY "Users read own messages"
ON public.messages
FOR SELECT
TO authenticated
USING (auth.uid() = sender_id OR auth.uid() = recipient_id);
Mistake 3: Missing DELETE Policies
Developers add SELECT and INSERT policies but forget DELETE. Without a DELETE policy (when RLS is enabled), users can't delete their own records - which seems safe. But if you later add a permissive DELETE policy, you might accidentally allow users to delete other people's data.
**The fix:** Explicitly add DELETE policies scoped to the owner:
CREATE POLICY "Users can delete own records"
ON public.user_posts
FOR DELETE
USING (auth.uid() = user_id);
Mistake 4: Not Using Security Definer Functions for Complex Logic
Sometimes access control requires checking multiple tables - for example, "admins can read all data." If you write this check directly in the RLS policy, it can cause **infinite recursion** when the policy on table A tries to check table B, which has its own RLS policies.
**The fix:** Use a security definer function:
CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role text)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $
SELECT EXISTS (
SELECT 1 FROM public.user_roles
WHERE user_id = _user_id AND role = _role::app_role
)
$;
-- Now use it in policies:
CREATE POLICY "Admins can read all"
ON public.orders
FOR SELECT
USING (has_role(auth.uid(), 'admin'));
Mistake 5: Forgetting Junction Tables
If you have a `team_members` junction table connecting `users` and `teams`, it needs its own RLS policies. AI tools commonly forget this, leaving the junction table unprotected.
---
RLS Patterns for Common Scenarios
Public Content (Blog Posts, Products)
-- Anyone can read published content
CREATE POLICY "Public can read published"
ON public.blog_posts
FOR SELECT
USING (published = true);
-- Only admins can manage content
CREATE POLICY "Admins can manage"
ON public.blog_posts
FOR ALL
TO authenticated
USING (has_role(auth.uid(), 'admin'))
WITH CHECK (has_role(auth.uid(), 'admin'));
Private User Data (Profiles, Settings)
-- Users can only access their own data
CREATE POLICY "Own data only"
ON public.user_settings
FOR ALL
TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
Shared Data (Team Projects)
-- Team members can access team projects
CREATE POLICY "Team members can read"
ON public.projects
FOR SELECT
TO authenticated
USING (
EXISTS (
SELECT 1 FROM public.team_members
WHERE team_members.team_id = projects.team_id
AND team_members.user_id = auth.uid()
)
);
---
Your RLS Implementation Checklist
✅ RLS enabled on **every** table that stores user data
✅ SELECT policies scoped to `auth.uid() = user_id` (not just `true`)
✅ INSERT policies with WITH CHECK ensuring users can only create their own records
✅ UPDATE policies with both USING and WITH CHECK clauses
✅ DELETE policies explicitly defined
✅ Junction tables and metadata tables have their own policies
✅ Admin access uses security definer functions (not direct table checks)
✅ Policies tested by attempting to access other users' data
---
Scan for Missing RLS
SimplyScan automatically detects missing and misconfigured RLS policies in your deployed application. Run a scan in 30 seconds and get actionable findings.
[Check your RLS policies now →](/)