Roles and row-level security

Reference

Your generated backoffice ships with four staff roles and a row-level security (RLS) policy on every table. This page lists the exact roles, the rank order, the read and write rules each policy enforces, the failure messages a rejected check returns, and where those rules are applied. Every fact here comes from the migrations in templates/backend/supabase/migrations/ that the AI copies into your repo, plus the auth helpers in src/lib/auth/.

The four staff roles

Staff identity lives in two tables. The staff_role Postgres enum defines exactly four values, in this order:

CREATE TYPE public.staff_role AS ENUM ('owner',
 'admin',
 'editor',
 'viewer'
);

Each staff member has one row in staff_users (id, email, full name, avatar) and one row in staff_roles that pins their role. A user with no staff_roles row is not staff and cannot read or write any backoffice table.

CREATE TABLE public.staff_roles (user_id uuid PRIMARY KEY REFERENCES public.staff_users(id) ON DELETE CASCADE,
 role public.staff_role NOT NULL DEFAULT 'viewer',
 assigned_at timestamptz NOT NULL DEFAULT now()
);

The default for a new staff_roles row is viewer, the lowest rank.

Rank order

Roles are ranked numerically. A higher rank includes every permission of the ranks below it. The ranking is hard-coded in the require_staff_role database function and mirrored in the app helper src/lib/auth/role-utils.ts:

Role	Rank	What it can do
viewer	1	Read every backoffice table. No writes.
editor	2	Read everything, plus create, update, and delete catalog, orders, customers, coupons, content, and most other business data.
admin	3	Everything an editor can do, plus manage staff, payment provider credentials, integrations, and branding.
owner	4	Everything an admin can do. Granting the owner role to another user is reserved for an existing owner.

What the RLS policies say

Every table in your backoffice has row-level security enabled. Each table carries two staff policies built on the same pattern:

• A staff_read_* policy: FOR SELECT, allowed for any authenticated user who has a row in staff_roles. Every role, including viewer, can read.
• A staff_write_* policy: FOR ALL (insert, update, delete), allowed only when the user's role is in a fixed list.

For the bulk of business tables, the write list is owner, admin, and editor. Here is the policy for products, which is representative:

CREATE POLICY "staff_read_products" ON public.products
 FOR SELECT TO authenticated
 USING (EXISTS (SELECT 1 FROM public.staff_roles WHERE user_id = auth.uid()));

CREATE POLICY "staff_write_products" ON public.products
 FOR ALL TO authenticated
 USING (EXISTS (SELECT 1 FROM public.staff_roles
 WHERE user_id = auth.uid() AND role IN ('owner', 'admin', 'editor')))
 WITH CHECK (EXISTS (SELECT 1 FROM public.staff_roles
 WHERE user_id = auth.uid() AND role IN ('owner', 'admin', 'editor')));

Both clauses key off auth.uid(), so these policies bind any client that carries a Supabase Auth identity. Tables that follow this owner/admin/editor write rule include catalog, inventory, customers, orders, coupons, shipping, blog content, analytics events, and settings.

Tables with a tighter write rule

A short list of sensitive tables drops editor from the write list, so only owner and admin can change them. The read policy stays open to all staff.

Table	Who can write	Why
staff_users	owner, admin	Adding or editing staff accounts.
staff_roles	owner, admin	Assigning roles to staff.
payment_providers	owner, admin	Holds payment credentials.
integrations	owner, admin	Third-party connection records.
branding	owner, admin	Store identity and theme.
gsc_connection, gsc_metrics	owner, admin	Search Console connection.

Public read, no public write

Your storefront needs to show products and posts to logged-out shoppers, which read through the Supabase anon key. A small set of tables adds a second read policy for the anon and authenticated roles, scoped so only published rows are visible, and grants SELECT to anon. No table grants public write.

Table	Public row condition
products	status = 'active'
product_variants	parent product is active
categories	is_published = true
collections	is_published = true
blog_posts	status = 'published'

CREATE POLICY "public_read_products" ON public.products
 FOR SELECT TO anon, authenticated
 USING (status = 'active');

A draft product, an unpublished category, or a scheduled blog post stays invisible to anonymous visitors because the condition fails. This is where RLS does its front-line work: it is the boundary between what a logged-out shopper on the anon key may read and what stays private.

How the dashboard actually reaches the data

Read this section before you reason about who can see what. The backoffice does not log in through Supabase Auth, so the RLS policies above are not the gate that governs a signed-in staff request.

