Make PostgreSQL WebAuthn get-or-create concurrency safe

[TheSilkky]

Make PostgreSQL WebAuthn get-or-create concurrency safe

Priority

P1

Type

bug

Labels

Suggested GitHub labels:

• backlog
• bug
• go
• testing

Branch scope

• Current branch: develop
• Current HEAD: 3555d1fd531ecd4b51b85779618bf4717514aa25
• Target branch: develop
• Original reviewed branch/ref: upgrade-codex-reports-and-repo-full-validation-security-review
• Original reviewed commit SHA: e609ff86028c81bd149839e03d1ffc0eb2ee9e4a
• Merged source pull request: #378
• Target release/version: unreleased
• Scope classification: release-blocker-current-branch
• Revalidation status: Revalidated against develop at 3555d1fd531ecd4b51b85779618bf4717514aa25 on 2026-07-12; repeat if develop moves before issue creation.

Summary

Make the optional PostgreSQL WebAuthn user get-or-create path return one durable result under concurrent requests instead of exposing a transient correctness failure.

Context

The technical review identified a concurrency correctness defect in the current PostgreSQL get-or-create behavior. This is a non-security availability/correctness bug in the optional backend.

Issue #276 implemented WebAuthn support, but it does not track this current regression. The repository has no postgresql label, so this draft uses the existing go and testing labels.

Proposed change

Use one concurrency-safe PostgreSQL statement and transaction pattern for WebAuthn user creation and lookup. Expected uniqueness races should converge on the already-created durable record without mapping to a false not-found response or an internal server error.

Review equivalent PostgreSQL get-or-create paths for the same transaction-recovery pattern, but keep unrelated authentication changes outside this issue.

Acceptance criteria

• Concurrent get-or-create calls for the same account and relying-party context converge on the same durable user handle.
• Expected uniqueness races do not map to account-not-found or an HTTP 500 response.
• Unexpected persistence errors remain distinguishable internally and safely mapped externally.
• A live disposable PostgreSQL regression test exercises simultaneous calls.
• SQLite behavior and main/private/public route boundaries remain unchanged.
• Tests and logs contain no credentials, session values, challenge values, private endpoints, or account-identifying data.

Tests / validation

• Run gofmt on changed Go files.
• Run go test ./...
• Run go vet ./...
• Run the focused live PostgreSQL concurrency test with a disposable database.
• Run git diff --check.
• Revalidate against the target branch before public issue creation.

Out of scope

• Redesigning WebAuthn, second-factor policy, relying-party configuration, or account recovery.
• Changing SQLite behavior.
• Adding public account or authentication routes.
• Changing listener separation, session policy, or key custody.
• Publishing security-sensitive analysis or private reproduction material.

Notes

Report reference: docs/reports/2026-07-10-proofline-unreleased-technical-review.md, public finding F-005.

Related completed work: issue #276 and PR #281.