Supabase Row Level Security: Scoping Data to Authenticated Users with auth.uid()

The Problem: Data Isolation in Multi-Tenant Apps

You're building a SaaS where each customer should only see their own records. The database is shared, but the data isn't. Postgres Row Level Security (RLS) solves this at the storage layer, and Supabase exposes it through auth.uid() — a function that returns the authenticated user's UUID from the JWT. The catch: enable RLS without policies and you've locked everyone out, including your own application.

How Supabase RLS Works

Supabase tables are standard Postgres tables. RLS is a native Postgres feature controlled with ALTER TABLE ... ENABLE ROW LEVEL SECURITY. Policies are created with CREATE POLICY and can reference the Supabase Auth context via auth.uid() and auth.role(). When a request hits the PostgREST API (or a direct Postgres connection using a user's JWT), the policy expression runs with that request's security context.

Two key behaviors to internalize:

• Default deny: Once RLS is enabled, a role with no matching policy sees zero rows. This is intentional — it forces explicit grants.
• Service role bypass: The service_role key ignores RLS entirely. It's for backend admin tasks, never for frontend or mobile clients.

Writing a User-Scoped Policy

The canonical pattern: a user_id column on each table, matched against auth.uid(). Here's the minimal setup for a notes table:

-- 1. Table with ownership column
create table notes (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id),
  content text not null,
  created_at timestamptz default now()
);

-- 2. Enable RLS
alter table notes enable row level security;

-- 3. Policy: users see only their own rows
create policy "Users can view own notes"
  on notes for select
  using (auth.uid() = user_id);

-- 4. Insert policy (optional but typical)
create policy "Users can insert own notes"
  on notes for insert
  with check (auth.uid() = user_id);

The using clause filters rows for select, update, delete. The with check clause validates new rows on insert and update. Both evaluate per-row with the requesting user's auth.uid().

Worked Example: Two Users, One Table

Assume two authenticated users: alice (UUID aaa...111) and bob (UUID bbb...222). Insert rows as each user via the Supabase client (which sends their JWT):

-- As alice (via client with alice's JWT)
insert into notes (user_id, content) values (auth.uid(), 'Alice private note');

-- As bob
insert into notes (user_id, content) values (auth.uid(), 'Bob private note');

Now query select * from notes; with each user's token:

• Alice sees 1 row: her note.
• Bob sees 1 row: his note.
• Run the same query with the service_role key (in a server-side script): both rows return.

Verify the policy exists:

select * from pg_policies where tablename = 'notes';

You should see two policies listed with permissive in the cmd column (for select and insert).

Version Differences: Permissive vs Restrictive Policies

Supabase runs on Postgres. As of this writing, the managed platform uses Postgres 15+. Postgres 15 introduced RESTRICTIVE policies (deny-by-default, combined with AND logic). Older versions only support PERMISSIVE policies (allow-by-default, combined with OR logic).

If you're on Postgres 15+, you can write:

create policy "Restrictive example"
  on notes for select
  as restrictive
  using (auth.uid() = user_id);

as restrictive means this policy must pass and any other restrictive policy for the same command. Useful for defense-in-depth (e.g., one policy checks ownership, another checks org membership). On older versions, you'd combine conditions with and inside a single permissive policy.

Trade-offs and Gotchas

• Unauthenticated requests: auth.uid() returns NULL. A policy using (auth.uid() = user_id) will never match an anon request. If you need public read access, add an explicit condition: using (auth.uid() = user_id or is_public = true).
• Table owners bypass RLS: The role that created the table (often postgres or a migration role) ignores policies unless you also run alter table notes force row level security;. Apply this in production migrations.
• Lockout risk: Enabling RLS before creating policies blocks all access for anon and authenticated roles. Create policies in the same transaction or migration step.
• Performance: Policy expressions run per row. For large tables, ensure user_id is indexed. The planner can push the auth.uid() filter into index scans when the expression is simple.

Actionable Checklist

1. Add user_id uuid references auth.users(id) to every table needing isolation.
2. Enable RLS and create select/insert/update/delete policies in one migration.
3. Run alter table force row level security; so owner bypass is disabled.
4. Test with two real JWTs (Supabase Dashboard → Authentication → Users → copy token) and the SQL Editor.
5. Confirm service_role bypass works only in your secure backend — never ship it to a client.

RLS moves authorization into the database where it can't be bypassed by a forgotten middleware check. The pattern above is the foundation; extend it with org IDs, role checks, or restrictive policies as your access model grows.