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 };
69 };
70
71 testScript =
72 let
73 backupName = if backupAll then "all" else "postgres";
74 backupService = if backupAll then "postgresqlBackup" else "postgresqlBackup-postgres";
75 backupFileBase = "/var/backup/postgresql/${backupName}";
76 in
77 ''
78 def check_count(statement, lines):
79 return 'test $(psql -U postgres postgres -tAc "{}"|wc -l) -eq {}'.format(
80 statement, lines
81 )
82
83
84 machine.start()
85 machine.wait_for_unit("postgresql")
86
87 with subtest("Postgresql is available just after unit start"):
88 machine.succeed(
89 "cat ${test-sql} | sudo -u postgres psql"
90 )
91
92 with subtest("Postgresql survives restart (bug #1735)"):
93 machine.shutdown()
94 import time
95 time.sleep(2)
96 machine.start()
97 machine.wait_for_unit("postgresql")
98
99 machine.fail(check_count("SELECT * FROM sth;", 3))
100 machine.succeed(check_count("SELECT * FROM sth;", 5))
101 machine.fail(check_count("SELECT * FROM sth;", 4))
102 machine.succeed(check_count("SELECT xpath('/test/text()', doc) FROM xmltest;", 1))
103
104 with subtest("Backup service works"):
105 machine.succeed(
106 "systemctl start ${backupService}.service",
107 "zcat ${backupFileBase}.sql.gz | grep '<test>ok</test>'",
108 "ls -hal /var/backup/postgresql/ >/dev/console",
109 "stat -c '%a' ${backupFileBase}.sql.gz | grep 600",
110 )
111 with subtest("Backup service removes prev files"):
112 machine.succeed(
113 # Create dummy prev files.
114 "touch ${backupFileBase}.prev.sql{,.gz,.zstd}",
115 "chown postgres:postgres ${backupFileBase}.prev.sql{,.gz,.zstd}",
116
117 # Run backup.
118 "systemctl start ${backupService}.service",
119 "ls -hal /var/backup/postgresql/ >/dev/console",
120
121 # Since nothing has changed in the database, the cur and prev files
122 # should match.
123 "zcat ${backupFileBase}.sql.gz | grep '<test>ok</test>'",
124 "cmp ${backupFileBase}.sql.gz ${backupFileBase}.prev.sql.gz",
125
126 # The prev files with unused suffix should be removed.
127 "[ ! -f '${backupFileBase}.prev.sql' ]",
128 "[ ! -f '${backupFileBase}.prev.sql.zstd' ]",
129
130 # Both cur and prev file should only be accessible by the postgres user.
131 "stat -c '%a' ${backupFileBase}.sql.gz | grep 600",
132 "stat -c '%a' '${backupFileBase}.prev.sql.gz' | grep 600",
133 )
134 with subtest("Backup service fails gracefully"):
135 # Sabotage the backup process
136 machine.succeed("rm /run/postgresql/.s.PGSQL.5432")
137 machine.fail(
138 "systemctl start ${backupService}.service",
139 )
140 machine.succeed(
141 "ls -hal /var/backup/postgresql/ >/dev/console",
142 "zcat ${backupFileBase}.prev.sql.gz | grep '<test>ok</test>'",
143 "stat ${backupFileBase}.in-progress.sql.gz",
144 )
145 # In a previous version, the second run would overwrite prev.sql.gz,
146 # so we test a second run as well.
147 machine.fail(
148 "systemctl start ${backupService}.service",
149 )
150 machine.succeed(
151 "stat ${backupFileBase}.in-progress.sql.gz",
152 "zcat ${backupFileBase}.prev.sql.gz | grep '<test>ok</test>'",
153 )
154
155
156 with subtest("Initdb works"):
157 machine.succeed("sudo -u postgres initdb -D /tmp/testpostgres2")
158
159 machine.log(machine.execute("systemd-analyze security postgresql.service | grep -v ✓")[1])
160
161 machine.shutdown()
162 '';
163 };
164
165 makeEnsureTestFor =
166 package:
167 makeTest {
168 name = "postgresql-clauses-${package.name}";
169 meta = with lib.maintainers; {
170 maintainers = [ zagy ];
171 };
172
173 nodes.machine =
174 { ... }:
175 {
176 services.postgresql = {
177 inherit package;
178 enable = true;
179 ensureUsers = [
180 {
181 name = "all-clauses";
182 ensureClauses = {
183 superuser = true;
184 createdb = true;
185 createrole = true;
186 "inherit" = true;
187 login = true;
188 replication = true;
189 bypassrls = true;
190 };
191 }
192 {
193 name = "default-clauses";
194 }
195 ];
196 };
197 };
198
199 testScript =
200 let
201 getClausesQuery =
202 user:
203 lib.concatStringsSep " " [
204 "SELECT row_to_json(row)"
205 "FROM ("
206 "SELECT"
207 "rolsuper,"
208 "rolinherit,"
209 "rolcreaterole,"
210 "rolcreatedb,"
211 "rolcanlogin,"
212 "rolreplication,"
213 "rolbypassrls"
214 "FROM pg_roles"
215 "WHERE rolname = '${user}'"
216 ") row;"
217 ];
218 in
219 ''
220 import json
221 machine.start()
222 machine.wait_for_unit("postgresql")
223
224 with subtest("All user permissions are set according to the ensureClauses attr"):
225 clauses = json.loads(
226 machine.succeed(
227 "sudo -u postgres psql -tc \"${getClausesQuery "all-clauses"}\""
228 )
229 )
230 print(clauses)
231 assert clauses['rolsuper'], 'expected user with clauses to have superuser clause'
232 assert clauses['rolinherit'], 'expected user with clauses to have inherit clause'
233 assert clauses['rolcreaterole'], 'expected user with clauses to have create role clause'
234 assert clauses['rolcreatedb'], 'expected user with clauses to have create db clause'
235 assert clauses['rolcanlogin'], 'expected user with clauses to have login clause'
236 assert clauses['rolreplication'], 'expected user with clauses to have replication clause'
237 assert clauses['rolbypassrls'], 'expected user with clauses to have bypassrls clause'
238
239 with subtest("All user permissions default when ensureClauses is not provided"):
240 clauses = json.loads(
241 machine.succeed(
242 "sudo -u postgres psql -tc \"${getClausesQuery "default-clauses"}\""
243 )
244 )
245 assert not clauses['rolsuper'], 'expected user with no clauses set to have default superuser clause'
246 assert clauses['rolinherit'], 'expected user with no clauses set to have default inherit clause'
247 assert not clauses['rolcreaterole'], 'expected user with no clauses set to have default create role clause'
248 assert not clauses['rolcreatedb'], 'expected user with no clauses set to have default create db clause'
249 assert clauses['rolcanlogin'], 'expected user with no clauses set to have default login clause'
250 assert not clauses['rolreplication'], 'expected user with no clauses set to have default replication clause'
251 assert not clauses['rolbypassrls'], 'expected user with no clauses set to have default bypassrls clause'
252
253 machine.shutdown()
254 '';
255 };
256in
257genTests { inherit makeTestFor; }