Keep accounts and sessions in PostgreSQL

The account API kept everything in the memory of its process, so every
restart and every deployment of the API deleted all accounts and signed
everyone out.

PostgresAccountStore implements IAccountStore on PostgreSQL through Npgsql.
The three uniqueness rules are unique constraints, so two requests that race
for one name, address or Google identity are decided by the database. A
refused account is an ordinary answer, not an exception, and the rules are
reported in the order the in-memory store reports them. A session refers to
its account, and a session of an account that is not stored is refused; the
in-memory store now refuses it as well.

The schema is a list of numbered steps. The service takes the missing ones
when it starts, in one transaction under an advisory lock, and does not
start when the database cannot be reached or is newer than the release.
/health answers 503 while the database does not answer.

ConnectionStrings:Accounts selects PostgreSQL. A production service that is
given no database does not start; keeping accounts in memory there has to
be asked for with Accounts:Store=Memory. Development and the tests keep the
in-memory store by default.

The store gains RemoveExpiredSessionsAsync, and a background service calls
it at the start and once an hour: a session whose token is never presented
again no longer keeps its row for ever.

PostgreSQL keeps a timestamp to the microsecond. The store cuts a moment to
that precision and returns what it stored, and the service uses the
returned account, so the creation time in the registration response equals
the one every later read returns.

Tests. AccountStoreContractTests states the rules once and runs them on
both stores. Further tests cover the schema steps applied concurrently and a
database that is too new, the choice of the store from the configuration,
the removal of expired sessions, and an account with its session surviving
a restart of the service over HTTP. The PostgreSQL tests read their
database from PROGMAWEB_TEST_POSTGRES and work in a schema of their own
each. Without the variable they are skipped; CI starts PostgreSQL 16 for
them, and a test fails the run when the variable is missing there.

Operations. ops/README.md describes the database: the Unix socket with peer
authentication, so that no password exists, the one-time preparation, and
rollback across a schema step. The service unit starts after PostgreSQL.
docs/LIMITATIONS.md no longer lists the in-memory store and now lists the
missing backup and the missing deletion of accounts.

1 files changed, 87 insertions, 0 deletions

diff --git a/apps/api-tests/PostgresTestDatabase.cs b/apps/api-tests/PostgresTestDatabase.cs
new file mode 100644
index 0000000..401a69c
--- /dev/null
+++ b/apps/api-tests/PostgresTestDatabase.cs
@@ -0,0 +1,87 @@
+// SPDX-FileCopyrightText: 2026 Progmasoft <support@progmasoft.com>
+// SPDX-License-Identifier: AGPL-3.0-or-later WITH AdditionRef-Progmasoft-Patent-Grant-1.1
+
+using Npgsql;
+
+namespace Progmasoft.Progmaweb.Api.Tests;
+
+/// <summary>A schema of its own in the PostgreSQL database of the test run, removed when the test ends.</summary>
+/// <remarks>
+/// <para>
+/// The tests that need PostgreSQL read its connection string from the environment variable
+/// <see cref="VariableName"/>. The user it names must be allowed to create schemas in its database. Without the
+/// variable those tests are skipped, and <see cref="PostgresConfigurationTests"/> fails the run where skipping
+/// them would hide a gap: in continuous integration.
+/// </para>
+/// <para>
+/// Each test works in a schema with a random name, selected through the search path of its connection string, so
+/// tests neither see nor wait for one another and nothing is left behind.
+/// </para>
+/// </remarks>
+internal sealed class PostgresTestDatabase : IAsyncDisposable
+{
+    /// <summary>Environment variable that holds the connection string of the test database.</summary>
+    public const string VariableName = "PROGMAWEB_TEST_POSTGRES";
+
+    private readonly string administrativeConnectionString;
+    private readonly string schema;
+
+    private PostgresTestDatabase(string administrativeConnectionString, string schema)
+    {
+        this.administrativeConnectionString = administrativeConnectionString;
+        this.schema = schema;
+        ConnectionString = new NpgsqlConnectionStringBuilder(administrativeConnectionString) { SearchPath = schema }
+            .ConnectionString;
+    }
+
+    /// <summary>The connection string of the test database, or <see langword="null"/> when none is configured.</summary>
+    public static string? ConfiguredConnectionString
+    {
+        get
+        {
+            string? value = Environment.GetEnvironmentVariable(VariableName);
+            return string.IsNullOrWhiteSpace(value) ? null : value;
+        }
+    }
+
+    /// <summary>A connection string whose statements run in the schema of this test.</summary>
+    public string ConnectionString { get; }
+
+    /// <summary>Creates the schema of a test; skips the test when no database is configured.</summary>
+    public static async Task<PostgresTestDatabase> CreateAsync()
+    {
+        string? configured = ConfiguredConnectionString;
+        Assert.SkipUnless(configured is not null, $"{VariableName} names no PostgreSQL database.");
+
+        string schema = "test_" + Guid.NewGuid().ToString("N");
+        await using NpgsqlDataSource dataSource = NpgsqlDataSource.Create(configured!);
+        await using NpgsqlCommand command = dataSource.CreateCommand($"CREATE SCHEMA {schema}");
+        await command.ExecuteNonQueryAsync(TestContext.Current.CancellationToken);
+        return new PostgresTestDatabase(configured!, schema);
+    }
+
+    /// <summary>Opens connections to the schema of this test. The caller disposes the data source.</summary>
+    public NpgsqlDataSource OpenDataSource() => NpgsqlDataSource.Create(ConnectionString);
+
+    public async ValueTask DisposeAsync()
+    {
+        // Pooled connections of disposed data sources are closed, so nothing holds the schema.
+        await using NpgsqlDataSource dataSource = NpgsqlDataSource.Create(administrativeConnectionString);
+        await using NpgsqlCommand command = dataSource.CreateCommand($"DROP SCHEMA IF EXISTS {schema} CASCADE");
+        await command.ExecuteNonQueryAsync(CancellationToken.None);
+    }
+}
+
+public sealed class PostgresConfigurationTests
+{
+    [Fact]
+    public void ContinuousIntegrationRunsTheDatabaseTests()
+    {
+        // GitHub Actions sets CI. A run there without the database would report the PostgreSQL tests as skipped and
+        // the run as passed, which is how an untested store would be merged.
+        if (string.Equals(Environment.GetEnvironmentVariable("CI"), "true", StringComparison.OrdinalIgnoreCase))
+        {
+            Assert.NotNull(PostgresTestDatabase.ConfiguredConnectionString);
+        }
+    }
+}