Row Level Security (RLS) and policies

Introduction

Row Level Security (RLS) is a PostgreSQL feature: each row can only be read/updated/deleted if it satisfies a policy. Supabase PostgREST uses the JWT from the request; in policies you use auth.uid() to get the user id and restrict access per user (or tenant). This post explains what RLS is, how to enable it, how to write SELECT/INSERT/UPDATE/DELETE policies and how to test them.

Goal: Protect data at the DB layer (least privilege); each user only sees and edits their own rows (or by tenant).

Features

What is RLS

• RLS: When enabled on a table, Postgres does not return or allow updates to any row unless a policy allows it. Default is “deny”; policies “allow” when their condition is true.
• Policy: Attached to a table and an operation (SELECT, INSERT, UPDATE, DELETE). The condition is a boolean expression (e.g. user_id = auth.uid()). You can use USING (read) and WITH CHECK (write).
• auth.uid(): Supabase function that returns the sub from the JWT (user id). It only has a value when the request sends a valid JWT; without JWT or with anon, auth.uid() is null.

When to use it

• Tables with “per-user” data (e.g. conversations, messages): enable RLS, policy “only where user_id = auth.uid()”.
• Multi-tenant: add a tenant_id column, policy “tenant_id = auth.jwt() ->> 'tenant_id'” (or a custom claim).
• Admin-only tables: policy “auth.jwt() ->> 'role' = 'admin'” (if you set role in the JWT).

Workflow / process

1. Enable RLS on the table: ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;
2. Write SELECT policy: Allow read when (e.g.) user_id = auth.uid().
3. Write INSERT policy: WITH CHECK (e.g. user_id = auth.uid()) so users can only insert their own rows.
4. Write UPDATE/DELETE policies: USING (e.g. user_id = auth.uid()) so users can only update/delete their own rows.
5. Test: Call PostgREST with user A’s JWT → only see user A’s rows; with user B’s JWT → only user B’s. Service role key bypasses RLS (backend only when needed).

Convention: Enable RLS by default on tables with sensitive data; minimal policies (least privilege); avoid “allow all” policies in production.

Sample code

Enable RLS and policies (in migration)

-- Enable RLS
ALTER TABLE public.conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.messages ENABLE ROW LEVEL SECURITY;
 
-- conversations: user can only read/write their own rows
CREATE POLICY "conversations_select_own"
  ON public.conversations FOR SELECT
  USING (user_id = auth.uid());
 
CREATE POLICY "conversations_insert_own"
  ON public.conversations FOR INSERT
  WITH CHECK (user_id = auth.uid());
 
CREATE POLICY "conversations_update_own"
  ON public.conversations FOR UPDATE
  USING (user_id = auth.uid());
 
CREATE POLICY "conversations_delete_own"
  ON public.conversations FOR DELETE
  USING (user_id = auth.uid());
 
-- messages: only read/write messages in conversations they own
CREATE POLICY "messages_select_own"
  ON public.messages FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.conversations c
      WHERE c.id = messages.conversation_id AND c.user_id = auth.uid()
    )
  );
 
CREATE POLICY "messages_insert_own"
  ON public.messages FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.conversations c
      WHERE c.id = messages.conversation_id AND c.user_id = auth.uid()
    )
  );
-- UPDATE/DELETE similarly by conversation ownership.

Test from client (after login)

const { data } = await supabase.from('conversations').select('*');
// Returns only conversations where user_id = current user

Apply in your architecture

• Secure by default: RLS ensures that even if the client sends a bad request (e.g. tampering with ids), the DB still won’t return or allow updates to rows that don’t belong to the user. Least privilege at the data layer.
• Secrets and permissions: Anon key can be public because RLS limits by JWT; service role key bypasses RLS so use it only on the server when needed (e.g. background jobs, admin).
• Clear boundary: Policies are tied to tables and claims (user_id, tenant_id); changing permissions = change policy or JWT claim, not scattered logic in the app.

Summary

RLS protects each row by condition (e.g. user_id = auth.uid()); SELECT/INSERT/UPDATE/DELETE policies give fine-grained control. Next: API — PostgREST vs custom API and when to use which.

Next: 08 — API: PostgREST vs custom API

Further reading: Supabase — Row Level Security