Supabase RBAC for Client Portals: Who Can Edit and Publish?

The button is visible only to publishers. But if an editor has permission to update every column and the policy checks only their role and workspace, they can send their own request with status: 'published'. The browser never needs to render the button.

This is where role-based access control (RBAC) gets practical. A role bundles actions a person can perform. In a client portal, that role belongs to the person’s workspace membership. PostgreSQL then enforces which rows the person can reach, which fields they can change and who can move an update from draft to published.

I built a small client portal example to test a common permission split: editors can save drafts, and publishers can release them. I ran nine access scenarios in PostgreSQL, including direct attempts to bypass the interface.

Decide what each role can do

In this example, publishing makes an update visible to viewers in the workspace. Every update stays private to that workspace.

A person can be an editor in Workspace A and a viewer in Workspace B. That’s one identity with two membership rows. A workspace switcher can use those memberships to decide what to show, and the database checks the same memberships when requests arrive.

Put the role on the membership

The SQL below is for a fresh example in a Supabase project with Auth and the Data API enabled. Create workspaces, memberships and drafts through a trusted administrative path. The browser permissions in this example cover reading, editing and publishing.

begin;

create table public.workspaces (
  id uuid primary key default gen_random_uuid(),
  name text not null
);

create table public.workspace_members (
  workspace_id uuid not null references public.workspaces(id),
  user_id uuid not null references auth.users(id) on delete cascade,
  role text not null check (role in ('viewer', 'editor', 'publisher')),
  primary key (workspace_id, user_id)
);

create index workspace_members_user_id_idx
  on public.workspace_members (user_id);

create table public.project_updates (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references public.workspaces(id),
  title text not null,
  body text not null,
  status text not null default 'draft'
    check (status in ('draft', 'published')),
  published_at timestamptz,
  check ((status = 'published') = (published_at is not null))
);

create index project_updates_workspace_id_idx
  on public.project_updates (workspace_id);

alter table public.workspaces enable row level security;
alter table public.workspace_members enable row level security;
alter table public.project_updates enable row level security;

revoke all on public.workspaces, public.workspace_members,
  public.project_updates from public, anon, authenticated;

grant select on public.workspaces, public.workspace_members,
  public.project_updates to authenticated;
grant update (title, body) on public.project_updates to authenticated;

create policy "Read your own workspace memberships"
  on public.workspace_members for select to authenticated
  using (user_id = (select auth.uid()));

create policy "Read workspaces you belong to"
  on public.workspaces for select to authenticated
  using (exists (
    select 1
    from public.workspace_members as m
    where m.workspace_id = workspaces.id
      and m.user_id = (select auth.uid())
  ));

commit;

The membership table exposes only the caller’s own rows. A signed-in user can read their own workspace role for the interface. The client receives no permission to add or change memberships.

Give editors a narrower update

Row Level Security (RLS) governs which rows a signed-in user can read or update. The column grant above allows updates to title and body only. It protects status, published_at and workspace_id even when the caller can edit the draft. Supabase documents how column privileges work alongside RLS.

create policy "Read updates visible to your workspace role"
  on public.project_updates for select to authenticated
  using (exists (
    select 1
    from public.workspace_members as m
    where m.workspace_id = project_updates.workspace_id
      and m.user_id = (select auth.uid())
      and (
        project_updates.status = 'published'
        or m.role in ('editor', 'publisher')
      )
  ));

create policy "Editors and publishers can revise drafts"
  on public.project_updates for update to authenticated
  using (
    status = 'draft' and exists (
      select 1
      from public.workspace_members as m
      where m.workspace_id = project_updates.workspace_id
        and m.user_id = (select auth.uid())
        and m.role in ('editor', 'publisher')
    )
  )
  with check (status = 'draft');

USING checks the row the caller is trying to edit. WITH CHECK checks the resulting row. Here, the column grant fixes the workspace for this write path, so the resulting-row check only needs to keep the update in draft. If you later allow workspace transfers, review the grants and check authorization for the destination workspace too. See PostgreSQL’s policy reference.

Keep the workspace filter in the frontend query so the interface requests the relevant rows. The policies still apply if someone removes that filter, changes the workspace ID or calls the Data API directly.

Make publishing a database operation

Publishing changes two fields together. The database function below reads the caller from the Supabase session, checks that they are a publisher in the update’s workspace and publishes only a draft. The membership check and the state change are part of the same SQL statement.

begin;

create function public.publish_project_update(p_update_id uuid)
returns boolean
language plpgsql
security definer
set search_path = pg_catalog, pg_temp
as $$
begin
  update public.project_updates as u
  set status = 'published',
      published_at = statement_timestamp()
  where u.id = p_update_id
    and u.status = 'draft'
    and exists (
      select 1
      from public.workspace_members as m
      where m.workspace_id = u.workspace_id
        and m.user_id = (select auth.uid())
        and m.role = 'publisher'
    );

  return found;
end;
$$;

revoke all on function public.publish_project_update(uuid)
  from public, anon, authenticated;
grant execute on function public.publish_project_update(uuid)
  to authenticated;

commit;

Run this example with a trusted migration role that owns the example tables and function, with the usual table-owner RLS behavior. The function executes with its owner’s privileges, so the membership check inside the function is essential. Use a fixed search path, qualify application objects by schema and restrict who can execute it. See Supabase’s database function guide.

The browser uses its normal signed-in client.

const { data: published, error } = await supabase.rpc(
  'publish_project_update',
  { p_update_id: updateId },
)

if (error) throw error

if (!published) {
  // Show “This update couldn’t be published” and refresh the list.
}

The function returns false for an unavailable draft, including an unknown ID, a draft outside the caller’s workspace, a caller without publishing rights or an update already published. A repeated request leaves the original publication timestamp intact.

Because the function reads the membership table, a committed role downgrade applies to subsequent requests even if the person still has the same access token. If you put workspace roles in JWT claims, an issued token can continue carrying the previous role until it is refreshed or expires. See Supabase’s RLS guide.

Test the requests your interface would never send

Use two workspaces and real test sessions in a development Supabase project. The same user should have different roles across the two workspaces. Run these requests with the regular browser client so the tests exercise the same privileges as the portal.

For update tests, assert the row count or check the stored value afterwards. An RLS-filtered update can finish without an API error and change zero rows. That distinction matters in the UI too. If someone loses access while the editor is open, a successful HTTP response alone is a poor reason to display “Saved.”

Three fixed roles are enough for this portal. If clients later need custom roles, keep the workspace membership and replace fixed role checks with a role-to-permission mapping for actions such as updates.edit and updates.publish. Start that change when a client needs a combination the existing roles can’t express.

For a broader look at Supabase access controls, see Reviewing an AI-built Supabase application before production.

Building a client portal on Supabase? Pinkdot can design its roles, RLS policies and publishing flow, then test them before launch.