Row Level Security Concepts¶

RLS là gì?¶

Row Level Security (RLS) là PostgreSQL feature cho phép control access tới từng row trong table.

┌─────────────────────────────────────────────────────────────┐
│                    WITHOUT RLS                               │
│                                                              │
│  User A ────▶ SELECT * FROM tasks ────▶ ALL tasks returned  │
│  User B ────▶ SELECT * FROM tasks ────▶ ALL tasks returned  │
│                                                              │
├─────────────────────────────────────────────────────────────┤
│                     WITH RLS                                 │
│                                                              │
│  User A ────▶ SELECT * FROM tasks ────▶ Only User A tasks   │
│  User B ────▶ SELECT * FROM tasks ────▶ Only User B tasks   │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Why RLS?¶

Vấn đề khi không có RLS¶

// ❌ BAD: Client-side filtering only
const { data: tasks } = await supabase
  .from('tasks')
  .select('*')
  .eq('user_id', currentUserId);

// Malicious user can modify request to see all tasks!
// Or call API directly without filter

Giải pháp với RLS¶

-- Database enforces access control
CREATE POLICY "Users can only see own tasks"
  ON tasks FOR SELECT
  USING (auth.uid() = user_id);

-- Now ANY query is automatically filtered
SELECT * FROM tasks;  -- Returns only current user's tasks

Enable RLS¶

Enable on Table¶

-- Enable RLS (required!)
ALTER TABLE public.tasks ENABLE ROW LEVEL SECURITY;

-- Without policies, NO rows are accessible
-- (except for table owner/service role)

Check RLS Status¶

-- View RLS status
SELECT
  tablename,
  rowsecurity
FROM pg_tables
WHERE schemaname = 'public';

Policy Basics¶

Policy Structure¶

CREATE POLICY "policy_name"
  ON table_name
  FOR operation           -- SELECT, INSERT, UPDATE, DELETE, ALL
  TO role                 -- authenticated, anon, public (default: public)
  USING (condition)       -- Filter existing rows
  WITH CHECK (condition); -- Validate new/modified rows

USING vs WITH CHECK¶

Clause	Applied to	Purpose
`USING`	SELECT, UPDATE, DELETE	Filter which rows can be accessed
`WITH CHECK`	INSERT, UPDATE	Validate new data

Common Policies¶

1. Users Own Data¶

-- Read own data
CREATE POLICY "Users can view own tasks"
  ON tasks FOR SELECT
  USING (auth.uid() = user_id);

-- Insert own data
CREATE POLICY "Users can create own tasks"
  ON tasks FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Update own data
CREATE POLICY "Users can update own tasks"
  ON tasks FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Delete own data
CREATE POLICY "Users can delete own tasks"
  ON tasks FOR DELETE
  USING (auth.uid() = user_id);

2. Public Read, Owner Write¶

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

-- Only author can modify
CREATE POLICY "Authors can update own posts"
  ON posts FOR UPDATE
  USING (auth.uid() = author_id);

Auth Helper Functions¶

Available Functions¶

-- Current user ID (from JWT)
auth.uid()

-- Current user role
auth.role()  -- 'authenticated', 'anon', 'service_role'

-- Current user email
auth.email()

-- Full JWT object
auth.jwt()

Usage Examples¶

-- Check authenticated
CREATE POLICY "Authenticated users only"
  ON items FOR SELECT
  USING (auth.role() = 'authenticated');

-- Check specific email domain
CREATE POLICY "Company users only"
  ON internal_docs FOR SELECT
  USING (auth.email() LIKE '%@company.com');

Policy Combinations¶

Multiple Policies = OR¶

-- Policy 1: Owner can access
CREATE POLICY "Owner access"
  ON documents FOR SELECT
  USING (auth.uid() = owner_id);

-- Policy 2: Shared users can access
CREATE POLICY "Shared access"
  ON documents FOR SELECT
  USING (
    id IN (
      SELECT document_id FROM document_shares
      WHERE user_id = auth.uid()
    )
  );

-- Result: User can access if EITHER condition is true

Testing Policies¶

Using SQL Editor¶

-- Set role to authenticated user
SET LOCAL ROLE authenticated;

-- Set specific user (for testing)
SET LOCAL request.jwt.claims = '{"sub": "user-uuid-here"}';

-- Run query
SELECT * FROM tasks;

-- Reset
RESET ROLE;

Using Supabase Client¶

// Test as authenticated user
const { data, error } = await supabase
  .from('tasks')
  .select('*');

// Should only return current user's tasks
console.log(data);

// Test insert
const { error: insertError } = await supabase
  .from('tasks')
  .insert({
    title: 'Test',
    user_id: 'different-user-id', // Should fail!
  });

console.log(insertError); // Policy violation

Common Mistakes¶

❌ Forgetting to Enable RLS¶

-- Table created but RLS not enabled
CREATE TABLE secrets (
  id UUID PRIMARY KEY,
  data TEXT
);

-- All data exposed! Enable RLS:
ALTER TABLE secrets ENABLE ROW LEVEL SECURITY;

❌ No Policies After Enable¶

-- RLS enabled but no policies
ALTER TABLE items ENABLE ROW LEVEL SECURITY;

-- Result: NO rows accessible (except service role)
-- Must add at least one policy!

❌ Wrong Column Reference¶

-- ❌ Wrong: Using 'id' instead of 'user_id'
CREATE POLICY "wrong_policy"
  ON tasks FOR SELECT
  USING (auth.uid() = id);  -- 'id' is task ID, not user ID!

-- ✅ Correct
CREATE POLICY "correct_policy"
  ON tasks FOR SELECT
  USING (auth.uid() = user_id);

RLS with Service Role¶

Bypass RLS¶

// Service role bypasses ALL RLS policies
const supabaseAdmin = createClient(url, serviceRoleKey);

// Returns ALL tasks regardless of policies
const { data } = await supabaseAdmin.from('tasks').select('*');

When to Use Service Role¶

Background jobs (cron, queue consumers)
Admin operations
Migrations
Webhooks from external services

Warning: Never expose service role key to client!

Tổng kết¶

RLS Checklist¶

✅ Enable RLS on every public table
✅ Create policies for each operation (SELECT, INSERT, UPDATE, DELETE)
✅ Use auth.uid() to identify current user
✅ Test policies with different users
✅ Review policies in code review

Key Concepts¶

RLS = Database-level security
USING = Filter existing rows
WITH CHECK = Validate new data
Multiple policies = OR logic
No policies = No access

Q&A¶

Đã dùng RLS trước đây chưa?
Có multi-tenant requirements không?
Questions về policy logic?