Row Level Security Explained: Protect Your Database
Row Level Security (RLS) is one of the most important concepts in modern database security. This guide explains what it is, why you need it, and how to implement it correctly.
What You'll Learn
- What is Row Level Security?
- Why RLS matters for modern apps
- How RLS works (with examples)
- RLS in Supabase
- RLS in plain PostgreSQL
- Common RLS patterns
What is Row Level Security?
Row Level Security (RLS) is a database feature that controls which rows a user can access in a table. Instead of giving users access to all data or no data, RLS lets you define fine-grained policies that filter data based on who's asking.
Think of it like a bouncer at every row in your database. Before any data is returned, the bouncer checks: "Is this user allowed to see this specific row?"
Without RLS
SELECT * FROM orders;
→ Returns ALL orders from everyone
With RLS
SELECT * FROM orders;
→ Returns only YOUR orders automatically
Why RLS Matters
In traditional applications, security was enforced in the backend API. But modern apps often give the frontend direct database access (like Supabase or Firebase). This is faster and simpler, but it means the database itself must enforce security.
The Real Danger
In apps using Supabase, Firebase, or similar BaaS platforms, your API key is public. Anyone can extract it from your frontend code and make direct database queries. Without RLS, they can access everything.
RLS is your last line of defense. Even if someone has your API key and knows your table structure, they still can't access data that doesn't belong to them.
How RLS Works
RLS works by automatically adding a WHERE clause to every query. You define "policies" that specify the conditions for access, and the database applies them transparently.
Example: User Can Only See Their Own Data
-- 1. Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- 2. Create a policy
CREATE POLICY "Users see own orders"
ON orders
FOR SELECT
USING (user_id = current_user_id());
Now when any user runs SELECT * FROM orders, the database automatically filters to only rows where user_id matches their ID.
RLS in Supabase
Supabase makes RLS easy with a built-in auth.uid() function that returns the current user's ID.
-- Supabase RLS policy
CREATE POLICY "Users can read own profile"
ON profiles FOR SELECT
TO authenticated
USING (auth.uid() = user_id);
The TO authenticated part means this policy only applies to logged-in users. You can also use TO anon for unauthenticated users.
Important!
Once you enable RLS, no one can access the table until you create policies. This is secure by default, but it means your app will break until you add the right policies.
Common RLS Patterns
1. User Owns Their Data
USING (auth.uid() = user_id)
2. Public Read, Private Write
-- Anyone can read
CREATE POLICY "Public read" ON posts
FOR SELECT USING (true);
-- Only owner can write
CREATE POLICY "Owner write" ON posts
FOR ALL USING (auth.uid() = author_id);
3. Team/Organization Access
USING (
team_id IN (
SELECT team_id FROM team_members
WHERE user_id = auth.uid()
)
)
4. Admin Override
USING (
auth.uid() = user_id
OR EXISTS (
SELECT 1 FROM profiles
WHERE id = auth.uid() AND role = 'admin'
)
)
Testing Your RLS
Always test your RLS policies before going to production:
- 1Try accessing data as an unauthenticated user
- 2Create two test users and verify they can't see each other's data
- 3Use VAS to automatically test for data exposure
RLS Checklist
Test Your RLS Automatically
VAS queries your database just like an attacker would, testing if your RLS policies are working correctly.