Two-layer identity models in Supabase: when auth and authorization disagree

When I take over a Supabase project, the first thing I check is how the identity model is wired. Not the auth provider, not the OAuth setup. The bit that connects auth.users (the identity layer) to whatever table holds roles or permissions (the authorization layer). Half the time it is wrong, and the people running the app do not know yet because the bug only shows up when somebody signs up for the first time after a botched deploy.

This post is the version of that explanation I give clients when they ask “is the auth setup okay?” It covers what the two layers are, why splitting them across two deploys is a P1 incident waiting to happen, and the exact pattern I use to ship them as one atomic migration.

What “two-layer identity” actually means

Supabase Auth gives you a single table called auth.users. It is created the moment you enable any provider (email, magic link, OAuth). Every signed-up user gets one row in it, with their hashed password or OAuth identifier, email, and a few timestamps.

What auth.users does not know is whether the user is an admin, a freelancer, a paid customer, or a viewer. That is the authorization layer, and you build it yourself in the public schema. A typical shape is a user_roles table:

create table public.user_roles (
  user_id uuid primary key references auth.users(id) on delete cascade,
  role text not null check (role in ('admin', 'member', 'viewer')),
  created_at timestamptz default now()
);

The two tables are joined on user_id. Every RLS policy in your app (Row-Level Security, the Postgres feature that decides which rows of a table a given user can see) checks user_roles to figure out what the current session is allowed to do.

The split is intentional. The auth schema is owned by Supabase and you should not modify it directly. The public schema is yours, and the authorization model lives there.

The failure mode: signed up, no role, nowhere to go

The bug I keep finding goes like this:

1. The app ships with email signup enabled at both the project level and the frontend.
2. The trigger that auto-inserts a default user_roles row when auth.users gets an INSERT ships in a separate migration.
3. Somebody signs up between the two deploys.
4. They are now authenticated. Their session is valid. They have a JWT (a signed token the browser sends with every request to prove who they are).
5. But they have no row in user_roles, so every RLS policy that checks the role returns false. Every query for their own data returns zero rows.
6. The frontend has no branch for “authenticated but no role.” It shows a blank page.

I have seen this on three inherited projects. Each time the team’s first instinct was “the user did something weird.” It is not the user. It is a deploy ordering bug.

The fix: a SECURITY DEFINER trigger on auth.users insert

The canonical pattern is a Postgres trigger on auth.users that fires AFTER INSERT and writes a default-role row into user_roles. The non-obvious detail is the SECURITY DEFINER clause and the set search_path = '' declaration.

Here is the full pattern:

-- Function: assign default role on signup
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.user_roles (user_id, role)
  values (new.id, 'member');
  return new;
end;
$$;

-- Trigger: fire on every auth.users insert
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

Two clauses are doing the heavy lifting here.

The first is security definer. It tells Postgres to run this function with the privileges of whoever owns it (usually postgres), not the session calling it. Without this clause the trigger fires as supabase_auth_admin (the internal role that runs the signup flow), which has no permissions in the public schema. The insert fails. The whole signup fails with it.

The second is set search_path = ''. This closes a search-path injection vulnerability that SECURITY DEFINER introduces. Without it, an attacker who can create a schema (rare but possible) could shadow public.user_roles with their own table and intercept writes. The empty search path forces every reference to be fully qualified (public.user_roles), which is what the function body already does.

The trigger fires after insert, so the new auth.users row already exists when the function runs. new.id resolves to the freshly-assigned UUID.

This is the same class of root-cause discipline I wrote about in silent failures in AI-built data pipelines: a partial-state failure that the build pipeline cannot detect, only production traffic surfaces.

If you would rather avoid SECURITY DEFINER entirely, the alternative is an RLS policy on user_roles that explicitly grants insert privileges to supabase_auth_admin:

create policy "auth admin can insert default roles"
on public.user_roles
for insert
to supabase_auth_admin
with check (true);

