The only thing I can think of is that the insert statement inside of the CTE is forcing all the connection constraints to deferred, but there’s no documentation that’s even remotely close to that explanation.

deferred_constraints_2.sql

create table users (
  name text primary key
);

create table groups (
  name text primary key,
  user_name text not null references users
);

alter table users
  add column group_name text not null references groups;

-- this fails
insert into users (name, group_name) values ('my name', 'my group');
insert into groups (name, user_name) values ('my group', 'my name');

-- this fails
begin;
insert into users (name, group_name) values ('my name', 'my group');
insert into groups (name, user_name) values ('my group', 'my name');
commit;

-- this works
with new_user as (
  insert into users (name, group_name)
  values ('my name', 'my group')
  returning name, group_name
)
insert into groups (name, user_name) 
select new_user.group_name, new_user.name
from new_user;

--cleanup
drop table groups cascade;
drop table users cascade;