enable-rls

How to enable Row-Level Security on a table

Status: 🟩 COMPLETE Last updated: 2026-06-19 Plain-English tagline: Lock down a Supabase table so the database itself enforces who can see what. ~5 minutes.

---

Goal

You have a table in Supabase that contains user data. At the end of this guide, RLS is enabled, policies are written that allow only the appropriate access, and you’ve tested the policies to confirm they work.

---

Prerequisites

• Supabase project with a table to secure
• Authentication wired up in your app (if user-specific policies — see Add Supabase auth)
• Access to the SQL Editor in the Supabase dashboard

---

Why this matters

Without RLS, every row in your table is readable by anyone with the anon key — and the anon key is in your browser bundle. Without RLS, “private” data isn’t private. With RLS, the database itself filters every query. Even if your app code has a bug, the database stops unauthorized reads at the boundary.

This is the single most important Supabase security practice. Don’t skip it.

---

Steps

1. Enable RLS on the table

In the SQL Editor:

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

The moment you run this, the table is unreadable to the anon key — no policies, no access. Test by running select("*") from your app and you’ll get back an empty array.

This default-deny posture is the whole point. You now have to explicitly grant the access you intend.

2. Decide the access model

Common patterns:

Access model	Who can read	Who can write
Public read, own write	Everyone	Only the owner
Owner only	Only the owner	Only the owner
Public	Everyone	Everyone
Org-scoped	Org members	Org members with role X
Admin only	Users with admin flag	Users with admin flag

Pick one. Write the matching policies.

3. Write policies

Each policy targets one operation: SELECT, INSERT, UPDATE, or DELETE.

Pattern A: Public read, owner-only write

For a table where everyone can read posts but only the author can modify their own:

-- Anyone can read
CREATE POLICY "Anyone can read posts"
  ON posts FOR SELECT
  USING (true);
 
-- Only the author can insert
CREATE POLICY "Authors insert own posts"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = user_id);
 
-- Only the author can update
CREATE POLICY "Authors update own posts"
  ON posts FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);
 
-- Only the author can delete
CREATE POLICY "Authors delete own posts"
  ON posts FOR DELETE
  USING (auth.uid() = user_id);

Note the USING vs WITH CHECK:

• USING — applies to rows being READ or modified (e.g. “you can update rows that match this”)
• WITH CHECK — applies to the resulting state (e.g. “after your INSERT/UPDATE, the row must match this”)

For INSERTs, only WITH CHECK makes sense. For UPDATEs, both apply (the row before AND after must match).

Pattern B: Owner-only (private data)

-- Owner can read their own rows only
CREATE POLICY "Owners read own data"
  ON private_notes FOR SELECT
  USING (auth.uid() = user_id);
 
-- Same for write
CREATE POLICY "Owners write own data"
  ON private_notes FOR ALL
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

FOR ALL is shorthand for SELECT, INSERT, UPDATE, and DELETE — useful when the rules are identical.

Pattern C: Service role bypass (built in)

The service_role key bypasses ALL RLS by design. Server-side code using the service_role key can access everything. Use this for admin tasks, background jobs, or operations the user shouldn’t be allowed to do directly.

4. Test the policies

Two ways:

Via the SQL Editor (with role switching)

-- Simulate the anonymous role
SET ROLE anon;
SELECT * FROM posts;
RESET ROLE;
 
-- Simulate an authenticated user (replace <uid> with a real user ID)
SET request.jwt.claim.sub = '<uid>';
SET ROLE authenticated;
SELECT * FROM posts;
RESET ROLE;

Via your app

Sign in as a real user, run a query, confirm you only get what you should. Sign in as a different user, confirm you don’t see the first user’s data.

The Supabase Studio’s Authentication → Users lets you create test users quickly.

5. Audit before shipping

For any table that holds user data:

• ☑️ Is RLS enabled?
• ☑️ Is there a policy for each operation you intend to allow?
• ☑️ Have you tested the policies with multiple user accounts?
• ☑️ Is the service_role key only used server-side?
• ☑️ Are there any policies that accidentally grant too much access (e.g. USING (true) on a sensitive table)?

---

Verification

• ✅ The Supabase Studio shows a green RLS indicator next to the table
• ✅ Running select("*") with the anon key returns only the rows your policies allow
• ✅ A user sees their own data, not other users’ data
• ✅ Operations the user shouldn’t do (write, delete) fail with a policy error

---

Common failures

”permission denied for table”

RLS is enabled but no policy covers the operation. Add the missing policy.

”new row violates row-level security policy”

The INSERT or UPDATE is blocked because the row wouldn’t match the WITH CHECK clause. Common cause: forgot to set user_id to auth.uid() when inserting.

auth.uid() returns null

The query isn’t being made with an authenticated session. Check that your app is using the anon key WITH a logged-in user’s session cookies, not just the raw anon key without auth.

service_role key being used client-side

This bypasses ALL RLS and exposes admin access. Audit your code immediately. The fix: move the calls server-side (Server Component, Server Action, Route Handler), use a server client with the service_role key, and NEVER prefix the env var with NEXT_PUBLIC_.

Policies don’t update after editing

The policy editor in Studio sometimes caches. Try refreshing, or run the policy SQL directly in the SQL Editor.

Public-read policy but data still not visible

Sometimes the issue isn’t RLS — it’s that the row literally doesn’t exist or has a different filter. Run SELECT count(*) FROM table with BYPASS RLS (service_role key) to verify there’s data.

---

Common policy patterns

A reference grab-bag:

Read your own + read public rows

CREATE POLICY "Read own or public"
  ON posts FOR SELECT
  USING (auth.uid() = user_id OR is_public = true);

Org-scoped

CREATE POLICY "Members read org data"
  ON org_data FOR SELECT
  USING (
    org_id IN (
      SELECT org_id FROM org_members WHERE user_id = auth.uid()
    )
  );

Time-bounded

CREATE POLICY "Read recent posts only"
  ON posts FOR SELECT
  USING (created_at > now() - interval '30 days');

Role-based

CREATE POLICY "Admins read everything"
  ON posts FOR SELECT
  USING (
    auth.uid() IN (SELECT user_id FROM admins)
  );

---

See also

• Row-Level Security 🟥
• Supabase 🟩 🟦
• How-to: Set up Supabase 🟩
• How-to: Add Supabase auth 🟩
• Authentication vs authorization 🟥
• Supabase gotchas 🟥

Sources

• Supabase — Row Level Security docs
• PostgreSQL — Row Security Policies