Secure Blog API with Supabase RBAC

Build an Express blog API with Supabase RBAC and database-enforced access.

Introduction

30 Second Summary

Publishing is easy until several people can change the same content. Readers, editors, and administrators each need clear boundaries so one mistaken request cannot erase a post.

In this project, you will build an Express blog REST API backed by Supabase. Database-enforced role permissions keep reading public while giving editors and admins different powers.

What You'll Build

From Windows PowerShell, you can demo a public read, a successful editor write, a rejected editor delete, and a successful admin delete against your finished API.

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

  • A public blog feed where anyone can call GET /posts to read the welcome post plus newly published content.
  • An editor workflow that returns a bearer token after login. You can publish or edit a post while an attempted delete returns 403.
  • An admin workflow that successfully deletes posts. Postgres grants plus Row Level Security keep the database in control of every authorization decision.
  • Secret Mission: Restrict editors to updating their own posts while admins retain permission to update any post.

Are there any prerequisites?

You need Node.js 22 or later, Visual Studio Code, and a Supabase account. You should also know JavaScript fundamentals and have experience building a small Express API.

Before We Start

Before any setup, this checkpoint commits you to the blog's authorization boundaries. Those boundaries are public reads, no unauthenticated writes, editor create and update access, admin create, update, and delete access, plus final enforcement by Supabase Postgres grants and Row Level Security.

Set Up the Express and Supabase Workspace

Your blog API depends on Node.js to run Express. Verifying version 22 or later prevents the pinned Supabase package from failing during installation.

The running API needs a publishable key. A separate setup file keeps the temporary secret key away from the normal process.

In this step, get ready to:
  • Verify Node.js 22 or later in Windows PowerShell.
  • Create a Supabase Free project with the required API keys.
  • Prepare the local workspace with its environment files and dependencies.
Verify Node.js 22 or later

The Supabase JavaScript package in this project requires Node.js 22 or later. Windows PowerShell can report your installed runtime before you create any files.

  • Press the Windows key to open Windows search.
  • Type Windows PowerShell into the search field.
  • Press Enter to open Windows PowerShell.
  • Check the installed Node.js version by running this command:
node --version

What does this command do?

The command prints the Node.js runtime installed on your computer. This project can continue when the major version is 22 or higher.

Choose the tab that matches the result printed in PowerShell.

✔️ I see version 22 or higher

Your runtime meets the project requirement. Node.js is ready to install the pinned packages.

ⓧ I see an older version

Your installed runtime cannot run the pinned Supabase JavaScript package. Update Node.js before continuing.

  • Open the official Node.js download page in your browser.
  • Choose a supported release at version 22 or later.
  • Download the Windows installer for your computer's architecture.
  • Run the downloaded installer.
  • Complete the installation prompts with the default options.
  • Close Windows PowerShell after the installation finishes.
  • Reopen Windows PowerShell through Windows search.
  • Repeat the version check from above.

ⓧ Command not found

Windows cannot find the Node.js executable. Installing a supported release adds the runtime plus npm to your computer.

  • Open the official Node.js download page in your browser.
  • Choose a supported release at version 22 or later.
  • Download the Windows installer for your computer's architecture.
  • Run the downloaded installer.
  • Complete the installation prompts with the default options.
  • Close Windows PowerShell after the installation finishes.
  • Reopen Windows PowerShell through Windows search.
  • Repeat the version check from above.

Still missing the required version?

Restart Windows if a new PowerShell session still reports the previous version. This refreshes the executable paths available to your terminal.

If the result is still incorrect, help me diagnose my Node.js installation.

Create the Supabase project and capture keys

The hosted database needs a Supabase project before your local code can connect. The Free plan provides a project URL plus separate publishable and secret keys.

Protect the database password

The database password protects direct access to your project. A password manager keeps it out of your project files.

  • Open the Supabase Dashboard in your browser.
  • Sign in to your Supabase account.
  • Choose the organization that should own the project.
  • Create a new project named supabase-rbac-blog.
  • Select the Free plan.
  • Generate a database password with your password manager.
  • Choose the region nearest to you.
  • Submit the project details.

Project provisioning takes a moment

The dashboard can remain in a provisioning state while Supabase prepares the database. Keep the page open until the project dashboard is ready.

Your hosted project is ready once the project dashboard loads. The next values connect your local API to that project.

  • Open the project's Connect dialog.
  • Copy the Project URL.
  • Store the Project URL here: https://your-project.supabase.co.
  • Copy the publishable key.
  • Store the publishable key here: your Supabase publishable key.

The next value has privileged access because a Supabase secret key bypasses Row Level Security. The temporary setup file keeps it away from the running API.

  • Select Settings in the project dashboard.
  • Select API Keys.
  • Open the Publishable and secret API keys tab.
  • Copy the project's default secret key.
  • Store the secret key temporarily in your password manager.

Why separate the keys?

The publishable key identifies the project while database grants plus policies control data access. The secret key bypasses those controls.

Only the one-time user setup script receives the secret key. The normal Express process loads the publishable key.

Build the local workspace and install dependencies

The local folder separates API code from setup scripts plus database scripts. Its configuration pins package versions so the workspace stays repeatable.

  • Switch back to Windows PowerShell from the version check.
  • Create the supabase-rbac-blog workspace on your Desktop by running these commands:
Set-Location -Path "$env:USERPROFILE\Desktop"
New-Item -Path 'supabase-rbac-blog' -ItemType Directory
Set-Location -Path 'supabase-rbac-blog'
New-Item -Path 'src','scripts','supabase' -ItemType Directory
New-Item -Path 'package.json','.gitignore','.env.example','.env.setup.example' -ItemType File
Get-ChildItem -Force

What do these commands create?

  • The first command moves PowerShell to your Desktop.
  • The next commands create the supabase-rbac-blog folder plus its three subfolders.
  • The file command creates the four authored configuration files.
  • The final command lists every created item.

You should see src, scripts, supabase, plus the four configuration files in the output.

Missing a folder or file?

Check that PowerShell is inside the supabase-rbac-blog folder. Rerun only the command for the missing item.

If the location is unclear, help me check my PowerShell project folder.

The folder structure is in place. Visual Studio Code now gives you one workspace for editing every project file.

  • Press the Windows key to open Windows search.
  • Type Visual Studio Code into the search field.
  • Press Enter to open Visual Studio Code.
  • Select File from the top menu.
  • Select Open Folder.
  • Choose the supabase-rbac-blog folder on your Desktop.
  • Select Select Folder.
  • Confirm that you trust the folder if the Workspace Trust dialog appears.

The Explorer sidebar should show supabase-rbac-blog with the three folders plus the four files you created.

  • Select package.json in the Explorer sidebar.
  • Replace its empty contents with this package configuration:
{
  "name": "supabase-rbac-blog",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "scripts": {
    "start": "node --env-file=.env src/server.js",
    "setup-users": "node --env-file=.env.setup scripts/create-test-users.js"
  },
  "dependencies": {
    "@supabase/supabase-js": "2.117.3",
    "express": "5.3.0"
  }
}

What does this configuration do?

  • The type value enables JavaScript module imports.
  • The start script loads the normal API environment from .env.
  • The setup-users script loads the temporary setup environment from .env.setup.
  • The dependencies pin Supabase JavaScript to 2.117.3.
  • Express is pinned to 5.3.0.
  • Save package.json by pressing Ctrl+S.
  • Confirm that Visual Studio Code shows no JSON problem marker beside package.json.

Seeing a JSON problem marker?

