Build a Supabase RBAC API with Express

Introduction

30 Second Summary

Blog readers expect posts to load without a sign-in screen. The publishing controls behind that feed still need to stop the wrong person from changing someone else's work.

In this project, you will build a public-read REST API with Express. Supabase Auth will verify each caller before PostgreSQL Row Level Security enforces their role plus row ownership.

What You'll Build

You can demo a public feed where author and admin actions succeed according to identity while forbidden requests leave protected posts unchanged.

By the end of this project, you'll have:

• A public posts feed that anyone can request without signing in. The response arrives as JSON.
• An author workflow where signed-in authors can publish posts. Each author can update or delete only their own posts.
• An admin override that can update or delete any post. Denied requests return visible authorization failures while the protected data stays unchanged.
• Secret Mission: add private drafts that only become visible to the public after an author publishes them.

Are there any prerequisites?

You need basic JavaScript knowledge of objects plus functions. No Express or SQL experience is required.

Before We Start

Before the hands-on work begins, lock in the access rules your blog API must enforce. This gives you a clear security goal for every layer you build.

Set Up the Project

Your API needs a hosted Supabase project before you can test any authorization behavior. The local Express workspace also needs pinned dependencies so every later request uses the same runtime.

In this step, you'll create the hosted project. You'll then build a local workspace for Node.js 22+ with Express 5.3.0 plus @supabase/supabase-js 2.117.3.

In this step, get ready to:

• Create a Supabase Free project.
• Prepare a local workspace with environment configuration.
• Install the pinned project dependencies.

Create the Supabase project

The Project URL points your local code to the correct hosted project. The publishable key identifies requests sent through the Supabase Data API.

The Free plan costs $0 per month, so creating this project does not start a paid resource. The creation form also asks for a database password that stays outside this project.

• Go to the Supabase Dashboard in your browser.
• Sign in with your Supabase account.
• Select the organization that should contain this project.
• Start the new-project creation flow from the Dashboard.
• Enter supabase-rbac-blog-api in the project name field.
• Choose the Free plan.
• Generate a strong database password.
• Store the database password in your password manager.

The remaining settings determine where Supabase hosts the project. A nearby region keeps future API requests responsive.

• Choose the available region closest to you.
• Submit the project creation form.

Provisioning can take a few minutes while Supabase prepares the database. The Dashboard status shows that work is still in progress.

• Wait until the Dashboard shows that the project is ready.
• Open the Connect dialog in the project Dashboard.
• Record the Project URL from Connect here: https://your-project.supabase.co.
• Copy the publishable key from Connect.
• Keep the Connect dialog open for the workspace configuration.

Why use a publishable key?

A publishable key is intended to identify client requests to your project. Requests made with it remain subject to Row Level Security.

A server administrative key would bypass that database boundary. The publishable key preserves the defense-in-depth design you'll build in later steps.

Build the local workspace

A workspace keeps every project file under one named folder. Visual Studio Code shows those files in its Explorer while its integrated terminal runs commands from the same location.

• Press Cmd+Space on macOS or the Windows key on Windows to open system search.
• Type Visual Studio Code into the search field.
• Press Enter to open Visual Studio Code.

Visual Studio Code is ready to turn a folder into your workspace. The folder picker lets you place that workspace somewhere easy to find.

• Click File in the top menu bar.
• Select Open Folder....
• Select Desktop in the folder picker.
• Select New Folder.
• Enter supabase-rbac-blog-api as the folder name.
• Select the new supabase-rbac-blog-api folder.
• Click Select Folder on Windows or Open on macOS.
• Confirm that you trust the empty folder if the Workspace Trust prompt appears.

You should see supabase-rbac-blog-api at the top of the Explorer sidebar. Your local workspace now has a clear home.

The package manifest records the runtime requirement plus the exact dependencies. It also defines the command that starts the API later.

• Select New File... at the top of the Explorer sidebar.
• Enter package.json as the filename.
• Press Enter to create the file.
• Fill package.json with the exact project manifest below:

{
  "name": "supabase-rbac-blog-api",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "engines": {
    "node": ">=22.0.0"
  },
  "scripts": {
    "start": "node --env-file=.env app.js"
  },
  "dependencies": {
    "@supabase/supabase-js": "2.117.3",
    "express": "5.3.0"
  }
}

What does this manifest control?

• The engines entry records Node.js >=22.0.0 as the runtime requirement.
• The start script loads project-specific values from .env before running app.js.
• The dependencies object pins both libraries to exact versions for reproducible installs.

• Save package.json by pressing Cmd+S on macOS or Ctrl+S on Windows.
• Confirm that package.json appears under supabase-rbac-blog-api in Explorer.

Seeing red lines in package.json?

• Compare every double quote against the example because JSON requires quoted keys.
• Check each comma because the final property in an object has no trailing comma.
• Still stuck? Help me find the JSON syntax problem in my package.json file.

The environment template documents the two values required by the API. Its placeholders make the expected format visible without exposing your real project configuration.

• Select New File... in the Explorer sidebar.
• Enter .env.example as the filename.
• Press Enter to create the file.
• Fill .env.example with the environment placeholders below:

SUPABASE_URL=https://your-project.supabase.co
SUPABASE_PUBLISHABLE_KEY=your-publishable-key-here

What does this template document?

• The SUPABASE_URL placeholder shows the expected hosted project URL format.
• The SUPABASE_PUBLISHABLE_KEY placeholder shows where a developer supplies their own project key.
• This template remains safe to share because it contains no live project values.

• Save .env.example.
• Confirm that .env.example appears in Explorer.

The live .env file uses the same variable names with your real values. Your local start command reads this file into process.env.

• Select New File... in the Explorer sidebar.
• Enter .env as the filename.
• Press Enter to create the file.
• Type SUPABASE_URL= on the first line.
• Paste https://your-project.supabase.co after the equals sign.

You can now see your own Project URL on the first line. The second line connects the API client to the same hosted project.

• Type SUPABASE_PUBLISHABLE_KEY= on the second line.
• Switch back to the open Connect dialog in the Supabase Dashboard.
• Copy the publishable key again.
• Return to .env in Visual Studio Code.
• Paste the publishable key after the second equals sign.
• Save .env.

The ignore file keeps generated dependencies out of version control. It also prevents Git from including your live environment configuration.

• Select New File... in the Explorer sidebar.
• Enter .gitignore as the filename.
• Press Enter to create the file.
• Fill .gitignore with these ignore rules:

node_modules/
.env

What do these ignore rules protect?

• The node_modules/ rule excludes packages that npm install can recreate.
• The .env rule keeps your project-specific connection values out of Git.

• Save .gitignore.
• Confirm that .gitignore appears in Explorer.
• Use the New Folder control in Explorer to create supabase inside supabase-rbac-blog-api.
• Confirm that the empty supabase folder appears in Explorer.

The supabase folder is ready for the database files added in later steps. Your current workspace contains configuration only.

✔️ Awesome, I've got everything!

• Save every open file in Visual Studio Code.
• Confirm that Explorer shows package.json, .env, .env.example, .gitignore, and the supabase folder.

ⓧ I'd like to double check the full code

Compare your shareable files against these complete versions. Keep the real values in .env out of this comparison.

{
  "name": "supabase-rbac-blog-api",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "engines": {
    "node": ">=22.0.0"
  },
  "scripts": {
    "start": "node --env-file=.env app.js"
  },
  "dependencies": {
    "@supabase/supabase-js": "2.117.3",
    "express": "5.3.0"
  }
}

What to compare in package.json

Check the project name, runtime requirement, start script, and both exact dependency versions.

Next, compare the safe environment template.

SUPABASE_URL=https://your-project.supabase.co
SUPABASE_PUBLISHABLE_KEY=your-publishable-key-here

What to compare in .env.example

Check that both variable names match exactly. Keep the placeholder values unchanged in this template.

Finally, compare the two ignore rules.

node_modules/
.env

What to compare in .gitignore

Check that the generated dependency folder and live environment file each have their own line.

Files in the wrong place?

• Confirm that supabase-rbac-blog-api appears as the top folder in Explorer.
• Drag any misplaced file onto that top folder in Explorer.
• Still stuck? Help me organize my Supabase RBAC blog workspace in Visual Studio Code.

Install the pinned dependencies

The npm installer reads package.json to build the local dependency tree. It records the resolved tree in package-lock.json so future installs can reproduce it.

