| | | 1 | | using Microsoft.EntityFrameworkCore; |
| | | 2 | | using Microsoft.EntityFrameworkCore.Infrastructure; |
| | | 3 | | using System.Diagnostics.CodeAnalysis; |
| | | 4 | | |
| | | 5 | | namespace Anichron.Infrastructure.Data; |
| | | 6 | | |
| | | 7 | | public static class DatabaseFacadeExtensions |
| | | 8 | | { |
| | | 9 | | // S2077 flags the two interpolated pg_advisory_lock command texts below as SQL built by |
| | | 10 | | // string formatting. There is no injection surface: the only interpolated value is |
| | | 11 | | // PostgresConstants.MigrationAdvisoryLockId, a `const long` fixed at compile time, and no |
| | | 12 | | // caller can influence it. The rule matches the interpolation PATTERN, not a reachable taint |
| | | 13 | | // path, so this is a false positive rather than a finding to fix. |
| | | 14 | | // |
| | | 15 | | // Suppressed at the method rather than in .editorconfig so the justification travels with the |
| | | 16 | | // code a reader is looking at. It became an ERROR rather than a warning when |
| | | 17 | | // SonarAnalyzer.CSharp went 10.25 → 10.34 under TreatWarningsAsErrors. |
| | | 18 | | // |
| | | 19 | | // 📌 Parameterising both commands would remove the suppression and is worth doing on its own |
| | | 20 | | // merits — but it is a behaviour change, and this is a dependency-bump PR. |
| | | 21 | | [SuppressMessage( |
| | | 22 | | "Major Code Smell", |
| | | 23 | | "S2077:Formatting SQL queries is security-sensitive", |
| | | 24 | | Justification = "Interpolates only a compile-time const; no caller-controlled input reaches this SQL.")] |
| | | 25 | | public static async Task MigrateWithAdvisoryLockAsync( |
| | | 26 | | this DatabaseFacade database, CancellationToken ct, int maxAttempts = 30) |
| | 0 | 27 | | { |
| | | 28 | | // Explicitly hold the connection open so that all operations — acquire lock, |
| | | 29 | | // migrate, release lock — run on the same PostgreSQL session. Session-level advisory |
| | | 30 | | // locks are tied to the session; a different connection would see a different lock. |
| | 0 | 31 | | await database.OpenConnectionAsync(ct); |
| | | 32 | | try |
| | 0 | 33 | | { |
| | 0 | 34 | | var conn = database.GetDbConnection(); |
| | | 35 | | |
| | 0 | 36 | | for (var attempt = 1; attempt <= maxAttempts; attempt++) |
| | 0 | 37 | | { |
| | | 38 | | bool acquired; |
| | 0 | 39 | | await using (var tryLockCmd = conn.CreateCommand()) |
| | 0 | 40 | | { |
| | 0 | 41 | | tryLockCmd.CommandText = |
| | 0 | 42 | | $"SELECT pg_try_advisory_lock({PostgresConstants.MigrationAdvisoryLockId})"; |
| | 0 | 43 | | acquired = (bool)(await tryLockCmd.ExecuteScalarAsync(ct))!; |
| | 0 | 44 | | } |
| | | 45 | | |
| | 0 | 46 | | if (acquired) |
| | 0 | 47 | | { |
| | | 48 | | try |
| | 0 | 49 | | { |
| | 0 | 50 | | await database.MigrateAsync(ct); |
| | 0 | 51 | | return; |
| | | 52 | | } |
| | | 53 | | finally |
| | 0 | 54 | | { |
| | 0 | 55 | | await using var unlockCmd = conn.CreateCommand(); |
| | 0 | 56 | | unlockCmd.CommandText = |
| | 0 | 57 | | $"SELECT pg_advisory_unlock({PostgresConstants.MigrationAdvisoryLockId})"; |
| | 0 | 58 | | await unlockCmd.ExecuteNonQueryAsync(ct); |
| | 0 | 59 | | } |
| | 0 | 60 | | } |
| | | 61 | | |
| | 0 | 62 | | if (attempt < maxAttempts) |
| | 0 | 63 | | await Task.Delay(TimeSpan.FromSeconds(1), ct); |
| | 0 | 64 | | } |
| | | 65 | | |
| | 0 | 66 | | throw new TimeoutException( |
| | 0 | 67 | | $"Could not acquire the migration advisory lock after {maxAttempts} attempts."); |
| | | 68 | | } |
| | | 69 | | finally |
| | 0 | 70 | | { |
| | 0 | 71 | | await database.CloseConnectionAsync(); |
| | 0 | 72 | | } |
| | 0 | 73 | | } |
| | | 74 | | } |