1# The stand-in managed database the sidecar tests point at: a Postgres
2# 18 on loopback port 5433, TLS and SCRAM only, whose certificate a
3# build-time CA signs for `localhost`. The key and certificate are test
4# fixtures. It holds appdb.public.tickets, readable by role `app`.
5{ pkgs }:
6let
7  postgresql = pkgs.postgresql_18;
8  password = "target-secret";
9  psql = "psql --no-psqlrc -v ON_ERROR_STOP=1 -h /run/pg-target -p 5433";
10  certs = pkgs.runCommand "sidecar-test-certs" { nativeBuildInputs = [ pkgs.openssl ]; } ''
11    mkdir $out
12    openssl req -x509 -newkey rsa:2048 -nodes -days 36500 -subj /CN=test-ca \
13      -keyout ca.key -out $out/ca.crt
14    openssl req -newkey rsa:2048 -nodes -subj /CN=localhost \
15      -keyout $out/server.key -out server.csr
16    printf 'subjectAltName=DNS:localhost\n' > ext
17    openssl x509 -req -in server.csr -CA $out/ca.crt -CAkey ca.key -CAcreateserial \
18      -days 36500 -extfile ext -out $out/server.crt
19  '';
20in
21{
22  inherit certs password psql;
23  module = {
24    environment.systemPackages = [ postgresql ];
25    users.users.postgres = {
26      isSystemUser = true;
27      group = "postgres";
28    };
29    users.groups.postgres = { };
30    systemd.services.pg-target = {
31      wantedBy = [ "multi-user.target" ];
32      path = [ postgresql ];
33      serviceConfig = {
34        Type = "notify";
35        User = "postgres";
36        Group = "postgres";
37        StateDirectory = "pg-target";
38        StateDirectoryMode = "0700";
39        RuntimeDirectory = "pg-target";
40      };
41      preStart = ''
42        d=/var/lib/pg-target
43        if [ ! -e $d/PG_VERSION ]; then
44          initdb -D $d -U postgres --auth-local=peer
45          install -m 0600 ${certs}/server.key $d/server.key
46          cp ${certs}/server.crt $d/server.crt
47          cat > $d/pg_hba.conf <<'HBA'
48        local   all all                  peer
49        hostssl all all 127.0.0.1/32     scram-sha-256
50        HBA
51        fi
52      '';
53      script = ''
54        exec postgres -D /var/lib/pg-target -p 5433 -c listen_addresses=127.0.0.1 \
55          -c unix_socket_directories=/run/pg-target -c ssl=on
56      '';
57      postStart = ''
58        ${psql} -d postgres <<'SQL'
59        DO $$ BEGIN
60          IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'app') THEN
61            CREATE ROLE app LOGIN PASSWORD '${password}';
62          END IF;
63        END $$;
64        SELECT 'CREATE DATABASE appdb OWNER app' WHERE NOT EXISTS
65          (SELECT FROM pg_database WHERE datname = 'appdb') \gexec
66        SQL
67        ${psql} -d appdb -U postgres <<'SQL'
68        CREATE TABLE IF NOT EXISTS public.tickets (id int PRIMARY KEY, body text);
69        INSERT INTO public.tickets VALUES (1, 'server down'), (2, 'typo on page')
70          ON CONFLICT DO NOTHING;
71        GRANT SELECT ON public.tickets TO app;
72        SQL
73      '';
74    };
75  };
76}