• Click Terminal in the Visual Studio Code menu bar.
• Select New Terminal.
• Use the terminal dropdown to select PowerShell if another shell opens.
• Confirm that the terminal path ends with supabase-rbac-blog-api.
• Install the exact dependencies declared in package.json by running:

npm install

What does this command create?

The command downloads the pinned dependencies into node_modules. It also creates package-lock.json with the resolved dependency tree.

Expect this first install to take about a minute while packages download. The terminal prints a completion summary when the install finishes.

• Wait for the PowerShell prompt to return.
• Confirm that Explorer now shows node_modules.
• Confirm that Explorer now shows package-lock.json.
• Check that the installation completed without an engine compatibility failure.

Before you verify the dependency tree, which two exact package versions do you expect the terminal to report?

• Check the installed dependency versions by running this command:

npm list express @supabase/supabase-js

What should the dependency check show?

• The dependency tree lists @supabase/supabase-js@2.117.3.
• The dependency tree lists express@5.3.0.

These two lines prove that the installed packages match the versions declared in package.json.

Dependency installation failed?

• Confirm that the PowerShell prompt points to the supabase-rbac-blog-api workspace.
• Compare package.json against the full-code tab for a missing quote or comma.
• Confirm that your installed Node.js version is 22 or higher.

Still stuck? Help me troubleshoot npm install in my Supabase RBAC blog workspace. You can also share the non-secret error text with the NextWork community.

Your dependency check now proves that the local workspace is reproducible. Keep .env closed in the screenshot so your project values stay out of view.

• Close the .env editor tab before taking your screenshot.
• Expand supabase-rbac-blog-api in Explorer so the generated items are visible.
• Keep the PowerShell terminal visible with the dependency check output.

That's the foundation locked in: your Supabase project now has a pinned local workspace. Next, you'll create the protected database behind its public posts feed.

Open the Blog for Public Reading

Your local workspace now has pinned packages. Your Supabase project credentials are stored in the ignored .env file.

A useful blog needs a visible read-only feed before you test its write restrictions. In this step, you will connect Express to a PostgreSQL posts table protected by Row Level Security.

In this step, get ready to:

• Apply the database schema with grants and operation-specific policies.
• Build an Express server with a public posts route.
• Request the public feed without an access token.

Apply the database security schema

PostgreSQL grants determine which operations a database role can attempt. Row Level Security determines which rows an allowed operation can reach.

Why use two database boundaries?

Grants stop unsupported operations before a policy runs. Row Level Security applies identity and ownership rules to permitted operations.

Together they create defense in depth. The database remains protected when an application route omits an authorization check.

• In the Explorer sidebar in Visual Studio Code, select the existing supabase folder.
• Create schema.sql inside the supabase folder.
• Add the private schema and role types to supabase/schema.sql by pasting this code:

create schema if not exists private;
revoke all on schema private from public;

create type public.app_role as enum ('author', 'admin');
create type public.app_permission as enum (
  'posts.create',
  'posts.update.own',
  'posts.delete.own',
  'posts.update.any',
  'posts.delete.any'
);

What does this SQL establish?

• The private schema holds authorization logic outside the exposed API schema.
• The app_role type restricts application roles to author or admin.
• The app_permission type defines the write capabilities that later policies check.

• Save supabase/schema.sql.
• Copy the SQL block you just added.
• Switch back to the Supabase Dashboard from the previous step.
• Select SQL Editor in the Dashboard sidebar.
• Create a blank query in the SQL Editor.
• Paste the copied SQL into the query.
• Run the query from the SQL Editor toolbar.

You should see a successful result. The database now recognizes the application roles and permissions.

• Return to supabase/schema.sql in VS Code.
• Append the table definitions below the role types:

create table public.user_roles (
  user_id uuid primary key references auth.users on delete cascade,
  role public.app_role not null
);

create table public.role_permissions (
  role public.app_role not null,
  permission public.app_permission not null,
  primary key (role, permission)
);

create table public.posts (
  id uuid primary key default gen_random_uuid(),
  title text not null check (char_length(title) between 1 and 120),
  content text not null check (char_length(content) between 1 and 5000),
  author_id uuid not null references auth.users on delete cascade,
  created_at timestamptz not null default now()
);

create index posts_author_id_idx on public.posts using btree (author_id);

What do these tables store?

• The user_roles table assigns one application role to each user.
• The role_permissions table maps each role to its permitted actions.
• The posts table stores each post with its owner's user ID.
• The posts_author_id_idx index supports ownership checks that filter on author_id.

• Save supabase/schema.sql.
• Copy the table block you just added.
• Return to the SQL Editor query.
• Replace the current query with the copied table block.
• Run the query from the SQL Editor toolbar.

You should see another successful result. The three tables now exist in your hosted database.

• Return to supabase/schema.sql in VS Code.
• Append the permission seed and table access rules below the index:

insert into public.role_permissions (role, permission)
values
  ('author', 'posts.create'),
  ('author', 'posts.update.own'),
  ('author', 'posts.delete.own'),
  ('admin', 'posts.create'),
  ('admin', 'posts.update.any'),
  ('admin', 'posts.delete.any')
on conflict (role, permission) do nothing;

alter table public.user_roles enable row level security;
alter table public.role_permissions enable row level security;
alter table public.posts enable row level security;

revoke all on table public.user_roles from anon, authenticated;
revoke all on table public.role_permissions from anon, authenticated;
revoke all on table public.posts from anon, authenticated;

grant select on table public.posts to anon, authenticated;
grant insert, update, delete on table public.posts to authenticated;

How does this restrict table access?

• The seed rows give authors permissions scoped to their own posts.
• The seed rows give admins permissions that apply to any post.
• The anon role receives read access to posts.
• The authenticated role receives access to write operations.
• Row Level Security remains responsible for deciding which writes are allowed.

• Save supabase/schema.sql.
• Copy the permission block you just added.
• Return to the SQL Editor query.
• Replace the current query with the copied permission block.
• Run the query from the SQL Editor toolbar.

You should see a successful result. Table access now starts from least-privilege grants.

• Return to supabase/schema.sql in VS Code.
• Append the custom access-token hook below the table grants:

create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
stable
set search_path = ''
as $$
declare
  claims jsonb;
  user_role public.app_role;
begin
  select role
  into user_role
  from public.user_roles
  where user_id = (event->>'user_id')::uuid;

  claims := event->'claims';

  if user_role is not null then
    claims := jsonb_set(claims, '{user_role}', to_jsonb(user_role));
  else
    claims := jsonb_set(claims, '{user_role}', 'null');
  end if;

  return jsonb_set(event, '{claims}', claims);
end;
$$;

What does the token hook do?

The hook finds the role assigned to the signing-in user. It adds that role to the verified user_role claim.

Later policies read this claim from the caller's JWT. The database remains the source of each role assignment.

• Save supabase/schema.sql.
• Copy the hook block you just added.
• Return to the SQL Editor query.
• Replace the current query with the copied hook block.
• Run the query from the SQL Editor toolbar.

You should see the hook function created successfully. You will enable it after creating the test users in a later step.

• Return to supabase/schema.sql in VS Code.
• Append the hook permissions below the hook function:

grant usage on schema public to supabase_auth_admin;
grant execute on function public.custom_access_token_hook(jsonb) to supabase_auth_admin;
revoke execute on function public.custom_access_token_hook(jsonb) from authenticated, anon, public;
grant all on table public.user_roles to supabase_auth_admin;

create policy "Allow auth admin to read user roles"
on public.user_roles
for select
to supabase_auth_admin
using (true);

Who can use the hook?

Supabase Auth can execute the hook during token creation. Application roles cannot call it directly.

The policy lets the Auth service read role assignments. The application still has no direct access to user_roles.

• Save supabase/schema.sql.
• Copy the hook-permission block you just added.
• Return to the SQL Editor query.
• Replace the current query with the copied hook-permission block.
• Run the query from the SQL Editor toolbar.

You should see another successful result. Hook access is now limited to the Supabase Auth service.

• Return to supabase/schema.sql in VS Code.
• Append the authorization helper below the hook policy:

create or replace function private.authorize(
  requested_permission public.app_permission
)
returns boolean
language plpgsql
stable
security definer
set search_path = ''
as $$
declare
  permission_count integer;
  user_role public.app_role;
