Row Level Security Deep Dive

"The best authorization layer is the one that developers cannot accidentally skip."

The Problem

You have 50 tables in a multi-tenant database. Every table has an organization_id column. Every query must filter by the current user's organization. Every developer, on every feature, in every commit, must remember to add WHERE organization_id = ?.

This is the application-level authorization model, and it fails in predictable ways.

A junior developer writes a reporting query and forgets the filter. A senior developer refactors a repository function and drops the condition because it "looked redundant." Someone builds an admin dashboard that queries across organizations for analytics and accidentally exposes it to regular users.

Each of these is a data leak. In a multi-tenant SaaS application, a data leak is not a bug -- it is a security incident that can trigger regulatory action, breach notification requirements, and catastrophic loss of customer trust.

The root cause is always the same: authorization is implemented in the wrong layer. When authorization lives in application code, it is a convention. When it lives in the database, it is a constraint.

The Principle

Row Level Security (RLS) moves authorization from application code into the database engine itself. The database evaluates a policy for every row in every query -- SELECT, INSERT, UPDATE, DELETE -- and silently filters out rows the user is not authorized to access.

This has a profound implication: a query that forgets to filter by organization still returns correct results. The developer does not need to remember. The database does it for them.

RLS policies are defined in SQL, evaluated by PostgreSQL, and cannot be bypassed by any query from any client. They are not middleware. They are not interceptors. They are constraints at the storage engine level.

We implement RLS in two phases:

Phase 1: Inline policies. One policy per table, directly checking the JWT claim against the organization ID. Simple, explicit, easy to understand.

Phase 2: Centralized functions. A single SECURITY DEFINER function that encapsulates the authorization logic, called by every policy. Change the logic once, every table updates automatically.

Phase 1 gets you secure. Phase 2 keeps you sane as the application grows.

The Pattern

Phase 1: Inline Policies

The simplest RLS implementation: enable RLS on the table, create a policy that compares organization_id to the JWT claim.

-- Enable RLS on the table
ALTER TABLE "public"."orders" ENABLE ROW LEVEL SECURITY;

-- Tenant isolation: users see only their organization's data
CREATE POLICY "orders_tenant_isolation"
  ON "public"."orders" FOR ALL
  TO authenticated
  USING (organization_id = (auth.jwt() ->> 'org_id')::text)
  WITH CHECK (organization_id = (auth.jwt() ->> 'org_id')::text);

-- Service role bypass: webhooks need cross-tenant access
CREATE POLICY "orders_service_role_bypass"
  ON "public"."orders" FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

The USING clause filters rows on SELECT, UPDATE, and DELETE. The WITH CHECK clause validates rows on INSERT and UPDATE. Together, they ensure a user can only read and write rows belonging to their organization.

Apply this to every table:

-- Repeat for each table with tenant data
ALTER TABLE "public"."products" ENABLE ROW LEVEL SECURITY;

CREATE POLICY "products_tenant_isolation"
  ON "public"."products" FOR ALL TO authenticated
  USING (organization_id = (auth.jwt() ->> 'org_id')::text)
  WITH CHECK (organization_id = (auth.jwt() ->> 'org_id')::text);

CREATE POLICY "products_service_role_bypass"
  ON "public"."products" FOR ALL TO service_role
  USING (true) WITH CHECK (true);

ALTER TABLE "public"."customers" ENABLE ROW LEVEL SECURITY;

CREATE POLICY "customers_tenant_isolation"
  ON "public"."customers" FOR ALL TO authenticated
  USING (organization_id = (auth.jwt() ->> 'org_id')::text)
  WITH CHECK (organization_id = (auth.jwt() ->> 'org_id')::text);

CREATE POLICY "customers_service_role_bypass"
  ON "public"."customers" FOR ALL TO service_role
  USING (true) WITH CHECK (true);

