> ## Documentation Index
> Fetch the complete documentation index at: https://devperez08-platform-list-anime-54.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Row Level Security

> Complete guide to RLS policies protecting EpiNeko database tables

## Overview

EpiNeko uses PostgreSQL Row Level Security (RLS) to ensure users can only access and modify their own data. All tables have RLS enabled with specific policies for different operations.

<Note>
  RLS policies are defined in `supabase/migrations/20260218_initial_schema.sql:13` and enforce security at the database level.
</Note>

<Warning>
  **Never disable RLS on production tables.** RLS is your primary defense against unauthorized data access.
</Warning>

## Why RLS?

Row Level Security provides database-level security that:

* **Cannot be bypassed** - Even if application code has bugs, RLS protects data
* **Works automatically** - No need to add WHERE clauses to every query
* **Leverages Supabase Auth** - Uses `auth.uid()` to identify current user
* **Prevents data leaks** - Users can only see their own data

## profiles Table Policies

```sql theme={null}
-- Enable RLS
alter table public.profiles enable row level security;
```

### SELECT Policy: Public profiles are viewable by everyone

```sql theme={null}
create policy "Public profiles are viewable by everyone." on public.profiles
  for select using (true);
```

<ParamField path="Policy Name" type="string">
  Public profiles are viewable by everyone.
</ParamField>

<ParamField path="Operation" type="SELECT">
  Applies to SELECT queries
</ParamField>

<ParamField path="Rule" type="using (true)">
  Always returns true - all profiles are publicly visible
</ParamField>

**Use Case:** Anyone can view user profiles, enabling features like public user pages and profile discovery.

<CodeGroup>
  ```typescript Example Query theme={null}
  // Anyone can read any profile
  const { data: profile } = await supabase
    .from('profiles')
    .select('*')
    .eq('username', 'johndoe')
    .single();

  // This works even for unauthenticated users
  ```

  ```sql SQL Equivalent theme={null}
  SELECT * FROM profiles
  WHERE username = 'johndoe';
  -- No user_id check needed - policy allows all reads
  ```
</CodeGroup>

***

### INSERT Policy: Users can insert their own profile

```sql theme={null}
create policy "Users can insert their own profile." on public.profiles
  for insert with check (auth.uid() = id);
```

<ParamField path="Policy Name" type="string">
  Users can insert their own profile.
</ParamField>

<ParamField path="Operation" type="INSERT">
  Applies to INSERT queries
</ParamField>

<ParamField path="Rule" type="with check (auth.uid() = id)">
  User can only insert if the profile `id` matches their authenticated `auth.uid()`
</ParamField>

**Use Case:** Prevents users from creating profiles for other users. The `handle_new_user()` trigger creates profiles automatically, so this policy rarely applies in practice.

<CodeGroup>
  ```typescript Example Query theme={null}
  // This succeeds - user creating their own profile
  const { data } = await supabase
    .from('profiles')
    .insert({
      id: user.id,  // Must match auth.uid()
      username: 'johndoe',
      full_name: 'John Doe'
    });

  // This fails - trying to create profile for another user
  const { error } = await supabase
    .from('profiles')
    .insert({
      id: 'some-other-user-id',  // Doesn't match auth.uid()
      username: 'hacker'
    });
  // Error: new row violates row-level security policy
  ```
</CodeGroup>

***

### UPDATE Policy: Users can update own profile

```sql theme={null}
create policy "Users can update own profile." on public.profiles
  for update using (auth.uid() = id);
```

<ParamField path="Policy Name" type="string">
  Users can update own profile.
</ParamField>

<ParamField path="Operation" type="UPDATE">
  Applies to UPDATE queries
</ParamField>

<ParamField path="Rule" type="using (auth.uid() = id)">
  User can only update rows where `id` matches their `auth.uid()`
</ParamField>

**Use Case:** Users can only edit their own profile information.

<CodeGroup>
  ```typescript Example Query theme={null}
  // This succeeds - updating own profile
  const { data } = await supabase
    .from('profiles')
    .update({
      full_name: 'Jane Doe',
      website: 'https://janedoe.com'
    })
    .eq('id', user.id);  // Matches auth.uid()

  // This fails - trying to update another user's profile
  const { error } = await supabase
    .from('profiles')
    .update({ full_name: 'Hacked' })
    .eq('username', 'someoneelse');
  // No rows updated - policy blocks access
  ```

  ```typescript Server Action theme={null}
  'use server';
  import { createClient } from '@/utils/supabase/server';

  export async function updateProfile(updates: {
    full_name?: string;
    website?: string;
  }) {
    const supabase = await createClient();
    const { data: { user } } = await supabase.auth.getUser();

    if (!user) return { error: 'Not authenticated' };

    // RLS ensures user can only update their own profile
    const { data, error } = await supabase
      .from('profiles')
      .update(updates)
      .eq('id', user.id)
      .select()
      .single();

    return { data, error };
  }
  ```