begin
  select (auth.jwt()->>'user_role')::public.app_role into user_role;

  select count(*)
  into permission_count
  from public.role_permissions
  where role = user_role
    and permission = requested_permission;

  return permission_count > 0;
end;
$$;

revoke all on function private.authorize(public.app_permission) from public;
grant usage on schema private to authenticated;
grant execute on function private.authorize(public.app_permission) to authenticated;

How are role permissions checked?

The private.authorize helper reads the verified user_role claim. It checks whether that role has the requested permission.

The helper runs as a security-definer function in the unexposed private schema. Only authenticated database requests can execute it.

• Save supabase/schema.sql.
• Copy the authorization-helper block you just added.
• Return to the SQL Editor query.
• Replace the current query with the copied authorization-helper block.
• Run the query from the SQL Editor toolbar.

You should see the helper created successfully. The database can now turn a role claim into a permission decision.

• Return to supabase/schema.sql in VS Code.
• Append the public-read and authenticated-create policies below the helper grants:

create policy "Anyone can read posts"
on public.posts
for select
to anon, authenticated
using (true);

create policy "Authorized users can create their own posts"
on public.posts
for insert
to authenticated
with check (
  (select auth.uid()) = author_id
  and (select private.authorize('posts.create'))
);

What do the first policies allow?

The read policy exposes every post to anonymous callers. This creates the public feed.

The insert policy requires the caller's user ID to match author_id. It also requires the posts.create permission.

• Save supabase/schema.sql.
• Copy the first policy block you just added.
• Return to the SQL Editor query.
• Replace the current query with the copied policy block.
• Run the query from the SQL Editor toolbar.

You should see both policies created successfully. Public reads can reach the table while inserts remain identity-bound.

• Return to supabase/schema.sql in VS Code.
• Append the update policy below the insert policy:

create policy "Owners or admins can update posts"
on public.posts
for update
to authenticated
using (
  (
    (select auth.uid()) = author_id
    and (select private.authorize('posts.update.own'))
  )
  or (select private.authorize('posts.update.any'))
)
with check (
  (
    (select auth.uid()) = author_id
    and (select private.authorize('posts.update.own'))
  )
  or (select private.authorize('posts.update.any'))
);

How are updates constrained?

The using expression controls which existing rows the caller can target. Owners need the own-post permission.

The with check expression validates the changed row. Admins pass both checks through the any-post permission.

• Save supabase/schema.sql.
• Copy the update-policy block you just added.
• Return to the SQL Editor query.
• Replace the current query with the copied update-policy block.
• Run the query from the SQL Editor toolbar.

You should see the update policy created successfully. Ownership and admin access are now enforced for updates.

• Return to supabase/schema.sql in VS Code.
• Append the delete policy below the update policy:

create policy "Owners or admins can delete posts"
on public.posts
for delete
to authenticated
using (
  (
    (select auth.uid()) = author_id
    and (select private.authorize('posts.delete.own'))
  )
  or (select private.authorize('posts.delete.any'))
);

How are deletes constrained?

Owners can target their rows when their role has the own-post permission. Admins can target any row through the any-post permission.

A caller who satisfies neither branch receives no matching row. The protected post remains unchanged.

• Save supabase/schema.sql.
• Copy the delete-policy block you just added.
• Return to the SQL Editor query.
• Replace the current query with the copied delete-policy block.
• Run the query from the SQL Editor toolbar.

You should see the final policy created successfully. Your database now permits public reads while protecting every write.

Schema query failing?

• Confirm that you ran the SQL blocks in the order shown. Later functions depend on the earlier types and tables.
• Check that each block was appended once to supabase/schema.sql.
• Still stuck? Help me diagnose my Supabase schema error.

✔️ Awesome, I've got everything!

Your hosted database has the complete schema. Double check that supabase/schema.sql is saved in VS Code.

ⓧ I'd like to double check the full code

Compare your saved supabase/schema.sql file with this complete reference:

create schema if not exists private;
revoke all on schema private from public;

create type public.app_role as enum ('author', 'admin');
create type public.app_permission as enum (
  'posts.create',
  'posts.update.own',
  'posts.delete.own',
  'posts.update.any',
  'posts.delete.any'
);

create table public.user_roles (
  user_id uuid primary key references auth.users on delete cascade,
  role public.app_role not null
);

create table public.role_permissions (
  role public.app_role not null,
  permission public.app_permission not null,
  primary key (role, permission)
);

create table public.posts (
  id uuid primary key default gen_random_uuid(),
  title text not null check (char_length(title) between 1 and 120),
  content text not null check (char_length(content) between 1 and 5000),
  author_id uuid not null references auth.users on delete cascade,
  created_at timestamptz not null default now()
);

create index posts_author_id_idx on public.posts using btree (author_id);

insert into public.role_permissions (role, permission)
values
  ('author', 'posts.create'),
  ('author', 'posts.update.own'),
  ('author', 'posts.delete.own'),
  ('admin', 'posts.create'),
  ('admin', 'posts.update.any'),
  ('admin', 'posts.delete.any')
on conflict (role, permission) do nothing;

alter table public.user_roles enable row level security;
alter table public.role_permissions enable row level security;
alter table public.posts enable row level security;

revoke all on table public.user_roles from anon, authenticated;
revoke all on table public.role_permissions from anon, authenticated;
revoke all on table public.posts from anon, authenticated;

grant select on table public.posts to anon, authenticated;
grant insert, update, delete on table public.posts to authenticated;

create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
stable
set search_path = ''
as $$
declare
  claims jsonb;
  user_role public.app_role;
begin
  select role
  into user_role
  from public.user_roles
  where user_id = (event->>'user_id')::uuid;

  claims := event->'claims';

  if user_role is not null then
    claims := jsonb_set(claims, '{user_role}', to_jsonb(user_role));
  else
    claims := jsonb_set(claims, '{user_role}', 'null');
  end if;

  return jsonb_set(event, '{claims}', claims);
end;
$$;

grant usage on schema public to supabase_auth_admin;
grant execute on function public.custom_access_token_hook(jsonb) to supabase_auth_admin;
revoke execute on function public.custom_access_token_hook(jsonb) from authenticated, anon, public;
grant all on table public.user_roles to supabase_auth_admin;

create policy "Allow auth admin to read user roles"
on public.user_roles
for select
to supabase_auth_admin
using (true);

create or replace function private.authorize(
  requested_permission public.app_permission
)
returns boolean
language plpgsql
stable
security definer
set search_path = ''
as $$
declare
  permission_count integer;
  user_role public.app_role;
begin
  select (auth.jwt()->>'user_role')::public.app_role into user_role;

  select count(*)
  into permission_count
  from public.role_permissions
  where role = user_role
    and permission = requested_permission;

  return permission_count > 0;
end;
$$;

revoke all on function private.authorize(public.app_permission) from public;
grant usage on schema private to authenticated;
grant execute on function private.authorize(public.app_permission) to authenticated;

create policy "Anyone can read posts"
on public.posts
for select
to anon, authenticated
using (true);

create policy "Authorized users can create their own posts"
on public.posts
for insert
to authenticated
with check (
  (select auth.uid()) = author_id
  and (select private.authorize('posts.create'))
);

create policy "Owners or admins can update posts"
on public.posts
for update
to authenticated
using (
  (
    (select auth.uid()) = author_id
    and (select private.authorize('posts.update.own'))
  )
  or (select private.authorize('posts.update.any'))
)
with check (
  (
    (select auth.uid()) = author_id
    and (select private.authorize('posts.update.own'))
  )
  or (select private.authorize('posts.update.any'))
);

create policy "Owners or admins can delete posts"
on public.posts
for delete
to authenticated
using (
  (
    (select auth.uid()) = author_id
    and (select private.authorize('posts.delete.own'))
  )
  or (select private.authorize('posts.delete.any'))
);

What should match?

The statements should appear in this exact order. Your SQL Editor should have completed each matching block successfully.

Build the public Express API

The database can now answer public post queries. Your API needs a public Supabase client that uses the Project URL and publishable key from .env.

• In the VS Code Explorer sidebar, select the top level of the supabase-rbac-blog-api workspace.
• Create app.js beside package.json.
• Add the server setup and health route to app.js by pasting this code:

import express from 'express'
import { createClient } from '@supabase/supabase-js'

const { SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY } = process.env

if (!SUPABASE_URL || !SUPABASE_PUBLISHABLE_KEY) {
  throw new Error('SUPABASE_URL and SUPABASE_PUBLISHABLE_KEY are required')
}

