Introduction to Row Level Security

---

The Security Challenge

When building applications, you need to control who can access what data. Traditionally, this is done in application code:

// Traditional approach: Check in application
async function getPosts(userId) {
  const user = await getCurrentUser();

  if (user.role === 'admin') {
    return await db.query('SELECT * FROM posts');
  } else {
    return await db.query('SELECT * FROM posts WHERE user_id = $1', [userId]);
  }
}

This works, but has significant problems.

---

Why Application-Level Security Fails

Multiple Access Points

                    ┌─────────────────────┐
                    │      Database       │
                    └─────────────────────┘
                              ▲
          ┌───────────────────┼───────────────────┐
          │                   │                   │
    ┌─────┴─────┐       ┌─────┴─────┐       ┌─────┴─────┐
    │  Web App  │       │Mobile App │       │Admin Tool │
    │ (secured) │       │ (secured?)│       │(secured??)│
    └───────────┘       └───────────┘       └───────────┘

Every access point must implement security correctly. Miss one, and data leaks.

Developer Mistakes

Security logic is easy to get wrong:

// Bug: Forgot to check ownership
async function deletePost(postId) {
  // Should check: Does this user own this post?
  await db.query('DELETE FROM posts WHERE id = $1', [postId]);
}

// Bug: Query returns too much
async function searchPosts(term) {
  // Returns ALL matching posts, not just user's
  return await db.query('SELECT * FROM posts WHERE title LIKE $1', [`%${term}%`]);
}

The Root Problem

Application code can always be bypassed by:

• Bugs in code
• Direct database access
• New access points without security
• API endpoints that forget checks

---

Row Level Security: A Better Way

Row Level Security (RLS) moves access control into the database itself. Security policies are checked on every query, regardless of how the data is accessed.

                    ┌─────────────────────┐
                    │      Database       │
                    │   ┌───────────────┐ │
                    │   │  RLS Policies │ │ ← Security enforced HERE
                    │   └───────────────┘ │
                    └─────────────────────┘
                              ▲
          ┌───────────────────┼───────────────────┐
          │                   │                   │
    ┌─────┴─────┐       ┌─────┴─────┐       ┌─────┴─────┐
    │  Web App  │       │Mobile App │       │Admin Tool │
    └───────────┘       └───────────┘       └───────────┘

    All access points automatically get the same security!

---

How RLS Works

Step 1: Enable RLS on a Table

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

Important: Once enabled, NO rows are accessible by default (deny all).

Step 2: Create Policies

Policies define who can do what:

-- Allow users to see their own posts
CREATE POLICY "Users can view own posts"
  ON posts
  FOR SELECT
  USING (user_id = auth.uid());

Step 3: PostgreSQL Enforces Automatically

Every query is filtered:

-- When user UUID-123 runs:
SELECT * FROM posts;

-- PostgreSQL automatically transforms it to:
SELECT * FROM posts WHERE user_id = 'UUID-123';

---

RLS in the Supabase Context

Supabase's architecture makes RLS especially powerful:

1. Client-Side Queries

Your frontend talks directly to the database:

// Frontend code
const { data } = await supabase.from('posts').select('*')

Without RLS, this would return ALL posts. With RLS, it automatically returns only what the user should see.

2. Authentication Integration

RLS policies use auth.uid() to know who's asking:

-- auth.uid() returns the current user's ID from the JWT
CREATE POLICY "View own data"
  ON profiles FOR SELECT
  USING (id = auth.uid());

3. Defense in Depth

Even if your frontend has bugs, the database enforces rules:

// Even if frontend sends wrong user_id
const { data } = await supabase
  .from('posts')
  .select('*')
  .eq('user_id', 'someone-elses-id')

// RLS policy still filters to only YOUR posts
// The eq() filter is ANDed with the RLS policy

---

A Simple Example

Let's secure a posts table:

Table Structure

CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) NOT NULL,
  title text NOT NULL,
  content text,
  published boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

Enable RLS

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

Create Policies

-- Anyone can see published posts
CREATE POLICY "Public posts are viewable"
  ON posts FOR SELECT
  USING (published = true);

-- Users can see their own posts (even unpublished)
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (user_id = auth.uid());

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

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

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

Result

Now these queries just work correctly:

// Gets published posts + user's own posts
const { data } = await supabase.from('posts').select('*')

// Creates post owned by current user
const { data } = await supabase.from('posts').insert({
  title: 'My Post',
  content: 'Hello'
  // user_id automatically constrained to auth.uid()
})

// Only succeeds if user owns the post
const { data } = await supabase
  .from('posts')
  .delete()
  .eq('id', postId)

---

Key Concepts

USING vs WITH CHECK

• USING: Filters which rows can be selected, updated, or deleted
• WITH CHECK: Validates data for inserts and updates

-- USING: "Which existing rows can this user see/modify?"
-- WITH CHECK: "Is this new/modified row valid for this user?"

CREATE POLICY "Own posts"
  ON posts
  FOR ALL
  USING (user_id = auth.uid())       -- Can access own rows
  WITH CHECK (user_id = auth.uid()); -- Can only create/modify own rows

Multiple Policies

Policies are combined with OR for the same operation:

-- Policy 1: See own posts
CREATE POLICY "Own posts" ON posts FOR SELECT
  USING (user_id = auth.uid());

-- Policy 2: See public posts
CREATE POLICY "Public posts" ON posts FOR SELECT
  USING (published = true);

-- Result: User sees posts WHERE (user_id = auth.uid() OR published = true)

The Role of auth.uid()

auth.uid() returns:

• The user's UUID when authenticated
• NULL when not authenticated (using anon key)

-- This policy effectively blocks anonymous users
CREATE POLICY "Authenticated users only"
  ON private_data FOR SELECT
  USING (user_id = auth.uid());  -- NULL != any user_id

---

Before and After RLS

Without RLS

// DANGER: Must remember security in every query
async function getPosts(userId) {
  // Easy to forget the WHERE clause!
  return await db.query('SELECT * FROM posts WHERE user_id = $1', [userId]);
}

async function deletePost(postId, userId) {
  // Easy to forget the ownership check!
  return await db.query('DELETE FROM posts WHERE id = $1 AND user_id = $2', [postId, userId]);
}

With RLS

// Security is automatic
const { data } = await supabase.from('posts').select('*')
// Only returns allowed posts

const { data } = await supabase.from('posts').delete().eq('id', postId)
// Only deletes if user owns it

---

Key Takeaways

1. RLS moves security to the database: Impossible to bypass from application code
2. Default deny: Enable RLS = no access until policies grant it
3. auth.uid() links to authentication: Policies know who's asking
4. Policies combine with OR: Multiple policies for same operation create alternatives
5. USING filters reads: Controls which rows are visible
6. WITH CHECK validates writes: Controls what can be inserted/updated

---

Looking Ahead

Now that you understand why RLS matters and how it works conceptually, we'll dive into the anatomy of RLS policies—how to write them effectively for various scenarios.

---

Row Level Security isn't just a feature—it's a paradigm shift. When security is enforced at the data layer, your application code becomes simpler and your security becomes more reliable.