Compare every comma plus quotation mark with the code block. JSON rejects missing commas plus trailing commas after the final property.

If the marker remains, help me find the package file syntax problem.

  • Select .gitignore in the Explorer sidebar.
  • Add these ignored paths to the file:
node_modules/
.env
.env.setup

Why ignore these paths?

The dependency folder can be recreated with an installation command. The environment files contain project-specific credentials that must stay outside source control.

  • Save .gitignore by pressing Ctrl+S.
  • Confirm that the file contains exactly three ignored paths.

Do the ignored paths look different?

Check that both environment filenames begin with a dot. Confirm that each ignored path appears on its own line.

If the names still differ, help me correct my gitignore file.

  • Select .env.example in the Explorer sidebar.
  • Add the normal API environment template:
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_PUBLISHABLE_KEY=your-publishable-key-here

What belongs in the API environment?

The running API receives the project URL plus the publishable key. Database grants plus policies remain responsible for authorization.

  • Save .env.example by pressing Ctrl+S.
  • Confirm that the first line defines SUPABASE_URL.
  • Confirm that the second line defines SUPABASE_PUBLISHABLE_KEY.

Missing an environment variable?

Check that each variable name matches the template exactly. Keep each value on the same line as its variable name.

If the template still differs, help me check the API environment template.

  • Select .env.setup.example in the Explorer sidebar.
  • Add the one-time setup environment template:
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_SECRET_KEY=your-secret-key-here

What belongs in the setup environment?

The one-time setup script receives the project URL plus the secret key. The normal API start script never loads this file.

  • Save .env.setup.example by pressing Ctrl+S.
  • Confirm that the first line defines SUPABASE_URL.
  • Confirm that the second line defines SUPABASE_SECRET_KEY.

Do the templates use the same key?

The second line must differ between the templates. The normal template uses the publishable key while the setup template uses the secret key.

If the variable names are mixed up, help me compare my environment templates.

  • Open the integrated terminal by selecting View from the top menu.
  • Select Terminal.
  • Create the working environment files from the safe templates by running these commands:
Copy-Item -Path '.env.example' -Destination '.env'
Copy-Item -Path '.env.setup.example' -Destination '.env.setup'
Get-ChildItem -Force

What do these commands do?

  • The first command creates .env from the publishable-key template.
  • The second command creates .env.setup from the secret-key template.
  • The final command includes hidden files in the folder listing.

You should see .env plus .env.setup in the terminal output.

Did a copy command fail?

Confirm that the integrated terminal is inside the supabase-rbac-blog folder. Check that both source filenames match the Explorer sidebar.

If a destination already exists, help me fix the environment file copy.

You are about to paste project credentials into ignored local files. Keep the secret key out of screenshots plus chat messages.

  • Select .env in the Explorer sidebar.
  • Replace the example URL with https://your-project.supabase.co.
  • Replace the publishable-key placeholder with your Supabase publishable key.
  • Save .env by pressing Ctrl+S.
  • Select .env.setup in the Explorer sidebar.
  • Replace the example URL with https://your-project.supabase.co.
  • Replace the secret-key placeholder with the secret key stored in your password manager.
  • Save .env.setup by pressing Ctrl+S.

Keep the secret local

Close the .env.setup editor tab after saving it. The one-time setup script can still load the file without leaving the secret visible on screen.

Use these tabs to compare the authored files before installing the dependencies.

✔️ Awesome, I've got everything!

Great. Save every open file before continuing to the installation check.

ⓧ I'd like to double check the full code

Compare each authored file below with the matching file in Visual Studio Code.

{
  "name": "supabase-rbac-blog",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "scripts": {
    "start": "node --env-file=.env src/server.js",
    "setup-users": "node --env-file=.env.setup scripts/create-test-users.js"
  },
  "dependencies": {
    "@supabase/supabase-js": "2.117.3",
    "express": "5.3.0"
  }
}

Package file check

This file must include both scripts plus the exact dependency pins. Keep every property name plus quotation mark unchanged.

node_modules/
.env
.env.setup

Ignored paths check

This file excludes the installed dependencies plus both credential-bearing environment files.

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

API template check

This safe template documents the values required by the running API.

SUPABASE_URL=https://your-project.supabase.co
SUPABASE_SECRET_KEY=your-secret-key-here

Setup template check

This safe template documents the temporary secret-key environment used by the setup script.

Your .env file should be a personalized copy of .env.example with your project URL plus publishable key.

Your .env.setup file should be a personalized copy of .env.setup.example with your project URL plus temporary secret key.

Before you run the final check, consider whether both checks will pass.

  • Verify the runtime plus install the pinned project dependencies by running these commands in the integrated terminal:
node --version
npm install

What do these commands confirm?

  • The version check confirms that Visual Studio Code can access the compatible Node.js runtime.
  • The installation command reads package.json.
  • The installation command adds Express plus Supabase JavaScript to node_modules.

You should see a Node.js version of v22 or later. PowerShell should return to the prompt after the dependency installation finishes without an error.

Did the dependency installation fail?

Confirm that the integrated terminal is inside the supabase-rbac-blog folder. Check that package.json has no JSON problem marker.

If the failure mentions runtime compatibility, repeat the version check. Help me diagnose the installation.

That is your workspace ready. Express plus Supabase JavaScript are installed with credentials separated by purpose.

Your local tools plus Supabase project are ready. Next, you will publish the first readable post and test the anonymous write barrier.

Publish a Public Blog Feed

Your local workspace is ready. Now you'll connect your Express API to Supabase to publish the first readable blog post.

A public blog needs a feed that anyone can read. Row Level Security keeps that read path open while Postgres evaluates the later write attempt.

In this step, get ready to:
  • Create the public posts table with a seeded welcome post.
  • Connect the Express API to Supabase.
  • Compare a public read with an anonymous publishing attempt.
Create the public posts table

The database needs a table for blog content before the API can return a feed. Its grants define which database roles may attempt each operation.

  • Create a folder named supabase inside the existing supabase-rbac-blog folder by using the new-folder control in Visual Studio Code's file sidebar.
  • Create 01-blog.sql inside the supabase folder by using the new-file control.

You'll see supabase/01-blog.sql in the file sidebar.

  • Define the posts table plus its public read access by pasting this migration into supabase/01-blog.sql:
create table public.posts (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  content text not null,
  author_id uuid references auth.users (id) on delete set null default auth.uid()
);

alter table public.posts enable row level security;

revoke all on table public.posts from anon, authenticated;
grant select on table public.posts to anon, authenticated;

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

insert into public.posts (title, content)
values (
  'Welcome to the RBAC Blog',
  'This post is public, but changing blog content requires an authorized role.'
);

What does this migration do?

  • The public.posts table stores each post's identifier plus its title.
  • The author_id column records the signed-in creator when one exists.
  • The grants allow anon plus authenticated users to attempt reads.
  • The SELECT policy permits those roles to read every post.
  • The final insert gives the public feed its first visible row.
  • Save supabase/01-blog.sql.
  • Return to your Supabase project in the browser.
  • Select SQL Editor from the project navigation.
  • Create a blank query in the SQL Editor.
  • Paste the contents of supabase/01-blog.sql into the query.
  • Execute the query by using the editor's run control.

You'll see a successful query result. Your database now contains the seeded Welcome to the RBAC Blog post.

Did the migration fail?

Check that you pasted the complete contents of supabase/01-blog.sql. A missing statement can leave the table without its grant or policy.

If you previously ran part of the migration, identify the completed statements before trying again. Help me diagnose my blog migration.