const app = express()
app.use(express.json())

const publicSupabase = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY)

app.get('/health', (req, res) => {
  res.json({ status: 'ok' })
})

app.listen(3000, () => {
  console.log('Server is running on http://localhost:3000')
})

What does this server setup do?

• The imports load Express and the Supabase client library.
• The environment check stops startup when either required value is missing.
• The express.json() middleware prepares the API to receive JSON request bodies.
• The publicSupabase client uses the publishable key for public database requests.
• The /health route provides a small local server check.

• Save app.js.
• Start the API in the existing VS Code PowerShell terminal by running this command:

npm start

What does this command start?

The start script loads the ignored .env file into Node.js. It then runs app.js.

You should see Server is running on http://localhost:3000. The terminal remains occupied while the API listens for requests.

Server not starting?

• Confirm that app.js is saved beside package.json.
• Confirm that the ignored .env file contains both required values.
• Still stuck? Help me troubleshoot my Express startup error.

• Stop the running server by pressing Ctrl+C in its PowerShell terminal.
• In app.js, find this line:

app.listen(3000, () => {

Where does the route belong?

Express registers routes before the server starts listening. The public posts route belongs immediately above this line.

• Insert the public posts route immediately above the line you found:

app.get('/posts', async (req, res) => {
  const { data, error } = await publicSupabase
    .from('posts')
    .select('id, title, content, author_id, created_at')
    .order('created_at', { ascending: false })

  if (error) {
    return res.status(500).json({ error: error.message, code: error.code })
  }

  res.json(data)
})

How does the posts route work?

• The GET /posts handler queries the posts table through the public client.
• The selected fields form the public response.
• The order modifier places the newest rows first.
• A database failure returns its message and code as JSON.
• A successful query returns the post collection as JSON.

• Save app.js.
• Restart the API in the same PowerShell terminal by running this command:

npm start

What changed after the restart?

Node.js loads the saved version of app.js. The running server now handles both /health and /posts.

You should see Server is running on http://localhost:3000 again. Keep this terminal running for the public request.

✔️ Awesome, I've got everything!

Your server is running with the public posts route. Double check that app.js is saved.

ⓧ I'd like to double check the full code

Compare your saved app.js file with this complete reference:

import express from 'express'
import { createClient } from '@supabase/supabase-js'

const { SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY } = process.env

if (!SUPABASE_URL || !SUPABASE_PUBLISHABLE_KEY) {
  throw new Error('SUPABASE_URL and SUPABASE_PUBLISHABLE_KEY are required')
}

const app = express()
app.use(express.json())

const publicSupabase = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY)

app.get('/health', (req, res) => {
  res.json({ status: 'ok' })
})

app.get('/posts', async (req, res) => {
  const { data, error } = await publicSupabase
    .from('posts')
    .select('id, title, content, author_id, created_at')
    .order('created_at', { ascending: false })

  if (error) {
    return res.status(500).json({ error: error.message, code: error.code })
  }

  res.json(data)
})

app.listen(3000, () => {
  console.log('Server is running on http://localhost:3000')
})

What should match?

The environment check should appear before the Express setup. The public posts route should appear between the health route and app.listen().

Verify the token-free public feed

The running server occupies your first terminal. A second PowerShell terminal lets you send a request while the API keeps listening.

• Create a second PowerShell terminal from the VS Code terminal panel.

Before you send the request, what do you expect the feed to contain when the table has no posts?

• Request the public posts feed without a token by running this command:

Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Get

What does this request prove?

The command sends a GET request without an authorization header. Express queries Supabase through the public client.

The public read grant and select policy allow the request to complete. No access token is required for the feed.

PowerShell returns no post objects because the API returned an empty JSON collection. That empty result proves the token-free public read path works.

Public request failing?

• Confirm that the first PowerShell terminal still shows the running server.
• Confirm that the SQL Editor created the posts table and its public read policy.
• Still stuck? Help me troubleshoot my public posts request.

Your public feed is live and protected by database rules. Next, you will deliberately attempt an anonymous write to see the first authorization boundary reject it.

Watch an Anonymous Write Fail

Your public feed now proves that the Express API can read posts from Supabase without a token. The next question is whether an anonymous caller can reach a write route.

This step deliberately leaves a temporary route open at the application layer. You will send one write request so PostgreSQL grants can reveal which authorization boundary acts first.

In this step, get ready to:

• Add a temporary POST /posts route without authentication middleware.
• Send an anonymous JSON request through the API.
• Confirm the rejected write left the public feed empty.

Add the temporary write route

The publicSupabase client sends requests with the publishable key. A request without a caller token reaches the database with an anonymous identity.

• Select app.js in the VS Code Explorer sidebar.
• Locate the closing }) for the app.get('/posts', ...) route.
• Add the temporary write route below the public read route by pasting this code:

app.post('/posts', async (req, res) => {
  const { title, content } = req.body

  const { data, error } = await publicSupabase
    .from('posts')
    .insert({ title, content, author_id: null })
    .select()
    .single()

  if (error) {
    return res.status(403).json({ error: error.message, code: error.code })
  }

  res.status(201).json(data)
})

What does this route do?

• The route reads the post fields from req.body.
• The insert() call attempts to create a row with a null anonymous identity.
• The select().single() chain requests the inserted row if the database accepts the write.
• The error response preserves the database error code when the attempt fails.

• Save app.js.
• Return to the running PowerShell terminal from the previous step.
• Stop the API by pressing Ctrl+C.
• Restart the API with the temporary route by running:

npm start

What does this command do?

The npm start command runs the start script from package.json. Restarting loads the new route into the Express process.

You should see Server is running on http://localhost:3000 in the terminal. The API is now ready to receive the anonymous write attempt.

Server does not restart?

• Check that the previous process stopped after you pressed Ctrl+C.
• Compare the braces around the new app.post() route with the reference code below.
• Still stuck? Help me troubleshoot the temporary Express route or server restart.

✔️ Awesome, I've got everything!

Your temporary write route is now part of app.js. The restarted server is ready for the anonymous request.

ⓧ I'd like to double check the full code

• Compare your complete app.js file with this reference:

import express from 'express'
import { createClient } from '@supabase/supabase-js'

const { SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY } = process.env

if (!SUPABASE_URL || !SUPABASE_PUBLISHABLE_KEY) {
  throw new Error('SUPABASE_URL and SUPABASE_PUBLISHABLE_KEY are required')
}

const app = express()
app.use(express.json())

const publicSupabase = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY)

app.get('/health', (req, res) => {
  res.json({ status: 'ok' })
})

app.get('/posts', async (req, res) => {
  const { data, error } = await publicSupabase
    .from('posts')
    .select('id, title, content, author_id, created_at')
    .order('created_at', { ascending: false })

  if (error) {
    return res.status(500).json({ error: error.message, code: error.code })
  }

  res.json(data)
})

app.post('/posts', async (req, res) => {
  const { title, content } = req.body

  const { data, error } = await publicSupabase
    .from('posts')
    .insert({ title, content, author_id: null })
    .select()
    .single()

  if (error) {
    return res.status(403).json({ error: error.message, code: error.code })
  }

  res.status(201).json(data)
})

app.listen(3000, () => {
  console.log('Server is running on http://localhost:3000')
})

Send an anonymous write

The temporary route performs no identity check before calling Supabase. This gives the anonymous request a clear path to the database authorization layer.

Before you send this request, which boundary do you think responds first: the Express route or the database?

• Send the anonymous post from the second PowerShell terminal by running:

Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Post -ContentType 'application/json' -Body (@{ title = 'Blocked'; content = 'Anonymous write' } | ConvertTo-Json)

Why was the write rejected?

The anonymous anon role has no insert grant on public.posts. PostgreSQL returns code 42501 before a Row Level Security policy evaluates the row.

That rejected request is the proof you were looking for. The API surfaces the database code in its forbidden response.

Cannot see code 42501?

• Look through the PowerShell error details for the response body returned by the API.
• Confirm the temporary route calls publicSupabase without authentication middleware.
• Confirm the request body uses Blocked as the title.
• Still stuck? Help me inspect the anonymous write failure.

Prove the feed stayed empty

A denied response proves the request failed. Reading the feed again proves that no partial row survived the failed attempt.

Before you read the feed again, do you expect to find a post named Blocked?

