Supabase Auth with Next.js: author sign-in and permissions

05-author-sign-in/
├── package.json                  # only @electric-sql/pglite 0.3.14
├── supabase/
│   ├── seed.sql                  # unchanged
│   └── migrations/
│       ├── 20261110080000_create_posts_table.sql   # unchanged
│       ├── 20261110090000_add_post_tags.sql        # unchanged
│       ├── 20261111080000_enable_rls_on_posts.sql  # unchanged
│       └── 20261112080000_author_permissions.sql   # NEW
├── policy-tests/
│   ├── a-any-signed-in-user.sql                    # NEW, first wrong option
│   └── b-user-metadata.sql                         # NEW, second wrong option
└── scripts/
    ├── fake-supabase.mjs                           # rewritten
    └── try-roles.mjs                               # NEW

// A minimal Supabase-like database with PGlite (real Postgres running inside Node).
// Supabase has the anon and authenticated roles and the auth.jwt(), auth.uid() functions built in; here I create them.
import { readdirSync, readFileSync } from "node:fs";
import { PGlite } from "@electric-sql/pglite";

// On Supabase, every request carries a JWT. The API layer decodes it, puts its contents (the claims) in the
// session setting request.jwt.claims, and only then runs the SQL. auth.jwt() just reads that setting back.
const FAKE_AUTH = `
  create role anon nologin;
  create role authenticated nologin;
  create schema auth;
  grant usage on schema auth to anon, authenticated;
  create function auth.jwt() returns jsonb language sql stable as $$
    select coalesce(nullif(current_setting('request.jwt.claims', true), ''), '{}')::jsonb $$;
  create function auth.uid() returns uuid language sql stable as $$
    select nullif(auth.jwt() ->> 'sub', '')::uuid $$;
`;

// migrationCount: how many migration files to run (in name order), then run seed.sql.
// extraSql: one more SQL file run last, used to try a set of policies that isn't in a migration.
export async function createDb(migrationCount, extraSql) {
  const db = new PGlite();
  await db.exec(FAKE_AUTH);
  for (const file of readdirSync("supabase/migrations").sort().slice(0, migrationCount)) {
    await db.exec(readFileSync(`supabase/migrations/${file}`, "utf8"));
  }
  await db.exec(readFileSync("supabase/seed.sql", "utf8"));
  if (extraSql) await db.exec(readFileSync(extraSql, "utf8"));
  return db;
}

// Run one SQL statement as if it came from a specific person. claims = null is someone not signed in (anon);
// with claims, it's a signed-in person (authenticated) with exactly that JWT content.
// Returns a short string: the result, the number of rows affected, or Postgres's error.
export async function runAs(db, claims, sql) {
  await db.query("select set_config('request.jwt.claims', $1, false)", [claims ? JSON.stringify(claims) : ""]);
  await db.exec(`set role ${claims ? "authenticated" : "anon"}`);
  try {
    const result = await db.query(sql);
    if (result.rows.length > 0) return result.rows.map((r) => Object.values(r).join(" | ")).join("; ");
    return `${result.affectedRows ?? 0} row${result.affectedRows === 1 ? "" : "s"}`;
  } catch (error) {
    return `ERROR ${error.message}`;
  } finally {
    await db.exec("reset role");
  }
}

// Try three sets of policies with four people, each doing three things: count visible posts, edit a title, add a post.
import { createDb, runAs } from "./fake-supabase.mjs";

// The JWT contents (claims) of each person. Supabase creates these when a user signs in.
const PEOPLE = [
  ["a stranger, not signed in", null],
  ["a visitor who just signed up", { sub: "00000000-0000-0000-0000-000000000002", role: "authenticated", app_metadata: {}, user_metadata: {} }],
  ["a visitor who wrote role=author into user_metadata", { sub: "00000000-0000-0000-0000-000000000003", role: "authenticated", app_metadata: {}, user_metadata: { role: "author" } }],
  ["the author (app_metadata.role=author)", { sub: "00000000-0000-0000-0000-000000000001", role: "authenticated", app_metadata: { role: "author" }, user_metadata: {} }],
];

const POLICY_SETS = [
  ["A. Anyone signed in may write", 3, "policy-tests/a-any-signed-in-user.sql"],
  ["B. Check user_metadata", 3, "policy-tests/b-user-metadata.sql"],
  ["C. Check app_metadata (the fourth migration)", 4, undefined],
];

for (const [name, migrationCount, extraSql] of POLICY_SETS) {
  console.log(`=== ${name} ===`);
  for (const [person, claims] of PEOPLE) {
    const db = await createDb(migrationCount, extraSql); // a fresh database per person, so nobody sees anyone else's changes
    const sees = await runAs(db, claims, "select count(*)::int from public.posts");
    const edit = await runAs(db, claims, "update public.posts set title = 'Edited' where slug = 'hello'");
    const add = await runAs(db, claims, "insert into public.posts (slug, title) values ('new-post', 'New post')");
    console.log(`${person}\n    sees ${sees} posts | edit: ${edit} | add: ${add}`);
  }
  console.log();
}

-- Option A (WRONG): anyone signed in may write.
grant insert, update, delete on public.posts to authenticated;

create policy "Signed-in users can read every post"
on public.posts for select
to authenticated
using (true);

create policy "Signed-in users can add posts"
on public.posts for insert
to authenticated
with check (true);

create policy "Signed-in users can edit posts"
on public.posts for update
to authenticated
using (true)
with check (true);

