Row Level Security

// Anyone could do this!
const { data } = await supabase
  .from('posts')
  .select('*')  // Gets ALL posts, including private ones

// Same code, but only returns authorized data
const { data } = await supabase
  .from('posts')
  .select('*')  // Only returns posts user can access

-- Enable RLS on table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Also force RLS for table owner (important!)
ALTER TABLE posts FORCE ROW LEVEL SECURITY;

CREATE POLICY "policy_name"
ON table_name
FOR operation  -- SELECT, INSERT, UPDATE, DELETE, ALL
TO role        -- anon, authenticated, or specific role
USING (condition)         -- For SELECT, UPDATE, DELETE
WITH CHECK (condition);   -- For INSERT, UPDATE

-- Get current user's ID
auth.uid()

-- Get current user's role (anon, authenticated)
auth.role()

-- Get current user's JWT claims
auth.jwt()

-- Users can only read their own posts
CREATE POLICY "Users can view own posts"
ON posts
FOR SELECT
TO authenticated
USING (auth.uid() = user_id);

-- Users can only insert posts as themselves
CREATE POLICY "Users can create own posts"
ON posts
FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = user_id);

-- Users can only update their own posts
CREATE POLICY "Users can update own posts"
ON posts
FOR UPDATE
TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Users can only delete their own posts
CREATE POLICY "Users can delete own posts"
ON posts
FOR DELETE
TO authenticated
USING (auth.uid() = user_id);

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- SELECT: Users can see published posts OR own posts
CREATE POLICY "View published or own posts"
ON posts FOR SELECT
USING (
  published = true
  OR auth.uid() = user_id
);

-- INSERT: Authenticated users can create posts
CREATE POLICY "Create own posts"
ON posts FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = user_id);

-- UPDATE: Users can update own posts
CREATE POLICY "Update own posts"
ON posts FOR UPDATE
TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- DELETE: Users can delete own posts
CREATE POLICY "Delete own posts"
ON posts FOR DELETE
TO authenticated
USING (auth.uid() = user_id);

-- Anyone can read
CREATE POLICY "Public read access"
ON posts FOR SELECT
USING (published = true);

-- Only authenticated users can write
CREATE POLICY "Authenticated write access"
ON posts FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = user_id);

-- Admins can do everything
CREATE POLICY "Admin full access"
ON posts FOR ALL
TO authenticated
USING (
  (auth.jwt() ->> 'role')::text = 'admin'
);

-- Regular users limited access
CREATE POLICY "User limited access"
ON posts FOR SELECT
TO authenticated
USING (
  auth.uid() = user_id
  OR published = true
);

-- Users can access posts in their organization
CREATE POLICY "Organization access"
ON posts FOR SELECT
TO authenticated
USING (
  organization_id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
  )
);

-- Users can see posts from users they follow
CREATE POLICY "See followed users posts"
ON posts FOR SELECT
TO authenticated
USING (
  published = true
  OR user_id = auth.uid()
  OR user_id IN (
    SELECT following_id
    FROM follows
    WHERE follower_id = auth.uid()
  )
);

-- Create profiles table
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  name TEXT,
  avatar_url TEXT,
  bio TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Enable RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Anyone can view profiles
CREATE POLICY "Public profiles"
ON profiles FOR SELECT
USING (true);

-- Users can update own profile
CREATE POLICY "Update own profile"
ON profiles FOR UPDATE
TO authenticated
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

-- Auto-create profile on signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO profiles (id, name, avatar_url)
  VALUES (
    NEW.id,
    NEW.raw_user_meta_data->>'name',
    NEW.raw_user_meta_data->>'avatar_url'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION handle_new_user();

-- Act as specific user
SET request.jwt.claim.sub = 'user-uuid-here';
SET request.jwt.claims = '{"role": "authenticated"}';

-- Now run queries
SELECT * FROM posts;

-- Check what policies apply
SELECT * FROM pg_policies WHERE tablename = 'posts';

-- Test policy as anonymous user
SET ROLE anon;
SELECT * FROM posts;  -- Should respect anon policies

-- Test as authenticated user
SET ROLE authenticated;
SET request.jwt.claim.sub = 'test-user-id';
SELECT * FROM posts;  -- Should respect authenticated policies

-- Reset
RESET ROLE;

-- Check if RLS is enabled
SELECT relname, relrowsecurity
FROM pg_class
WHERE relname = 'posts';

-- Check existing policies
SELECT * FROM pg_policies WHERE tablename = 'posts';

-- Verify user ID matches
SELECT auth.uid();  -- Check current user

-- Check policy conditions manually
SELECT *
FROM posts
WHERE auth.uid() = user_id;  -- What should match?

-- User can access if:
-- Policy 1 OR Policy 2 OR Policy 3
-- Any true = access granted

// Server-side only!
import { createClient } from '@supabase/supabase-js'

const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!  // Secret!
)

// This bypasses all RLS
const { data } = await supabaseAdmin
  .from('posts')
  .select('*')  // Gets ALL posts

"Create a posts table with RLS policies:
- Public can read published posts
- Authenticated users can create posts
- Users can only edit/delete their own posts"

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "read_published" ON posts
FOR SELECT USING (published = true);

CREATE POLICY "authenticated_read_own" ON posts
FOR SELECT TO authenticated
USING (auth.uid() = user_id);

CREATE POLICY "insert_own" ON posts
FOR INSERT TO authenticated
WITH CHECK (auth.uid() = user_id);

CREATE POLICY "update_own" ON posts
FOR UPDATE TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

CREATE POLICY "delete_own" ON posts
FOR DELETE TO authenticated
USING (auth.uid() = user_id);