• Request the public posts feed without a token by running:

Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Get

What does the empty feed prove?

You should see an empty collection with no post objects. This confirms the anonymous insert created no row in public.posts.

You have now tested both sides of the boundary. Anonymous callers can read the feed while database grants block their writes.

Seeing a post in the feed?

• Confirm the returned post has the title Blocked before treating it as part of this test.
• Return to the hosted SQL Editor from earlier to confirm the original grants remain deployed.
• Still stuck? Help me find why the anonymous post exists.

Your database has proved that anonymous writes cannot pass its grants. Next, you will add verified identities and role claims so legitimate authors can publish.

Add Authentication and Role Claims

Your anonymous write test proved that PostgreSQL grants can stop a route that forgets to protect itself. The database stayed empty because the request had no permission to insert a row.

Legitimate authors now need a verified identity before they can publish. Supabase Auth will verify each user. A custom JWT claim will carry the user's role into Row Level Security so the database can enforce RBAC permissions.

In this step, get ready to:

• Add signup and login routes with verified bearer-token middleware.
• Create author and admin users with database-issued role claims.
• Publish the first author-owned post through the protected route.

Configure authentication for test users

Hosted Supabase projects require email confirmation by default. Temporarily disabling that requirement lets both test users sign in immediately after signup.

This change affects the two test identities you create here. You will restore email confirmation during cleanup.

• Return to the signed-in Supabase Dashboard from earlier.
• Select Authentication in the left sidebar.
• Open the Providers page.
• Turn off the requirement to confirm an email before sign-in.
• Save the authentication setting.

Why disable email confirmation?

The API needs two test sessions during this project. Immediate sign-in keeps the authorization test focused on identity verification and role claims.

A request-scoped Supabase client forwards the caller's authorization header with every database query. PostgreSQL can then evaluate policies using that caller's verified identity.

• Switch back to app.js in Visual Studio Code.
• Find the publicSupabase declaration.
• Add the request-scoped client function directly below that declaration by pasting this code:

function createRequestClient(authorization) {
  return createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY, {
    global: {
      headers: { Authorization: authorization },
    },
  })
}

What does this function do?

• The authorization parameter holds the caller's bearer-token header.
• The global.headers configuration attaches that header to queries made by this client.
• Each protected request gets its own client so database policies evaluate the correct caller.

• Save app.js.
• Stop the running server by pressing Ctrl+C in its PowerShell terminal.
• Restart the API with the new helper by running:

npm start

What does this check confirm?

A successful restart confirms that Node.js can parse the new function. You should see the server listening on http://localhost:3000.

Server failing after the client helper?

• Check that createRequestClient sits outside every route.
• Compare the braces around global and headers with the snippet above.
• Still stuck? Help me debug the request-scoped Supabase client.

The middleware is the application-level gate. It extracts the bearer token before asking Supabase Auth to verify the token against the hosted Auth service.

• Add the authentication middleware directly below createRequestClient by pasting this code:

async function requireUser(req, res, next) {
  const authorization = req.headers.authorization

  if (!authorization?.startsWith('Bearer ')) {
    return res.status(401).json({ error: 'Bearer token required' })
  }

  const token = authorization.slice('Bearer '.length)
  const {
    data: { user },
    error,
  } = await publicSupabase.auth.getUser(token)

  if (error || !user) {
    return res.status(401).json({ error: 'Invalid or expired token' })
  }

  req.user = user
  req.supabase = createRequestClient(authorization)
  next()
}

How does the middleware verify a caller?

• The header check rejects requests that do not present a bearer token.
• The getUser(token) call asks Supabase Auth for the authentic user represented by the token.
• The verified user becomes req.user for route ownership checks.
• The request-scoped client becomes req.supabase for RLS-protected database queries.

• Save app.js.
• Stop the server by pressing Ctrl+C.
• Confirm the middleware parses successfully by restarting the API:

npm start

What should you see?

You should see the server listening on http://localhost:3000. The middleware is now ready to guard a route.

Seeing a syntax error in requireUser?

• Check that requireUser appears after the closing brace of createRequestClient.
• Check the nested data: { user } destructuring for matching braces.
• Still stuck? Help me fix my bearer-token middleware.

The signup route creates users through the publishable Supabase client. It returns each Auth user ID so you can confirm the identities exist before assigning roles.

• Find the existing GET /health route in app.js.
• Add the signup route directly below it by pasting this code:

app.post('/auth/signup', async (req, res) => {
  const { email, password } = req.body

  if (!email || !password) {
    return res.status(400).json({ error: 'email and password are required' })
  }

  const authClient = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY)
  const { data, error } = await authClient.auth.signUp({ email, password })

  if (error) {
    return res.status(400).json({ error: error.message })
  }

  res.status(201).json({
    user_id: data.user?.id,
    message: 'User created. Assign a role before signing in.',
  })
})

What does the signup route return?

• The route requires both an email address and a password.
• The signUp() call creates the user in Supabase Auth.
• The response returns the new user's ID without returning the password.

• Save app.js.
• Stop the server by pressing Ctrl+C.
• Load the signup route by restarting the API:

npm start

What does the restart prove?

The listening message confirms that Express registered the signup route without a syntax error. The route is ready for the test-user requests.

Signup route stopping the server?

• Confirm the signup route sits outside the GET /health callback.
• Check that the route ends with }).
• Still stuck? Help me debug my Supabase signup route.

A login issues a fresh access token for an existing user. Tokens issued after role assignment can include the custom role claim added by the Auth Hook.

• Add the login route directly below the signup route by pasting this code:

app.post('/auth/login', async (req, res) => {
  const { email, password } = req.body
  const authClient = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY)
  const { data, error } = await authClient.auth.signInWithPassword({ email, password })

  if (error || !data.session) {
    return res.status(401).json({ error: error?.message ?? 'Sign-in failed' })
  }

  res.json({
    access_token: data.session.access_token,
    user_id: data.user.id,
  })
})

Why return the access token?

• The signInWithPassword() call creates a session for valid credentials.
• The access token lets later API requests prove which user is calling.
• The user ID gives you a safe value to compare with the post's author_id.

• Save app.js.
• Stop the server by pressing Ctrl+C.
• Load both authentication routes by restarting the API:

npm start

What should you see?

You should see the server listening on http://localhost:3000. Both Auth routes are now available.

Login route stopping the server?

• Check that the login route begins after the signup route's closing }).
• Compare the data.session check with the snippet above.
• Still stuck? Help me debug the password login route.

Assign roles and issue fresh tokens

The Custom Access Token Auth Hook adds the role stored in public.user_roles to each newly issued token. Enabling the hook before signup prepares Supabase Auth to issue role-aware sessions.

• Return to Authentication in the Supabase Dashboard.
• Open Hooks (Beta).
• Choose the Custom Access Token Hook.
• Select public.custom_access_token_hook as the Postgres function.
• Enable the hook configuration.

What does the hook add?

The hook reads the role assigned to the signing-in user. It places that value in the token's user_role claim before Supabase issues the token.

These test credentials create access to your local learning project. Use a temporary password that you do not reuse anywhere else.

• Switch to the PowerShell terminal you used for API requests earlier.
• Replace each your-test-password-here placeholder below with the same temporary password.
• Create the author and admin Auth users by running this code:

$authorSignup = Invoke-RestMethod -Uri 'http://localhost:3000/auth/signup' -Method Post -ContentType 'application/json' -Body (@{ email = 'author@example.com'; password = 'your-test-password-here' } | ConvertTo-Json)
$adminSignup = Invoke-RestMethod -Uri 'http://localhost:3000/auth/signup' -Method Post -ContentType 'application/json' -Body (@{ email = 'admin@example.com'; password = 'your-test-password-here' } | ConvertTo-Json)
$authorSignup
$adminSignup

What do these requests create?

• The first request creates author@example.com.
• The second request creates admin@example.com.
• Each response shows a user_id plus a reminder to assign the role before sign-in.

You should see two different user IDs. The users now exist in Supabase Auth without holding role-aware sessions yet.

User signup failing?

• Confirm the API server is still running in the first PowerShell terminal.
• Check that email confirmation is disabled for this temporary test.
• Still stuck? Help me troubleshoot the two signup requests.

The role-assignment script connects each Auth user ID to an application role. The hook reads these rows the next time each user signs in.