=== A. Anyone signed in may write ===
a stranger, not signed in
    sees 2 posts | edit: ERROR permission denied for table posts | add: ERROR permission denied for table posts
a visitor who just signed up
    sees 4 posts | edit: 1 row | add: 1 row
a visitor who wrote role=author into user_metadata
    sees 4 posts | edit: 1 row | add: 1 row
the author (app_metadata.role=author)
    sees 4 posts | edit: 1 row | add: 1 row

-- Option B (WRONG): check the role in user_metadata, which users can edit themselves.
grant insert, update, delete on public.posts to authenticated;

create policy "Authors can read every post"
on public.posts for select
to authenticated
using ((select auth.jwt() -> 'user_metadata' ->> 'role') = 'author');

create policy "Authors can add posts"
on public.posts for insert
to authenticated
with check ((select auth.jwt() -> 'user_metadata' ->> 'role') = 'author');

create policy "Authors can edit posts"
on public.posts for update
to authenticated
using ((select auth.jwt() -> 'user_metadata' ->> 'role') = 'author')
with check ((select auth.jwt() -> 'user_metadata' ->> 'role') = 'author');

=== B. Check user_metadata ===
a stranger, not signed in
    sees 2 posts | edit: ERROR permission denied for table posts | add: ERROR permission denied for table posts
a visitor who just signed up
    sees 2 posts | edit: 0 rows | add: ERROR new row violates row-level security policy for table "posts"
a visitor who wrote role=author into user_metadata
    sees 4 posts | edit: 1 row | add: 1 row
the author (app_metadata.role=author)
    sees 2 posts | edit: 0 rows | add: ERROR new row violates row-level security policy for table "posts"

-- The author (a signed-in user with app_metadata.role = 'author') may read every post, and add, edit and delete posts.
-- app_metadata can only be changed from the admin side (SQL, Dashboard); users can't change it themselves.

-- Layer 1: the authenticated role may write to the table.
grant insert, update, delete on public.posts to authenticated;

-- Layer 2: but only the rows a policy allows, and the policies only accept the author.
create policy "Authors can read every post"
on public.posts for select
to authenticated
using ((select auth.jwt() -> 'app_metadata' ->> 'role') = 'author');

create policy "Authors can add posts"
on public.posts for insert
to authenticated
with check ((select auth.jwt() -> 'app_metadata' ->> 'role') = 'author');

create policy "Authors can edit posts"
on public.posts for update
to authenticated
using ((select auth.jwt() -> 'app_metadata' ->> 'role') = 'author')
with check ((select auth.jwt() -> 'app_metadata' ->> 'role') = 'author');

create policy "Authors can delete posts"
on public.posts for delete
to authenticated
using ((select auth.jwt() -> 'app_metadata' ->> 'role') = 'author');

=== C. Check app_metadata (the fourth migration) ===
a stranger, not signed in
    sees 2 posts | edit: ERROR permission denied for table posts | add: ERROR permission denied for table posts
a visitor who just signed up
    sees 2 posts | edit: 0 rows | add: ERROR new row violates row-level security policy for table "posts"
a visitor who wrote role=author into user_metadata
    sees 2 posts | edit: 0 rows | add: ERROR new row violates row-level security policy for table "posts"
the author (app_metadata.role=author)
    sees 4 posts | edit: 1 row | add: 1 row

update auth.users
set raw_app_meta_data = raw_app_meta_data || '{"role":"author"}'
where email = 'author-email@example.com';

type Claims = { sub?: string; email?: string; app_metadata?: { [key: string]: unknown } | null } | null | undefined;

/**
 * Authorship comes only from app_metadata, which users cannot edit. user_metadata is ignored on
 * purpose: it is writable by the user and would let anyone promote themselves.
 */
export function isAuthorClaims(claims: Claims): claims is { sub: string; email?: string; app_metadata: { role: "author" } } {
  return typeof claims?.sub === "string" && claims.app_metadata?.role === "author";
}

/** Verifies the session JWT (getClaims) and returns the author, or null for anyone else. */
export async function getAuthor() {
  if (!getSupabaseEnv()) return null;
  const supabase = await createServerSupabase();
  const { data, error } = await supabase.auth.getClaims();
  const claims = error ? null : data?.claims;
  if (!isAuthorClaims(claims)) return null;
  return { supabase, userId: claims.sub, email: claims.email ?? "" };
}

/** For admin pages and Server Actions: every entry point checks again, the proxy is not enough. */
export async function requireAuthor() {
  const author = await getAuthor();
  if (!author) redirect(LOGIN_PATH);
  return author;
}

const { data } = await supabase.auth.getClaims();
const isLoginPage = request.nextUrl.pathname === LOGIN_PATH;

if (!isLoginPage && !isAuthorClaims(data?.claims)) {
  const redirect = NextResponse.redirect(new URL(LOGIN_PATH, request.url));
  // Keep any refreshed/cleared auth cookies on the redirect response.
  for (const cookie of response.cookies.getAll()) redirect.cookies.set(cookie);
  redirect.headers.set("cache-control", "private, no-store");
  return redirect;
}

const supabase = await createServerSupabase();
const { error } = await supabase.auth.signInWithPassword({ email, password });
// One generic message for every failure so the form does not reveal which accounts exist.
if (error) return { error: "Wrong email or password." };

const { data } = await supabase.auth.getClaims();
if (!isAuthorClaims(data?.claims)) {
  await supabase.auth.signOut();
  return { error: "This account doesn't have author access yet." };
}

redirect("/admin");