Then the trigger function can drop security definer and run as the calling role. The Supabase docs now recommend this approach for new projects because it keeps the function-privilege surface smaller. I have used both. The SECURITY DEFINER pattern is more compact; the policy-based approach is more auditable. Pick one and stick with it.

Why the velvet rope is not the same as the lock

A real-world failure I saw on an inherited project: the team had “removed the signup link” from the frontend by hiding the button and rerouting /signup to a 404. They believed signup was disabled. It was not.

Anyone with DevTools open can POST directly to /auth/v1/signup with their email and password. The endpoint is still listening because the project-level setting was on. The frontend was a velvet rope. The actual gate is the Supabase project setting at Authentication → Providers → Email → Enable signups. With that off, the endpoint returns 403 regardless of where the request originates.

For an invite-only product, both layers need to close:

• Frontend. Remove the signup UI entirely. Not just hidden; gone. The login page should be login-only.
• Project-level toggle. Disable “Enable email signups” in the Auth Providers panel. This makes the signup endpoint return 403.
• Invite path. Write an edge function that uses the service-role key (an admin-level credential that bypasses the signup toggle) to call supabase.auth.admin.createUser() for the invited address. The service-role path keeps working because it is the path Supabase itself uses for admin operations.

Of those three only the project toggle is load-bearing. The frontend layer is honesty so a developer who finds the login page knows what they are looking at. The invite path is the controlled way through.

The atomic-deployment lesson

The reason all of this needs to ship together is that any partial state is broken. The migration order I use:

-- Migration: bootstrap two-layer identity (run as one transaction)
begin;

-- 1. The role table itself
create table public.user_roles ( ... );

-- 2. RLS policies on user_roles
alter table public.user_roles enable row level security;
create policy "users can read own role"
  on public.user_roles for select
  using (auth.uid() = user_id);

-- 3. The trigger function
create function public.handle_new_user() ...;

-- 4. The trigger
create trigger on_auth_user_created ...;

-- 5. (Optional) Backfill: write default role for any auth.users that exist already
insert into public.user_roles (user_id, role)
select id, 'member' from auth.users
on conflict (user_id) do nothing;

commit;

Five steps in one transaction. If any step fails, the whole thing rolls back and the previous state is intact. If all five succeed, your identity model is wired end to end before anyone can sign up again.

The signup-disable toggle is its own change, but it can ship in the same Supabase CLI deploy if you put it in the project config. Supabase’s CLI now respects auth-provider settings in supabase/config.toml, which means the project-level disable is also versionable.

What to check on an inherited project

If you are auditing a Supabase project you did not build, these are the four checks I run in order:

1. Open the public schema and look for a role table. If there is one, look at the trigger on auth.users that populates it. If the trigger does not exist, write the migration above and ship it before signup runs again.
2. Open the Supabase project’s Auth → Providers panel. Look at “Enable email signups.” For an invite-only product, this should be off.
3. Open the frontend login page. There should be no signup link, no signup form, no /signup route. If there is, it is dead code or a velvet rope. Remove it.
4. Grep the codebase for auth.signUp(. Every call should either be removed or routed through an admin edge function that uses the service role. Direct client-side signup on an invite-only product is the same bug as the project-level toggle being on.

The discipline is small. The cost of getting it wrong is a customer signing up, getting an unreachable session, and emailing support to ask why “the app does not work.” Build it right once and forget about it.

If your Supabase project went through the publishable-key migration in 2025, the Supabase publishable-keys fix for a Chrome extension is worth reading alongside this one. The publishable-key change cleaned up the auth layer in a different way; the two-layer identity model is what keeps the cleanup from leaving you with an unreachable user state.

If you are running a Supabase-backed product and you want a second pair of eyes on the identity model before the next compliance audit or the next batch of users, let’s talk. The audit takes about three hours and covers everything in this post plus the RLS-policy chains downstream of user_roles.