Row Level Security

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.

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

Never disable RLS on production tables. RLS is your primary defense against unauthorized data access.

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

-- Enable RLS
alter table public.profiles enable row level security;

SELECT Policy: Public profiles are viewable by everyone

create policy "Public profiles are viewable by everyone." on public.profiles
  for select using (true);

Policy Name

string

Public profiles are viewable by everyone.

Operation

SELECT

Applies to SELECT queries

Rule

using (true)

Always returns true - all profiles are publicly visible

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

// Anyone can read any profile
const { data: profile } = await supabase
  .from('profiles')
  .select('*')
  .eq('username', 'johndoe')
  .single();

// This works even for unauthenticated users

INSERT Policy: Users can insert their own profile

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

Policy Name

string

Users can insert their own profile.

Operation

INSERT

Applies to INSERT queries

Rule

with check (auth.uid() = id)

User can only insert if the profile id matches their authenticated auth.uid()

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.

// 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

UPDATE Policy: Users can update own profile

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

Policy Name

string

Users can update own profile.

Operation

UPDATE

Applies to UPDATE queries

Rule

using (auth.uid() = id)

User can only update rows where id matches their auth.uid()

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

// 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

user_library Table Policies

-- Enable RLS
alter table public.user_library enable row level security;

SELECT Policy: Users can view their own library items

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

Policy Name

string

Users can view their own library items.

Operation

SELECT

Applies to SELECT queries

Rule

using (auth.uid() = user_id)

User can only select rows where user_id matches their auth.uid()

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

// 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

INSERT Policy: Users can insert their own library items

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

Policy Name

string

Users can insert their own library items.

Operation

INSERT

Applies to INSERT queries

Rule

with check (auth.uid() = user_id)

User can only insert if user_id matches their auth.uid()

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

// 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

UPDATE Policy: Users can update their own library items

create policy "Users can update their own library items." on public.user_library
  for update using (auth.uid() = user_id);

Policy Name

string

Users can update their own library items.

Operation

UPDATE

Applies to UPDATE queries

Rule

using (auth.uid() = user_id)

User can only update rows where user_id matches their auth.uid()

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

// 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

DELETE Policy: Users can delete their own library items

create policy "Users can delete their own library items." on public.user_library
  for delete using (auth.uid() = user_id);

Policy Name

string

Users can delete their own library items.

Operation

DELETE

Applies to DELETE queries

Rule

using (auth.uid() = user_id)

User can only delete rows where user_id matches their auth.uid()

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

// 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

How RLS Works

Authentication Context

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

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:

SELECT: Check USING clause - if false, row is filtered out
INSERT: Check WITH CHECK clause - if false, insert fails
UPDATE: Check USING clause - if false, row cannot be updated
DELETE: Check USING clause - if false, row cannot be deleted

Example Flow

// 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

// 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

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

-- 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)

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

Service Role Key

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

// 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:

-- 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

Error: new row violates row-level security policyThis 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

No rows returned when you expect dataRLS is filtering out the rows. Common causes:

Wrong user authenticated
Not authenticated when required
Querying with wrong user_id

Best Practices

Show 1. Always Include user_id in Queries

Even though RLS will filter, explicitly including user_id makes intent clear:

// 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('*');

Show 2. Check Authentication Before Operations

const { data: { user } } = await supabase.auth.getUser();

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

// Now safe to proceed

Show 3. Use Type-Safe Queries

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
  });

Show 4. Test RLS Policies Thoroughly

Test as authenticated user
Test as different user
Test as unauthenticated user
Test all CRUD operations

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

Database Schema

View complete database schema

Supabase Integration

How to use Supabase clients

Library Service

High-level library operations with RLS

Supabase RLS Docs

Official RLS documentation

External Services

Database

​Overview

​Why RLS?

​profiles Table Policies

​SELECT Policy: Public profiles are viewable by everyone

​INSERT Policy: Users can insert their own profile

​UPDATE Policy: Users can update own profile

​user_library Table Policies

​SELECT Policy: Users can view their own library items

​INSERT Policy: Users can insert their own library items

​UPDATE Policy: Users can update their own library items

​DELETE Policy: Users can delete their own library items

​How RLS Works

​Authentication Context

​Policy Evaluation

​Example Flow

​Testing RLS Policies

​Test as Different Users

​Using the Supabase Dashboard

​Bypassing RLS (Admin Operations)

​Service Role Key

​Security Definer Functions

​Common Issues

​Best Practices

​Policy Summary

​Related Resources

Database Schema

Supabase Integration

Library Service

Supabase RLS Docs

Overview

Why RLS?

profiles Table Policies

SELECT Policy: Public profiles are viewable by everyone

INSERT Policy: Users can insert their own profile

UPDATE Policy: Users can update own profile

user_library Table Policies

SELECT Policy: Users can view their own library items

INSERT Policy: Users can insert their own library items

UPDATE Policy: Users can update their own library items

DELETE Policy: Users can delete their own library items

How RLS Works

Authentication Context

Policy Evaluation

Example Flow

Testing RLS Policies

Test as Different Users

Using the Supabase Dashboard

Bypassing RLS (Admin Operations)

Service Role Key

Security Definer Functions

Common Issues

Best Practices

Policy Summary

Related Resources