The problem with Phase 1 becomes obvious as you scale: the same logic is copy-pasted across every table. When you have 20 tables, you have 40 policies with identical logic. If the authorization model changes -- say you need to add admin bypass or cross-organization access for certain roles -- you must update every single policy.

Phase 2: Centralized Functions

The solution is a set of SECURITY DEFINER functions that encapsulate authorization logic in one place. Every policy delegates to these functions.

-- The master authorization function
CREATE OR REPLACE FUNCTION can_access_organization_data(p_organization_id text)
  RETURNS boolean
  LANGUAGE sql
  SECURITY DEFINER
  SET search_path = 'public'
AS $$
  SELECT CASE
    WHEN (auth.jwt() ->> 'role') = 'service_role' THEN true
    ELSE (auth.jwt() ->> 'org_id')::text = p_organization_id
  END;
$$;

SECURITY DEFINER means the function runs with the privileges of the user who created it (typically the superuser), not the caller. This is important because it allows the function to read JWT claims efficiently without triggering nested RLS checks.

Now every policy is a one-liner:

-- Clean, consistent, maintainable
CREATE POLICY "orders_org_access"
  ON "public"."orders" FOR ALL TO authenticated
  USING (can_access_organization_data(organization_id))
  WITH CHECK (can_access_organization_data(organization_id));

CREATE POLICY "products_org_access"
  ON "public"."products" FOR ALL TO authenticated
  USING (can_access_organization_data(organization_id))
  WITH CHECK (can_access_organization_data(organization_id));

CREATE POLICY "customers_org_access"
  ON "public"."customers" FOR ALL TO authenticated
  USING (can_access_organization_data(organization_id))
  WITH CHECK (can_access_organization_data(organization_id));

The Power of Centralization

Now imagine the requirement changes: "Admins should be able to view data across related organizations."

With inline policies, you edit 20 policies across 20 tables. You risk missing one. You risk introducing inconsistencies.

With centralized functions, you edit one function:

DROP FUNCTION can_access_organization_data(text);

CREATE FUNCTION can_access_organization_data(p_organization_id text)
  RETURNS boolean
  LANGUAGE sql
  SECURITY DEFINER
  SET search_path = 'public'
AS $$
  SELECT CASE
    WHEN (auth.jwt() ->> 'role') = 'service_role' THEN true
    WHEN (auth.jwt() ->> 'org_role')::text = 'org:admin' THEN true
    ELSE (auth.jwt() ->> 'org_id')::text = p_organization_id
  END;
$$;

All 20 tables immediately use the new logic. No policy edits. No risk of inconsistency.

Supporting Functions

Beyond the master access function, we maintain a small library of security helpers:

-- Extract current user's organization
CREATE FUNCTION get_current_organization_id()
  RETURNS text
  LANGUAGE sql SECURITY DEFINER
  SET search_path = 'public'
AS $$
  SELECT (auth.jwt() ->> 'org_id')::text;
$$;

-- Check if user is an organization admin
CREATE FUNCTION is_organization_admin()
  RETURNS boolean
  LANGUAGE sql SECURITY DEFINER
  SET search_path = 'public'
AS $$
  SELECT (auth.jwt() ->> 'org_role')::text = 'org:admin';
$$;

-- Check membership in a specific organization
CREATE FUNCTION is_authenticated_user_in_organization(p_org_id text)
  RETURNS boolean
  LANGUAGE sql SECURITY DEFINER
  SET search_path = 'public'
AS $$
  SELECT (auth.jwt() ->> 'org_id')::text = p_org_id;
$$;

These enable fine-grained policies when needed:

-- Admin-only table access
CREATE POLICY "settings_admin_only"
  ON "public"."organization_settings" FOR ALL TO authenticated
  USING (
    can_access_organization_data(organization_id)
    AND is_organization_admin()
  );

-- Read-only for regular members
CREATE POLICY "reports_member_read"
  ON "public"."reports" FOR SELECT TO authenticated
  USING (can_access_organization_data(organization_id));