• Switch back to Visual Studio Code.
• Select the existing supabase folder in the Explorer sidebar.
• Create assign-roles.sql inside the supabase folder.
• Paste this role-assignment SQL into the new file:

insert into public.user_roles (user_id, role)
select id, 'author'::public.app_role
from auth.users
where email = 'author@example.com'
on conflict (user_id) do update set role = excluded.role;

insert into public.user_roles (user_id, role)
select id, 'admin'::public.app_role
from auth.users
where email = 'admin@example.com'
on conflict (user_id) do update set role = excluded.role;

How are the roles assigned?

• The first statement finds the Auth user with the author email address.
• The second statement finds the Auth user with the admin email address.
• The conflict handling updates an existing role row if you execute the script again.

• Save supabase/assign-roles.sql.
• Copy the complete SQL from supabase/assign-roles.sql.
• Return to the existing Supabase SQL Editor session.
• Paste the copied SQL into a new query.
• Execute the query.

You should see a successful query result without a returned data set. The two rows now connect each Auth identity to its application role.

Role rows not being assigned?

• Confirm both signup responses returned a user_id before executing the SQL.
• Check that the email addresses in the SQL exactly match the signup requests.
• Still stuck? Help me troubleshoot the user-role assignments.

Existing tokens do not gain new claims after a database role changes. Signing in now causes the enabled hook to issue fresh tokens with the assigned roles.

• Return to the API-request PowerShell terminal.
• Replace each your-test-password-here placeholder below with the same temporary password used for signup.
• Sign in both users after role assignment by running this code:

$authorLogin = Invoke-RestMethod -Uri 'http://localhost:3000/auth/login' -Method Post -ContentType 'application/json' -Body (@{ email = 'author@example.com'; password = 'your-test-password-here' } | ConvertTo-Json)
$adminLogin = Invoke-RestMethod -Uri 'http://localhost:3000/auth/login' -Method Post -ContentType 'application/json' -Body (@{ email = 'admin@example.com'; password = 'your-test-password-here' } | ConvertTo-Json)
$authorToken = $authorLogin.access_token
$authorUserId = $authorLogin.user_id
$adminToken = $adminLogin.access_token
$adminUserId = $adminLogin.user_id
$authorUserId
$adminUserId

Why keep the tokens in PowerShell variables?

• The login requests issue fresh access tokens after the role assignments.
• The PowerShell variables let you authenticate later requests without printing the token values.
• The user ID variables give you safe ownership values to inspect.

You should see two different user IDs without seeing either access token. Keep this PowerShell terminal open because the final request uses $authorToken and $authorUserId.

Sign-in failing after role assignment?

• Confirm each password matches the temporary password used during signup.
• Confirm email confirmation remains disabled during this test.
• Still stuck? Help me troubleshoot fresh author and admin sessions.

Protect post creation with the caller's token

The protected write route needs consistent error handling for database permission failures. PostgreSQL code 42501 becomes an HTTP forbidden response while other database errors remain bad requests.

• Switch back to app.js.
• Add the write-error helper directly below requireUser by pasting this code:

function writeFailure(res, error, deniedMessage) {
  const status = error.code === '42501' ? 403 : 400
  return res.status(status).json({
    error: status === 403 ? deniedMessage : error.message,
    code: error.code,
  })
}

How are database failures translated?

• Permission code 42501 maps to HTTP status 403.
• Other database errors map to HTTP status 400.
• The response keeps the database code available for authorization tests.

• Save app.js.
• Stop the server by pressing Ctrl+C.
• Confirm the helper parses successfully by restarting the API:

npm start

What does this restart confirm?

The listening message confirms that the error helper is valid JavaScript. The server is ready for the protected route replacement.

Error helper stopping the server?

• Check that writeFailure sits outside requireUser.
• Check the response object for matching braces and parentheses.
• Still stuck? Help me fix the writeFailure helper.

The temporary write route uses the public client without a verified user. The final route requires the middleware before building the inserted row from req.user.id.

• Find the temporary POST /posts route from the previous step.
• Select the complete temporary route.
• Replace it with this authenticated route:

app.post('/posts', requireUser, async (req, res) => {
  const { title, content } = req.body

  if (!title || !content) {
    return res.status(400).json({ error: 'title and content are required' })
  }

  const { data, error } = await req.supabase
    .from('posts')
    .insert({ title, content, author_id: req.user.id })
    .select()
    .single()

  if (error) {
    return writeFailure(res, error, 'Your role cannot create this post')
  }

  res.status(201).json(data)
})

How does the protected insert work?

• The requireUser middleware runs before the route handler.
• The inserted author_id comes from the verified Auth user.
• The query uses req.supabase so grants and RLS evaluate the caller's token.
• The returned row provides immediate proof of ownership.

• Save app.js.
• Stop the server by pressing Ctrl+C.
• Load the protected route by restarting the API:

npm start

What should you see?

You should see the server listening on http://localhost:3000. The anonymous route has now been replaced by the authenticated route.

Protected route stopping the server?

• Confirm the old temporary POST /posts route was replaced completely.
• Check that requireUser appears between the route path and the async handler.
• Still stuck? Help me debug the authenticated post route.

✔️ Awesome, I've got everything!

Your authentication helpers and protected post route are in place. Double check that app.js is saved before testing the author token.

ⓧ I'd like to double check the full code

Compare your complete app.js with this version:

import express from 'express'
import { createClient } from '@supabase/supabase-js'

const { SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY } = process.env

if (!SUPABASE_URL || !SUPABASE_PUBLISHABLE_KEY) {
  throw new Error('SUPABASE_URL and SUPABASE_PUBLISHABLE_KEY are required')
}

const app = express()
app.use(express.json())

const publicSupabase = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY)

function createRequestClient(authorization) {
  return createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY, {
    global: {
      headers: { Authorization: authorization },
    },
  })
}

async function requireUser(req, res, next) {
  const authorization = req.headers.authorization

  if (!authorization?.startsWith('Bearer ')) {
    return res.status(401).json({ error: 'Bearer token required' })
  }

  const token = authorization.slice('Bearer '.length)
  const {
    data: { user },
    error,
  } = await publicSupabase.auth.getUser(token)

  if (error || !user) {
    return res.status(401).json({ error: 'Invalid or expired token' })
  }

  req.user = user
  req.supabase = createRequestClient(authorization)
  next()
}

function writeFailure(res, error, deniedMessage) {
  const status = error.code === '42501' ? 403 : 400
  return res.status(status).json({
    error: status === 403 ? deniedMessage : error.message,
    code: error.code,
  })
}

app.get('/health', (req, res) => {
  res.json({ status: 'ok' })
})

app.post('/auth/signup', async (req, res) => {
  const { email, password } = req.body

  if (!email || !password) {
    return res.status(400).json({ error: 'email and password are required' })
  }

  const authClient = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY)
  const { data, error } = await authClient.auth.signUp({ email, password })

  if (error) {
    return res.status(400).json({ error: error.message })
  }

  res.status(201).json({
    user_id: data.user?.id,
    message: 'User created. Assign a role before signing in.',
  })
})

app.post('/auth/login', async (req, res) => {
  const { email, password } = req.body
  const authClient = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY)
  const { data, error } = await authClient.auth.signInWithPassword({ email, password })

  if (error || !data.session) {
    return res.status(401).json({ error: error?.message ?? 'Sign-in failed' })
  }

  res.json({
    access_token: data.session.access_token,
    user_id: data.user.id,
  })
})

app.get('/posts', async (req, res) => {
  const { data, error } = await publicSupabase
    .from('posts')
    .select('id, title, content, author_id, created_at')
    .order('created_at', { ascending: false })

  if (error) {
    return res.status(500).json({ error: error.message, code: error.code })
  }

  res.json(data)
})

app.post('/posts', requireUser, async (req, res) => {
  const { title, content } = req.body

  if (!title || !content) {
    return res.status(400).json({ error: 'title and content are required' })
  }

  const { data, error } = await req.supabase
    .from('posts')
    .insert({ title, content, author_id: req.user.id })
    .select()
    .single()

  if (error) {
    return writeFailure(res, error, 'Your role cannot create this post')
  }

  res.status(201).json(data)
})

app.listen(3000, () => {
  console.log('Server is running on http://localhost:3000')
})

Before you send the request, predict whether the author token will satisfy identity, role, and ownership checks.

