Row Level Security (RLS) Best Practices: Data Security Implementation

ContentQR Team
9 min read
Technical Development
Supabase
RLS
Security
PostgreSQL

Row Level Security (RLS) is a powerful PostgreSQL feature that enables fine-grained access control at the row level. As you build multi-tenant applications or applications with user-specific data, implementing proper RLS policies becomes crucial for data security. This guide covers RLS best practices for Supabase and PostgreSQL, showing you how to implement secure data access patterns. You'll learn how to create effective policies, handle common scenarios, and avoid common mistakes that can lead to data leaks. Whether you're building a new application or securing an existing one, understanding RLS will help you protect user data at the database level. We'll explore real-world examples, testing strategies, and best practices that you can apply immediately to your Supabase projects.

What is Row Level Security?

Row Level Security (RLS) allows you to control access to individual rows in a database table based on the user executing the query. It's essential for multi-tenant applications and user-specific data access.

Why Use RLS?

Benefits:

  • Fine-grained access control
  • Database-level security
  • Prevents data leaks
  • Enforces security policies
  • Works with Supabase Auth

Enabling RLS

Basic Setup

-- Enable RLS on a table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Create a policy
CREATE POLICY "Users can view their own posts"
ON posts
FOR SELECT
USING (auth.uid() = user_id);

Supabase Implementation

In Supabase, RLS is enabled through the dashboard or SQL:

-- Enable RLS
ALTER TABLE qr_codes ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their own QR codes
CREATE POLICY "Users can view own QR codes"
ON qr_codes
FOR SELECT
USING (auth.uid() = user_id);

Common RLS Patterns

Pattern 1: User-Owned Data

Use Case: Users can only access their own data.

-- Table structure
CREATE TABLE qr_codes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id),
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE qr_codes ENABLE ROW LEVEL SECURITY;

-- Policy: Users can SELECT their own QR codes
CREATE POLICY "Users can view own QR codes"
ON qr_codes
FOR SELECT
USING (auth.uid() = user_id);

-- Policy: Users can INSERT their own QR codes
CREATE POLICY "Users can insert own QR codes"
ON qr_codes
FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Policy: Users can UPDATE their own QR codes
CREATE POLICY "Users can update own QR codes"
ON qr_codes
FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Policy: Users can DELETE their own QR codes
CREATE POLICY "Users can delete own QR codes"
ON qr_codes
FOR DELETE
USING (auth.uid() = user_id);

Pattern 2: Public Read, Authenticated Write

Use Case: Public can read, but only authenticated users can write.

-- Blog posts table
CREATE TABLE blog_posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT false,
  author_id UUID REFERENCES auth.users(id)
);

ALTER TABLE blog_posts ENABLE ROW LEVEL SECURITY;

-- Policy: Anyone can view published posts
CREATE POLICY "Public can view published posts"
ON blog_posts
FOR SELECT
USING (published = true);

-- Policy: Authenticated users can create posts
CREATE POLICY "Authenticated users can create posts"
ON blog_posts
FOR INSERT
WITH CHECK (auth.role() = 'authenticated');

-- Policy: Authors can update their posts
CREATE POLICY "Authors can update own posts"
ON blog_posts
FOR UPDATE
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);

Pattern 3: Role-Based Access

Use Case: Different access levels based on user roles.

-- Users table with roles
CREATE TABLE user_profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id),
  email TEXT,
  role TEXT DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator'))
);

ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

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

-- Policy: Admins can view all profiles
CREATE POLICY "Admins can view all profiles"
ON user_profiles
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM user_profiles
    WHERE id = auth.uid() AND role = 'admin'
  )
);

ContentQR Implementation

QR Codes Table

-- ContentQR's QR codes table with RLS
CREATE TABLE qr_codes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id) NOT NULL,
  content TEXT NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('url', 'text', 'email', 'phone')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE qr_codes ENABLE ROW LEVEL SECURITY;

-- Policy: Users can view their own QR codes
CREATE POLICY "Users can view own QR codes"
ON qr_codes
FOR SELECT
USING (auth.uid() = user_id);

-- Policy: Users can create QR codes
CREATE POLICY "Users can create QR codes"
ON qr_codes
FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Policy: Users can update their own QR codes
CREATE POLICY "Users can update own QR codes"
ON qr_codes
FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Policy: Users can delete their own QR codes
CREATE POLICY "Users can delete own QR codes"
ON qr_codes
FOR DELETE
USING (auth.uid() = user_id);

Analytics Table

-- Analytics table with RLS
CREATE TABLE qr_analytics (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  qr_code_id UUID REFERENCES qr_codes(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id),
  event_type TEXT NOT NULL,
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE qr_analytics ENABLE ROW LEVEL SECURITY;

-- Policy: Users can view analytics for their QR codes
CREATE POLICY "Users can view own analytics"
ON qr_analytics
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM qr_codes
    WHERE qr_codes.id = qr_analytics.qr_code_id
    AND qr_codes.user_id = auth.uid()
  )
);

-- Policy: System can insert analytics (via service role)
CREATE POLICY "Service role can insert analytics"
ON qr_analytics
FOR INSERT
WITH CHECK (true); -- Service role bypasses RLS

Best Practices

1. Always Enable RLS

Enable RLS on all tables that contain user data:

-- ✅ Good: Enable RLS
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

