Secure Auth & RLS in EliteSaaS: Supabase + Next.js Guide

Introduction

Building a secure multi-tenant SaaS means getting authentication, session handling, and database access control absolutely right. EliteSaaS uses Supabase (PostgreSQL + Auth) and Next.js App Router to deliver production-ready flows: email sign-in, verification, password recovery, secure sessions with token refresh, middleware-based route protection, and PostgreSQL Row-Level Security (RLS) for tenant isolation.

This guide walks through pragmatic, copy‑pasteable patterns you can use in EliteSaaS: Supabase Auth configuration, secure session handling, middleware protection, concrete RLS policies for profiles, organizations, and organization_members, TanStack Query patterns, Zod validation examples, and edge cases like invitation tokens, service-role webhooks, and audit logging. A migration checklist and tests are included so you can deploy with confidence.

(If you need role-hierarchy patterns or compliance details, see the Team Management & RBAC Patterns and Security Deep Dive posts in the EliteSaaS docs.)

1) Supabase Auth: setup & core flows

Quick checklist to configure Supabase Auth for EliteSaaS:

• Enable Email + Password provider in Supabase Auth.
• Configure Email templates (verification, password reset). In EliteSaaS we integrate Resend for consistent branded templates.
• Configure redirect URLs (email confirmations) to your Next.js app (use env var NEXT_PUBLIC_APP_URL).
• Keep SUPABASE_SERVICE_ROLE_KEY secret — use only on server (webhooks, migrations, admin tasks).

Client & server clients (examples):

// lib/supabaseClient.ts (browser-safe)
import { createClient } from '@supabase/supabase-js';
export const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

// lib/supabaseAdmin.ts (server-only, service role)
import { createClient } from '@supabase/supabase-js';
export const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  { auth: { persistSession: false } }
);

Email verification & password recovery rely on Supabase auth flows. Use signUp and resetPasswordForEmail on the client and handle redirects server-side to display friendly pages.

Security tip: never expose the service role key to the browser or commit it to source control.

2) Secure session handling & automatic token refresh

Supabase's browser client handles refresh tokens for you, but server endpoints and middleware must validate the incoming token. Recommended patterns:

• Use secure, HttpOnly cookies for server SSR/API access when possible (or forward access_token in Authorization header).
• For client-side interactions, use the Supabase client and onAuthStateChange to keep UI in sync.
• For server APIs, accept Authorization: Bearer <access_token> and validate with the admin client.

Server-side token validation example (API route / server handler):

// app/api/protected/route.ts (Next.js App Router handler)
import { supabaseAdmin } from '@/lib/supabaseAdmin';

export async function GET(req: Request) {
  const authHeader = req.headers.get('authorization');
  const token = authHeader?.split(' ')[1];
  if (!token) return new Response('Unauthorized', { status: 401 });

  const { data, error } = await supabaseAdmin.auth.getUser(token);
  if (error || !data.user) return new Response('Unauthorized', { status: 401 });

  // user is valid: continue
  return new Response(JSON.stringify({ user: data.user }));
}

This pattern is safe because the admin client performs token validation using Supabase Auth.

3) Middleware-based route protection (Next.js App Router)

Use Next.js Middleware to redirect unauthenticated users away from /app or /admin. Middleware runs at the edge so keep it minimal and use Supabase's REST auth endpoint to validate tokens.

Example middleware.ts (edge-compatible):

// middleware.ts
import { NextResponse } from 'next/server';
import type { NextRequest } from 'next/server';

export async function middleware(req: NextRequest) {
  const token = req.cookies.get('sb-access-token')?.value || req.headers.get('authorization')?.split(' ')[1];
  if (!token) return NextResponse.redirect(new URL('/login', req.url));

  const resp = await fetch(`${process.env.NEXT_PUBLIC_SUPABASE_URL}/auth/v1/user`, {
    headers: {
      Authorization: `Bearer ${token}`,
      apikey: process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    },
  });

  if (!resp.ok) return NextResponse.redirect(new URL('/login', req.url));
  return NextResponse.next();
}

export const config = { matcher: ['/app/:path*', '/admin/:path*'] };

Notes:

• Keep middleware lightweight and avoid heavy DB access from the edge.
• For permission or subscription checks, prefer server-side checks in page loaders (server components) where you can use the admin client.