The merchant authenticates on MaShop, which mints a signed hand-off token; the /auth/sso route verifies it and issues a first-party, HMAC-signed session cookie (src/lib/auth/session.ts). There is no Supabase Auth session, so inside a dashboard request auth.uid() is null and the staff_read_* / staff_write_* policies would match no rows.

The dashboard therefore reads and writes business data through a service-role client (src/lib/supabase/admin.ts, built with SUPABASE_SERVICE_ROLE_KEY), which bypasses RLS. Access is gated one layer up, in application code, by the requireStaffRole(min) helper that runs before any query. The anon-key server client (src/lib/supabase/server.ts) is reserved for unauthenticated paths such as the storefront and the payment webhook, where RLS is the enforcement layer.

The result: RLS protects storefront and any direct database access, and it is the backstop if a stray query ever runs on an RLS-scoped client. The live authorization gate for the dashboard is requireStaffRole plus the require_staff_role check inside the RPCs, both described next.

The require_staff_role database function

Stored procedures (RPCs) that run with elevated privileges carry their own gate: they call require_staff_role before doing any work. The function reads the caller's role from staff_roles, compares it against a minimum, and raises an exception if the caller falls short.

CREATE FUNCTION public.require_staff_role(min_role public.staff_role DEFAULT 'viewer')
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $
DECLARE
 ranks CONSTANT jsonb:= '{"viewer":1,"editor":2,"admin":3,"owner":4}'::jsonb;
 current_role public.staff_role;
BEGIN
 SELECT role INTO current_role FROM public.staff_roles WHERE user_id = auth.uid() LIMIT 1;
 IF current_role IS NULL THEN
 RAISE EXCEPTION 'forbidden: not staff' USING ERRCODE = 'insufficient_privilege';
 END IF;
 IF (ranks->>current_role::text)::int < (ranks->>min_role::text)::int THEN
 RAISE EXCEPTION 'forbidden: requires % role', min_role USING ERRCODE = 'insufficient_privilege';
 END IF;
END;
$;

Two failure modes, both with SQLSTATE insufficient_privilege:

• The caller has no staff_roles row for auth.uid(). The function raises forbidden: not staff.
• The caller's rank is below the required minimum. The function raises forbidden: requires <role> role, for example forbidden: requires admin role.

Because the dashboard reaches these RPCs through the service-role client, the app-layer requireStaffRole check is what admits or rejects the staff request; this database check is the second line if the RPC is ever called from an RLS-scoped client. The minimum each RPC demands is fixed in the migration, not configurable:

RPC	Minimum role
Order status transition	editor
Inventory stock adjustment	editor
Set payment provider credentials	admin
Read payment provider credentials	admin

The app layer is the live gate

Server actions and pages do not wait for the database to reject a request. They call the helper requireStaffRole(min) from src/lib/auth/require-staff-role.ts first, reading the role from the signed session cookie and using the same rank table as the database. If the session is missing or expired, the helper redirects to /auth/signed-out. If the role is too low, it redirects to /auth/signed-out?reason=insufficient_role.

export async function requireStaffRole(min: StaffRole = 'viewer') {
 const session = await getSession();
 if (!session) {
 redirect('/auth/signed-out');
 }
 if (!hasMinRole(session.role, min)) {
 redirect('/auth/signed-out?reason=insufficient_role');
 }
 return { user: { id: session.merchant_user_id, email: session.email }, role: session.role };
}

So a viewer who tries to reach a write action is stopped here, in application code, before the service-role client runs. The Staff settings page at /settings/staff calls requireStaffRole('admin'), so a viewer or editor who navigates there is bounced before the page renders. RLS and require_staff_role remain in place underneath as the database-level backstop.

The service-role key

The service-role client is how nearly every signed-in dashboard read and write runs, which is why the requireStaffRole gate above matters: with RLS out of the picture for that client, the application check is the boundary. Staff management is one example, where an admin creates the staff_users and staff_roles rows for a new member; those mutations call requireStaffRole('admin') first. Keep SUPABASE_SERVICE_ROLE_KEY server-side only. It never reaches the browser, and any code path that uses it must gate on requireStaffRole before it touches a row.

Related

• Database schema lists every table the policies protect.
• Stored procedures documents the RPCs that call require_staff_role.
• Your Supabase project explains where these migrations run and who owns the database.