Common Row Level Security policy patterns for Supabase: owner-only, org-scoped, role-based, and public read access with SQL examples.
Common Row Level Security policy patterns for Supabase: owner-only, org-scoped, role-based, and public read access with SQL examples.
BeforeMerge offers hundreds of code review rules, guides, and detection patterns to help your team ship better code.
Row Level Security (RLS) is the primary authorization layer in Supabase. Every table with sensitive data should have RLS enabled and appropriate policies defined.
ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;Once enabled, no rows are accessible until you create policies. This is secure by default.
Users can only read and modify their own rows.
-- Read own rows
CREATE POLICY "users read own posts" ON public.posts
FOR SELECT USING (auth.uid() = user_id);
-- Insert own rows
CREATE POLICY "users insert own posts" ON public.posts
FOR INSERT WITH CHECK (auth.uid() = user_id);
-- Update own rows
CREATE POLICY "users update own posts" ON public.posts
FOR UPDATE USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
-- Delete own rows
CREATE POLICY "users delete own posts" ON public.posts
FOR DELETE USING (auth.uid() = user_id);Members of an organization can access all rows belonging to that org.
CREATE POLICY "org members read" ON public.documents
FOR SELECT USING (
organization_id IN (
SELECT om.organization_id
FROM public.organization_member om
WHERE om.user_id = auth.uid()
)
);Performance tip: Create an index on organization_member(user_id) to avoid sequential scans.
Different access levels based on user role within an organization.
CREATE POLICY "admins manage all" ON public.settings
FOR ALL USING (
EXISTS (
SELECT 1 FROM public.organization_member om
WHERE om.user_id = auth.uid()
AND om.organization_id = settings.organization_id
AND om.role IN ('owner', 'admin')
)
);
CREATE POLICY "members read only" ON public.settings
FOR SELECT USING (
EXISTS (
SELECT 1 FROM public.organization_member om
WHERE om.user_id = auth.uid()
AND om.organization_id = settings.organization_id
)
);Anyone can read published content; only the owner can modify it.
CREATE POLICY "public read published" ON public.articles
FOR SELECT USING (is_published = true AND visibility = 'public');
CREATE POLICY "owner write" ON public.articles
FOR ALL USING (auth.uid() = created_by)
WITH CHECK (auth.uid() = created_by);Server-side operations using service_role key bypass RLS entirely. For explicit policies:
CREATE POLICY "service_role full access" ON public.internal_logs
FOR ALL USING (auth.role() = 'service_role')
WITH CHECK (auth.role() = 'service_role');WITH CHECK: USING controls which rows are visible; WITH CHECK controls which rows can be written. For UPDATE, you need both.EXISTS instead of IN for large tables.STABLE or IMMUTABLE functions in policies to allow query plan caching.SET ROLE authenticated and SET request.jwt.claims in psql.