Compare commits

...
Author SHA1 Message Date
Connor Yoh 6772619d6f Serialise SaaS first-login user provisioning to stop duplicate-INSERT races
New (mostly auto-anonymous) users have no local `users` row yet, and the SPA
fires many authenticated requests in parallel the instant a Supabase token
exists. Each request missed the `findBySupabaseId` lookup and raced to INSERT
the same `supabase_auth_id`, so all but one hit the `users_supabase_auth_id_key`
unique constraint — a burst of Postgres 23505 errors per new visitor. The code
recovered (catch + refetch) but still attempted the duplicate creates and
polluted the DB error logs.

Guard the first-login create path with a per-Supabase-id striped lock and
re-check `findBySupabaseId` under it: the first request creates the row, the
rest find it and reuse it, so no duplicate INSERT is attempted. The existing
catch in createUser() stays as a backstop for the rarer cross-instance race.

The already-provisioned fast path is unchanged (single indexed lookup, no
lock), so returning users see no behaviour or latency change.

Adds a concurrent-first-login test asserting a 16-way stampede results in
exactly one saveUser/createSupabaseUser while every request still authenticates.
2026-07-15 12:39:59 +01:00
3 changed files with 134 additions and 8 deletions
@@ -17,6 +17,7 @@ import java.util.Locale;
import java.util.Map;
import java.util.Optional;
import java.util.UUID;
import java.util.concurrent.locks.Lock;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.security.core.Authentication;
@@ -31,6 +32,8 @@ import org.springframework.security.web.AuthenticationEntryPoint;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.filter.OncePerRequestFilter;
import com.google.common.util.concurrent.Striped;
import jakarta.servlet.FilterChain;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServletRequest;
@@ -60,6 +63,13 @@ public class SupabaseAuthenticationFilter extends OncePerRequestFilter {
public static final String BEARER_PREFIX = "Bearer ";
public static final String ANON_PREFIX = "anon_";
// Per-Supabase-id provisioning locks for the first-login create path (see
// getOrCreateUser). Striped so memory stays fixed regardless of how many users are
// seen; distinct ids only contend on a hash collision, which is harmless. The
// fast path for already-provisioned users never acquires one.
private static final int PROVISIONING_LOCK_STRIPES = 256;
private final Striped<Lock> provisioningLocks = Striped.lock(PROVISIONING_LOCK_STRIPES);
private final TeamService teamService;
private final UserService userService;
private final SupabaseUserService supabaseUserService;
@@ -222,18 +232,33 @@ public class SupabaseAuthenticationFilter extends OncePerRequestFilter {
// If not present, the JWT references a Supabase user this server hasn't synced.
SupabaseUser supabaseUser = supabaseUserService.getUser(supabaseId);
// Resolve to a local User by supabase_id.
// Fast path: an already-provisioned user is resolved with a single indexed
// lookup and no locking, so returning users see no behaviour or latency change.
Optional<User> linkedUser = userService.findBySupabaseId(supabaseId);
if (linkedUser.isPresent()) {
User user = linkedUser.get();
if (ANONYMOUS.toString().equalsIgnoreCase(user.getAuthenticationType())
&& !supabaseUser.isAnonymous()) {
user = upgradeAnonymousUser(user, supabaseUser, jwt);
}
return user;
return resolveExistingUser(linkedUser.get(), supabaseUser, jwt);
}
return createUser(jwt, supabaseId, email, appMetadata);
// First-login slow path. A freshly signed-in user (notably an auto-anonymous
// guest) has no local row yet, and the SPA fires many authenticated requests in
// parallel the instant a token exists — all would miss the lookup above and race
// to INSERT the same supabase_auth_id, so all but one hit the unique constraint
// (Postgres 23505). Serialise provisioning per Supabase id within this instance
// and re-check under the lock: the first request creates the row, the rest find
// it and reuse it, so no duplicate INSERT is even attempted. The create path in
// createUser() keeps its own catch as a backstop for the rarer cross-instance
// race, where two nodes each get the very first request at the same moment.
Lock lock = provisioningLocks.get(supabaseId);
lock.lock();
try {
Optional<User> recheck = userService.findBySupabaseId(supabaseId);
if (recheck.isPresent()) {
return resolveExistingUser(recheck.get(), supabaseUser, jwt);
}
return createUser(jwt, supabaseId, email, appMetadata);
} finally {
lock.unlock();
}
} catch (UserNotFoundException e) {
throw new InvalidBearerTokenException("User not found", e);
} catch (InvalidBearerTokenException e) {
@@ -247,6 +272,19 @@ public class SupabaseAuthenticationFilter extends OncePerRequestFilter {
}
}
/**
* Resolve an already-linked local user, upgrading a still-anonymous row to the real
* provider/email once the Supabase account is no longer anonymous. Shared by the fast-path
* lookup and the under-lock re-check so both resolve identically.
*/
private User resolveExistingUser(User user, SupabaseUser supabaseUser, Jwt jwt) {
if (ANONYMOUS.toString().equalsIgnoreCase(user.getAuthenticationType())
&& !supabaseUser.isAnonymous()) {
return upgradeAnonymousUser(user, supabaseUser, jwt);
}
return user;
}
/** Promote a local anonymous user to the real provider+email carried on the JWT. */
@Transactional
protected User upgradeAnonymousUser(User user, SupabaseUser supabaseUser, Jwt jwt) {
@@ -514,7 +514,11 @@ class SupabaseAuthenticationFilterMoreTest {
User winner = newUser("winner@example.com");
winner.setSupabaseId(supabaseId);
// Fast-path miss, then a miss on the under-lock re-check too — i.e. a
// cross-instance race the in-instance lock can't cover — so createUser is
// attempted; its INSERT loses and the catch fetches the committed winner.
when(userService.findBySupabaseId(supabaseId))
.thenReturn(Optional.empty())
.thenReturn(Optional.empty())
.thenReturn(Optional.of(winner));
when(userService.saveUser(any(User.class)))
@@ -2,18 +2,26 @@ package stirling.software.saas.security;
import static org.assertj.core.api.Assertions.assertThat;
import static org.mockito.ArgumentMatchers.any;
import static org.mockito.ArgumentMatchers.eq;
import static org.mockito.Mockito.never;
import static org.mockito.Mockito.times;
import static org.mockito.Mockito.verify;
import static org.mockito.Mockito.when;
import java.time.Instant;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.UUID;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicReference;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
@@ -171,6 +179,82 @@ class SupabaseAuthenticationFilterTest {
.isInstanceOf(EnhancedJwtAuthenticationToken.class);
}
@Test
void concurrentFirstLoginCreatesUserExactlyOnce() throws Exception {
// Reproduces the first-login stampede: a brand-new user's SPA fires many
// authenticated requests in parallel before the local row exists. Without the
// per-Supabase-id provisioning lock every request would attempt the INSERT and all
// but one would hit the users_supabase_auth_id_key unique constraint. The lock must
// collapse this to a single create while every request still authenticates.
UUID supabaseId = UUID.randomUUID();
Jwt jwt = jwtFor(supabaseId, "frank@example.com", false, "google");
when(jwtDecoder.decode("token")).thenReturn(jwt);
when(supabaseUserService.getUser(supabaseId))
.thenReturn(supabaseUserMatching(supabaseId, "frank@example.com", false));
// findBySupabaseId reflects the real DB: empty until the winning save commits, then
// returns the created row for every subsequent (and re-checked) lookup.
AtomicReference<User> created = new AtomicReference<>();
when(userService.findBySupabaseId(supabaseId))
.thenAnswer(inv -> Optional.ofNullable(created.get()));
when(userService.saveUser(any(User.class)))
.thenAnswer(
inv -> {
User u = inv.getArgument(0);
created.set(u);
return u;
});
int threads = 16;
ExecutorService pool = Executors.newFixedThreadPool(threads);
try {
CountDownLatch ready = new CountDownLatch(threads);
CountDownLatch go = new CountDownLatch(1);
List<Future<Boolean>> results = new ArrayList<>();
for (int i = 0; i < threads; i++) {
results.add(
pool.submit(
() -> {
MockHttpServletRequest req = new MockHttpServletRequest();
req.setRequestURI("/api/v1/something");
req.setMethod("POST");
req.addHeader("Authorization", "Bearer token");
SecurityContextHolder.clearContext();
ready.countDown();
go.await();
filter.doFilter(
req,
new MockHttpServletResponse(),
new MockFilterChain());
boolean authed =
SecurityContextHolder.getContext().getAuthentication()
instanceof EnhancedJwtAuthenticationToken;
SecurityContextHolder.clearContext();
return authed;
}));
}
assertThat(ready.await(5, TimeUnit.SECONDS)).isTrue();
go.countDown();
int authenticated = 0;
for (Future<Boolean> f : results) {
if (Boolean.TRUE.equals(f.get(10, TimeUnit.SECONDS))) {
authenticated++;
}
}
assertThat(authenticated)
.as("every concurrent request authenticates")
.isEqualTo(threads);
} finally {
pool.shutdownNow();
}
// The guard collapses the stampede to a single create; the losers reuse the row.
verify(userService, times(1)).saveUser(any(User.class));
verify(supabaseUserService, times(1)).createSupabaseUser(eq(supabaseId), any(), eq(false));
verify(saasTeamService, times(1)).ensurePersonalTeam(any(User.class));
}
@Test
void appleProviderClassifiedAsOauth2NotWeb() throws Exception {
UUID supabaseId = UUID.randomUUID();