Files
ExecuteR/tests/Shared.Tests/InstallerPostgresTests.cs
Alphaeus Mote 23b6feae6b feat(installer): database, folders and network pages for the server MSI
The server MSI's wizard is now Welcome > Folders > Database > Certificate >
Server Address > Ready, after StifleR's flow:

- Folders: install, data, log and backup folders with Browse (defaults follow
  a changed install folder), Enable Windows Long Paths while they are off.
- Database: SQLite, PostgreSQL (host, port, database, SSL mode, user,
  password) or SQL Server (server\instance, port, database, Windows or SQL
  login, Encrypt, Trust Server Certificate), Create If Missing, and Test
  Connection with the server's own error; Next needs a passing test or
  Continue Without a Successful Test.
- Server Address: HTTPS port and an HTTP redirect port, Listen On all or one
  of this computer's addresses, NATS and object store ports, public URL,
  firewall rules; a port in use names the program holding it.

Every choice is a public property (DATAFOLDER LOGFOLDER BACKUPFOLDER
ENABLELONGPATHS DBPROVIDER DBSERVER DBPORT DBNAME DBAUTH DBUSER DBPASSWORD
DBSSLMODE DBENCRYPT DBTRUSTCERT DBCREATE DBSKIPTEST HTTPREDIRECTPORT
OBJECTSTOREPORT PUBLICSERVERURL FIREWALL), checked the same way for /qn
before anything changes. The custom actions test PostgreSQL with their own
protocol client (SCRAM-SHA-256, MD5, SSL) and SQL Server with
System.Data.SqlClient, create the database when the account may (and give
the service's Windows login db_owner), write appsettings.json in place, keep
the password DPAPI-protected and out of the log, restrict the file and new
folders to SYSTEM and Administrators, create long folders through \\?\, and
keep netsh firewall rules for the ports in effect.

Upgrades find the install folder again, keep the configuration and change
the database only when the command line gives another; the choices but the
password are remembered for a reinstall. The banner and Welcome/Finish
artwork are drawn from the product icon by Generate-Icons.ps1.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-11-06 17:37:59 -05:00

320 lines
12 KiB
C#

using System.Net;
using System.Net.Sockets;
using System.Text;
using ExecuteR.Installer.CustomActions;
using Xunit;
namespace Shared.Tests;
/// <summary>
/// The server MSI's PostgreSQL test (PostgresWire, DatabaseProbe.Postgres): SCRAM against RFC 7677's example, the probe's steps against
/// a scripted server, and, when PGHOST, PGUSER and PGPASSWORD are set, a real server (a database created and dropped).
/// </summary>
public sealed class InstallerPostgresTests
{
[Fact]
public void ScramMatchesRfc7677()
{
var scram = new ScramSha256("pencil", "rOprNGfwEbeRWgbNEkqO", "user");
Assert.Equal("n,,n=user,r=rOprNGfwEbeRWgbNEkqO", scram.ClientFirst());
Assert.Equal(
"c=biws,r=rOprNGfwEbeRWgbNEkqO%hvYDpWUa2RaTCAfuxFIlj)hNlF$k0,p=dHzbZapWIk4jUhN+Ute9ytag9zjfMHgsqmmiz7AndVQ=",
scram.ClientFinal("r=rOprNGfwEbeRWgbNEkqO%hvYDpWUa2RaTCAfuxFIlj)hNlF$k0,s=W22ZaJ0SNY7soEsUEjb6gQ==,i=4096"));
Assert.True(scram.VerifyServerFinal("v=6rriTRBi23WpRR/wtup+mMhUZUn/dB5nLTJRsjl95G4="));
Assert.False(scram.VerifyServerFinal("v=AAAA"));
}
[Fact]
public void ScramRefusesAChallengeForAnotherNonce()
{
var scram = new ScramSha256("pencil", "abc");
scram.ClientFirst();
Assert.Throws<System.Security.Authentication.AuthenticationException>(() => scram.ClientFinal("r=xyz,s=AAAA,i=4096"));
}
[Fact]
public void Md5IsPostgresqlsOwn()
{
// md5(md5("secret" + "alice") + salt), the way libpq computes it.
using var md5 = System.Security.Cryptography.MD5.Create();
string Hex(byte[] bytes) => Convert.ToHexString(bytes).ToLowerInvariant();
var salt = new byte[] { 1, 2, 3, 4 };
var inner = Hex(md5.ComputeHash(Encoding.UTF8.GetBytes("secretalice")));
var expected = "md5" + Hex(md5.ComputeHash(Encoding.ASCII.GetBytes(inner).Concat(salt).ToArray()));
Assert.Equal(expected, PostgresWire.Md5Password("alice", "secret", salt));
}
[Fact]
public void AnErrorResponseCarriesTheServersWords()
{
var body = Encoding.UTF8.GetBytes("SFATAL\0C28P01\0Mpassword authentication failed for user \"x\"\0Hcheck it\0\0");
var error = PostgresWire.ErrorResponse(body);
Assert.Equal("28P01", error.Code);
Assert.Equal("password authentication failed for user \"x\" check it", error.Full);
}
[Fact]
public async Task AnExistingDatabasePasses()
{
await using var server = new ScriptedPostgres(database => database == "executer" ? null : "3D000");
var result = DatabaseProbe.Postgres(Settings(server.Port, "executer"), create: false, timeoutMilliseconds: 5000);
Assert.True(result.Passed, result.Line);
Assert.Contains("PostgreSQL 17.0", result.Line);
Assert.Equal("secret", server.Passwords.Single());
}
[Fact]
public async Task AMissingDatabaseIsCreatedWhenTheUserMay()
{
await using var server = new ScriptedPostgres(database => database == "postgres" ? null : "3D000", mayCreate: true);
var test = DatabaseProbe.Postgres(Settings(server.Port, "newdb"), create: false, timeoutMilliseconds: 5000);
Assert.True(test.Passed, test.Line);
Assert.True(test.Missing);
Assert.Contains("setup creates it", test.Line);
Assert.DoesNotContain(server.Queries, query => query.StartsWith("CREATE", StringComparison.Ordinal));
var install = DatabaseProbe.Postgres(Settings(server.Port, "newdb"), create: true, timeoutMilliseconds: 5000);
Assert.True(install.Created, install.Line);
Assert.Contains("CREATE DATABASE \"newdb\"", server.Queries);
}
[Fact]
public async Task AMissingDatabaseTheUserMayNotCreateSaysWhatIsNeeded()
{
await using var server = new ScriptedPostgres(database => database == "postgres" ? null : "3D000", mayCreate: false);
var result = DatabaseProbe.Postgres(Settings(server.Port, "newdb"), create: true, timeoutMilliseconds: 5000);
Assert.False(result.Passed);
Assert.Contains("may not create it", result.Line);
Assert.Contains("CREATEDB", result.Line);
}
[Fact]
public async Task AWrongPasswordIsTheServersError()
{
await using var server = new ScriptedPostgres(_ => "28P01");
var result = DatabaseProbe.Postgres(Settings(server.Port, "executer"), create: false, timeoutMilliseconds: 5000);
Assert.False(result.Passed);
Assert.Contains("(28P01)", result.Line);
}
[Fact]
public void NoServerIsANetworkLine()
{
var port = FreePort();
var result = DatabaseProbe.Postgres(Settings(port, "x"), create: false, timeoutMilliseconds: 3000);
Assert.False(result.Passed);
Assert.StartsWith($"Cannot reach PostgreSQL at 127.0.0.1:{port}", result.Line);
}
[Fact]
public void ARealServerSignsInWithScramAndCreatesTheDatabase()
{
var host = Environment.GetEnvironmentVariable("PGHOST");
var user = Environment.GetEnvironmentVariable("PGUSER");
var password = Environment.GetEnvironmentVariable("PGPASSWORD");
if (string.IsNullOrEmpty(host) || string.IsNullOrEmpty(user) || password is null)
{
Assert.Skip("No PostgreSQL to test against (PGHOST, PGUSER, PGPASSWORD).");
}
var name = $"executer_setup_{Guid.NewGuid():N}"[..30];
var settings = new DatabaseSettings
{
Provider = DatabaseSettings.PostgreSql,
Server = host!,
Port = Environment.GetEnvironmentVariable("PGPORT") ?? "5432",
Name = name,
User = user!,
Password = password,
SslMode = "Prefer",
};
var test = DatabaseProbe.Postgres(settings, create: false);
Assert.True(test.Passed || test.Line.Contains("may not create", StringComparison.Ordinal), test.Line);
if (!test.Passed)
{
return;
}
try
{
var created = DatabaseProbe.Postgres(settings, create: true);
Assert.True(created.Created, created.Line);
var again = DatabaseProbe.Postgres(settings, create: false);
Assert.True(again.Passed && !again.Missing, again.Line);
settings.Password = password + "-wrong";
var wrong = DatabaseProbe.Postgres(settings, create: false);
Assert.False(wrong.Passed);
Assert.Contains("28P01", wrong.Line);
}
finally
{
using var wire = PostgresWire.Open(host!, settings.PostgresPort, "postgres", user!, password, "Prefer", 15000);
wire.Query($"DROP DATABASE IF EXISTS {DatabaseSettings.QuotePostgresIdentifier(name)}");
}
}
private static DatabaseSettings Settings(int port, string database) => new()
{
Provider = DatabaseSettings.PostgreSql,
Server = "127.0.0.1",
Port = port.ToString(System.Globalization.CultureInfo.InvariantCulture),
Name = database,
User = "executer",
Password = "secret",
SslMode = "Prefer",
};
private static int FreePort()
{
using var listener = new TcpListener(IPAddress.Loopback, 0);
listener.Start();
return ((IPEndPoint)listener.LocalEndpoint).Port;
}
/// <summary>
/// A PostgreSQL server that speaks just enough: refuses SSL, asks for a cleartext password, then for each database either
/// signs in (server_version 17.0) or sends the SQLSTATE <c>refuse</c> gives; answers the probe's queries.
/// </summary>
private sealed class ScriptedPostgres : IAsyncDisposable
{
private readonly TcpListener listener = new(IPAddress.Loopback, 0);
private readonly CancellationTokenSource stop = new();
private readonly Task loop;
private readonly Func<string, string?> refuse;
private readonly bool mayCreate;
public ScriptedPostgres(Func<string, string?> refuse, bool mayCreate = true)
{
this.refuse = refuse;
this.mayCreate = mayCreate;
listener.Start();
loop = Task.Run(AcceptAsync);
}
public int Port => ((IPEndPoint)listener.LocalEndpoint).Port;
public List<string> Queries { get; } = [];
public List<string> Passwords { get; } = [];
public async ValueTask DisposeAsync()
{
stop.Cancel();
listener.Stop();
try
{
await loop;
}
catch (Exception exception) when (exception is OperationCanceledException or SocketException or ObjectDisposedException)
{
// Stopped.
}
}
private async Task AcceptAsync()
{
while (!stop.IsCancellationRequested)
{
using var client = await listener.AcceptTcpClientAsync(stop.Token);
try
{
await ServeAsync(client.GetStream());
}
catch (Exception exception) when (exception is IOException or EndOfStreamException or SocketException)
{
// The client hung up; the next one is served.
}
}
}
private async Task ServeAsync(NetworkStream stream)
{
var length = ReadInt32(await ReadAsync(stream, 4));
var body = await ReadAsync(stream, length - 4);
if (ReadInt32(body) == 80877103)
{
stream.WriteByte((byte)'N');
length = ReadInt32(await ReadAsync(stream, 4));
body = await ReadAsync(stream, length - 4);
}
var fields = Encoding.UTF8.GetString(body, 4, body.Length - 4).Split('\0');
var database = fields[Array.IndexOf(fields, "database") + 1];
await SendAsync(stream, 'R', Int32(3));
var (_, password) = await ReceiveAsync(stream);
Passwords.Add(Encoding.UTF8.GetString(password).TrimEnd('\0'));
if (refuse(database) is { } code)
{
await SendAsync(stream, 'E', Encoding.UTF8.GetBytes($"SFATAL\0C{code}\0Mrefused for {database}\0\0"));
return;
}
await SendAsync(stream, 'R', Int32(0));
await SendAsync(stream, 'S', Encoding.UTF8.GetBytes("server_version\017.0\0"));
await SendAsync(stream, 'Z', [(byte)'I']);
while (true)
{
var (type, message) = await ReceiveAsync(stream);
if (type != 'Q')
{
return;
}
var sql = Encoding.UTF8.GetString(message).TrimEnd('\0');
Queries.Add(sql);
if (sql.Contains("rolcreatedb", StringComparison.Ordinal))
{
await SendAsync(stream, 'D', Row(mayCreate ? "t" : "f"));
}
await SendAsync(stream, 'C', Encoding.UTF8.GetBytes("SELECT 1\0"));
await SendAsync(stream, 'Z', [(byte)'I']);
}
}
private static byte[] Row(string value)
{
var bytes = Encoding.UTF8.GetBytes(value);
return [0, 1, .. Int32(bytes.Length), .. bytes];
}
private static async Task<(char Type, byte[] Body)> ReceiveAsync(NetworkStream stream)
{
var header = await ReadAsync(stream, 5);
return ((char)header[0], await ReadAsync(stream, ReadInt32(header.AsSpan(1).ToArray()) - 4));
}
private static async Task SendAsync(NetworkStream stream, char type, byte[] body)
{
await stream.WriteAsync((byte[])[(byte)type, .. Int32(body.Length + 4), .. body]);
}
private static async Task<byte[]> ReadAsync(NetworkStream stream, int count)
{
var buffer = new byte[count];
await stream.ReadExactlyAsync(buffer);
return buffer;
}
private static byte[] Int32(int value) => [(byte)(value >> 24), (byte)(value >> 16), (byte)(value >> 8), (byte)value];
private static int ReadInt32(byte[] bytes) => (bytes[0] << 24) | (bytes[1] << 16) | (bytes[2] << 8) | bytes[3];
}
}