</CodeGroup>

***

## user\_library Table Policies

```sql theme={null}
-- Enable RLS
alter table public.user_library enable row level security;
```

### SELECT Policy: Users can view their own library items

```sql theme={null}
create policy "Users can view their own library items." on public.user_library
  for select using (auth.uid() = user_id);
```

<ParamField path="Policy Name" type="string">
  Users can view their own library items.
</ParamField>

<ParamField path="Operation" type="SELECT">
  Applies to SELECT queries
</ParamField>

<ParamField path="Rule" type="using (auth.uid() = user_id)">
  User can only select rows where `user_id` matches their `auth.uid()`
</ParamField>

**Use Case:** Users can only see anime in their own library, not other users' libraries.

<CodeGroup>
  ```typescript Example Query theme={null}
  // Returns only current user's library
  const { data: myLibrary } = await supabase
    .from('user_library')
    .select('*');

  // Returns empty array - can't see other users' libraries
  const { data: otherLibrary } = await supabase
    .from('user_library')
    .select('*')
    .eq('user_id', 'some-other-user-id');
  // RLS filters this out automatically
  ```

  ```typescript src/services/library.ts:63 theme={null}
  export const getLibrary = async () => {
    const supabase = createClient();
    const { data, error } = await supabase
      .from('user_library')
      .select('*')
      .order('updated_at', { ascending: false });

    if (error) throw error;
    return data;
    // RLS ensures only current user's items are returned
  };
  ```
</CodeGroup>

***

### INSERT Policy: Users can insert their own library items

```sql theme={null}
create policy "Users can insert their own library items." on public.user_library
  for insert with check (auth.uid() = user_id);
```

<ParamField path="Policy Name" type="string">
  Users can insert their own library items.
</ParamField>

<ParamField path="Operation" type="INSERT">
  Applies to INSERT queries
</ParamField>

<ParamField path="Rule" type="with check (auth.uid() = user_id)">
  User can only insert if `user_id` matches their `auth.uid()`
</ParamField>

**Use Case:** Users can only add anime to their own library.

<CodeGroup>
  ```typescript Example Query theme={null}
  // This succeeds - adding to own library
  const { data } = await supabase
    .from('user_library')
    .insert({
      user_id: user.id,  // Must match auth.uid()
      anime_id_jikan: 5114,
      title: 'FMA: Brotherhood',
      status: 'watching'
    });

  // This fails - trying to add to another user's library
  const { error } = await supabase
    .from('user_library')
    .insert({
      user_id: 'some-other-user-id',  // Doesn't match auth.uid()
      anime_id_jikan: 5114,
      title: 'FMA: Brotherhood',
      status: 'watching'
    });
  // Error: new row violates row-level security policy
  ```

  ```typescript src/services/library.ts:16 theme={null}
  export const addToLibrary = async (item: LibraryItem) => {
    const supabase = createClient();
    const { data: { user } } = await supabase.auth.getUser();

    if (!user) throw new Error('User must be logged in');

    const { data, error } = await supabase
      .from('user_library')
      .insert([{ ...item, user_id: user.id }])  // RLS checks this
      .select()
      .single();

    if (error) throw error;
    return data;
  };
  ```
</CodeGroup>

***

### UPDATE Policy: Users can update their own library items

```sql theme={null}
create policy "Users can update their own library items." on public.user_library
  for update using (auth.uid() = user_id);
```

<ParamField path="Policy Name" type="string">
  Users can update their own library items.
</ParamField>

<ParamField path="Operation" type="UPDATE">
  Applies to UPDATE queries
</ParamField>

<ParamField path="Rule" type="using (auth.uid() = user_id)">
  User can only update rows where `user_id` matches their `auth.uid()`
</ParamField>

**Use Case:** Users can only modify anime in their own library (change status, update score, etc.).