• Return to the PowerShell terminal that holds $authorToken.
• Create the first author-owned post by running this code:

$post = Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Post -Headers @{ Authorization = 'Bearer ' + $authorToken } -ContentType 'application/json' -Body (@{ title = 'Author post'; content = 'Created by author' } | ConvertTo-Json)
$post
$authorUserId

What does this request prove?

• The authorization header presents the fresh author access token.
• The middleware verifies the token before the route inserts anything.
• The custom claim grants the author permission to create a post.
• The database accepts the row because author_id matches the verified user.

You should see the new post followed by the author's user ID. The post's author_id should match that ID exactly.

Author post returning a forbidden response?

• Confirm the Custom Access Token Hook points to public.custom_access_token_hook.
• Run the login request again after confirming the role assignment so $authorToken receives a fresh token.
• Still stuck? Help me trace the author role from login to RLS.

That is the identity chain working from end to end. Your API now verifies the caller, carries the author role in a fresh token, and stores a post owned by that user.

Next up, you will use row ownership to limit author changes while giving the admin role an override.

Enforce Ownership and Admin Overrides

Your author can now sign in with a verified JWT role claim. Authentication proves identity. Ownership rules determine which rows that identity can change.

In this step, you will add protected update and delete routes to your Express API. Operation-specific Row Level Security policies will enforce ownership plus the admin override. This keeps RBAC active inside Supabase even when a request reaches the database.

In this step, get ready to:

• Add the protected post update route.
• Add the protected post deletion route.
• Prove author ownership limits and the admin override.

Add the protected update route

An authenticated author needs permission to update their own rows. The request-scoped client passes the caller's bearer token to Supabase, where the update policy checks both ownership and role permissions.

• In app.js, find the closing brace of the authenticated POST /posts route.
• Paste this route above app.listen(3000, ...):

app.patch('/posts/:id', requireUser, async (req, res) => {
  const changes = {}
  if (typeof req.body.title === 'string') changes.title = req.body.title
  if (typeof req.body.content === 'string') changes.content = req.body.content

  if (Object.keys(changes).length === 0) {
    return res.status(400).json({ error: 'Provide title or content' })
  }

  const { data, error } = await req.supabase
    .from('posts')
    .update(changes)
    .eq('id', req.params.id)
    .select()

  if (error) {
    return writeFailure(res, error, 'Your role cannot update this post')
  }

  if (data.length === 0) {
    return res.status(403).json({ error: 'Your role or ownership does not allow this update' })
  }

  res.json(data[0])
})

What does this route do?

• The changes object accepts string values for title or content. This limits the columns that a request can change.
• The requireUser middleware verifies the token. It also provides the request-scoped req.supabase client.
• The returned-row check detects when RLS filters out the target post. A zero-row result becomes an observable 403 response.

• Save app.js.
• Switch back to the PowerShell terminal running the API.
• Stop the current server by pressing Ctrl+C.
• Restart the API with the new route by running:

npm start

What should I see?

The server starts from the updated app.js file. You will see Server is running on http://localhost:3000 in the terminal.

• Return to the second PowerShell terminal from earlier.
• Read the current public feed by running:

Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Get

Find the author-owned post

The response contains the author-owned post from the previous step. Its id identifies the exact row that you will update.

• Record the author-owned post's id here: your author post ID.

Protect your access tokens

This step uses your fresh access tokens again. They stay only in your local PowerShell session, so your project files remain secret-free.

• Replace your-author-access-token-here in the command below with the author access token from earlier.
• Update the author-owned post by running this command:

Invoke-RestMethod -Uri 'http://localhost:3000/posts/[[AUTHOR_POST_ID="your author post ID"]]' -Method Patch -Headers @{ Authorization = 'Bearer your-author-access-token-here' } -ContentType 'application/json' -Body (@{ title = 'Author updated' } | ConvertTo-Json)

What should I see?

The response contains the updated post with Author updated as its title. Its author_id still matches the author account, which proves the author updated their own row.

Update returning a forbidden response?

• Check that the URL contains the author-owned post ID from the public feed.
• Confirm that you pasted the fresh author token issued after the role assignment.
• Still stuck? Help me diagnose why my author-owned post update returns a forbidden response.

Add the protected delete route

Delete policies can silently filter out rows that the caller cannot remove. The route must inspect the returned rows so a denied deletion becomes a clear authorization response.

• In app.js, find the closing brace of the new PATCH /posts/:id route.
• Paste this delete route immediately below it:

app.delete('/posts/:id', requireUser, async (req, res) => {
  const { data, error } = await req.supabase
    .from('posts')
    .delete()
    .eq('id', req.params.id)
    .select()

  if (error) {
    return writeFailure(res, error, 'Your role cannot delete this post')
  }

  if (data.length === 0) {
    return res.status(403).json({ error: 'Your role or ownership does not allow this delete' })
  }

  res.json({ deleted: data[0] })
})

How does deletion stay protected?

• The requireUser middleware blocks requests without a verified bearer token.
• The delete() query runs with the caller's database identity through req.supabase.
• The select() modifier returns the deleted row when the policy permits the operation. An empty result reveals an ownership or role denial.

• Find the app.listen(3000, ...) block near the bottom of app.js.
• Paste this error middleware immediately above it:

app.use((err, req, res, next) => {
  console.error(err)
  res.status(500).json({ error: 'Unexpected server error' })
})

What does the error middleware do?

Express sends unexpected route errors to this final middleware. The server records the full error locally while the caller receives a consistent JSON response.

• Save app.js.
• Switch back to the PowerShell terminal running the API.
• Stop the current server by pressing Ctrl+C.
• Restart the completed API by running:

npm start

What should I see?

You will see Server is running on http://localhost:3000 again. The running API now includes protected create, update, and delete routes.

✔️ Awesome, I've got everything!

Great work. Your app.js file now contains every route required for the ownership and admin tests.

ⓧ I'd like to double check the full code

• Compare your saved app.js file with this complete version:

import express from 'express'
import { createClient } from '@supabase/supabase-js'

const { SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY } = process.env

if (!SUPABASE_URL || !SUPABASE_PUBLISHABLE_KEY) {
  throw new Error('SUPABASE_URL and SUPABASE_PUBLISHABLE_KEY are required')
}

const app = express()
app.use(express.json())

const publicSupabase = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY)

function createRequestClient(authorization) {
  return createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY, {
    global: {
      headers: { Authorization: authorization },
    },
  })
}

async function requireUser(req, res, next) {
  const authorization = req.headers.authorization

  if (!authorization?.startsWith('Bearer ')) {
    return res.status(401).json({ error: 'Bearer token required' })
  }

  const token = authorization.slice('Bearer '.length)
  const {
    data: { user },
    error,
  } = await publicSupabase.auth.getUser(token)

  if (error || !user) {
    return res.status(401).json({ error: 'Invalid or expired token' })
  }

  req.user = user
  req.supabase = createRequestClient(authorization)
  next()
}

function writeFailure(res, error, deniedMessage) {
  const status = error.code === '42501' ? 403 : 400
  return res.status(status).json({
    error: status === 403 ? deniedMessage : error.message,
    code: error.code,
  })
}

app.get('/health', (req, res) => {
  res.json({ status: 'ok' })
})

app.post('/auth/signup', async (req, res) => {
  const { email, password } = req.body

  if (!email || !password) {
    return res.status(400).json({ error: 'email and password are required' })
  }

  const authClient = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY)
  const { data, error } = await authClient.auth.signUp({ email, password })

  if (error) {
    return res.status(400).json({ error: error.message })
  }

  res.status(201).json({
    user_id: data.user?.id,
    message: 'User created. Assign a role before signing in.',
  })
})

app.post('/auth/login', async (req, res) => {
  const { email, password } = req.body
  const authClient = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY)
  const { data, error } = await authClient.auth.signInWithPassword({ email, password })

  if (error || !data.session) {
    return res.status(401).json({ error: error?.message ?? 'Sign-in failed' })
  }

  res.json({
    access_token: data.session.access_token,
    user_id: data.user.id,
  })
})

app.get('/posts', async (req, res) => {
  const { data, error } = await publicSupabase
    .from('posts')
    .select('id, title, content, author_id, created_at')
    .order('created_at', { ascending: false })

  if (error) {
    return res.status(500).json({ error: error.message, code: error.code })
  }

  res.json(data)
})

