pyrox.dev
1{
2 pkgs,
3 makeTest,
4 genTests,
5}:
6
7let
8 inherit (pkgs) lib;
9
10 makeTestFor =
11 package:
12 lib.recurseIntoAttrs {
13 postgresql = makeTestForWithBackupAll package false;
14 postgresql-backup-all = makeTestForWithBackupAll package true;
15 postgresql-clauses = makeEnsureTestFor package;
16 };
17
18 test-sql = pkgs.writeText "postgresql-test" (''
19 CREATE EXTENSION pgcrypto; -- just to check if lib loading works
20 CREATE TABLE sth (
21 id int
22 );
23 INSERT INTO sth (id) VALUES (1);
24 INSERT INTO sth (id) VALUES (1);
25 INSERT INTO sth (id) VALUES (1);
26 INSERT INTO sth (id) VALUES (1);
27 INSERT INTO sth (id) VALUES (1);
28 CREATE TABLE xmltest ( doc xml );
29 INSERT INTO xmltest (doc) VALUES ('<test>ok</test>'); -- check if libxml2 enabled
30
31 -- check if hardening gets relaxed
32 CREATE EXTENSION plv8;
33 -- try to trigger the V8 JIT, which requires MemoryDenyWriteExecute
34 DO $$
35 let xs = [];
36 for (let i = 0, n = 400000; i < n; i++) {
37 xs.push(Math.round(Math.random() * n))
38 }
39 console.log(xs.reduce((acc, x) => acc + x, 0));
40 $$ LANGUAGE plv8;
41 '');
42
43 makeTestForWithBackupAll =
44 package: backupAll:
45 makeTest {
46 name = "postgresql${lib.optionalString backupAll "-backup-all"}-${package.name}";
47 meta = with lib.maintainers; {
48 maintainers = [ zagy ];
49 };
50
51 nodes.machine =
52 { config, ... }:
53 {
54 services.postgresql = {
55 inherit package;
56 enable = true;
57 identMap = ''
58 postgres root postgres
59 '';
60 # TODO(@Ma27) split this off into its own VM test and move a few other
61 # extension tests to use postgresqlTestExtension.
62 extensions = ps: with ps; [ plv8 ];
63 };
64
65 services.postgresqlBackup = {
66 enable = true;
67 databases = lib.optional (!backupAll) "postgres";
68 pgdumpOptions = "--restrict-key=ABCDEFGHIJKLMNOPQRSTUVWXYZ";
69 pgdumpAllOptions = "--restrict-key=ABCDEFGHIJKLMNOPQRSTUVWXYZ";
70 };
71 };
72
73 testScript =
74 let
75 backupName = if backupAll then "all" else "postgres";
76 backupService = if backupAll then "postgresqlBackup" else "postgresqlBackup-postgres";
77 backupFileBase = "/var/backup/postgresql/${backupName}";
78 in
79 ''
80 def check_count(statement, lines):
81 return 'test $(psql -U postgres postgres -tAc "{}"|wc -l) -eq {}'.format(
82 statement, lines
83 )
84
85
86 machine.start()
87 machine.wait_for_unit("postgresql.target")
88
89 with subtest("Postgresql is available just after unit start"):
90 machine.succeed(
91 "cat ${test-sql} | sudo -u postgres psql"
92 )
93
94 with subtest("Postgresql survives restart (bug #1735)"):
95 machine.shutdown()
96 import time
97 time.sleep(2)
98 machine.start()
99 machine.wait_for_unit("postgresql.target")
100
101 machine.fail(check_count("SELECT * FROM sth;", 3))
102 machine.succeed(check_count("SELECT * FROM sth;", 5))
103 machine.fail(check_count("SELECT * FROM sth;", 4))
104 machine.succeed(check_count("SELECT xpath('/test/text()', doc) FROM xmltest;", 1))
105
106 with subtest("killing postgres process should trigger an automatic restart"):
107 machine.succeed("systemctl kill -s KILL postgresql")
108
109 machine.wait_until_succeeds("systemctl is-active postgresql.service")
110 machine.wait_until_succeeds("systemctl is-active postgresql.target")
111
112 with subtest("Backup service works"):
113 machine.succeed(
114 "systemctl start ${backupService}.service",
115 "zcat ${backupFileBase}.sql.gz | grep '<test>ok</test>'",
116 "ls -hal /var/backup/postgresql/ >/dev/console",
117 "stat -c '%a' ${backupFileBase}.sql.gz | grep 600",
118 )
119 with subtest("Backup service removes prev files"):
120 machine.succeed(
121 # Create dummy prev files.
122 "touch ${backupFileBase}.prev.sql{,.gz,.zstd}",
123 "chown postgres:postgres ${backupFileBase}.prev.sql{,.gz,.zstd}",
124
125 # Run backup.
126 "systemctl start ${backupService}.service",
127 "ls -hal /var/backup/postgresql/ >/dev/console",
128
129 # Since nothing has changed in the database, the cur and prev files
130 # should match.
131 "zcat ${backupFileBase}.sql.gz | grep '<test>ok</test>'",
132 "cmp ${backupFileBase}.sql.gz ${backupFileBase}.prev.sql.gz",
133
134 # The prev files with unused suffix should be removed.
135 "[ ! -f '${backupFileBase}.prev.sql' ]",
136 "[ ! -f '${backupFileBase}.prev.sql.zstd' ]",
137
138 # Both cur and prev file should only be accessible by the postgres user.
139 "stat -c '%a' ${backupFileBase}.sql.gz | grep 600",
140 "stat -c '%a' '${backupFileBase}.prev.sql.gz' | grep 600",
141 )
142 with subtest("Backup service fails gracefully"):
143 # Sabotage the backup process
144 machine.succeed("rm /run/postgresql/.s.PGSQL.5432")
145 machine.fail(
146 "systemctl start ${backupService}.service",
147 )
148 machine.succeed(
149 "ls -hal /var/backup/postgresql/ >/dev/console",
150 "zcat ${backupFileBase}.prev.sql.gz | grep '<test>ok</test>'",
151 "stat ${backupFileBase}.in-progress.sql.gz",
152 )
153 # In a previous version, the second run would overwrite prev.sql.gz,
154 # so we test a second run as well.
155 machine.fail(
156 "systemctl start ${backupService}.service",
157 )
158 machine.succeed(
159 "stat ${backupFileBase}.in-progress.sql.gz",
160 "zcat ${backupFileBase}.prev.sql.gz | grep '<test>ok</test>'",
161 )
162
163
164 with subtest("Initdb works"):
165 machine.succeed("sudo -u postgres initdb -D /tmp/testpostgres2")
166
167 machine.log(machine.execute("systemd-analyze security postgresql.service | grep -v ✓")[1])
168
169 machine.shutdown()
170 '';
171 };
172
173 makeEnsureTestFor =
174 package:
175 makeTest {
176 name = "postgresql-clauses-${package.name}";
177 meta = with lib.maintainers; {
178 maintainers = [ zagy ];
179 };
180
181 nodes.machine =
182 { ... }:
183 {
184 services.postgresql = {
185 inherit package;
186 enable = true;
187 ensureUsers = [
188 {
189 name = "all-clauses";
190 ensureClauses = {
191 superuser = true;
192 createdb = true;
193 createrole = true;
194 "inherit" = true;
195 login = true;
196 replication = true;
197 bypassrls = true;
198 };
199 }
200 {
201 name = "default-clauses";
202 }
203 ];
204 };
205 };
206
207 testScript =
208 let
209 getClausesQuery =
210 user:
211 lib.concatStringsSep " " [
212 "SELECT row_to_json(row)"
213 "FROM ("
214 "SELECT"
215 "rolsuper,"
216 "rolinherit,"
217 "rolcreaterole,"
218 "rolcreatedb,"
219 "rolcanlogin,"
220 "rolreplication,"
221 "rolbypassrls"
222 "FROM pg_roles"
223 "WHERE rolname = '${user}'"
224 ") row;"
225 ];
226 in
227 ''
228 import json
229 machine.start()
230 machine.wait_for_unit("postgresql.target")
231
232 with subtest("All user permissions are set according to the ensureClauses attr"):
233 clauses = json.loads(
234 machine.succeed(
235 "sudo -u postgres psql -tc \"${getClausesQuery "all-clauses"}\""
236 )
237 )
238 print(clauses)
239 assert clauses['rolsuper'], 'expected user with clauses to have superuser clause'
240 assert clauses['rolinherit'], 'expected user with clauses to have inherit clause'
241 assert clauses['rolcreaterole'], 'expected user with clauses to have create role clause'
242 assert clauses['rolcreatedb'], 'expected user with clauses to have create db clause'
243 assert clauses['rolcanlogin'], 'expected user with clauses to have login clause'
244 assert clauses['rolreplication'], 'expected user with clauses to have replication clause'
245 assert clauses['rolbypassrls'], 'expected user with clauses to have bypassrls clause'
246
247 with subtest("All user permissions default when ensureClauses is not provided"):
248 clauses = json.loads(
249 machine.succeed(
250 "sudo -u postgres psql -tc \"${getClausesQuery "default-clauses"}\""
251 )
252 )
253 assert not clauses['rolsuper'], 'expected user with no clauses set to have default superuser clause'
254 assert clauses['rolinherit'], 'expected user with no clauses set to have default inherit clause'
255 assert not clauses['rolcreaterole'], 'expected user with no clauses set to have default create role clause'
256 assert not clauses['rolcreatedb'], 'expected user with no clauses set to have default create db clause'
257 assert clauses['rolcanlogin'], 'expected user with no clauses set to have default login clause'
258 assert not clauses['rolreplication'], 'expected user with no clauses set to have default replication clause'
259 assert not clauses['rolbypassrls'], 'expected user with no clauses set to have default bypassrls clause'
260
261 machine.shutdown()
262 '';
263 };
264in
265genTests { inherit makeTestFor; }