added postgre rls tests

georgiy.rusanov · georgiy.rusanov · commit a72077fc1a67 · 2025-07-13T22:52:12.000+02:00
diff --git a/supabase/migrations/20250422000000_create_todos_table.sql b/supabase/migrations/20250422000000_create_todos_table.sql
@@ -3,15 +3,52 @@ CREATE TABLE IF NOT EXISTS public.todos (
     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
     task TEXT NOT NULL,
     is_complete BOOLEAN NOT NULL DEFAULT FALSE,
-    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    user_id UUID REFERENCES auth.users(id)
 );
 
 -- Set up Row Level Security (RLS)
 ALTER TABLE public.todos ENABLE ROW LEVEL SECURITY;
 
--- Create policies
-CREATE POLICY "Allow anonymous access to todos" ON public.todos
-    FOR ALL
+-- Allow anonymous users to read all todos (public data)
+CREATE POLICY "Allow anonymous read access" ON public.todos
+    FOR SELECT
+    TO anon
+    USING (true);
+
+-- Allow anonymous users to insert todos (for backward compatibility with old tests)
+CREATE POLICY "Allow anonymous insert access" ON public.todos
+    FOR INSERT
     TO anon
-    USING (true)
     WITH CHECK (true);
+
+-- Allow anonymous users to delete todos (for backward compatibility with old tests)
+CREATE POLICY "Allow anonymous delete access" ON public.todos
+    FOR DELETE
+    TO anon
+    USING (true);
+
+-- Allow authenticated users to read their own todos
+CREATE POLICY "Allow authenticated read own todos" ON public.todos
+    FOR SELECT
+    TO authenticated
+    USING (auth.uid() = user_id);
+
+-- Allow authenticated users to insert their own todos
+CREATE POLICY "Allow authenticated insert own todos" ON public.todos
+    FOR INSERT
+    TO authenticated
+    WITH CHECK (auth.uid() = user_id);
+
+-- Allow authenticated users to update their own todos
+CREATE POLICY "Allow authenticated update own todos" ON public.todos
+    FOR UPDATE
+    TO authenticated
+    USING (auth.uid() = user_id)
+    WITH CHECK (auth.uid() = user_id);
+
+-- Allow authenticated users to delete their own todos
+CREATE POLICY "Allow authenticated delete own todos" ON public.todos
+    FOR DELETE
+    TO authenticated
+    USING (auth.uid() = user_id);
diff --git a/test/integration.test.ts b/test/integration.test.ts
@@ -18,7 +18,7 @@ describe('Supabase Integration Tests', () => {
   })
 
   describe('PostgREST', () => {
-    test('should query data from public schema', async () => {
+    test('should connect to PostgREST API', async () => {
       const { data, error } = await supabase.from('todos').select('*').limit(5)
 
       // The default schema includes a 'todos' table, but it might be empty
@@ -57,6 +57,120 @@ describe('Supabase Integration Tests', () => {
     })
   })
 
+  describe('PostgreSQL RLS', () => {
+    let user1Email: string
+    let user2Email: string
+    let user1Id: string
+    let user2Id: string
+    let user1TodoId: string
+    let user2TodoId: string
+
+    beforeAll(async () => {
+      // Create two test users
+      user1Email = `user1-${Date.now()}@example.com`
+      user2Email = `user2-${Date.now()}@example.com`
+      const password = 'password123'
+
+      const { data: user1Data } = await supabase.auth.signUp({
+        email: user1Email,
+        password,
+      })
+      user1Id = user1Data.user!.id
+
+      const { data: user2Data } = await supabase.auth.signUp({
+        email: user2Email,
+        password,
+      })
+      user2Id = user2Data.user!.id
+
+      // Create todos for both users
+      await supabase.auth.signInWithPassword({ email: user1Email, password })
+      const { data: user1Todo } = await supabase
+        .from('todos')
+        .insert({ task: 'User 1 Todo', is_complete: false, user_id: user1Id })
+        .select()
+        .single()
+      user1TodoId = user1Todo!.id
+
+      await supabase.auth.signInWithPassword({ email: user2Email, password })
+      const { data: user2Todo } = await supabase
+        .from('todos')
+        .insert({ task: 'User 2 Todo', is_complete: false, user_id: user2Id })
+        .select()
+        .single()
+      user2TodoId = user2Todo!.id
+    })
+
+    afterAll(async () => {
+      await supabase.auth.signOut()
+    })
+
+    test('should allow anonymous access via RLS policies', async () => {
+      await supabase.auth.signOut()
+
+      const { data, error } = await supabase.from('todos').select('*').limit(5)
+
+      expect(error).toBeNull()
+      expect(Array.isArray(data)).toBe(true)
+    })
+
+    test('should allow authenticated user to access their own data', async () => {
+      await supabase.auth.signInWithPassword({ email: user1Email, password: 'password123' })
+
+      const { data, error } = await supabase
+        .from('todos')
+        .select('*')
+        .eq('id', user1TodoId)
+        .single()
+
+      expect(error).toBeNull()
+      expect(data).toBeDefined()
+      expect(data!.task).toBe('User 1 Todo')
+    })
+
+    test('should prevent access to other users data', async () => {
+      await supabase.auth.signInWithPassword({ email: user1Email, password: 'password123' })
+
+      const { data, error } = await supabase
+        .from('todos')
+        .select('*')
+        .eq('id', user2TodoId)
+        .single()
+
+      expect(error).not.toBeNull()
+      expect(data).toBeNull()
+    })
+
+    test('should allow authenticated user to create their own data', async () => {
+      await supabase.auth.signInWithPassword({ email: user1Email, password: 'password123' })
+
+      const { data, error } = await supabase
+        .from('todos')
+        .insert({ task: 'New User 1 Todo', is_complete: false, user_id: user1Id })
+        .select()
+        .single()
+
+      expect(error).toBeNull()
+      expect(data).toBeDefined()
+      expect(data!.task).toBe('New User 1 Todo')
+    })
+
+    test('should allow authenticated user to update their own data', async () => {
+      await supabase.auth.signInWithPassword({ email: user1Email, password: 'password123' })
+
+      const { data, error } = await supabase
+        .from('todos')
+        .update({ task: 'Updated User 1 Todo' })
+        .eq('id', user1TodoId)
+        .select()
+        .single()
+
+      expect(error).toBeNull()
+      expect(data).toBeDefined()
+      expect(data!.task).toBe('Updated User 1 Todo')
+    })
+  })
+
   describe('Authentication', () => {
     afterAll(async () => {
       // Clean up by signing out the user
