fix(core): Resolve multi-main startup race condition in AuthRolesService

[afitzek]

Summary

Fixes a multi-main startup race condition where concurrent n8n instances running AuthRolesService.syncScopes() against the same Postgres database hit duplicate key constraint violations.

• Introduces a centralized DbLockService in @n8n/db that wraps Postgres advisory locks (pg_advisory_xact_lock) inside transactions, with a DbLock enum to prevent lock ID collisions
• Supports an optional timeoutMs on withLock (via SET LOCAL lock_timeout) and a non-blocking tryWithLock (via pg_try_advisory_xact_lock) that fails fast if the lock is already held
• On SQLite, advisory locks are skipped — the transaction alone provides serialization
• Refactors AuthRolesService.init() to use DbLockService.withLock(DbLock.AUTH_ROLES_SYNC, ...) instead of repository-level calls
• Removes the leader-only guard in start.ts — all main instances now call AuthRolesService.init(), and the advisory lock serializes them safely
• Lock contention throws OperationalError (timeout exceeded or try-lock busy)

Test plan

• 10 unit tests for DbLockService (withLock + tryWithLock, Postgres/SQLite, timeout, error propagation)
• 8 integration tests against live database (transaction behavior, advisory lock serialization, timeout, try-lock contention)
• 27 existing AuthRolesService unit tests updated and passing
• 4 existing start.ts unit tests updated (non-leader now calls init)
• Typecheck and lint clean on both @n8n/db and cli packages

Related Linear tickets, Github issues, and Community forum posts

closes https://linear.app/n8n/issue/IAM-298

Review / Merge checklist

• PR title and summary are descriptive. (conventions)
• Docs updated or follow-up ticket created.
• Tests included.
• PR Labeled with release/backport (if the PR is an urgent fix that needs to be backported)

[codecov]

Codecov Report

❌ Patch coverage is 57.89474% with 24 lines in your changes missing coverage. Please review.

Files with missing lines	Patch %	Lines
...ackages/@n8n/db/src/services/auth.roles.service.ts	8.00%	23 Missing ⚠️
packages/@n8n/db/src/services/index.ts	0.00%	1 Missing ⚠️

📢 Thoughts on this report? Let us know!

[guillaumejacquart]

Just a few questions

[guillaumejacquart]

Why not use the tryWithLock ? I feel like it would be faster, and would fail only if another main instance is doing the job ?
Also, why no catching the OperationalError in case of timeout ? Do we want this to prevent the instance from starting ?

[afitzek]

The OperationalError is only thrown when the timeout parameter is set, which it is not in this call. The thinking for not using tryWithLock is that I want all instances to sync there changes to avoid any potential miss behaviors. The downside is that instances might wait for each other if they reach this point at the same time, but since this is a few ms, it should be fine during the bootstrap process.

[phyllis-noester]

just to make sure I understand: the reason why we want to do this whenever a new instance starts is that the instance start could be related to a deployment that either added or removed scopes. correct?

[afitzek]

Yes correct, this approach safes us from adding a DB migration for every scope that we want to add.

[phyllis-noester]

given that we don't do rolling updates, there is no scenario where there could be a race condition and the scopes remain in the old state right?
I was trying to think of edge cases e.g.:

• a container restarts for non deployment related reason
• we deploy (miliseconds after)
• container restart locks the table
• deploy does not sync roles
  => scopes remain in old state

but I assume that would only be possible if we did rolling updates and even then it would be super unlikely

[afitzek]

If the container restarts it would acquire the advisory lock, and sync its roles, the deployment (the new versions), would reach this point and wait for the advisory lock to be released. One of the new instances would acquire the lock and sync its roles, the other still wait. So one after another passes this point.

[phyllis-noester]

ah i see, that's essentially the answer to guillaumes question :)

[n8n-assistant]

Got released with n8n@2.11.0