Row Level Security Policies

Overview

Row Level Security (RLS) is PostgreSQL’s built-in mechanism for restricting database access at the row level. Studley AI uses RLS extensively to ensure users can only access their own data while allowing admins to manage the platform.

RLS policies are the foundation of Studley AI’s security model. They ensure data isolation between users even if the application layer is compromised.

How RLS Works

Basic Concepts

Enable RLS on a table

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Create policies to define access rules

CREATE POLICY "policy_name" ON table_name
  FOR SELECT
  USING (condition);

Policies are checked on every query

If no policies match, access is denied by default
Multiple policies are combined with OR logic
Policies can restrict SELECT, INSERT, UPDATE, DELETE

User Profile Policies

user_profiles Table

-- Enable RLS
ALTER TABLE public.user_profiles ENABLE ROW LEVEL SECURITY;

-- Users can view their own profile
CREATE POLICY "Users can view their own profile" 
ON public.user_profiles
FOR SELECT 
USING (auth.uid() = user_id);

-- Users can update their own profile
CREATE POLICY "Users can update their own profile" 
ON public.user_profiles
FOR UPDATE 
USING (auth.uid() = user_id);

-- Users can insert their own profile
CREATE POLICY "Users can insert their own profile" 
ON public.user_profiles
FOR INSERT 
WITH CHECK (auth.uid() = user_id);

How it works:

auth.uid() returns the current authenticated user’s ID
Policy compares it to the user_id column in each row
Only rows where user_id matches auth.uid() are accessible

Security Note: The INSERT policy uses WITH CHECK instead of USING. This prevents users from creating profiles for other users.

Library & Content Policies

user_folders Table

ALTER TABLE public.user_folders ENABLE ROW LEVEL SECURITY;

-- Users can view their own folders
CREATE POLICY "Users can view their own folders" 
ON public.user_folders
FOR SELECT 
USING (auth.uid() = user_id);

-- Users can insert their own folders
CREATE POLICY "Users can insert their own folders" 
ON public.user_folders
FOR INSERT 
WITH CHECK (auth.uid() = user_id);

-- Users can update their own folders
CREATE POLICY "Users can update their own folders" 
ON public.user_folders
FOR UPDATE 
USING (auth.uid() = user_id);

-- Users can delete their own folders
CREATE POLICY "Users can delete their own folders" 
ON public.user_folders
FOR DELETE 
USING (auth.uid() = user_id);

user_library_items Table

ALTER TABLE public.user_library_items ENABLE ROW LEVEL SECURITY;

-- Users can view their own library items
CREATE POLICY "Users can view their own library items" 
ON public.user_library_items
FOR SELECT 
USING (auth.uid() = user_id);

-- Users can insert their own library items
CREATE POLICY "Users can insert their own library items" 
ON public.user_library_items
FOR INSERT 
WITH CHECK (auth.uid() = user_id);

-- Users can update their own library items
CREATE POLICY "Users can update their own library items" 
ON public.user_library_items
FOR UPDATE 
USING (auth.uid() = user_id);

-- Users can delete their own library items
CREATE POLICY "Users can delete their own library items" 
ON public.user_library_items
FOR DELETE 
USING (auth.uid() = user_id);

Admin Policies

admin_notifications Table

ALTER TABLE public.admin_notifications ENABLE ROW LEVEL SECURITY;

-- Anyone can view enabled notifications
CREATE POLICY "Anyone can view enabled notifications" 
ON public.admin_notifications
FOR SELECT 
USING (enabled = true);

-- Admins can manage notifications
CREATE POLICY "Admins can manage notifications" 
ON public.admin_notifications
FOR ALL 
USING (auth.uid() IN (SELECT id FROM public.admin_users));

How admin check works:

Assumes an admin_users table exists with admin user IDs
Alternatively, check for an is_admin flag:

CREATE POLICY "Admins can manage notifications" 
ON public.admin_notifications
FOR ALL 
USING (
  EXISTS (
    SELECT 1 FROM user_profiles 
    WHERE user_id = auth.uid() 
    AND is_admin = true
  )
);

admin_config Table

ALTER TABLE public.admin_config ENABLE ROW LEVEL SECURITY;

-- Anyone can view admin config
CREATE POLICY "Anyone can view admin config" 
ON public.admin_config 
FOR SELECT 
USING (true);

-- Admins can manage admin config
CREATE POLICY "Admins can manage admin config" 
ON public.admin_config 
FOR ALL 
USING (true) 
WITH CHECK (true);

Note: These policies use USING (true) which allows all authenticated users. In production, implement proper admin role checking.

Feedback & Reporting Policies

bug_reports Table

ALTER TABLE bug_reports ENABLE ROW LEVEL SECURITY;

-- Anyone can submit bug reports (including anonymous users)
CREATE POLICY "Anyone can submit bug reports"
ON bug_reports 
FOR INSERT
TO anon, authenticated
WITH CHECK (true);

-- Admins can view all bug reports
CREATE POLICY "Admins can view all bug reports"
ON bug_reports 
FOR SELECT
TO authenticated
USING (true);

