Implement Authorization using Row Level Security (RLS) with Supabase (Step By Step Guide)

Overview (what the tutorial covers)

• The video teaches how to implement authorization using PostgreSQL Row Level Security (RLS) in Supabase, step-by-step, for a small blog app.
• It explains the core idea: RLS policies enforce access at the database layer, based on the authenticated user and row data, instead of relying solely on application/API logic.

Key concept: Why RLS (technological/architectural analysis)

• Traditional “3-piece” model: client → API → database
  • The API decides access, then the database returns data.
• With RLS:
  • The client can send the auth token directly.
  • The database applies row-level policies to decide which rows the user can SELECT/INSERT/UPDATE/DELETE.

Benefits emphasized

• Fewer hops / potentially more efficient queries
  • Avoids extra API permission logic “hops.”
• Safer authorization
  • Even if application code is buggy or malicious, the database still blocks unauthorized operations.
  • Helps prevent data leaks and unauthorized deletes.

Tutorial project used (product features / schema)

A “blog” Supabase project with two tables (one-to-many):

• posts table
  • Fields: id, title, content, user_id (author), created_at, is_published
• comments table
  • Fields: id, content, user_id (author), created_at, post_id (associated post)

Step-by-step RLS policies implemented

1) Enable RLS on posts

• RLS is disabled by default.
• Enabling it locks the table down:
  • By default, requests are denied for SELECT, INSERT, UPDATE, DELETE until policies are added.

Policy A: “Enable all actions for users based on their user id”

• Purpose:
  • Only the post author can SELECT/INSERT/UPDATE/DELETE their own posts (including unpublished posts).
• Condition concept:
  • Use the Supabase auth helper auth.uid() (current user) compared against posts.user_id.
  • This condition is applied to relevant commands (including update/delete via the policy’s USING / WITH CHECK logic).

Policy B: “Allow users to select published posts”

• Purpose:
  • Any user can SELECT posts where is_published = true.
• Result:
  • If a post is unpublished, access to that post disappears automatically for users.

Edge case demonstrated (security value)

• Even if someone copies a URL meant for editing a post, RLS prevents unauthorized actions (e.g., deleting someone else’s post).
• If app logic is missing, RLS still blocks the operation at the database layer.

---

2) Enable RLS on comments

• After enabling RLS, users cannot see comments until policies exist.

Policy C (SELECT): “View comments for posts the user can view”

• Purpose:
  • A user can view comments only if they can view the associated post.
• Technique:
  • Uses a subquery to check whether the current user can SELECT the related post.
  • Conceptually:
    • It runs a conditional check like: select from post where post.id = comment.post_id
    • Returns a boolean/existence result, so comment visibility depends on post visibility.

Policy D (INSERT): “Insert comments only on posts the user can view, and only as themselves”

• Purpose:
  • Users can add comments only if:
    1. The user can view the related post
    2. The comment’s user_id matches the current authenticated user (auth.uid() = comment.user_id)
• Prevents spoofing by ensuring users can’t insert comments as someone else.

Policy E (UPDATE): “Update only your own comments for viewable posts”

• Purpose:
  • Users can update a comment only if:
    • They can view the related post
    • They own the comment (auth.uid() = comment.user_id)
• Uses the same-style condition in both USING and WITH CHECK.

Policy F (DELETE): “Allow authors of the post to moderate/delete comments on their posts”

• Two delete rules exist conceptually:
  • Users deleting their own comments
  • Moderation rule: post owners can delete comments on their posts
• Final moderation behavior in the tutorial:
  • Ash (post owner) can delete Gary’s comments.
  • Gary cannot delete Ash’s comments.

Final outcome (what the app behavior becomes)

After all policies:

• Unpublished posts: only the author can see them.
• Published posts: everyone can read them.
• Comments:
  • Only visible users can view comments for visible posts.
  • Users can add comments only as themselves.
  • Users can update/delete their own comments.
  • Post owners can moderate/delete comments on their posts.
• Unauthorized deletes and access via direct URLs are blocked by RLS.

Main speakers/sources

• Jon from Supabase (host)
• Source references within tutorial: Supabase RLS / auth documentation (mentioned for further reading)