CREATE POLICY "reports_admin_write"
  ON "public"."reports" FOR INSERT TO authenticated
  WITH CHECK (
    can_access_organization_data(organization_id)
    AND is_organization_admin()
  );

Application Code: Trust RLS

With RLS in place, your application code becomes dramatically simpler. You stop writing permission checks. You trust the database.

// With RLS — clean and correct
"use server";
export async function deleteOrder(id: string) {
  // RLS automatically filters to current user's organization
  // If the order belongs to a different org, this returns null — not an error
  await orderRepository.remove(id);
}

// Without RLS — fragile and error-prone
"use server";
export async function deleteOrder(id: string) {
  const { orgId } = await auth();
  const order = await orderRepository.getById(id);

  // This check is what you forget on the 47th table
  if (order.organization_id !== orgId) {
    throw new Error("Unauthorized");
  }

  await orderRepository.remove(id);
}

The RLS version is shorter, safer, and structurally impossible to get wrong.

Service Role: The Escape Hatch

Webhooks and system operations need to write data across organizations. The service role client bypasses RLS entirely. Use it surgically.

// ONLY in webhook handlers and system operations
import { createServiceRoleClient } from "@/lib/supabase/server";

export async function POST(req: Request) {
  // Verify webhook signature FIRST
  const event = verifySignature(req);

  // Now use service role — RLS bypassed
  const supabase = await createServiceRoleClient();
  await supabase.from("orders").insert({
    organization_id: event.organization_id,
    // ...
  });
}

Never use the service role client in server actions or API routes that handle user requests. Those must go through RLS.

Migration Strategy: Phase 1 to Phase 2

The upgrade from inline to centralized is a zero-downtime operation:

-- Migration: upgrade from inline to centralized

-- Step 1: Create functions (no impact on existing policies)
CREATE FUNCTION can_access_organization_data(p_organization_id text) ...

-- Step 2: Drop old policies
DROP POLICY "orders_tenant_isolation" ON "public"."orders";
DROP POLICY "orders_service_role_bypass" ON "public"."orders";

-- Step 3: Create new policies using functions
CREATE POLICY "orders_org_access" ON "public"."orders" FOR ALL TO authenticated
  USING (can_access_organization_data(organization_id))
  WITH CHECK (can_access_organization_data(organization_id));

-- Repeat steps 2-3 for each table

Test in staging first. The behavior is identical -- only the implementation changes.

Verification

After applying RLS, verify coverage:

-- Check which tables have RLS enabled
SELECT tablename,
       rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename NOT LIKE 'pg_%';

-- Check all policies
SELECT tablename,
       policyname,
       permissive,
       roles,
       cmd
FROM pg_policies
WHERE schemaname = 'public';

Every table with an organization_id column must have RLS enabled and at least one policy.

The Business Case

Elimination of a bug class. Cross-tenant data leakage is not an individual bug. It is a class of bugs that recurs every time a developer writes a query without proper filtering. RLS eliminates the entire class at the database layer. You cannot write a query that leaks data to another tenant. It is structurally impossible.

Audit simplicity. With centralized functions, a security auditor reviews 5 functions instead of 50 policies. Audit time drops from days to hours. Compliance reviews become predictable instead of adversarial.

Maintenance cost. When authorization logic lives in one function instead of scattered across 40 policies, changes take 15 minutes instead of 2 hours. More importantly, they cannot be applied inconsistently.

Developer velocity. Developers stop writing authorization checks in every server action. They write straightforward CRUD logic. The database handles isolation. Code reviews go faster because reviewers do not need to verify tenant filtering on every query.

Performance. Centralized SECURITY DEFINER functions are slightly faster than inline checks because PostgreSQL can optimize the function call path. In practice, the performance difference is negligible, but it is never worse.

The migration from Phase 1 to Phase 2 is a one-time investment of a few hours. The return is permanent: lower maintenance cost, faster audits, safer code, and a security model that scales with your table count without scaling your risk.

Try It

npx modh-playbook init rls-deep-dive