app.post('/posts', requireUser, async (req, res) => {
  const { title, content } = req.body

  if (!title || !content) {
    return res.status(400).json({ error: 'title and content are required' })
  }

  const { data, error } = await req.supabase
    .from('posts')
    .insert({ title, content, author_id: req.user.id })
    .select()
    .single()

  if (error) {
    return writeFailure(res, error, 'Your role cannot create this post')
  }

  res.status(201).json(data)
})

app.patch('/posts/:id', requireUser, async (req, res) => {
  const changes = {}
  if (typeof req.body.title === 'string') changes.title = req.body.title
  if (typeof req.body.content === 'string') changes.content = req.body.content

  if (Object.keys(changes).length === 0) {
    return res.status(400).json({ error: 'Provide title or content' })
  }

  const { data, error } = await req.supabase
    .from('posts')
    .update(changes)
    .eq('id', req.params.id)
    .select()

  if (error) {
    return writeFailure(res, error, 'Your role cannot update this post')
  }

  if (data.length === 0) {
    return res.status(403).json({ error: 'Your role or ownership does not allow this update' })
  }

  res.json(data[0])
})

app.delete('/posts/:id', requireUser, async (req, res) => {
  const { data, error } = await req.supabase
    .from('posts')
    .delete()
    .eq('id', req.params.id)
    .select()

  if (error) {
    return writeFailure(res, error, 'Your role cannot delete this post')
  }

  if (data.length === 0) {
    return res.status(403).json({ error: 'Your role or ownership does not allow this delete' })
  }

  res.json({ deleted: data[0] })
})

app.use((err, req, res, next) => {
  console.error(err)
  res.status(500).json({ error: 'Unexpected server error' })
})

app.listen(3000, () => {
  console.log('Server is running on http://localhost:3000')
})

What should match?

Check that the PATCH /posts/:id and DELETE /posts/:id routes both use requireUser plus req.supabase. Confirm that the error middleware appears above app.listen(3000, ...).

The delete route is ready. You will now create an admin-owned row so the author can encounter the ownership boundary directly.

• Replace your-admin-access-token-here in the command below with the fresh admin access token.
• Create the admin-owned post by running this command:

$adminPost = Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Post -Headers @{ Authorization = 'Bearer your-admin-access-token-here' } -ContentType 'application/json' -Body (@{
  title = 'Admin post'
  content = 'Admin-owned content'
} | ConvertTo-Json)
$adminPost

What should I see?

The response contains a new post titled Admin post. PowerShell also stores the response in $adminPost so the next request can use its id.

Admin post creation failing?

• Confirm that you used the admin token issued after the admin role assignment.
• Check that the server terminal still shows the API running.
• Still stuck? Help me diagnose why the admin cannot create a post.

Before you run the next request, do you think an authenticated author can delete a post owned by the admin?

• Replace your-author-access-token-here in the command below with the fresh author access token.
• Attempt to delete the admin-owned post by running this command:

try {
  Invoke-RestMethod -Uri "http://localhost:3000/posts/$($adminPost.id)" -Method Delete -Headers @{ Authorization = 'Bearer your-author-access-token-here' }
} catch {
  $_.Exception.Response.StatusCode.value__
  $_.ErrorDetails.Message
}

The ownership boundary worked

You will see status 403 with Your role or ownership does not allow this delete. The author has delete permission for owned rows, so the admin-owned row is filtered out by RLS.

• Confirm that the denied request preserved both posts by running:

Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Get

What proves the row stayed safe?

The public feed still contains the updated author post plus Admin post. The forbidden request changed no database rows.

Did the author delete the admin post?

• Confirm that the delete request used the author token.
• Check that $adminPost.id points to the row created with the admin token.
• Still stuck? Help me inspect why an author deleted an admin-owned post.

Prove the admin override

The admin role carries the permission to delete any post. The same delete route now succeeds because the database reads the admin claim from the verified token.

Before you send the request, do you think the admin permission will override ownership for the author's row?

• Replace your-admin-access-token-here in the command below with the fresh admin access token.
• Delete the author-owned post by running this command:

Invoke-RestMethod -Uri 'http://localhost:3000/posts/[[AUTHOR_POST_ID="your author post ID"]]' -Method Delete -Headers @{ Authorization = 'Bearer your-admin-access-token-here' }

The admin override worked

The response contains a deleted object for the author-owned post. The admin permission satisfied the delete policy even though the row belonged to another user.

Before you check the final feed, which post do you expect the token-free request to return?

• Verify the final public database state by running:

Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Get

What should the final feed contain?

You will see only the admin-owned post titled Admin post. The token-free read still works while grants plus operation-specific policies protect every write.

Still seeing the author post?

• Check that the admin delete request used the author post ID recorded earlier.
• Confirm that the delete response contained the author-owned row inside deleted.
• Still stuck? Help me verify the final post state after the admin deletion.

You have proved the complete authorization boundary. Authors manage their own posts, admins can manage every post, and public callers retain read-only access.

Secret mission

Add Draft Publishing

Your public feed currently reveals every post. Add a publication state that keeps drafts private until their author deliberately publishes them.

Clean Up Your Resources

Clean Up Your Resources

Your Supabase Free project and local files have no ongoing paid cost. Choose whether to keep the project available, leave it inactive for automatic pausing, or delete the cloud and local resources.

Resources you used:

• One Supabase Free project containing the author and admin users, custom access-token hook, database objects, policies, roles, and test posts.
• The running Express API in the VS Code PowerShell terminal.
• The local Node.js project folder containing app.js, package.json, .env, .env.example, .gitignore, installed dependencies, and the supabase folder.

Keep everything running

No cleanup is needed. Choose this option if you want to keep testing the API or demonstrating its role and ownership rules.

• Restore email verification on the Auth Providers page.
• Keep the custom access-token hook enabled for future role tests.
• Leave the API running in the VS Code PowerShell terminal for more local requests.
• Retain the local project folder for future changes to the routes and policies.

Pause - I'll come back to this later

Stop the local API to free its port. Keep the Supabase project and local files ready for later practice.

• Press Ctrl+C in the VS Code PowerShell terminal to stop the running API.
• Restore email verification on the Auth Providers page.
• Leave the Supabase project inactive.

After one week of inactivity, Supabase pauses the Free project. Your local project files remain available for when you return.

Delete - I don't want to use this again

Deleting these resources is permanent, so this option fits when you are finished with the API. It removes the hosted users, policies, test data, and local project files.

• Return to the signed-in Supabase Dashboard from earlier.
• Open the current project's settings area to access its project deletion control.
• Use the project deletion control to delete the current Supabase project.
• Complete the confirmation requested by Supabase.

You should no longer see the project in your Supabase project list. This confirms that its Auth users, database objects, policies, and test data are gone.

• Switch back to the VS Code PowerShell terminal from earlier.
• Press Ctrl+C to stop the API if it is still running.
• Locate the local project folder in File Explorer by finding the folder that contains package.json and app.js.
• Copy the folder's full path from the address bar into full path to your local project folder.
• Close VS Code to release any open files in the project folder.

The cloud project and running process are now removed. The final command deletes the local workspace in one pass.

• Press the Windows key to open the search bar.
• Type PowerShell into the search bar.
• Press Enter to open PowerShell.
• Delete the local project folder by running this command:

cmd /c rmdir /s /q "[[PROJECT_FOLDER_PATH="full path to your local project folder"]]"

What Does This Command Remove?

The command permanently deletes the folder at full path to your local project folder. Every project file and installed dependency inside that folder is removed.

• Return to the parent location in File Explorer where the local project folder was stored.

You should no longer see the local project folder. Your Supabase project and local workspace are now fully removed.

Does the Folder Remain?

• Close any File Explorer window displaying files inside the project folder.
• Run the deletion command again from PowerShell.

If the folder still remains, get help removing the local project folder.

Nice Work!

Nice Work!

That is a wrap! You've built a public-read blog API with Express where Supabase authorizes writes by identity, role, and ownership.

You've learned how to:

• Build a public post feed with Express. It returns Supabase data as JSON without requiring a token.
• Enforce role-based access control with verified JWTs. PostgreSQL grants and Row Level Security policies keep authorization at the database boundary.
• Trigger a designed authorization failure for an anonymous write. You also proved that cross-owner deletes fail without changing protected rows.
• Complete the optional Secret Mission by adding private drafts. Authors can publish them through an explicit transition when they are ready.

Ready to quiz yourself?