4) Row-Level Security (RLS): practical policies

RLS is the single most important control for multi-tenant isolation. Below are compact, battle-tested policies you can adapt.

Prerequisites: enable RLS on each table used by tenants.

Profiles: users can only read/update their own profile

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Profiles: select own" ON public.profiles
  FOR SELECT USING (id = auth.uid());

CREATE POLICY "Profiles: update own" ON public.profiles
  FOR UPDATE USING (id = auth.uid()) WITH CHECK (id = auth.uid());

Organizations: members can view org rows

ALTER TABLE public.organizations ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Orgs: select if member" ON public.organizations
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.organization_members om
      WHERE om.organization_id = organizations.id AND om.user_id = auth.uid()
    )
  );

Organization members: allow read for org members; updates by owners/admins

ALTER TABLE public.organization_members ENABLE ROW LEVEL SECURITY;

CREATE POLICY "OrgMembers: select if related" ON public.organization_members
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.organization_members om
      WHERE om.organization_id = organization_members.organization_id
        AND om.user_id = auth.uid()
    )
  );

CREATE POLICY "OrgMembers: update by admin/owner" ON public.organization_members
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM public.organization_members om
      WHERE om.organization_id = organization_members.organization_id
        AND om.user_id = auth.uid()
        AND om.role IN ('owner','admin')
    )
  );

Important: complex checks (prevent removing last owner) are best implemented as a server-side check or stored procedure run with service role privileges. RLS alone can be bypassed by a service-role update if misused; therefore constrain service-role usage strictly to server endpoints with signature verification.

Example function to prevent removing last owner (run as service role in webhook/endpoint):

CREATE OR REPLACE FUNCTION ensure_not_remove_last_owner(org uuid, removing_user uuid)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE owner_count int;
BEGIN
  SELECT COUNT(*) INTO owner_count
  FROM organization_members
  WHERE organization_id = org AND role = 'owner' AND deleted_at IS NULL;

  IF owner_count <= 1 THEN
    RAISE EXCEPTION 'Cannot remove last owner of organization %', org;
  END IF;
END; $$;

Call this function from your server-side code before performing a destructive role change.

5) Client patterns: TanStack Query + Zod validation

Use TanStack Query for server-state and optimistic updates. Validate inbound API payloads with Zod on server endpoints.

Zod invite acceptance schema example:

import { z } from 'zod';

export const acceptInviteSchema = z.object({
  token: z.string().min(10),
});

TanStack Query patterns (fetch orgs and accept invite mutation):

// hooks/useOrganizations.ts
import { useQuery, useMutation, useQueryClient } from '@tanstack/react-query';
import { supabase } from '@/lib/supabaseClient';

export function useOrganizations() {
  return useQuery(['organizations'], async () => {
    const { data, error } = await supabase.from('organizations').select('*');
    if (error) throw error;
    return data;
  }, { staleTime: 60_000 });
}

export function useAcceptInvite() {
  const qc = useQueryClient();
  return useMutation(async (token: string) => {
    const res = await fetch('/api/teams/accept-invite', {
      method: 'POST', headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ token })
    });
    if (!res.ok) throw new Error('Invite acceptance failed');
    return res.json();
  }, {
    onSuccess: () => qc.invalidateQueries(['organizations'])
  });
}

Use optimistic updates when changing membership lists to keep the UI snappy. Always invalidate queries on server-confirmed results.

6) Edge cases & operational patterns

Invitation tokens

• Generate cryptographically-random tokens server-side (e.g. crypto.randomBytes(32).toString('base64url')).
• Store only a hashed token in DB (SHA256 or better). On acceptance, hash provided token and find the invitation.
• Set expiration (e.g. expires_at) and 7-day default lifetime.

Service role client for webhooks

• Use the service role client only in server endpoints that verify webhook signatures (Stripe Webhook signature or Supabase verifySig).
• Example: handle Stripe invoice.payment_failed -> use supabaseAdmin to update subscription rows and fire internal jobs.

Audit logging patterns

• Keep an audit_logs table and insert audit records from your server-side service functions when changing permissions.
• Optionally add DB triggers for certain tables, but prefer server-side logs so you can include the acting user id and request metadata.