✔️ Awesome, I've got everything!

Your saved migration now matches the database structure used by this project.

ⓧ I'd like to double check the full code

  • Compare your supabase/01-blog.sql file with this complete reference:
create table public.posts (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  content text not null,
  author_id uuid references auth.users (id) on delete set null default auth.uid()
);

alter table public.posts enable row level security;

revoke all on table public.posts from anon, authenticated;
grant select on table public.posts to anon, authenticated;

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

insert into public.posts (title, content)
values (
  'Welcome to the RBAC Blog',
  'This post is public, but changing blog content requires an authorized role.'
);

This reference contains the complete migration for the public blog feed.

Connect Express to Supabase

The API needs a reusable database client built from the publishable key in .env. This client represents public requests until a signed-in user's token is added later.

  • Create a folder named src inside supabase-rbac-blog by using the new-folder control in Visual Studio Code's file sidebar.
  • Create supabase.js inside the src folder by using the new-file control.

You'll see src/supabase.js in the file sidebar.

  • Create the public database client by pasting this code into src/supabase.js:
import { createClient } from '@supabase/supabase-js'

const supabaseUrl = process.env.SUPABASE_URL
const supabasePublishableKey = process.env.SUPABASE_PUBLISHABLE_KEY

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

const authOptions = {
  auth: {
    autoRefreshToken: false,
    persistSession: false,
    detectSessionInUrl: false,
  },
}

export const publicSupabase = createClient(
  supabaseUrl,
  supabasePublishableKey,
  authOptions
)

What does this client do?

  • The environment variables provide the project address plus its publishable key.
  • The key check stops the API when either required value is missing.
  • The auth options prevent a server process from storing browser-style sessions.
  • The exported publicSupabase client sends unsigned database requests as the public role.
  • Save src/supabase.js.
  • Select the problems view in Visual Studio Code's bottom panel to check the file.

You'll see no JavaScript syntax errors for src/supabase.js.

Seeing a client setup problem?

Check that .env contains both SUPABASE_URL plus SUPABASE_PUBLISHABLE_KEY on separate lines.

Check that the import uses the installed Supabase package name exactly. Help me fix my Supabase client.

✔️ Awesome, I've got everything!

Your public Supabase client is ready for the API routes.

ⓧ I'd like to double check the full code

  • Compare your src/supabase.js file with this complete reference:
import { createClient } from '@supabase/supabase-js'

const supabaseUrl = process.env.SUPABASE_URL
const supabasePublishableKey = process.env.SUPABASE_PUBLISHABLE_KEY

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

const authOptions = {
  auth: {
    autoRefreshToken: false,
    persistSession: false,
    detectSessionInUrl: false,
  },
}

export const publicSupabase = createClient(
  supabaseUrl,
  supabasePublishableKey,
  authOptions
)

This reference matches the complete public client used by the API.

The database client is ready. The next file turns it into a local REST API with a public endpoint.

  • Create server.js inside the existing src folder by using the new-file control.

You'll see src/server.js beside src/supabase.js.

  • Build the first runnable version of the public feed by pasting this code into src/server.js:
import express from 'express'
import { publicSupabase } from './supabase.js'

const app = express()
const port = 3000

app.use(express.json())

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

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

  return res.json(data)
})

app.listen(port, () => {
  console.log(`Blog API listening on http://localhost:${port}`)
})

How does the public feed work?

  • Express parses incoming JSON request bodies.
  • The GET /posts route asks publicSupabase for the four post columns.
  • The route returns the database rows as JSON when the query succeeds.
  • The listener keeps the API available on port 3000.
  • Save src/server.js.
  • Start the API from the project terminal by running:
npm start

What starts here?

The start script loads .env before running src/server.js. You'll see Blog API listening on http://localhost:3000 when the server is ready.

Keep this terminal running because it now hosts your local API.

Before you run the first request, what do you expect the public feed to contain? Hold that prediction.

  • Create a second PowerShell terminal by using the new-terminal control in Visual Studio Code's terminal panel.
  • Request the public post feed from the second terminal by running:
Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Get

Your public feed is live

The command sends a GET request without a login token. You'll see Welcome to the RBAC Blog plus its public content in the response.

You have a working public blog feed. Anyone who can reach the API can now read its posts.

Can't load the public feed?

Confirm that the first terminal still shows the running API. Check that the request uses port 3000.

If Supabase returns a database error, confirm that the migration completed successfully. Help me debug the public feed.

Test the anonymous write barrier

The successful read proves that the public client can access the feed. Now the same client will attempt to publish a post through a temporary route.

  • In src/server.js locate the app.listen block near the bottom of the file.
  • Insert this temporary publishing route directly above the app.listen block:
app.post('/posts', async (req, res) => {
  const { title, content } = req.body
  const { data, error } = await publicSupabase
    .from('posts')
    .insert({ title, content })
    .select('id, title, content, author_id')

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

  return res.status(201).json(data[0])
})

What does this route attempt?

  • The route reads a title plus content from the JSON request body.
  • The public client sends those values to the posts table.
  • A successful insert would return the new row with status 201.
  • A database rejection returns status 403 through this temporary handler.
  • Save src/server.js.
  • Stop the running API by pressing Ctrl+C in its terminal.
  • Restart the API with the new route by running:
npm start

The new route is loaded

Restarting the process loads the saved POST /posts handler. You'll see the local API address again when it is ready.

Before you send this request, can an unsigned public client publish after receiving read access? Hold that prediction.

  • Attempt to publish from the second PowerShell terminal by running:
Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Post -ContentType 'application/json' -Body '{"title":"Anonymous post","content":"Published from the public client."}'

The database blocked the write

PowerShell reports a 403 response. Supabase rejected the insert because anon has no INSERT grant.

The table also has no INSERT policy for anonymous requests. This is the exact write barrier that authentication plus role permissions must solve.

Did you get a different result?

If the route is missing, confirm that you restarted the server after saving src/server.js. If a new row was created, review the table grants plus policies in your Supabase project.

If the request failed before reaching the API, check the JSON body plus the local server address. Help me debug the anonymous write test.

✔️ Awesome, I've got everything!

Your API now serves public posts while the database rejects anonymous publishing.

ⓧ I'd like to double check the full code

  • Compare your src/server.js file with this complete reference:
import express from 'express'
import { publicSupabase } from './supabase.js'

const app = express()
const port = 3000

app.use(express.json())

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

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

  return res.json(data)
})

app.post('/posts', async (req, res) => {
  const { title, content } = req.body
  const { data, error } = await publicSupabase
    .from('posts')
    .insert({ title, content })
    .select('id, title, content, author_id')

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

  return res.status(201).json(data[0])
})

app.listen(port, () => {
  console.log(`Blog API listening on http://localhost:${port}`)
})

This reference matches the complete temporary API used for the public read plus anonymous write test.

Your public feed works while unauthorized publishing stays blocked. Next, you'll create legitimate editor plus admin paths without weakening the database boundary.

Configure Roles and Permissions

Your public feed works, but the failed anonymous write exposed a missing security boundary. The database needs a legitimate path for trusted users to change posts.

In this step, Supabase will add editor and admin roles through role-based access control. Postgres grants and Row Level Security policies will enforce each permission inside the database.

In this step, get ready to:
  • Create editor and admin roles with separate post permissions.
  • Enable the Custom Access Token Hook for role claims.
  • Create two confirmed test users with assigned roles.
Define roles and permission data

