Row Level Security (RLS)

CREATE POLICY policy_name
ON table
FOR operation      -- SELECT, INSERT, UPDATE, DELETE, ALL
TO role           -- public, authenticated, anon
USING (condition)  -- When row can be accessed (SELECT, UPDATE, DELETE)
WITH CHECK (condition)  -- When row can be inserted/updated (INSERT, UPDATE)

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
 
-- Anyone can read published posts
CREATE POLICY "Published posts are public"
ON posts FOR SELECT
TO public
USING (published = true);
 
-- Only authenticated can create posts
CREATE POLICY "Authenticated users can create posts"
ON posts FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = author_id);
 
-- Only author can update their posts
CREATE POLICY "Authors can update their posts"
ON posts FOR UPDATE
TO authenticated
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);
 
-- Only author can delete their posts
CREATE POLICY "Authors can delete their posts"
ON posts FOR DELETE
TO authenticated
USING (auth.uid() = author_id);

ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
 
-- User sees only their profile
CREATE POLICY "Users see only their profile"
ON user_profiles FOR SELECT
TO authenticated
USING (auth.uid() = user_id);
 
-- User can only update their profile
CREATE POLICY "Users update only their profile"
ON user_profiles FOR UPDATE
TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Add role column to users table
ALTER TABLE user_profiles ADD COLUMN role TEXT DEFAULT 'user';
 
-- Create helper function
CREATE OR REPLACE FUNCTION is_admin()
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1
    FROM user_profiles
    WHERE user_id = auth.uid()
    AND role = 'admin'
  );
$$ LANGUAGE SQL SECURITY DEFINER;
 
-- Policy for admins
CREATE POLICY "Admins can do everything"
ON posts FOR ALL
TO authenticated
USING (is_admin())
WITH CHECK (is_admin());

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
 
-- Organization members table
CREATE TABLE organization_members (
  org_id UUID REFERENCES organizations(id),
  user_id UUID REFERENCES auth.users(id),
  role TEXT DEFAULT 'member',
  PRIMARY KEY (org_id, user_id)
);
 
-- Helper function
CREATE OR REPLACE FUNCTION user_orgs()
RETURNS SETOF UUID AS $$
  SELECT org_id
  FROM organization_members
  WHERE user_id = auth.uid();
$$ LANGUAGE SQL SECURITY DEFINER;
 
-- Policy: user accesses their org's documents
CREATE POLICY "Users access org documents"
ON documents FOR SELECT
TO authenticated
USING (org_id IN (SELECT user_orgs()));

-- NEVER do this in production
-- Without RLS = no protection!
 
-- ALWAYS enable RLS
ALTER TABLE table ENABLE ROW LEVEL SECURITY;

-- WRONG: Allows unauthenticated access to everything
CREATE POLICY "bad_policy"
ON sensitive_table FOR ALL
TO public
USING (true);
 
-- CORRECT: Specify conditions
CREATE POLICY "good_policy"
ON posts FOR SELECT
TO public
USING (published = true);

-- USING: Determines which rows can be READ/MODIFIED
-- WITH CHECK: Determines which values can be INSERTED/UPDATED
 
-- WRONG: Allows user to change author_id on update
CREATE POLICY "bad_update"
ON posts FOR UPDATE
TO authenticated
USING (auth.uid() = author_id);  -- Missing WITH CHECK!
 
-- CORRECT
CREATE POLICY "good_update"
ON posts FOR UPDATE
TO authenticated
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);  -- Ensures author_id doesn't change

-- Test as authenticated user
SET LOCAL ROLE authenticated;
SET LOCAL "request.jwt.claims" TO '{"sub": "user-uuid-here"}';
 
SELECT * FROM posts;  -- Should return only user's posts
 
-- Reset
RESET ROLE;

import { createClient } from '@/lib/supabase/server'
 
async function testRLS() {
  const supabase = await createClient()
 
  // Try to access another user's posts
  const { data, error } = await supabase
    .from('posts')
    .select('*')
    .eq('author_id', 'another-user-id')
 
  if (data && data.length === 0) {
    console.log('RLS working: did not return other users posts')
  }
}

-- On ALL sensitive tables
ALTER TABLE table ENABLE ROW LEVEL SECURITY;

// NEVER do this client-side
const supabase = createClient(url, SERVICE_ROLE_KEY)
 
// Use only in Server Actions/Components
import { createAdminClient } from '@/lib/supabase/server'

import { z } from 'zod'
 
const PostSchema = z.object({
  title: z.string().min(5).max(200),
  content: z.string().min(10),
  published: z.boolean().default(false),
})
 
export async function createPost(input: unknown) {
  const validated = PostSchema.parse(input)
  // Proceed with insert...
}