Row-Level Security in Postgres: A Practical Guide for 2026

Row-Level Security has been in Postgres since 9.5 (late 2015), but the way teams actually use it has changed dramatically in the last three years. Supabase, Neon, and most JWT-based stacks now treat RLS as the primary authorization boundary: the database itself decides whether a given user can see a given row.

That is, when it works. RLS is also where the most production-impacting bugs hide. This guide is what we've learned from operating it across dozens of Supabase projects in 2025 and 2026.

What RLS actually is#

Row-Level Security is a per-table feature that lets you attach policies to a relation. A policy is an SQL expression that returns boolean for each row, evaluated automatically on every SELECT/INSERT/UPDATE/DELETE that touches the table.

RLS has two switches you have to flip explicitly per table:

-- 1. Turn the feature on.
ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;

-- 2. (Optional but recommended) make it apply to table owners too.
ALTER TABLE public.posts FORCE ROW LEVEL SECURITY;

-- 3. Add policies. WITHOUT a policy, RLS denies everything by default.
CREATE POLICY "Authors read their own posts"
  ON public.posts FOR SELECT
  USING (auth.uid() = author_id);

The claim-based pattern#

In a Supabase or Neon-style stack, the database sees an authenticated request as a JWT signed by your auth service. PostgREST (or pg_jwt-aware Postgres) decodes the JWT and sets two GUCs at the statement level:

• request.jwt.claim.role, the JWT's role claim, e.g. authenticated.
• request.jwt.claims, the full claims object as a JSON string.

Supabase exposes the helper auth.uid() which reads request.jwt.claims->sub and returns it as a uuid. This gives you a clean primitive:

CREATE POLICY "Authors manage their own posts"
  ON public.posts FOR ALL TO authenticated
  USING (auth.uid() = author_id)
  WITH CHECK (auth.uid() = author_id);

Two things to notice. First, TO authenticated scopes the policy to the Postgres role the request is using; anon requests will still get denied. Second, WITH CHECK matters for writes, without it, an authenticated user could create rows with a author_id that isn't theirs.

One policy per verb, almost always#

New teams reach for FOR ALL because it's tidy. In practice, splitting policies by verb (SELECT / INSERT / UPDATE / DELETE) almost always pays off:

• Read policies and write policies tend to diverge over time. A user can usually see more rows than they can edit.
• Verb-specific policies are easier to grep for, easier to test, and easier for the RLS debugger in tools like Suparbase to surface meaningfully.
• DELETE policies are often where bugs live (a soft-delete flag in UPDATE that should also have been blocked by DELETE).

-- Read: authors and reviewers can see drafts; everyone can see published.
CREATE POLICY "Public read of published posts"
  ON public.posts FOR SELECT
  USING (status = 'published');

CREATE POLICY "Author read of their drafts"
  ON public.posts FOR SELECT TO authenticated
  USING (auth.uid() = author_id);

-- Write: only the author can insert/update; nobody deletes through the API.
CREATE POLICY "Author writes their own"
  ON public.posts FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = author_id);

CREATE POLICY "Author updates their own draft"
  ON public.posts FOR UPDATE TO authenticated
  USING (auth.uid() = author_id AND status = 'draft')
  WITH CHECK (auth.uid() = author_id);

-- (intentionally no DELETE policy: archive instead.)

The three bugs every team ships#

We've audited dozens of production Supabase projects. Three classes of RLS bug account for most of the incidents we've seen.

1. The missing WITH CHECK

USING filters which rows the user can see; WITH CHECK filters what they can write. A policy with only USING on a write operation lets the user change any column to any value as long as they could see the row at all. The canonical incident: a user can read a public profile, then update its email column because nothing checks who they are.

2. The wrong role binding

A policy without TO <role> applies to everyone, including anon. The fix is mechanical (always scope write policies to authenticated) but is easy to forget when you copy a SELECT policy and forget to add the role.

