How to Add Row Level Security to Supabase
Row Level Security is the foundation of Supabase security. It controls which rows each user can see and modify. Without RLS, your Supabase anon key gives full database access to anyone. This comprehensive guide covers everything from basic policies to advanced patterns.
Find security issues automatically before attackers do.
Follow These Steps
Audit your current RLS status
Check which tables have RLS enabled and which are exposed.
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY rowsecurity ASC, tablename;Tables with rowsecurity = false are completely open to anyone with the anon key.
Enable RLS on all unprotected tables
Run ALTER TABLE for every table that has RLS disabled.
-- Enable on all tables at once
DO $$
DECLARE
t RECORD;
BEGIN
FOR t IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND rowsecurity = false
LOOP
EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t.tablename);
END LOOP;
END $$;Create the standard CRUD policy template
For user-owned tables, apply this four-policy template.
-- Template for any user-owned table
CREATE POLICY "Users can view own data" ON "your_table"
FOR SELECT TO authenticated
USING ((select auth.uid()) = user_id);
CREATE POLICY "Users can insert own data" ON "your_table"
FOR INSERT TO authenticated
WITH CHECK ((select auth.uid()) = user_id);
CREATE POLICY "Users can update own data" ON "your_table"
FOR UPDATE TO authenticated
USING ((select auth.uid()) = user_id);
CREATE POLICY "Users can delete own data" ON "your_table"
FOR DELETE TO authenticated
USING ((select auth.uid()) = user_id);Handle public read / private write patterns
For tables like blog posts where all users can read but only owners can write.
-- Anyone authenticated can read
CREATE POLICY "Public read access" ON "posts"
FOR SELECT TO authenticated
USING (true);
-- Only the author can write
CREATE POLICY "Authors can insert" ON "posts"
FOR INSERT TO authenticated
WITH CHECK ((select auth.uid()) = author_id);
CREATE POLICY "Authors can update" ON "posts"
FOR UPDATE TO authenticated
USING ((select auth.uid()) = author_id);
CREATE POLICY "Authors can delete" ON "posts"
FOR DELETE TO authenticated
USING ((select auth.uid()) = author_id);Implement role-based access for admin users
Create policies that give admin users broader access while restricting regular users.
-- Helper function to check admin status
CREATE OR REPLACE FUNCTION is_admin()
RETURNS boolean AS $$
SELECT EXISTS (
SELECT 1 FROM profiles
WHERE id = (select auth.uid())
AND role = 'admin'
);
$$ LANGUAGE sql SECURITY DEFINER;
-- Admin can read all, users read own
CREATE POLICY "Admin or own data" ON "orders"
FOR SELECT TO authenticated
USING (is_admin() OR (select auth.uid()) = user_id);Protect junction tables and related data
For tables that reference other tables, verify ownership through joins.
-- Comments table where user_id is the commenter
-- but the post must exist and be accessible
CREATE POLICY "Users can comment on accessible posts" ON "comments"
FOR INSERT TO authenticated
WITH CHECK (
(select auth.uid()) = user_id
AND EXISTS (
SELECT 1 FROM posts WHERE id = post_id AND published = true
)
);Test policies with the Supabase client
Write test queries using the Supabase JS client to verify policies work correctly.
// Test as an authenticated user
const { data: ownData } = await supabase
.from('profiles')
.select('*')
// This should only return the current user's profile
console.log('Own data:', ownData)
// Verify you cannot read other users' data
const { data: allData } = await supabase
.from('profiles')
.select('*')
.neq('user_id', currentUserId)
// This should return empty array
console.log('Other users data:', allData) // Should be []Verify with a security scan
Run VAS to confirm RLS is enabled on every table and policies are properly restrictive.
Re-run this check after every database migration to catch new tables without RLS.
What You'll Achieve
Every table has RLS enabled with appropriate policies for CRUD operations. User-owned data is scoped to the authenticated user, public data has read-only access, and admin roles have controlled elevated access. Your Supabase database is properly secured.
Common Mistakes to Avoid
Mistake
Using auth.uid() instead of (select auth.uid())
Fix
Always wrap in a subquery: (select auth.uid()). This evaluates once per query instead of once per row and avoids the auth_rls_initplan planner warning.
Mistake
Creating policies with TO public or without specifying TO
Fix
Always specify TO authenticated. Omitting the TO clause applies the policy to all roles including anonymous access.
Mistake
Adding service_role policies
Fix
service_role bypasses RLS entirely. Extra policies for it cause multiple_permissive_policies warnings. Only create policies with TO authenticated.
Mistake
Using SECURITY DEFINER functions without care
Fix
SECURITY DEFINER functions run with the privileges of the function creator. Use them sparingly and never let user input influence the query inside.
Frequently Asked Questions
Can I enable RLS on all tables at once?
Yes. Use a DO block with a loop over pg_tables to enable RLS on all public tables. But you must also create policies for each table, which requires understanding each table's access patterns.
What happens to existing data when I enable RLS?
Existing data is not affected. But access to it is immediately restricted. If you enable RLS without policies, no one can read or write any rows until you create policies.
How do RLS policies affect performance?
Well-written policies with (select auth.uid()) have minimal performance impact. Policies with subqueries or joins may be slower on large tables. Always index the columns used in policy conditions.
Can I have multiple policies on the same table?
Yes. Multiple permissive policies are combined with OR logic. If any permissive policy allows access, the request succeeds. Restrictive policies (RESTRICTIVE keyword) are combined with AND logic.
Ready to Secure Your App?
VAS automatically scans your deployed app for the security issues covered in this guide. Get actionable results in minutes.
Start Security Scan