-- ❌ Bad: No RLS protection
-- Table is accessible to all users

2. Use USING and WITH CHECK

USING: Controls which rows can be accessed (SELECT, UPDATE, DELETE)
WITH CHECK: Controls which rows can be inserted/updated

-- Correct usage
CREATE POLICY "Users can update own posts"
ON posts
FOR UPDATE
USING (auth.uid() = user_id)        -- Can only update own posts
WITH CHECK (auth.uid() = user_id);  -- Can only set user_id to own ID

3. Test Policies Thoroughly

Test RLS policies with different user contexts:

// Test RLS policies
import { createClient } from '@supabase/supabase-js';

// Test as user A
const userA = createClient(url, userAToken);
const { data } = await userA.from('qr_codes').select('*');
// Should only see user A's QR codes

// Test as user B
const userB = createClient(url, userBToken);
const { data } = await userB.from('qr_codes').select('*');
// Should only see user B's QR codes

4. Use Service Role Carefully

Service role bypasses RLS. Use only for:

  • Background jobs
  • Admin operations
  • System-level operations

When designing your Supabase architecture, RLS policies work together with your overall database design. Learn more about Supabase architecture design to understand how RLS fits into your application architecture.

// ✅ Good: Use service role for admin operations
const adminClient = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // Service role
);

// ❌ Bad: Using service role in client code
// This bypasses all RLS policies

5. Handle NULL Values

Consider NULL values in policies:

-- Handle NULL user_id
CREATE POLICY "Users can view own or public posts"
ON posts
FOR SELECT
USING (
  auth.uid() = user_id OR
  user_id IS NULL  -- Public posts
);

Common Mistakes

Mistake 1: Forgetting RLS

-- ❌ Bad: No RLS enabled
CREATE TABLE qr_codes (
  id UUID PRIMARY KEY,
  user_id UUID,
  content TEXT
);
-- Anyone can access all QR codes!

-- ✅ Good: RLS enabled
ALTER TABLE qr_codes ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view own QR codes"
ON qr_codes FOR SELECT
USING (auth.uid() = user_id);

Mistake 2: Incorrect Policy Logic

-- ❌ Bad: Wrong comparison
CREATE POLICY "Users can view own QR codes"
ON qr_codes
FOR SELECT
USING (user_id = auth.uid()); -- Should be auth.uid() = user_id

-- ✅ Good: Correct comparison
CREATE POLICY "Users can view own QR codes"
ON qr_codes
FOR SELECT
USING (auth.uid() = user_id);

Mistake 3: Missing WITH CHECK

-- ❌ Bad: Missing WITH CHECK
CREATE POLICY "Users can update QR codes"
ON qr_codes
FOR UPDATE
USING (auth.uid() = user_id);
-- Users could update user_id to someone else's!

-- ✅ Good: Include WITH CHECK
CREATE POLICY "Users can update own QR codes"
ON qr_codes
FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

Testing RLS Policies

Unit Testing

// __tests__/rls-policies.test.ts
import { createClient } from '@supabase/supabase-js';

describe('RLS Policies', () => {
  it('should only return user\'s own QR codes', async () => {
    const user1Client = createClient(url, user1Token);
    const { data } = await user1Client
      .from('qr_codes')
      .select('*');
    
    // All QR codes should belong to user1
    expect(data?.every(qr => qr.user_id === user1Id)).toBe(true);
  });
  
  it('should not allow users to see other users\' QR codes', async () => {
    const user1Client = createClient(url, user1Token);
    const { data } = await user1Client
      .from('qr_codes')
      .select('*')
      .eq('user_id', user2Id); // Try to access user2's data
    
    expect(data).toEqual([]); // Should be empty
  });
});

Conclusion

Row Level Security is essential for building secure, multi-tenant applications. By following these best practices, you can ensure that users can only access data they're authorized to see. The key is to enable RLS on all user data tables, create comprehensive policies, and test them thoroughly. Remember that RLS works at the database level, providing an additional layer of security beyond your application code.

Key Takeaways:

  • Always enable RLS on user data tables to prevent unauthorized access
  • Use USING clause for SELECT/UPDATE/DELETE operations
  • Use WITH CHECK clause for INSERT/UPDATE operations
  • Test policies thoroughly with different user scenarios
  • Use service role carefully and only when necessary
  • Handle NULL values appropriately in your policies

Next Steps:

  • Review your current database tables and enable RLS where needed
  • Create comprehensive RLS policies for user-owned data
  • Implement role-based access control if your application requires it
  • Write tests to verify your RLS policies work correctly
  • Document your RLS policies for future reference

For more Supabase insights, check out our articles on Supabase Architecture Design and Next.js Middleware Chain Design.

Published on

Share:

Related Posts

Technical Development
Featured

ContentQR Full-Stack Architecture Evolution: From Monolith to Modular Design

Learn how to evolve your architecture from monolith to modular design. Practical insights and lessons learned from real-world experience.

ContentQR Team
7 min read
Technical Development

Advanced Type Handling: Generics and Utility Types Usage Tips

Master advanced TypeScript type handling with generics and utility types. Learn practical tips and patterns for complex type scenarios.

ContentQR Team
10 min read
Technical Development
Featured

Next.js App Router Best Practices: Migration from Pages Router

Sharing our experience migrating ContentQR from Pages Router to App Router, including best practices and lessons learned.

ContentQR Team
6 min read