<CodeGroup>
  ```typescript Example Query theme={null}
  // This succeeds - updating own library item
  const { data } = await supabase
    .from('user_library')
    .update({
      status: 'completed',
      score: 10,
      episodes_watched: 64
    })
    .eq('anime_id_jikan', 5114)
    .eq('user_id', user.id);

  // This fails silently - trying to update another user's item
  const { error } = await supabase
    .from('user_library')
    .update({ score: 1 })
    .eq('anime_id_jikan', 5114)
    .eq('user_id', 'some-other-user-id');
  // No rows updated - RLS blocks access
  ```

  ```typescript src/services/library.ts:46 theme={null}
  export const updateLibraryItem = async (
    animeIdJikan: number, 
    updates: Partial<LibraryItem>
  ) => {
    const supabase = createClient();
    const { data: { user } } = await supabase.auth.getUser();

    if (!user) throw new Error('User must be logged in');

    const { data, error } = await supabase
      .from('user_library')
      .update(updates)
      .match({ user_id: user.id, anime_id_jikan: animeIdJikan })
      .select()
      .single();

    if (error) throw error;
    return data;
  };
  ```
</CodeGroup>

***

### DELETE Policy: Users can delete their own library items

```sql theme={null}
create policy "Users can delete their own library items." on public.user_library
  for delete using (auth.uid() = user_id);
```

<ParamField path="Policy Name" type="string">
  Users can delete their own library items.
</ParamField>

<ParamField path="Operation" type="DELETE">
  Applies to DELETE queries
</ParamField>

<ParamField path="Rule" type="using (auth.uid() = user_id)">
  User can only delete rows where `user_id` matches their `auth.uid()`
</ParamField>

**Use Case:** Users can remove anime from their own library but cannot delete other users' library items.

<CodeGroup>
  ```typescript Example Query theme={null}
  // This succeeds - removing from own library
  const { error } = await supabase
    .from('user_library')
    .delete()
    .eq('anime_id_jikan', 5114)
    .eq('user_id', user.id);

  // This fails - trying to delete from another user's library
  const { error } = await supabase
    .from('user_library')
    .delete()
    .eq('anime_id_jikan', 5114)
    .eq('user_id', 'some-other-user-id');
  // No rows deleted - RLS blocks access
  ```

  ```typescript src/services/library.ts:32 theme={null}
  export const removeFromLibrary = async (animeIdJikan: number) => {
    const supabase = createClient();
    const { data: { user } } = await supabase.auth.getUser();

    if (!user) throw new Error('User must be logged in');

    const { error } = await supabase
      .from('user_library')
      .delete()
      .match({ user_id: user.id, anime_id_jikan: animeIdJikan });

    if (error) throw error;
  };
  ```
</CodeGroup>

***

## How RLS Works

### Authentication Context

RLS policies use `auth.uid()` to get the current user's ID from the JWT token:

```sql theme={null}
auth.uid() = user_id
```

* **Authenticated users**: `auth.uid()` returns their UUID
* **Unauthenticated users**: `auth.uid()` returns `NULL`

### Policy Evaluation

PostgreSQL evaluates RLS policies for every query:

1. **SELECT**: Check `USING` clause - if false, row is filtered out
2. **INSERT**: Check `WITH CHECK` clause - if false, insert fails
3. **UPDATE**: Check `USING` clause - if false, row cannot be updated
4. **DELETE**: Check `USING` clause - if false, row cannot be deleted

### Example Flow

```typescript theme={null}
// User A (auth.uid() = 'uuid-a') tries to query library
const { data } = await supabase
  .from('user_library')
  .select('*');

// PostgreSQL executes:
// SELECT * FROM user_library WHERE 'uuid-a' = user_id
// Only returns rows where user_id = 'uuid-a'
```

## Testing RLS Policies

### Test as Different Users

```typescript theme={null}
// Create test users
const { data: user1 } = await supabase.auth.signUp({
  email: 'user1@test.com',
  password: 'password123'
});

const { data: user2 } = await supabase.auth.signUp({
  email: 'user2@test.com',
  password: 'password123'
});

// Sign in as user1
await supabase.auth.signInWithPassword({
  email: 'user1@test.com',
  password: 'password123'
});

// Add to library as user1
await supabase.from('user_library').insert({
  user_id: user1.user.id,
  anime_id_jikan: 5114,
  title: 'FMA: Brotherhood',
  status: 'watching'
});

// Sign in as user2
await supabase.auth.signInWithPassword({
  email: 'user2@test.com',
  password: 'password123'
});

// Try to read user1's library - should return empty
const { data } = await supabase
  .from('user_library')
  .select('*');
console.log(data); // []

// Try to update user1's item - should fail
const { error } = await supabase
  .from('user_library')
  .update({ score: 1 })
  .eq('user_id', user1.user.id)
  .eq('anime_id_jikan', 5114);
console.log(error); // No rows updated
```