-- Admins can update bug reports
CREATE POLICY "Admins can update bug reports"
ON bug_reports 
FOR UPDATE
TO authenticated
USING (true);

Policy Roles:

anon - Unauthenticated users
authenticated - Logged-in users
TO clause restricts which roles the policy applies to

user_feedback Table

ALTER TABLE user_feedback ENABLE ROW LEVEL SECURITY;

-- Anyone can submit feedback
CREATE POLICY "Anyone can submit feedback"
ON user_feedback 
FOR INSERT
TO anon, authenticated
WITH CHECK (true);

-- Admins can view all feedback
CREATE POLICY "Admins can view all feedback"
ON user_feedback 
FOR SELECT
TO authenticated
USING (true);

-- Admins can update feedback
CREATE POLICY "Admins can update feedback"
ON user_feedback 
FOR UPDATE
TO authenticated
USING (true);

shared_materials Table

ALTER TABLE shared_materials ENABLE ROW LEVEL SECURITY;

-- Owner can manage their shared materials
CREATE POLICY shared_materials_owner_policy 
ON shared_materials
FOR ALL
USING (true)
WITH CHECK (true);

-- Shared user can view their shared materials
CREATE POLICY shared_materials_shared_user_policy 
ON shared_materials
FOR SELECT
USING (true);

-- Public access (no user restriction)
CREATE POLICY shared_materials_public_policy 
ON shared_materials
FOR SELECT
USING (public_access_enabled = true AND public_token IS NOT NULL);

The shared materials policies use USING (true) currently. In production, these should be tightened to check ownership and sharing relationships.

Recommended improvement:

-- Better owner policy
CREATE POLICY shared_materials_owner_policy 
ON shared_materials
FOR ALL
USING (auth.uid() = owner_id)
WITH CHECK (auth.uid() = owner_id);

-- Better shared user policy
CREATE POLICY shared_materials_shared_user_policy 
ON shared_materials
FOR SELECT
USING (
  auth.uid() = shared_with_user_id 
  OR auth.uid() = owner_id
);

shared_quizzes Table

ALTER TABLE shared_quizzes ENABLE ROW LEVEL SECURITY;

-- Anyone can view shared quizzes
CREATE POLICY "Anyone can view shared quizzes" 
ON shared_quizzes
FOR SELECT
USING (true);

-- Anyone can insert shared quizzes
CREATE POLICY "Anyone can insert shared quizzes" 
ON shared_quizzes
FOR INSERT
WITH CHECK (true);

-- Update access count on view
CREATE POLICY "Update access count on view" 
ON shared_quizzes
FOR UPDATE
USING (true)
WITH CHECK (true);

AI Workspace Policies

ai_chat_sessions Table

ALTER TABLE public.ai_chat_sessions ENABLE ROW LEVEL SECURITY;

-- Allow all for now (should be restricted in production)
CREATE POLICY "allow_all_chat_sessions"
ON public.ai_chat_sessions 
FOR ALL
USING (true);

Production-ready policy:

-- Users can only access their own chat sessions
CREATE POLICY "Users can manage their own chat sessions"
ON public.ai_chat_sessions
FOR ALL
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

ai_chat_messages Table

ALTER TABLE public.ai_chat_messages ENABLE ROW LEVEL SECURITY;

-- Production policy: Users can only access messages from their sessions
CREATE POLICY "Users can view messages from their sessions"
ON public.ai_chat_messages
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM ai_chat_sessions 
    WHERE id = session_id 
    AND user_id = auth.uid()
  )
);

CREATE POLICY "Users can insert messages to their sessions"
ON public.ai_chat_messages
FOR INSERT
WITH CHECK (
  EXISTS (
    SELECT 1 FROM ai_chat_sessions 
    WHERE id = session_id 
    AND user_id = auth.uid()
  )
);

Notifications Policies

notifications Table

ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;

-- Users can view their own notifications
CREATE POLICY notifications_user_policy 
ON notifications
FOR SELECT
USING (true);  -- Should be: auth.uid() = user_id

-- Users can update read status of their own notifications
CREATE POLICY notifications_update_policy 
ON notifications
FOR UPDATE
USING (true)  -- Should be: auth.uid() = user_id
WITH CHECK (true);  -- Should be: auth.uid() = user_id

-- System/API can insert notifications
CREATE POLICY notifications_insert_policy 
ON notifications
FOR INSERT
WITH CHECK (true);

-- Users can delete their own notifications
CREATE POLICY notifications_delete_policy 
ON notifications
FOR DELETE
USING (true);  -- Should be: auth.uid() = user_id

Security Issue: The notifications policies currently use USING (true) which allows any user to access any notification. This should be fixed in production.

Clever Integration Policies

classrooms Table

ALTER TABLE public.classrooms ENABLE ROW LEVEL SECURITY;

-- Teachers can view and manage their own classrooms
CREATE POLICY "Teachers can manage their classrooms"
ON public.classrooms
FOR ALL
USING (auth.uid() = teacher_id)
WITH CHECK (auth.uid() = teacher_id);

students Table

ALTER TABLE public.students ENABLE ROW LEVEL SECURITY;