Example audit table:

CREATE TABLE audit_logs (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id uuid,
  action text NOT NULL,
  resource text,
  meta jsonb,
  created_at timestamptz DEFAULT now()
);

Insert audit entries from your role-change endpoint using supabaseAdmin.

7) Migration checklist & tests to validate enforcement

Migration checklist

• Add indexes on organization_members(user_id, organization_id) and on token hashes.
• Enable RLS on profiles, organizations, organization_members, organization_invitations, subscriptions.
• Add SELECT/INSERT/UPDATE policies per table.
• Create stored procedures for last-owner checks and other atomic constraints.
• Create audit_logs table and add server-side inserts from admin endpoints.
• Add tests (see below).

Tests to run (integration)

• Auth flow: sign-up -> verify email -> login -> ensure session present.
• RLS: authenticated user fetches only their own profile; attempt to fetch another profile returns empty/error.
• Organization access: non-member cannot SELECT organization row; member can.
• Invitation: create invitation, accept with token, confirm membership exists.
• Last-owner: attempt to remove the only owner should fail (assert error response).

Simple Jest + Supabase test skeleton:

import { createClient } from '@supabase/supabase-js';
const anon = createClient(process.env.SUPABASE_URL!, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!);

test('non-member cannot read organization', async () => {
  const { data, error } = await anon.from('organizations').select('*').eq('id', 'some-org-id');
  expect(data).toEqual(null);
  expect(error).toBeTruthy();
});

Run your test suite after applying migrations and before flipping any feature flags to enabled in production.

Quick code gist (copy-paste)

RLS + middleware + TanStack Query quick snippet (abbreviated):

-- SQL: profiles policy
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Profiles: self" ON public.profiles FOR SELECT USING (id = auth.uid());

// middleware.ts (edge)
const token = req.cookies.get('sb-access-token')?.value;
const resp = await fetch(`${process.env.NEXT_PUBLIC_SUPABASE_URL}/auth/v1/user`, { headers: { Authorization: `Bearer ${token}`, apikey: process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY } });
if (!resp.ok) redirect('/login');

// TanStack Query: fetch orgs
export function useOrganizations() {
  return useQuery(['orgs'], async () => {
    const { data, error } = await supabase.from('organizations').select('*');
    if (error) throw error; return data;
  });
}

Social media copy (developer-optimized)

• Twitter/X (short):
  "Secure multi-tenant SaaS with Supabase + Next.js? Use RLS, server-validated tokens, service-role webhooks, and audit logs. I wrote a step-by-step guide for EliteSaaS: profiles, orgs, invites, tests. #NextJS #Supabase #SaaS"
• LinkedIn (developer/business):
  "If you're building a multi-tenant SaaS, RLS is non-negotiable. My new EliteSaaS guide shows how to wire Supabase Auth, Next.js middleware, token handling, and RLS policies for profiles, organizations, and membership. Includes code, migration checklist, and tests."
• Reddit (r/webdev / r/IndieHackers):
  "Practical walkthrough: Supabase Auth + RLS for multi-tenant apps. Covers invites, hashed tokens, service-role webhooks, and preventing last-owner deletion. Looking for feedback from folks who've hardened their RLS in production."

Conclusion & next steps

Securing authentication and enforcing Row-Level Security are the backbone of any multi-tenant SaaS. With Supabase + Next.js you get a compact, auditable, and performant stack — but only if you pair client flows, middleware checks, and RLS policies correctly. Use the patterns in this guide to:

• Keep your service role key server-only and used only in signed contexts (webhooks, migrations).
• Hash invitation tokens and enforce expirations.
• Write RLS policies that mirror your app-level permission model.
• Add server-side audits for permissions changes.

Ready to apply these patterns? Start by enabling RLS on profiles and organization_members, add the SQL policies above, then run the migration checklist and integration tests.

If you want deeper role hierarchies or compliance context, consult the Team Management & RBAC Patterns and Security Deep Dive posts in the EliteSaaS docs. If you’re shipping a SaaS product and want the full starter with these patterns pre‑wired, try EliteSaaS and get your core security done the right way.

Call-to-action: implement these policies in your staging workspace this week and run the tests above — and if you need help, open an issue in your EliteSaaS repo or ask in the community for a migration review.