3. The implicit join across tables

Policies that join through related tables look right and feel right - until a malicious user constructs a query that brings the related row in via a different path. The mitigation: when your policy references another table, that table also needs an RLS policy that the same user can pass.

Testing RLS in CI#

The reason RLS bugs ship is that teams test the happy path manually and ignore the negative cases. The pattern that actually works is to write focused integration tests that:

1. Open a transaction.
2. Use set_config to set request.jwt.claims and role to the user you're simulating.
3. Run the query you expect to allow OR deny.
4. Roll back so no test data sticks around.

BEGIN;

-- Pretend to be user X with the authenticated role.
SELECT set_config(
  'request.jwt.claims',
  '{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}',
  true
);
SET LOCAL ROLE authenticated;

-- Negative test: user X cannot see user Y's draft.
SELECT 1
FROM posts
WHERE id = 'the-other-user-draft-id'
  AND status = 'draft';
-- Expect: zero rows.

-- Negative test: user X cannot update someone else's post.
UPDATE posts
SET title = 'pwned'
WHERE author_id <> auth.uid();
-- Expect: zero rows updated.

ROLLBACK;

This is exactly what Suparbase's RLS debugger automates: pick a table, pick a role, paste a claims object, and the simulator runs each verb inside a rolled-back transaction and reports allow/deny with the visible row count. The same pattern in CI catches regressions before you ship.

Performance: when RLS gets slow#

RLS adds a predicate to every query plan. For ownership checks like auth.uid() = author_id, this is essentially free, the planner pushes the predicate down and uses your existing index on author_id.

The two slow patterns to watch for:

• Policies that call auth.uid() repeatedly in joins. Postgres can't always cache the result, and you end up calling the JWT decoder per row. Materialise it once into a CTE.
• Policies that join through three or more tables to decide visibility. The planner can't always push the join down, and you get a nested loop on every read. Denormalise the authorization-relevant key onto the row itself.

-- Slow: every read on `messages` joins `channels` to check membership.
CREATE POLICY "Members read messages"
  ON messages FOR SELECT TO authenticated
  USING (
    channel_id IN (
      SELECT channel_id FROM memberships WHERE user_id = auth.uid()
    )
  );

-- Faster: store the workspace_id on every message row, then index it.
ALTER TABLE messages ADD COLUMN workspace_id uuid;
CREATE INDEX messages_workspace_user_idx ON messages (workspace_id);

CREATE POLICY "Members read messages"
  ON messages FOR SELECT TO authenticated
  USING (
    workspace_id IN (
      SELECT workspace_id FROM workspace_members WHERE user_id = auth.uid()
    )
  );

Debugging RLS in production#

When a customer says "I can't see my data", the worst debug path is reading the policy SQL with your eyes. The fast path:

1. Reproduce the user's context. Get their JWT claims (sub, role, and any custom claims your auth function reads).
2. Open a transaction, set those claims, run the query, ROLLBACK.
3. If it fails, run the query again with EXPLAIN to see which policy filtered the row out.

A few minutes inside a simulator beats half an hour grepping policy files. Both PG's native tools and Supabase Studio give you the building blocks; what they don't give you is the friendly "run every verb at once and tell me which one denied" view. That's what we built our debugger around.

Pre-ship checklist#

Before merging a table-touching PR:

• RLS is enabled and at least one policy exists per verb you allow.
• WITH CHECK is present on every INSERT / UPDATE policy.
• Every write policy is scoped TO authenticated (or stricter).
• If your policy references another table, that table also has policies you control.
• You ran the simulator for each role you support (anon / authenticated / service_role) and the verb matrix matches expectations.
• At least one negative test sits in CI so a future migration can't silently widen access.

RLS rewards the team that treats it as production code, not as a configuration step. The good news is that with the right tooling and a discipline of always testing the deny path, it's one of the cleanest authorization stories Postgres has ever shipped.