-- Teachers can view students in their classrooms
CREATE POLICY "Teachers can view their students"
ON public.students
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM classrooms 
    WHERE id = classroom_id 
    AND teacher_id = auth.uid()
  )
);

-- Teachers can manage students in their classrooms
CREATE POLICY "Teachers can manage their students"
ON public.students
FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM classrooms 
    WHERE id = classroom_id 
    AND teacher_id = auth.uid()
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1 FROM classrooms 
    WHERE id = classroom_id 
    AND teacher_id = auth.uid()
  )
);

Policy Management

Viewing Existing Policies

-- View all policies on a table
SELECT 
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE tablename = 'your_table_name';

-- List all tables with RLS enabled
SELECT 
  schemaname,
  tablename,
  rowsecurity
FROM pg_tables
WHERE rowsecurity = true;

Modifying Policies

-- Drop a policy
DROP POLICY IF EXISTS "policy_name" ON table_name;

-- Recreate with new rules
CREATE POLICY "policy_name" ON table_name
  FOR SELECT
  USING (new_condition);

Disabling RLS (Not Recommended)

-- Disable RLS on a table (use with caution!)
ALTER TABLE table_name DISABLE ROW LEVEL SECURITY;

-- Force RLS even for table owner
ALTER TABLE table_name FORCE ROW LEVEL SECURITY;

Never disable RLS in production without understanding the security implications. It exposes all data to all users.

Bypassing RLS (Service Role)

Supabase’s service role key bypasses all RLS policies:

import { createClient } from '@supabase/supabase-js'

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

// Regular client - respects RLS
const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

Critical: The service role key should:

Never be exposed to the client
Only be used in server-side code
Be treated as a master password
Have strict access controls

Testing RLS Policies

Test as Specific User

-- Set session to specific user
SET LOCAL ROLE authenticated;
SET LOCAL request.jwt.claims = '{"sub": "user-uuid-here"}';

-- Run test query
SELECT * FROM user_library_items;

-- Reset
RESET ROLE;

Verify Policy Works

-- Create test users
INSERT INTO auth.users (id, email) VALUES 
  ('user1-uuid', '[email protected]'),
  ('user2-uuid', '[email protected]');

-- Insert test data
INSERT INTO user_library_items (user_id, title, item_type, content) VALUES
  ('user1-uuid', 'Test Item 1', 'quiz', '{}'),
  ('user2-uuid', 'Test Item 2', 'quiz', '{}');

-- Test as user1 (should only see their item)
SET LOCAL request.jwt.claims = '{"sub": "user1-uuid"}';
SELECT * FROM user_library_items;  -- Should return only 1 row
RESET ROLE;

Common Issues

Policy Not Working

Check:

RLS is enabled: ALTER TABLE x ENABLE ROW LEVEL SECURITY;
Policy syntax is correct
auth.uid() returns expected value
User is authenticated

”Row-level security policy violated”

Causes:

No matching policy for the operation
WITH CHECK clause fails on INSERT/UPDATE
User not authenticated when policy requires it

Solution:

-- Add permissive policy for debugging
CREATE POLICY "debug_policy" ON table_name
  FOR ALL
  TO authenticated
  USING (true)
  WITH CHECK (true);

Database Schema

View complete table structure

Admin Dashboard

Admin dashboard overview

Migrations

Running database migrations

User Management

Managing user data

Administration

Database

​Overview

​How RLS Works

​Basic Concepts

​User Profile Policies

​user_profiles Table

​Library & Content Policies

​user_folders Table

​user_library_items Table

​Admin Policies

​admin_notifications Table

​admin_config Table

​Feedback & Reporting Policies

​bug_reports Table

​user_feedback Table

​Sharing Policies

​shared_materials Table

​shared_quizzes Table

​AI Workspace Policies

​ai_chat_sessions Table

​ai_chat_messages Table

​Notifications Policies

​notifications Table

​Clever Integration Policies

​classrooms Table

​students Table

​Policy Management

​Viewing Existing Policies

​Modifying Policies

​Disabling RLS (Not Recommended)

​Bypassing RLS (Service Role)

​Testing RLS Policies

​Test as Specific User

​Verify Policy Works

​Common Issues

​Policy Not Working

​”Row-level security policy violated”

​Related Documentation

Database Schema

Admin Dashboard

Migrations

User Management

Build docs developers (and LLMs) love

Overview

How RLS Works

Basic Concepts

User Profile Policies

user_profiles Table

Library & Content Policies

user_folders Table

user_library_items Table

Admin Policies

admin_notifications Table

admin_config Table

Feedback & Reporting Policies

bug_reports Table

user_feedback Table

Sharing Policies

shared_materials Table

shared_quizzes Table

AI Workspace Policies

ai_chat_sessions Table

ai_chat_messages Table

Notifications Policies

notifications Table

Clever Integration Policies

classrooms Table

students Table

Policy Management

Viewing Existing Policies

Modifying Policies

Disabling RLS (Not Recommended)

Bypassing RLS (Service Role)

Testing RLS Policies

Test as Specific User

Verify Policy Works

Common Issues

Policy Not Working

”Row-level security policy violated”

Related Documentation