The blocked insert needs two layers of approval. A database grant allows an operation to be attempted, while an RLS policy decides whether the signed-in role may perform it.

  • In Visual Studio Code's file sidebar, create 02-rbac.sql inside the existing supabase folder.
  • Add the role model to supabase/02-rbac.sql by pasting this code:
create type public.app_role as enum ('editor', 'admin');
create type public.app_permission as enum (
  'posts.create',
  'posts.update',
  'posts.delete'
);

create table public.user_roles (
  id bigint generated by default as identity primary key,
  user_id uuid references auth.users on delete cascade not null unique,
  role public.app_role not null
);

create table public.role_permissions (
  id bigint generated by default as identity primary key,
  role public.app_role not null,
  permission public.app_permission not null,
  unique (role, permission)
);

insert into public.role_permissions (role, permission)
values
  ('editor', 'posts.create'),
  ('editor', 'posts.update'),
  ('admin', 'posts.create'),
  ('admin', 'posts.update'),
  ('admin', 'posts.delete');

What does this role model do?

  • The app_role enum limits account roles to editor or admin.
  • The app_permission enum defines the three protected post operations.
  • The mapping rows allow editors to create and update posts. Admins also receive delete permission.
  • Save supabase/02-rbac.sql.
  • Copy the SQL block you just added.
  • Switch back to the Supabase SQL Editor from earlier.
  • Replace the query text with the copied SQL.
  • Execute the query from the SQL Editor.

You should see a success confirmation. The role types, mapping tables, and five permission rows now exist.

Role objects already exist?

This usually means the same block ran more than once. Continue only after confirming the five mappings were inserted once.

Ask for help with the duplicate objects:

  • Append the role-table protections to supabase/02-rbac.sql by pasting this code:
alter table public.user_roles enable row level security;
alter table public.role_permissions enable row level security;

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

grant all on table public.user_roles to supabase_auth_admin;

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

Why protect the role tables?

Role assignments control every later authorization decision. RLS and revoked client access prevent ordinary API users from reading or changing those assignments.

The Auth service receives the access it needs to read a user's assigned role when issuing a token.

  • Save supabase/02-rbac.sql.
  • Copy the SQL block you just added.
  • Replace the current SQL Editor query with the copied SQL.
  • Execute the query from the SQL Editor.

You should see a success confirmation. Both mapping tables now have RLS enabled.

Role-table security failed?

Confirm the first SQL block created public.user_roles and public.role_permissions before this block ran.

Help me diagnose the role-table setup:

  • Append the token hook to supabase/02-rbac.sql by pasting this code:
create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
stable
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 to supabase_auth_admin;
revoke execute on function public.custom_access_token_hook from authenticated, anon, public;

How does the token hook work?

  • The hook looks up the signing user's role in public.user_roles.
  • It adds that role to the token as the user_role claim.
  • Only the Supabase Auth service can execute the hook.
  • Save supabase/02-rbac.sql.
  • Copy the token-hook SQL block.
  • Replace the current SQL Editor query with the copied SQL.
  • Execute the query from the SQL Editor.

You should see a success confirmation. The database can now add a role claim when Supabase issues a token.

Token hook creation failed?

Confirm public.app_role and public.user_roles already exist. The function depends on both objects.

Help me debug the token-hook SQL:

  • Append the authorization helper to supabase/02-rbac.sql by pasting this code:
create schema if not exists private;
revoke all on schema private from public;
grant usage on schema private to authenticated;

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

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

  return matched_permissions > 0;
end;
$$;

Why use a private authorization helper?

The private.authorize() function reads the role claim from the request's JWT. It returns whether that role has the requested permission.

The private schema keeps this security helper outside the exposed API schema.

  • Save supabase/02-rbac.sql.
  • Copy the authorization-helper SQL block.
  • Replace the current SQL Editor query with the copied SQL.
  • Execute the query from the SQL Editor.

You should see a success confirmation. Authenticated requests can now be evaluated against the permission mappings.

Authorization helper failed?

Check that the earlier block inserted rows into public.role_permissions. Confirm the SQL uses the schema-qualified names shown above.

Help me diagnose the authorization helper:

  • Append the helper and table grants to supabase/02-rbac.sql by pasting this code:
revoke all on function private.authorize(public.app_permission) from public;
grant execute on function private.authorize(public.app_permission) to authenticated;

grant insert (title, content) on table public.posts to authenticated;
grant update (title, content) on table public.posts to authenticated;
grant delete on table public.posts to authenticated;

What do these grants allow?

Authenticated users may attempt create, update, and delete operations on posts. The RLS policies in the next block still make the final authorization decision.

Anonymous users keep read-only access because they receive none of these write grants.

  • Save supabase/02-rbac.sql.
  • Copy the grant SQL block.
  • Replace the current SQL Editor query with the copied SQL.
  • Execute the query from the SQL Editor.

You should see a success confirmation. Authenticated requests may now reach the write policies.

Post grants failed?

Confirm public.posts still contains the title and content columns created earlier.

Help me fix the post grants:

  • Finish supabase/02-rbac.sql by appending the role-based policies:
create policy "Roles can create posts"
on public.posts for insert
to authenticated
with check (
  (select private.authorize('posts.create'))
  and author_id = (select auth.uid())
);

create policy "Roles can update posts"
on public.posts for update
to authenticated
using ((select private.authorize('posts.update')))
with check ((select private.authorize('posts.update')));

create policy "Admins can delete posts"
on public.posts for delete
to authenticated
using ((select private.authorize('posts.delete')));

How do the policies divide access?

  • Create access requires the posts.create permission. The new row must also belong to the signed-in user.
  • Update access requires the posts.update permission.
  • Delete access requires the posts.delete permission, which only the admin role receives.
  • Save supabase/02-rbac.sql.
  • Copy the policy SQL block.
  • Replace the current SQL Editor query with the copied SQL.
  • Execute the query from the SQL Editor.

You should see a success confirmation. The posts table now has separate create, update, and delete policies.

Policy creation failed?

Confirm the public read policy from supabase/01-blog.sql still exists. Updates require that companion read access.

Help me debug the post policies:

✔️ Awesome, I've got everything!

Great. Double-check that supabase/02-rbac.sql is saved before you configure the hook.

ⓧ I'd like to double check the full code

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

create table public.user_roles (
  id bigint generated by default as identity primary key,
  user_id uuid references auth.users on delete cascade not null unique,
  role public.app_role not null
);

create table public.role_permissions (
  id bigint generated by default as identity primary key,
  role public.app_role not null,
  permission public.app_permission not null,
  unique (role, permission)
);

insert into public.role_permissions (role, permission)
values
  ('editor', 'posts.create'),
  ('editor', 'posts.update'),
  ('admin', 'posts.create'),
  ('admin', 'posts.update'),
  ('admin', 'posts.delete');

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

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

grant all on table public.user_roles to supabase_auth_admin;

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

create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
stable
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 to supabase_auth_admin;
revoke execute on function public.custom_access_token_hook from authenticated, anon, public;

create schema if not exists private;
revoke all on schema private from public;
grant usage on schema private to authenticated;

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

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

  return matched_permissions > 0;
end;
$$;

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

grant insert (title, content) on table public.posts to authenticated;
grant update (title, content) on table public.posts to authenticated;
grant delete on table public.posts to authenticated;

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

create policy "Roles can update posts"
on public.posts for update
to authenticated
using ((select private.authorize('posts.update')))
with check ((select private.authorize('posts.update')));

create policy "Admins can delete posts"
on public.posts for delete
to authenticated
using ((select private.authorize('posts.delete')));
Activate role claims and prepare request clients