### Using the Supabase Dashboard

1. Go to **SQL Editor** in Supabase Dashboard
2. Use `auth.uid()` to test as specific users:

```sql theme={null}
-- Test as specific user
SELECT set_config('request.jwt.claim.sub', 'user-uuid-here', TRUE);

-- Now run queries as that user
SELECT * FROM user_library;

-- Reset to no user
SELECT set_config('request.jwt.claim.sub', '', TRUE);
```

## Bypassing RLS (Admin Operations)

<Warning>
  Only bypass RLS for trusted admin operations. Never expose these functions to client-side code.
</Warning>

### Service Role Key

Use the service role key for operations that need to bypass RLS:

```typescript theme={null}
// Server-side only!
import { createClient } from '@supabase/supabase-js';

const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!  // Service role bypasses RLS
);

// Can access all data
const { data: allLibraries } = await supabaseAdmin
  .from('user_library')
  .select('*');
```

### Security Definer Functions

Create database functions with `SECURITY DEFINER` to run with elevated privileges:

```sql theme={null}
-- Function runs with creator's permissions, not caller's
CREATE OR REPLACE FUNCTION get_user_stats()
RETURNS TABLE(user_count bigint, anime_count bigint)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  SELECT 
    COUNT(DISTINCT user_id),
    COUNT(*)
  FROM user_library;
END;
$$;
```

## Common Issues

<Warning>
  **Error: new row violates row-level security policy**

  This means you're trying to insert/update data that doesn't pass the RLS policy check. Common causes:

  * Inserting with wrong `user_id`
  * Not authenticated when required
  * Trying to modify another user's data
</Warning>

<Warning>
  **No rows returned when you expect data**

  RLS is filtering out the rows. Common causes:

  * Wrong user authenticated
  * Not authenticated when required
  * Querying with wrong `user_id`
</Warning>

## Best Practices

<Expandable title="1. Always Include user_id in Queries">
  Even though RLS will filter, explicitly including `user_id` makes intent clear:

  ```typescript theme={null}
  // Good - explicit and clear
  const { data } = await supabase
    .from('user_library')
    .select('*')
    .eq('user_id', user.id);

  // Also works - RLS handles filtering
  const { data } = await supabase
    .from('user_library')
    .select('*');
  ```
</Expandable>

<Expandable title="2. Check Authentication Before Operations">
  ```typescript theme={null}
  const { data: { user } } = await supabase.auth.getUser();

  if (!user) {
    throw new Error('Must be authenticated');
  }

  // Now safe to proceed
  ```
</Expandable>

<Expandable title="3. Use Type-Safe Queries">
  ```typescript theme={null}
  const { data, error } = await supabase
    .from('user_library')
    .insert({
      user_id: user.id,  // Type-checked
      anime_id_jikan: 5114,
      title: 'FMA: Brotherhood',
      status: 'watching'  // Enum validated
    });
  ```
</Expandable>

<Expandable title="4. Test RLS Policies Thoroughly">
  * Test as authenticated user
  * Test as different user
  * Test as unauthenticated user
  * Test all CRUD operations
</Expandable>

## Policy Summary

| Table          | Operation | Policy           | Allows                                 |
| -------------- | --------- | ---------------- | -------------------------------------- |
| `profiles`     | SELECT    | Public viewable  | Everyone can read all profiles         |
| `profiles`     | INSERT    | Own profile only | User can create their own profile      |
| `profiles`     | UPDATE    | Own profile only | User can update their own profile      |
| `user_library` | SELECT    | Own items only   | User can view their own library        |
| `user_library` | INSERT    | Own items only   | User can add to their own library      |
| `user_library` | UPDATE    | Own items only   | User can update their own library      |
| `user_library` | DELETE    | Own items only   | User can remove from their own library |

## Related Resources

<CardGroup cols={2}>
  <Card title="Database Schema" icon="table" href="/integration/database-schema">
    View complete database schema
  </Card>

  <Card title="Supabase Integration" icon="database" href="/integration/supabase">
    How to use Supabase clients
  </Card>

  <Card title="Library Service" icon="code" href="/development/services">
    High-level library operations with RLS
  </Card>

  <Card title="Supabase RLS Docs" icon="book" href="https://supabase.com/docs/guides/auth/row-level-security">
    Official RLS documentation
  </Card>
</CardGroup>