1# The sidecar module booted in a VM (flake check `sidecar`): the target
2# of nix/test-target.nix (loopback port 5433, TLS and SCRAM only) stands
3# in for the managed database, and the module's own Postgres reaches it
4# through postgres_fdw. It checks what the module promises:
5#
6# - `CREATE EXTENSION postjevsql` succeeds on the sidecar;
7# - the target's tables are imported and readable, over verify-full TLS
8#   with the mapping's password read from its file;
9# - a jev call over a foreign table plans as the scan over a ForeignScan;
10# - restarting the unit (the sidecar CLI's `converge` and `sync --apply`)
11#   converges: a column added on the target appears, and an `extensions`
12#   option added by hand is dropped;
13# - the mapping's password never reaches the store: the generated config
14#   names only the unit's credential path.
15{
16  pkgs,
17  sidecar,
18}:
19let
20  target = import ./test-target.nix { inherit pkgs; };
21in
22pkgs.testers.runNixOSTest {
23  name = "postjevsql-sidecar";
24
25  nodes.machine = {
26    imports = [
27      sidecar
28      target.module
29    ];
30    virtualisation.memorySize = 2048;
31    environment.etc."sidecar/target-password" = {
32      text = target.password;
33      mode = "0400";
34      user = "postgres";
35    };
36    environment.etc."sidecar/typesafe-key" = {
37      text = "test-key-never-sent";
38      mode = "0400";
39      user = "postgres";
40    };
41
42    services.postgresql.package = pkgs.postgresql_18;
43    services.postjevsql-sidecar = {
44      enable = true;
45      apiKeyFile = "/etc/sidecar/typesafe-key";
46      settings.model = "jev-1.13.0";
47      servers.target = {
48        host = "localhost";
49        port = 5433;
50        dbname = "appdb";
51        sslRootCert = "${target.certs}/ca.crt";
52        mappings.postgres = {
53          remoteUser = "app";
54          passwordFile = "/etc/sidecar/target-password";
55        };
56        schemas = [ "public" ];
57      };
58    };
59    systemd.services.postjevsql-sidecar = {
60      after = [ "pg-target.service" ];
61      requires = [ "pg-target.service" ];
62    };
63  };
64
65  testScript = ''
66    from shlex import quote
67
68    def sidecar(sql):
69        return machine.succeed(
70            f"sudo -u postgres psql --no-psqlrc -v ON_ERROR_STOP=1 -At -d postjevsql -c {quote(sql)}"
71        ).strip()
72
73    def target(sql):
74        return machine.succeed(
75            f"sudo -u postgres ${target.psql} -At -d appdb -c {quote(sql)}"
76        ).strip()
77
78    machine.wait_for_unit("pg-target.service")
79    machine.wait_for_unit("postjevsql-sidecar.service")
80
81    with subtest("the extension is installed on the sidecar"):
82        installed = sidecar("SELECT string_agg(extname, ',' ORDER BY extname) FROM pg_extension")
83        assert "postjevsql" in installed.split(","), installed
84        assert "postgres_fdw" in installed.split(","), installed
85
86    with subtest("the target's tables are imported and read over verify-full TLS"):
87        options = sidecar("SELECT array_to_string(srvoptions, ' ') FROM pg_foreign_server WHERE srvname = 'target'")
88        assert "sslmode=verify-full" in options, options
89        assert "use_remote_estimate=true" in options, options
90        assert "extensions=" not in options, options
91        rows = sidecar("SELECT string_agg(id || ':' || body, ';' ORDER BY id) FROM public.tickets")
92        assert rows == "1:server down;2:typo on page", rows
93
94    with subtest("a jev call over a foreign table plans as the scan over a ForeignScan"):
95        plan = sidecar("EXPLAIN (COSTS OFF) SELECT id FROM public.tickets t WHERE jev(t, 'Urgent?')")
96        assert "Custom Scan" in plan, plan
97        assert "Foreign Scan" in plan, plan
98
99    with subtest("restarting the unit converges the server and the imports"):
100        target("ALTER TABLE public.tickets ADD COLUMN IF NOT EXISTS priority int")
101        sidecar("ALTER SERVER target OPTIONS (ADD extensions 'postjevsql')")
102        machine.succeed("systemctl restart postjevsql-sidecar.service")
103        options = sidecar("SELECT array_to_string(srvoptions, ' ') FROM pg_foreign_server WHERE srvname = 'target'")
104        assert "extensions=" not in options, options
105        columns = sidecar(
106            "SELECT string_agg(attname, ',' ORDER BY attnum) FROM pg_attribute "
107            "WHERE attrelid = 'public.tickets'::regclass AND attnum > 0"
108        )
109        assert columns == "id,body,priority", columns
110
111    with subtest("the password is a credential path, never in the store"):
112        start = machine.succeed(
113            "systemctl show -P ExecStart postjevsql-sidecar.service | grep -o '/nix/store/[^ ;]*' | head -1"
114        ).strip()
115        toml = machine.succeed(f"grep -o '/nix/store/[^ ]*[.]toml' {start} | head -1").strip()
116        machine.succeed(f"grep -q /run/credentials/postjevsql-sidecar.service/ {toml}")
117        machine.fail(f"grep -q ${target.password} {start} {toml}")
118  '';
119}