The database function exists, but Supabase Auth must be configured to call it when issuing tokens. Each new token can then carry the user's user_role claim.

  • Return to your Supabase project dashboard.
  • Select Authentication from the project navigation.
  • Select Hooks.
  • Choose the Custom Access Token hook type.
  • Select public.custom_access_token_hook as the Postgres function.
  • Confirm the hook configuration so it becomes active.

You should see the Custom Access Token Hook enabled with public.custom_access_token_hook selected.

Function missing from the hook list?

Return to the SQL Editor and confirm the token-hook block completed successfully. The function also needs its Auth service grants from the same block.

Help me find the missing hook function:

Authenticated routes also need clients for token verification and user-scoped database requests. These factories preserve the existing public client while preparing that request path.

  • Return to src/supabase.js in Visual Studio Code.
  • Add the client factories below the existing publicSupabase export by pasting this code:
export function createAuthSupabase() {
  return createClient(supabaseUrl, supabasePublishableKey, authOptions)
}

export function createUserSupabase(token) {
  return createClient(supabaseUrl, supabasePublishableKey, {
    ...authOptions,
    global: {
      headers: {
        Authorization: `Bearer ${token}`,
      },
    },
  })
}

What do these clients do?

  • The createAuthSupabase() factory creates a client for verifying a supplied access token.
  • The createUserSupabase() factory forwards that token through the Authorization header.
  • Supabase can use the forwarded user context when evaluating the new RLS policies.
  • Save src/supabase.js.
  • Return to the PowerShell terminal running the API.
  • Press Ctrl+C to stop the running process.
  • Restart the API from the project folder by running:
npm start

What does this check prove?

The start script reloads src/supabase.js through the existing Express server. A successful listener message confirms that the new exports contain valid JavaScript.

You should see Blog API listening on http://localhost:3000 in PowerShell.

API no longer starts?

Check that both functions sit after the closing parenthesis of the existing publicSupabase declaration. Compare every brace and comma with the full file below.

Help me fix the client factories:

✔️ Awesome, I've got everything!

Your public client still loads, and both authenticated client factories are ready.

ⓧ I'd like to double check the full code

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

const supabaseUrl = process.env.SUPABASE_URL
const supabasePublishableKey = process.env.SUPABASE_PUBLISHABLE_KEY

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

const authOptions = {
  auth: {
    autoRefreshToken: false,
    persistSession: false,
    detectSessionInUrl: false,
  },
}

export const publicSupabase = createClient(
  supabaseUrl,
  supabasePublishableKey,
  authOptions
)

export function createAuthSupabase() {
  return createClient(supabaseUrl, supabasePublishableKey, authOptions)
}

export function createUserSupabase(token) {
  return createClient(supabaseUrl, supabasePublishableKey, {
    ...authOptions,
    global: {
      headers: {
        Authorization: `Bearer ${token}`,
      },
    },
  })
}
Create the editor and admin users

The role tables are ready, but they need real user IDs to connect each test account to a role. A one-time server-side script creates confirmed users with the temporary secret key.

Why isolate the secret key?

The secret key bypasses RLS, so the running API must never load it. The setup script reads it from .env.setup for this one provisioning task.

  • In Visual Studio Code's file sidebar, create create-test-users.js inside the existing scripts folder.
  • Add the admin client and test-account definitions by pasting this code into scripts/create-test-users.js:
import { createClient } from '@supabase/supabase-js'

const supabaseUrl = process.env.SUPABASE_URL
const supabaseSecretKey = process.env.SUPABASE_SECRET_KEY

if (!supabaseUrl || !supabaseSecretKey) {
  throw new Error('SUPABASE_URL and SUPABASE_SECRET_KEY are required')
}

const supabaseAdmin = createClient(supabaseUrl, supabaseSecretKey, {
  auth: {
    autoRefreshToken: false,
    persistSession: false,
    detectSessionInUrl: false,
  },
})

const accounts = [
  {
    email: 'editor@example.com',
    password: 'EditorPass123!',
    role: 'editor',
  },
  {
    email: 'admin@example.com',
    password: 'AdminPass123!',
    role: 'admin',
  },
]

What does this setup hold?

  • The script reads the project URL and temporary secret key from the setup environment.
  • The admin client disables browser session behavior because this is a one-time server-side task.
  • The accounts array pairs each confirmed test account with its intended role.
  • Save scripts/create-test-users.js.
  • Confirm create-test-users.js appears inside the scripts folder in the file sidebar.

Setup file in the wrong folder?

Confirm the path is exactly scripts/create-test-users.js. The existing setup-users package script points to that location.

Help me check the setup-file path:

  • Complete scripts/create-test-users.js by appending the provisioning loop:
for (const account of accounts) {
  const { data, error } = await supabaseAdmin.auth.admin.createUser({
    email: account.email,
    password: account.password,
    email_confirm: true,
  })

  if (error) {
    throw new Error(`Could not create ${account.email}: ${error.message}`)
  }

  const { error: roleError } = await supabaseAdmin
    .from('user_roles')
    .insert({ user_id: data.user.id, role: account.role })

  if (roleError) {
    throw new Error(`Could not assign ${account.role}: ${roleError.message}`)
  }

  console.log(`Created ${account.email} as ${account.role}`)
}

How does provisioning work?

  • The loop creates each account with email confirmation already completed.
  • It inserts the returned user ID with the account's role into user_roles.
  • The script stops if either user creation or role assignment fails.
  • Save scripts/create-test-users.js.
  • Create a second PowerShell terminal in Visual Studio Code for the one-time setup task.

Before you run this, consider this question: which two role assignments will PowerShell confirm.

  • Create the confirmed users and assign their roles by running:
npm run setup-users

What does this command do?

The package script loads .env.setup and executes scripts/create-test-users.js. The secret key exists only inside that one-time process.

You should see Created editor@example.com as editor and Created admin@example.com as admin in PowerShell.

User setup failed?

Confirm .env.setup contains your project URL and temporary secret key. Confirm the token hook and role tables were configured before rerunning the script.

Help me diagnose the setup script:

That is the provisioning path working: both confirmed users now have database-backed roles.

  • Return to your Supabase project dashboard.
  • Select Authentication from the project navigation.
  • Select Users.

You should see editor@example.com and admin@example.com in the users list.

This cleanup is deliberate: deleting .env.setup removes the privileged key from the working project without deleting your Supabase project.

  • In Visual Studio Code's file sidebar, right-click .env.setup.
  • Choose the deletion option.
  • Confirm the deletion if prompted.

You should no longer see .env.setup in the project file list. The normal API process can only load the publishable key from .env.

✔️ Awesome, I've got everything!

Both users are provisioned, their roles are assigned, and the temporary setup environment is deleted.

ⓧ I'd like to double check the full code

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

const supabaseUrl = process.env.SUPABASE_URL
const supabaseSecretKey = process.env.SUPABASE_SECRET_KEY

if (!supabaseUrl || !supabaseSecretKey) {
  throw new Error('SUPABASE_URL and SUPABASE_SECRET_KEY are required')
}

const supabaseAdmin = createClient(supabaseUrl, supabaseSecretKey, {
  auth: {
    autoRefreshToken: false,
    persistSession: false,
    detectSessionInUrl: false,
  },
})

const accounts = [
  {
    email: 'editor@example.com',
    password: 'EditorPass123!',
    role: 'editor',
  },
  {
    email: 'admin@example.com',
    password: 'AdminPass123!',
    role: 'admin',
  },
]

