Testing RLS Policies

Tại sao cần Test RLS?

Risks khi không test

┌─────────────────────────────────────────────────────────────┐
│                    RLS TESTING RISKS                         │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  ❌ Without testing:                                         │
│     - Data leaks between users                              │
│     - Unauthorized access                                   │
│     - Policy logic errors                                   │
│     - Performance issues (slow queries)                     │
│                                                              │
│  ✅ With testing:                                            │
│     - Verified data isolation                               │
│     - Confirmed access control                              │
│     - Caught edge cases                                     │
│     - Measured policy performance                           │
│                                                              │
└─────────────────────────────────────────────────────────────┘

---

Testing trong SQL Editor

Impersonate User

-- 1. Set role to authenticated
SET LOCAL ROLE authenticated;

-- 2. Set specific user ID (simulate JWT)
SET LOCAL request.jwt.claims = '{
  "sub": "user-uuid-here",
  "email": "user@example.com",
  "role": "authenticated"
}'::jsonb;

-- 3. Test queries
SELECT * FROM tasks;  -- Should only see user's tasks

-- 4. Reset
RESET ROLE;
RESET request.jwt.claims;

Test Different Scenarios

-- Test User A (owner of project-1)
SET LOCAL request.jwt.claims = '{"sub": "user-a-uuid"}';

SELECT * FROM projects WHERE id = 'project-1';
-- Should return 1 row

-- Test User B (not in project-1)
SET LOCAL request.jwt.claims = '{"sub": "user-b-uuid"}';

SELECT * FROM projects WHERE id = 'project-1';
-- Should return 0 rows

RESET request.jwt.claims;

---

Testing với Anonymous Role

Test Public Access

-- Set to anonymous role
SET LOCAL ROLE anon;

-- Test public content
SELECT * FROM posts WHERE status = 'published';
-- Should work

-- Test private content
SELECT * FROM tasks;
-- Should return 0 rows (or error if no anon policy)

-- Test insert
INSERT INTO posts (title, content, status)
VALUES ('Test', 'Content', 'draft');
-- Should fail

RESET ROLE;

---

Automated Testing với Supabase Client

Jest Test Setup

// tests/rls.test.ts
import { createClient } from '@supabase/supabase-js';

const supabaseUrl = process.env.SUPABASE_URL!;
const supabaseKey = process.env.SUPABASE_ANON_KEY!;
const serviceKey = process.env.SUPABASE_SERVICE_KEY!;

// Regular client (follows RLS)
const supabase = createClient(supabaseUrl, supabaseKey);

// Admin client (bypasses RLS)
const supabaseAdmin = createClient(supabaseUrl, serviceKey);

describe('RLS Policies', () => {
  let userAId: string;
  let userBId: string;
  let userASession: any;
  let userBSession: any;

  beforeAll(async () => {
    // Create test users
    // ... setup code
  });

  afterAll(async () => {
    // Cleanup test data
    // ... cleanup code
  });
});

---

Test Owner Access

describe('Task Owner Policy', () => {
  let taskId: string;

  beforeEach(async () => {
    // Create task as User A
    await supabase.auth.setSession(userASession);
    const { data } = await supabase
      .from('tasks')
      .insert({ title: 'User A Task', user_id: userAId })
      .select()
      .single();
    taskId = data.id;
  });

  test('Owner can read own task', async () => {
    await supabase.auth.setSession(userASession);

    const { data, error } = await supabase
      .from('tasks')
      .select('*')
      .eq('id', taskId)
      .single();

    expect(error).toBeNull();
    expect(data.title).toBe('User A Task');
  });

  test('Non-owner cannot read task', async () => {
    await supabase.auth.setSession(userBSession);

    const { data, error } = await supabase
      .from('tasks')
      .select('*')
      .eq('id', taskId)
      .single();

    expect(data).toBeNull();
  });

  test('Non-owner cannot update task', async () => {
    await supabase.auth.setSession(userBSession);

    const { error } = await supabase
      .from('tasks')
      .update({ title: 'Hacked!' })
      .eq('id', taskId);

    // Should not affect any rows
    expect(error).toBeNull();

    // Verify unchanged
    const { data } = await supabaseAdmin
      .from('tasks')
      .select('*')
      .eq('id', taskId)
      .single();

    expect(data.title).toBe('User A Task');
  });
});

---

Test Team Access

