Row-Level Security

Row-Level Security is the foundation of CodePlanet's data protection. It ensures that users can only access their own data, enforced at the database level.

What is RLS?

Row-Level Security is a PostgreSQL feature that allows you to define policies controlling which rows users can see or modify. Unlike application-level security, RLS works at the database level, meaning it cannot be bypassed — even if there's a bug in the application code.

How It Works

-- Without RLS
SELECT * FROM submissions;
-- Returns ALL submissions from ALL users ❌
 
-- With RLS enabled
SELECT * FROM submissions;
-- Returns only submissions where user_id = current_user ✓

Enabling RLS

Every table in CodePlanet has RLS enabled:

-- Enable RLS on a table
ALTER TABLE public.submissions ENABLE ROW LEVEL SECURITY;
 
-- Force RLS even for table owners (important!)
ALTER TABLE public.submissions FORCE ROW LEVEL SECURITY;

Policy Types

SELECT Policies (Read)

Control which rows can be read:

CREATE POLICY "Users can read own submissions"
ON public.submissions
FOR SELECT
USING (auth.uid() = user_id);

INSERT Policies (Create)

Control what data can be inserted:

CREATE POLICY "Users can create own submissions"
ON public.submissions
FOR INSERT
WITH CHECK (auth.uid() = user_id);

UPDATE Policies (Modify)

Control which rows can be updated:

CREATE POLICY "Users can update own submissions"
ON public.submissions
FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

DELETE Policies (Remove)

Control which rows can be deleted:

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

CodePlanet Policies

Profiles Table

-- Users can only view their own profile
CREATE POLICY "Own profile read"
ON public.profiles
FOR SELECT
USING (auth.uid() = id);
 
-- Users can only update their own profile
CREATE POLICY "Own profile update"
ON public.profiles
FOR UPDATE
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

Submissions Table

-- Users see only their own submissions
CREATE POLICY "Own submissions"
ON public.submissions
FOR ALL
USING (auth.uid() = user_id);

Topic Performance Table

-- Learning data is private
CREATE POLICY "Own topic performance"
ON public.topic_performance
FOR ALL
USING (auth.uid() = user_id);

Payments Table

-- Payment records are private
CREATE POLICY "Own payments"
ON public.payments
FOR SELECT
USING (auth.uid() = user_id);
 
-- Only server can insert (via service role)
CREATE POLICY "Server insert payments"
ON public.payments
FOR INSERT
WITH CHECK (false); -- Blocked for regular users

Public vs Private Tables

Some tables are intentionally public:

-- Problems are public (read-only)
CREATE POLICY "Problems are public"
ON public.problems
FOR SELECT
USING (true); -- Everyone can read
 
-- No insert/update/delete for regular users

Service Role Bypass

For server-side operations, the service role bypasses RLS:

// Admin client bypasses RLS
import { createClient } from "@supabase/supabase-js";
 
const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!, // ⚠️ Never expose this!
);
 
// This can access all rows
const { data } = await supabaseAdmin.from("submissions").select("*");

Important: The service role key must never be exposed to the client.

Common Patterns

Owner Access

USING (auth.uid() = user_id)

Role-Based Access

USING (
  auth.uid() = user_id 
  OR 
  EXISTS (
    SELECT 1 FROM admins WHERE admins.user_id = auth.uid()
  )
)

Time-Based Access

USING (
  auth.uid() = user_id
  AND created_at > NOW() - INTERVAL '30 days'
)

Plan-Based Access

USING (
  auth.uid() = user_id
  OR
  EXISTS (
    SELECT 1 FROM profiles 
    WHERE profiles.id = auth.uid() 
    AND profiles.plan = 'pro'
  )
)

Testing RLS

Test your policies in the Supabase SQL editor:

-- Impersonate a user
SET request.jwt.claims = '{"sub": "user-uuid-here"}';
 
-- Now queries will be filtered by RLS
SELECT * FROM submissions;

Debugging RLS Issues

"No rows returned"

Check if RLS is enabled (it blocks all if no matching policy)
Verify the user ID matches
Check policy conditions

"Permission denied"

Check if there's a policy for the operation type
Verify the user is authenticated
Check the WITH CHECK clause for inserts/updates

View Active Policies

SELECT * FROM pg_policies WHERE tablename = 'submissions';

Best Practices

Always enable RLS on new tables
Use FORCE ROW LEVEL SECURITY to apply to table owners
Test policies before deploying
Prefer USING over WITH CHECK where possible
Keep policies simple — complex policies hurt performance
Document your policies in code

Performance Considerations

RLS adds overhead to queries. Optimize by:

Indexing columns used in policies (especially user_id)
Keeping policy conditions simple
Avoiding subqueries in frequently-called policies

-- Create index for RLS performance
CREATE INDEX idx_submissions_user_id ON submissions(user_id);

Next Steps

API Security — Securing API endpoints
Security Overview — Complete security guide
Data Privacy — Privacy policies

On this page