for (const account of accounts) {
  const { data, error } = await supabaseAdmin.auth.admin.createUser({
    email: account.email,
    password: account.password,
    email_confirm: true,
  })

  if (error) {
    throw new Error(`Could not create ${account.email}: ${error.message}`)
  }

  const { error: roleError } = await supabaseAdmin
    .from('user_roles')
    .insert({ user_id: data.user.id, role: account.role })

  if (roleError) {
    throw new Error(`Could not assign ${account.role}: ${roleError.message}`)
  }

  console.log(`Created ${account.email} as ${account.role}`)
}

Your database now distinguishes editors from admins without exposing a key that bypasses RLS. Next, you will verify bearer tokens in Express and route each write through the signed-in user's Supabase client.

Route Authenticated Writes Through Express

Your Supabase database now distinguishes editors from admins. The local API still sends write requests through its public client.

This step gives Express an authentication path for verifying each bearer token. Each protected request carries its signed-in user's JWT to the database so Row Level Security can make the final authorization decision.

In this step, get ready to:
  • Add login and bearer-token verification to the Express API.
  • Route create, update, and delete operations through a user-scoped Supabase client.
  • Prove that an editor can create and update a post while receiving a 403 response for deletion.
Add login and token verification

Login exchanges an email and password for an access token. Authentication middleware verifies that token before a protected handler runs.

  • In the VS Code Explorer from earlier, select src/server.js.
  • Replace the Supabase import at the top of src/server.js with this import block:
import {
  createAuthSupabase,
  createUserSupabase,
  publicSupabase,
} from './supabase.js'

What does this import add?

  • The existing publicSupabase client keeps public reads available.
  • The createAuthSupabase() helper creates a client for login and token verification.
  • The createUserSupabase() helper creates a database client carrying one user's access token.
  • Insert the login route below app.use(express.json()) by copying this code:
app.post('/auth/login', async (req, res) => {
  const { email, password } = req.body

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

  const authSupabase = createAuthSupabase()
  const { data, error } = await authSupabase.auth.signInWithPassword({
    email,
    password,
  })

  if (error || !data.session) {
    return res.status(401).json({ error: 'Invalid login' })
  }

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

How does login work?

  • The route validates that both credentials arrived as strings.
  • The signInWithPassword() call asks Supabase Auth to check the credentials.
  • A successful response returns the access token and authenticated user ID to the client.
  • Save src/server.js.
  • Stop the API process from earlier by pressing Ctrl+C in its PowerShell window.
  • Restart the API from the supabase-rbac-blog folder by running:
npm start

What should you see?

The start script loads .env before running src/server.js. You should see the API listening at http://localhost:3000.

  • Press the Windows key to open Windows search.
  • Type PowerShell into the search field.
  • Press Enter to open a second PowerShell window.
  • Test the new login route with the editor account by running:
Invoke-RestMethod -Uri 'http://localhost:3000/auth/login' -Method Post -ContentType 'application/json' -Body '{"email":"editor@example.com","password":"EditorPass123!"}'

What does this request prove?

The request sends the confirmed editor credentials to POST /auth/login. A successful response contains an access_token and a user_id.

Your editor can now authenticate through the API. The token is the identity proof that later write requests carry to Supabase.

Login request rejected?

Confirm that the API terminal is still running. Check that the editor user exists in Supabase Authentication with the email from the setup script.

If the response mentions an invalid login, confirm that you copied the test password exactly.

Ask for help with the login request.

A login proves the credentials are valid. Protected routes also need middleware that verifies the returned token on every request.

  • Insert the authentication middleware below app.use(express.json()) by copying this function:
async function requireAuth(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).trim()
  const authSupabase = createAuthSupabase()
  const { data, error } = await authSupabase.auth.getUser(token)

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

  req.user = data.user
  req.supabase = createUserSupabase(token)
  next()
}

How does the middleware protect a route?

  • The middleware requires an authorization header beginning with Bearer .
  • The auth.getUser(token) call verifies the access token with Supabase Auth.
  • The verified user is stored in req.user for the current request.
  • The token-scoped database client is stored in req.supabase for protected handlers.
  • Insert the database error mapper directly below requireAuth() by copying this function:
function databaseErrorStatus(error) {
  return error?.code === '42501' ? 403 : 400
}

Why map database errors?

Postgres uses error code 42501 when a database role lacks permission. The API converts that authorization failure into an HTTP 403 response.

  • Save src/server.js.
  • Stop the current API process by pressing Ctrl+C in its PowerShell window.
  • Confirm that the new middleware has valid syntax by restarting the API:
npm start

What does this restart confirm?

You should see the listening message again. That output confirms Node.js loaded the new middleware and error mapper without a syntax error.

API stopped during startup?

Check that both helper functions sit outside every route handler. Confirm that each closing brace matches the reference snippets.

Ask for help with the middleware syntax.

Protect the write handlers

Each write handler now uses requireAuth before touching the database. The handler passes the request to Supabase through req.supabase so the user's grants and RLS policies remain authoritative.

  • Replace the existing public read route in src/server.js with this final version:
app.get('/posts', async (req, res) => {
  const { data, error } = await publicSupabase
    .from('posts')
    .select('id, title, content, author_id')

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

  return res.json(data)
})

Why does the read route stay public?

The route still uses publicSupabase without authentication middleware. Anyone can read posts because the database grants and SELECT policy allow public access.

  • Replace the temporary anonymous post route with this protected handler:
app.post('/posts', requireAuth, async (req, res) => {
  const { title, content } = req.body

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

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

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

  return res.status(201).json(data[0])
})

How is post creation authorized?

  • The requireAuth middleware rejects requests without a valid access token.
  • The handler validates the title and content before querying the database.
  • The insert runs through req.supabase with the editor's token attached.
  • Postgres grants and the create policy decide whether the insert is allowed.
  • Save src/server.js.
  • Stop the current API process by pressing Ctrl+C.
  • Confirm that the final read and create routes load by restarting the API:
npm start

What should you see?

You should see the API listening at http://localhost:3000. The successful restart confirms both replacement routes loaded.

Server reports a route error?

Confirm that the temporary POST /posts handler was replaced completely. Two handlers for the same path can hide which version is processing the request.

Ask for help comparing the route definitions.

The update handler has two responsibilities. It first builds a safe set of changes from the request body.

  • Insert the first half of the update handler below the protected post route:
app.patch('/posts/:id', requireAuth, async (req, res) => {
  const updates = {}

  if (typeof req.body.title === 'string') {
    updates.title = req.body.title
  }

  if (typeof req.body.content === 'string') {
    updates.content = req.body.content
  }

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

What does the first half validate?

The updates object receives only string values for the title or content. An empty update receives a 400 response before reaching the database.

  • Complete the same update handler by placing this second half directly below the first half:

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

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

  if (data.length === 0) {
    return res.status(403).json({ error: 'Update not allowed or post not found' })
  }

  return res.json(data[0])
})

How does the update reach the correct post?

  • The eq() filter targets the post ID from the route parameter.
  • The database returns the updated row when the policy allows the change.
  • An empty result becomes a 403 response because the requested row was unavailable to this user.
  • Save src/server.js.
  • Stop the API process by pressing Ctrl+C.
  • Confirm that the completed update handler loads by restarting the API:
npm start

What does this check prove?

You should see the listening message. Node.js has parsed both halves as one complete PATCH /posts/:id handler.

Seeing a syntax error near the update route?

Confirm that the second half sits inside the update handler. Keep one closing }) at the end of the complete route.

Ask for help joining the update handler.

  • Insert the protected delete handler below the completed update route:
app.delete('/posts/:id', requireAuth, async (req, res) => {
  const { data, error } = await req.supabase
    .from('posts')
    .delete()
    .eq('id', req.params.id)
    .select('id, title, content, author_id')

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

  if (data.length === 0) {
    return res.status(403).json({ error: 'Delete not allowed or post not found' })
  }

  return res.json(data[0])
})

How is deletion decided?

The handler submits the delete through the user's scoped client. The database allows the operation only when the token's role has the delete permission.

  • Insert the final error middleware directly above app.listen() by copying this code:
app.use((error, req, res, next) => {
  console.error(error)
  res.status(500).json({ error: 'Internal server error' })
})

What does the final middleware catch?

Express sends unexpected route errors to this middleware. The client receives a controlled 500 response while the terminal records the underlying error.

  • Save the completed src/server.js file.
  • Stop the current API process by pressing Ctrl+C.
  • Restart the completed API by running:
npm start

What should you see?

You should see Blog API listening on http://localhost:3000. The completed authentication and write routes are now running.

Completed server does not start?

Compare the order of your routes with the full-file reference below. Keep the error middleware after the routes and before app.listen().

Ask for help checking the final server structure.

✔️ Awesome, I've got everything!

Your src/server.js now authenticates users and sends protected writes through a token-scoped Supabase client.

ⓧ I'd like to double check the full code

import express from 'express'
import {
  createAuthSupabase,
  createUserSupabase,
  publicSupabase,
} from './supabase.js'

const app = express()
const port = 3000

app.use(express.json())

async function requireAuth(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).trim()
  const authSupabase = createAuthSupabase()
  const { data, error } = await authSupabase.auth.getUser(token)

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

  req.user = data.user
  req.supabase = createUserSupabase(token)
  next()
}

function databaseErrorStatus(error) {
  return error?.code === '42501' ? 403 : 400
}

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

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

  const authSupabase = createAuthSupabase()
  const { data, error } = await authSupabase.auth.signInWithPassword({
    email,
    password,
  })

  if (error || !data.session) {
    return res.status(401).json({ error: 'Invalid login' })
  }

  return 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')

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

  return res.json(data)
})

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

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

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

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

  return res.status(201).json(data[0])
})

app.patch('/posts/:id', requireAuth, async (req, res) => {
  const updates = {}

  if (typeof req.body.title === 'string') {
    updates.title = req.body.title
  }

  if (typeof req.body.content === 'string') {
    updates.content = req.body.content
  }

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

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

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

  if (data.length === 0) {
    return res.status(403).json({ error: 'Update not allowed or post not found' })
  }

  return res.json(data[0])
})

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

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

  if (data.length === 0) {
    return res.status(403).json({ error: 'Delete not allowed or post not found' })
  }

  return res.json(data[0])
})

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

app.listen(port, () => {
  console.log(`Blog API listening on http://localhost:${port}`)
})
Verify the editor permissions

The completed API can now authenticate the editor and forward that identity to Supabase. You will keep the access token inside a PowerShell variable while testing each protected route.

  • Keep the API terminal running.
  • Return to the second PowerShell window from earlier.
  • Log in as the editor and store the returned token by running:
$editorLogin = Invoke-RestMethod -Uri 'http://localhost:3000/auth/login' -Method Post -ContentType 'application/json' -Body '{"email":"editor@example.com","password":"EditorPass123!"}'
$editorToken = $editorLogin.access_token
$headers = @{ Authorization = "Bearer $editorToken" }

What do these variables hold?

  • The $editorLogin variable holds the login response.
  • The $editorToken variable holds the access token without printing it.
  • The $headers variable prepares the authorization header for each protected request.
  • Create an editor-owned post and display the returned row by running:
$editorPost = Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Post -Headers $headers -ContentType 'application/json' -Body '{"title":"Editor draft","content":"Created through the protected API."}'
$editorPost

What does the created row prove?

The response contains the new post's ID, title, content, and author ID. The editor's token passed authentication plus the database create policy.

You should see Editor draft in the returned post data. PowerShell keeps the post ID inside $editorPost for the next request.

Post creation denied?

Confirm that $headers was created in the same PowerShell window. Sign in again if the token is missing or expired.

If the request still receives a 403 response, confirm that the editor role has posts.create in the permission table.

Ask for help tracing the create request.

  • Update the same post and display the returned row by running:
$editorPost = Invoke-RestMethod -Uri "http://localhost:3000/posts/$($editorPost.id)" -Method Patch -Headers $headers -ContentType 'application/json' -Body '{"title":"Published by editor"}'
$editorPost

What does the update prove?

The request targets the ID returned by the create call. The editor role passes the database update permission and receives the changed row.

You should see Published by editor as the title. The create and update paths now work through the editor's token.

  • Pause for a prediction before the final check. Decide whether the editor role passes the database delete policy.
  • Send the delete request with the same editor token by running:
Invoke-RestMethod -Uri "http://localhost:3000/posts/$($editorPost.id)" -Method Delete -Headers $headers

Why is this rejection expected?

The request carries a valid token, so authentication succeeds. The editor role lacks posts.delete, so the database denies the operation.

PowerShell reports a 403 response. The editor-owned post remains in the database.

Delete unexpectedly succeeded?

Confirm that $headers still contains the editor token. Check that the delete request uses $editorPost.id from the editor-created post.

Confirm that the Admins can delete posts policy is the active delete policy on public.posts.

Ask for help investigating the unexpected delete.

That rejection is a security success. Your editor can publish and revise content while the database blocks deletion with a 403 response.

Your authenticated write path is complete. Next, you will test every public, editor, and admin outcome as one complete access matrix.

Prove the Access Matrix

Your final Express API now carries each signed-in user's identity into Supabase. The editor can publish content. Supabase blocks the same editor from deleting it.

A complete authorization test checks every actor against every protected action. You'll use PowerShell to exercise the finished access matrix. The results put every security boundary side by side.

In this step, get ready to:
  • Test requests that do not include a bearer token.
  • Compare editor permissions with admin permissions.
  • Record the completed access matrix after the final public read.
Test requests without a token

Requests without a bearer token reveal the API's outer boundary. Public reads pass through without a login.

  • Return to the PowerShell window from your editor test in the previous step.

Before you send both requests, which one do you think can reach the database without a token?

  • Test the public read and anonymous write paths by running this sequence:
$publicPosts = Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Get
$publicPosts

try {
  Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Post -ContentType 'application/json' -Body '{"title":"Anonymous post","content":"No token is attached."}'
} catch {
  [int]$_.Exception.Response.StatusCode
}

What does this sequence test?

  • The first request calls GET /posts without an authorization header. The public client reads rows through the SELECT grant and policy.
  • The second request calls POST /posts without a token. requireAuth rejects it before the insert reaches the database.
  • The catch block turns the rejected request into a visible status code.

You'll see the public posts followed by 401. This proves that reading stays public while anonymous writing requires authentication.

Seeing a connection or status mismatch?

  • Confirm that the API terminal from earlier still shows the local server listening on port 3000.
  • Check that the anonymous request does not include -Headers.
  • Confirm that the request URL ends with /posts.

Help me diagnose unexpected results from the public GET and anonymous POST requests.

Compare editor and admin decisions

A successful login returns a JWT containing the user's role claim. The same Express handlers then rely on Postgres grants and Row Level Security to decide which writes succeed.

Before you run the editor sequence, which operation do you think the database will stop?

  • Run the editor's create, update, and delete decisions with this sequence:
$editorLogin = Invoke-RestMethod -Uri 'http://localhost:3000/auth/login' -Method Post -ContentType 'application/json' -Body '{"email":"editor@example.com","password":"EditorPass123!"}'
$editorToken = $editorLogin.access_token
$editorHeaders = @{ Authorization = "Bearer $editorToken" }

$editorPost = Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Post -Headers $editorHeaders -ContentType 'application/json' -Body '{"title":"Access matrix post","content":"Created by the editor."}'
$postId = $editorPost.id
$editorPost

$updatedPost = Invoke-RestMethod -Uri "http://localhost:3000/posts/$postId" -Method Patch -Headers $editorHeaders -ContentType 'application/json' -Body '{"title":"Access matrix post updated"}'
$updatedPost

try {
  Invoke-RestMethod -Uri "http://localhost:3000/posts/$postId" -Method Delete -Headers $editorHeaders
} catch {
  [int]$_.Exception.Response.StatusCode
}

How does the editor test work?

  • The login response supplies access_token. $editorToken stores it for the remaining requests.
  • The authorization header identifies the editor to requireAuth and the user-scoped Supabase client.
  • The create request stores a new post ID in $postId. The update and delete requests target that exact row.
  • The role policies allow posts.create and posts.update for editors. The editor has no posts.delete permission.

You'll see the created post followed by its updated title. The final request prints 403 because database authorization rejects the editor's delete.

Editor requests not matching the matrix?

  • Sign in again if the login request returns an authentication error.
  • Confirm that the Custom Access Token Hook remains enabled for public.custom_access_token_hook.
  • Check that $postId contains the ID returned by the editor's create request.

Help me debug the editor create, update, and denied delete results.

The editor's denied delete leaves the post available for the admin test. Both users call the same route, so the role claim is the deciding difference.

Before you reuse the post ID, do you expect the admin delete to follow the editor result?

  • Delete the editor's post as the admin by running this sequence:
$adminLogin = Invoke-RestMethod -Uri 'http://localhost:3000/auth/login' -Method Post -ContentType 'application/json' -Body '{"email":"admin@example.com","password":"AdminPass123!"}'
$adminToken = $adminLogin.access_token
$adminHeaders = @{ Authorization = "Bearer $adminToken" }

$deletedPost = Invoke-RestMethod -Uri "http://localhost:3000/posts/$postId" -Method Delete -Headers $adminHeaders
$deletedPost

Why does the admin request succeed?

  • The admin login produces a separate token stored in $adminToken.
  • The admin request targets the same DELETE /posts/:id route used by the editor.
  • The database reads the admin role claim. The delete policy authorizes posts.delete for that role.

You'll see the deleted post returned as an object. That response proves the admin passed the database's delete policy.

Admin delete still rejected?

  • Sign in again if the admin request returns 401.
  • Confirm that admin@example.com has the admin entry in public.user_roles.
  • Check that the delete request uses $adminHeaders.

Help me trace an unexpected admin delete failure.

Confirm the final public feed

The final read checks two boundaries at once. Public access must still work after an authenticated delete, while the deleted row must stay absent.

Before this last request, do you expect the access matrix post to remain in the feed?

  • Check the public feed one last time by running this command:
$finalPosts = Invoke-RestMethod -Uri 'http://localhost:3000/posts' -Method Get
$finalPosts

What does the final read prove?

  • The request sends no bearer token. The public SELECT grant and policy still return the readable posts.
  • The post titled Access matrix post updated is absent because the admin deleted it.
  • The seeded welcome post remains available unless you deleted it separately.

You'll see the remaining public posts without the access matrix post. The delete changed the database without closing the public read route.

Deleted post still visible?

  • Confirm that $deletedPost.id matches $postId.
  • Repeat the final GET after confirming that the admin delete returned the post object.
  • Check the title carefully if your project contains other posts from earlier tests.

Help me investigate why the deleted post still appears in the public feed.

Your Completed Access Matrix

  • Public read allowed. GET /posts returns posts without an authorization header.
  • Anonymous write denied. POST /posts returns 401 through requireAuth.
  • Editor write allowed. POST /posts and PATCH /posts/:id return post data.
  • Editor delete denied. DELETE /posts/:id returns 403 through database authorization.
  • Admin delete allowed. DELETE /posts/:id returns the deleted post.

That's the proof complete. Your blog API now demonstrates public reading, authenticated identity, role-based permissions, and database-enforced authorization with visible evidence.

  • Keep the earlier login commands outside your screenshot because they contain the disposable test account passwords.

Secret mission

Limit Editors to Their Own Posts

Your editor role currently allows an editor to update every post. Add row ownership to the database policy so editors control only their own posts while admins retain full update access.

Clean Up Your Resources

Clean Up Your Resources

Your Supabase Free project costs $0/month within its included limits. Your local Express API does not create a cloud bill.

Resources you used:

  • One Supabase Free project.
  • Two disposable Supabase Auth test users inside that project.
  • Three Postgres tables inside that project.
  • Two enum types inside that project.
  • Two database functions inside that project.
  • The Custom Access Token Hook inside that project.
  • Database grants and Row Level Security policies inside that project.
  • One locally running Express API process.
  • Local source files in the folder that contains package.json.
  • The local .env file.
  • The local node_modules dependency folder.

Keep everything running

No action is needed. Choose this if you are still testing the access matrix or extending the authorization rules.

  • Leave the Supabase project active while you continue using the API.
  • Keep .env.setup deleted unless another one-time admin task requires the secret key.
  • Keep .env out of source control because it contains your project URL and publishable key.
  • Expect the Free project to pause after one week of inactivity.

Pause - I'll come back to this later

Shut down the local process to free up memory. Pause the Supabase project while preserving its hosted data.

  • Return to the PowerShell window running the API.
  • Close that PowerShell window to stop the local process.
  • Select your project in the Supabase dashboard.
  • Open Settings for that project.
  • Select General.
  • Open Project availability.
  • Click Pause Project.

You can step away knowing the database state is preserved. The Free project can be resumed when you return.

Delete - I don't want to use this again

Remove all local project resources before deleting the hosted Supabase project. This gives you a clean starting point if you build the API again.

This Cleanup Is Permanent

Local folder deletion removes your source files. Supabase project deletion permanently removes the hosted project.

The project confirmation screen provides one final check before the hosted resources are removed.

  • Return to the PowerShell window running the API.
  • Close that PowerShell window to stop the local process.
  • Locate the local folder containing package.json in your file manager.
  • Delete the .env file from that folder.
  • Delete the node_modules folder from that folder.
  • Delete the remaining local project folder.

You have cleared the local runtime and its files from your machine. The final cleanup removes the hosted project.

  • Select your project in the Supabase dashboard.
  • Open Settings for that project.
  • Select General.
  • Open Delete project.
  • Click Delete Project.
  • Enter the project name in the confirmation field.
  • Click the final Delete button.

Your cleanup is complete. The database, Auth users, keys, hook configuration, grants, and policies are permanently removed.

Nice Work!

Nice Work!

Outstanding work! You built an Express REST API backed by Supabase Postgres. Your PowerShell access matrix proves that every read or write receives the intended database-enforced decision.

You've learned how to:

  • Kept public reads open through GET /posts while rejecting anonymous writes.
  • Added editor and admin role-based access control through user_role claims in each JWT.
  • Combined bearer-token verification with Postgres grants plus Row Level Security to keep the database in control of authorization.
  • Secret Mission: Combined roles with row ownership so editors can update only their own posts while admins can update any post.

Ready to quiz yourself?