Supabase Row Level Security: Database Access Control

-- Enable RLS on a table
alter table posts enable row level security;

-- Without policies, table becomes inaccessible (secure by default)
-- Users can't read, insert, update, or delete ANY rows

-- Disable RLS (not recommended for production)
alter table posts disable row level security;

-- Check if RLS is enabled
select tablename, rowsecurity 
from pg_tables 
where schemaname = 'public';

-- Enable RLS on multiple tables
alter table users enable row level security;
alter table posts enable row level security;
alter table comments enable row level security;
alter table profiles enable row level security;

-- Public read access (anyone can view)
create policy "Public posts are viewable by everyone"
on posts for select
using ( true );

-- Users can only view their own posts
create policy "Users can view their own posts"
on posts for select
using ( auth.uid() = user_id );

-- Users can insert their own posts
create policy "Users can create their own posts"
on posts for insert
with check ( auth.uid() = user_id );

-- Users can update their own posts
create policy "Users can update their own posts"
on posts for update
using ( auth.uid() = user_id )
with check ( auth.uid() = user_id );

-- Users can delete their own posts
create policy "Users can delete their own posts"
on posts for delete
using ( auth.uid() = user_id );

-- Policy breakdown:
-- for select/insert/update/delete: Operation type
-- using: Check before operation (existing rows)
-- with check: Validate new/updated data

-- auth.uid() returns the authenticated user's ID from JWT token
-- Returns NULL for unauthenticated users

-- Example: Users table with self-access only
create table profiles (
  id uuid references auth.users(id) primary key,
  username text unique,
  bio text,
  avatar_url text
);

alter table profiles enable row level security;

-- Users can only view their own profile
create policy "Users can view own profile"
on profiles for select
using ( auth.uid() = id );

-- Users can update their own profile
create policy "Users can update own profile"
on profiles for update
using ( auth.uid() = id );

-- Anyone can insert their profile (after signup)
create policy "Users can insert own profile"
on profiles for insert
with check ( auth.uid() = id );

-- Testing auth.uid()
-- When logged in as user with ID 'abc-123':
select auth.uid(); -- Returns 'abc-123'
-- When not logged in:
select auth.uid(); -- Returns NULL

-- Pattern 1: Blog posts (public read, owner write)
alter table posts enable row level security;

create policy "Posts are publicly readable"
on posts for select using ( true );

create policy "Authors can CRUD their posts"
on posts for all
using ( auth.uid() = author_id );

-- Pattern 2: Private messages (sender/receiver only)
alter table messages enable row level security;

create policy "Users can view their messages"
on messages for select
using ( 
  auth.uid() = sender_id or 
  auth.uid() = receiver_id 
);

-- Pattern 3: Multi-tenant SaaS (organization-based)
alter table documents enable row level security;

create policy "Users can access their org's documents"
on documents for all
using (
  organization_id in (
    select organization_id 
    from user_organizations 
    where user_id = auth.uid()
  )
);

-- Pattern 4: Role-based access
create policy "Admins can view all posts"
on posts for select
using (
  exists (
    select 1 from users
    where id = auth.uid()
    and role = 'admin'
  )
);

-- Pattern 5: Published content
create policy "View published or own drafts"
on posts for select
using (
  status = 'published' or 
  auth.uid() = author_id
);

-- Multiple SELECT policies (combined with OR)

-- Policy 1: View own posts
create policy "View own posts"
on posts for select
using ( auth.uid() = author_id );

-- Policy 2: View published posts
create policy "View published posts"
on posts for select
using ( status = 'published' );

-- Policy 3: Admins view all
create policy "Admins view all posts"
on posts for select
using (
  exists (
    select 1 from users
    where id = auth.uid() and role = 'admin'
  )
);

-- Result: Users can view posts if ANY condition is true:
-- - They own the post OR
-- - Post is published OR  
-- - They are an admin

-- Test policies by impersonating users in SQL Editor

-- Method 1: Set JWT token claims
set request.jwt.claim.sub = 'user-id-here';

-- Now queries run as that user
select * from posts; -- Only returns posts accessible to that user

-- Reset to service role (see everything)
reset request.jwt.claim.sub;

-- Method 2: Test in JavaScript
const { data, error } = await supabase
  .from('posts')
  .select('*')

// RLS automatically applied based on logged-in user
// To test as different user, log in as that user

-- Method 3: Check policy definitions
select 
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
from pg_policies
where tablename = 'posts';