describe('Project Team Policy', () => {
  let projectId: string;

  beforeEach(async () => {
    // Create project with User A as member
    const { data: project } = await supabaseAdmin
      .from('projects')
      .insert({ name: 'Team Project' })
      .select()
      .single();
    projectId = project.id;

    // Add User A as member
    await supabaseAdmin.from('project_members').insert({
      project_id: projectId,
      user_id: userAId,
      role: 'member'
    });
  });

  test('Team member can access project', async () => {
    await supabase.auth.setSession(userASession);

    const { data } = await supabase
      .from('projects')
      .select('*')
      .eq('id', projectId)
      .single();

    expect(data).not.toBeNull();
    expect(data.name).toBe('Team Project');
  });

  test('Non-member cannot access project', async () => {
    await supabase.auth.setSession(userBSession);

    const { data } = await supabase
      .from('projects')
      .select('*')
      .eq('id', projectId)
      .single();

    expect(data).toBeNull();
  });
});

---

Debug RLS Policies

Check Policy Definition

-- View all policies on a table
SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,       -- USING clause
  with_check  -- WITH CHECK clause
FROM pg_policies
WHERE tablename = 'tasks';

Debug USING Expression

-- Test policy expression directly
SELECT
  id,
  title,
  user_id,
  -- Check if policy would pass
  (auth.uid() = user_id) as policy_result
FROM tasks;

Check auth.uid()

-- Verify auth.uid() returns expected value
SELECT auth.uid();

-- Check full JWT
SELECT auth.jwt();

---

Common RLS Bugs

Bug 1: Forgot to Enable RLS

-- Check RLS status
SELECT
  tablename,
  rowsecurity as rls_enabled
FROM pg_tables
WHERE schemaname = 'public';

-- Fix: Enable RLS
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

Bug 2: No Policies = No Access

-- If RLS enabled but no policies exist
-- NO ONE can access (except service role)

-- Check if policies exist
SELECT COUNT(*) FROM pg_policies WHERE tablename = 'my_table';

-- Add basic policy
CREATE POLICY "Enable access" ON my_table FOR ALL USING (true);

Bug 3: Wrong Column in USING

-- ❌ Wrong: Comparing wrong column
USING (auth.uid() = id)  -- id is row ID, not user ID!

-- ✅ Correct
USING (auth.uid() = user_id)

---

Bug 4: Missing WITH CHECK

-- ❌ Wrong: USING only (can't insert)
CREATE POLICY "manage_tasks" ON tasks
  FOR ALL USING (auth.uid() = user_id);

-- ✅ Correct: Include WITH CHECK for INSERT/UPDATE
CREATE POLICY "manage_tasks" ON tasks
  FOR ALL
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

Bug 5: Policy Logic Error

-- ❌ Wrong: AND instead of OR
CREATE POLICY "access_policy" ON docs
  USING (
    auth.uid() = owner_id AND  -- Should be OR
    is_public = true
  );
-- Only works if BOTH conditions true

-- ✅ Correct: Separate policies (OR logic)
CREATE POLICY "owner_access" ON docs
  USING (auth.uid() = owner_id);

CREATE POLICY "public_access" ON docs
  USING (is_public = true);

---

Performance Testing

Measure Query Time

-- Check if policy is slow
EXPLAIN ANALYZE
SELECT * FROM tasks
WHERE project_id = 'uuid-here';

-- Look for:
-- - Seq Scan (bad for large tables)
-- - High execution time
-- - Missing index warnings

Index for Policy Performance

-- Add indexes for columns used in policies
CREATE INDEX idx_tasks_user ON tasks(user_id);
CREATE INDEX idx_project_members_user ON project_members(user_id);

-- Re-run EXPLAIN ANALYZE to compare

---

Testing Checklist

Manual Tests

• [ ] Each role can access appropriate data
• [ ] Each role cannot access restricted data
• [ ] INSERT works with correct user_id
• [ ] INSERT fails with wrong user_id
• [ ] UPDATE limited to owned records
• [ ] DELETE limited to owned records
• [ ] Anonymous access as expected

Automated Tests

• [ ] Unit tests for each policy
• [ ] Integration tests with real data
• [ ] Cross-user access attempts
• [ ] Edge cases (null values, deleted users)

---

Tổng kết

Testing Methods

Method	Use Case
SQL Editor	Quick manual testing
Supabase Client	Integration tests
pg_policies view	Policy inspection
EXPLAIN ANALYZE	Performance testing

Key Commands

-- Impersonate user
SET LOCAL request.jwt.claims = '{"sub": "uuid"}';

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

-- Check auth context
SELECT auth.uid(), auth.role();

-- Reset
RESET ALL;

---

Q&A

1. Đã có test suite cho RLS chưa?
2. Gặp bug RLS nào trước đây?
3. Cần help debug policy nào?