pyrox.dev
1{ system ? builtins.currentSystem,
2 config ? {},
3 pkgs ? import ../.. { inherit system config; }
4}:
5
6with import ../lib/testing-python.nix { inherit system pkgs; };
7with pkgs.lib;
8
9let
10 postgresql-versions = import ../../pkgs/servers/sql/postgresql pkgs;
11 test-sql = pkgs.writeText "postgresql-test" ''
12 CREATE EXTENSION pgcrypto; -- just to check if lib loading works
13 CREATE TABLE sth (
14 id int
15 );
16 INSERT INTO sth (id) VALUES (1);
17 INSERT INTO sth (id) VALUES (1);
18 INSERT INTO sth (id) VALUES (1);
19 INSERT INTO sth (id) VALUES (1);
20 INSERT INTO sth (id) VALUES (1);
21 CREATE TABLE xmltest ( doc xml );
22 INSERT INTO xmltest (doc) VALUES ('<test>ok</test>'); -- check if libxml2 enabled
23 '';
24 make-postgresql-test = postgresql-name: postgresql-package: backup-all: makeTest {
25 name = postgresql-name;
26 meta = with pkgs.lib.maintainers; {
27 maintainers = [ zagy ];
28 };
29
30 nodes.machine = {...}:
31 {
32 services.postgresql = {
33 enable = true;
34 package = postgresql-package;
35 };
36
37 services.postgresqlBackup = {
38 enable = true;
39 databases = optional (!backup-all) "postgres";
40 };
41 };
42
43 testScript = let
44 backupName = if backup-all then "all" else "postgres";
45 backupService = if backup-all then "postgresqlBackup" else "postgresqlBackup-postgres";
46 backupFileBase = "/var/backup/postgresql/${backupName}";
47 in ''
48 def check_count(statement, lines):
49 return 'test $(sudo -u postgres psql postgres -tAc "{}"|wc -l) -eq {}'.format(
50 statement, lines
51 )
52
53
54 machine.start()
55 machine.wait_for_unit("postgresql")
56
57 with subtest("Postgresql is available just after unit start"):
58 machine.succeed(
59 "cat ${test-sql} | sudo -u postgres psql"
60 )
61
62 with subtest("Postgresql survives restart (bug #1735)"):
63 machine.shutdown()
64 import time
65 time.sleep(2)
66 machine.start()
67 machine.wait_for_unit("postgresql")
68
69 machine.fail(check_count("SELECT * FROM sth;", 3))
70 machine.succeed(check_count("SELECT * FROM sth;", 5))
71 machine.fail(check_count("SELECT * FROM sth;", 4))
72 machine.succeed(check_count("SELECT xpath('/test/text()', doc) FROM xmltest;", 1))
73
74 with subtest("Backup service works"):
75 machine.succeed(
76 "systemctl start ${backupService}.service",
77 "zcat ${backupFileBase}.sql.gz | grep '<test>ok</test>'",
78 "ls -hal /var/backup/postgresql/ >/dev/console",
79 "stat -c '%a' ${backupFileBase}.sql.gz | grep 600",
80 )
81 with subtest("Backup service removes prev files"):
82 machine.succeed(
83 # Create dummy prev files.
84 "touch ${backupFileBase}.prev.sql{,.gz,.zstd}",
85 "chown postgres:postgres ${backupFileBase}.prev.sql{,.gz,.zstd}",
86
87 # Run backup.
88 "systemctl start ${backupService}.service",
89 "ls -hal /var/backup/postgresql/ >/dev/console",
90
91 # Since nothing has changed in the database, the cur and prev files
92 # should match.
93 "zcat ${backupFileBase}.sql.gz | grep '<test>ok</test>'",
94 "cmp ${backupFileBase}.sql.gz ${backupFileBase}.prev.sql.gz",
95
96 # The prev files with unused suffix should be removed.
97 "[ ! -f '${backupFileBase}.prev.sql' ]",
98 "[ ! -f '${backupFileBase}.prev.sql.zstd' ]",
99
100 # Both cur and prev file should only be accessible by the postgres user.
101 "stat -c '%a' ${backupFileBase}.sql.gz | grep 600",
102 "stat -c '%a' '${backupFileBase}.prev.sql.gz' | grep 600",
103 )
104 with subtest("Backup service fails gracefully"):
105 # Sabotage the backup process
106 machine.succeed("rm /run/postgresql/.s.PGSQL.5432")
107 machine.fail(
108 "systemctl start ${backupService}.service",
109 )
110 machine.succeed(
111 "ls -hal /var/backup/postgresql/ >/dev/console",
112 "zcat ${backupFileBase}.prev.sql.gz | grep '<test>ok</test>'",
113 "stat ${backupFileBase}.in-progress.sql.gz",
114 )
115 # In a previous version, the second run would overwrite prev.sql.gz,
116 # so we test a second run as well.
117 machine.fail(
118 "systemctl start ${backupService}.service",
119 )
120 machine.succeed(
121 "stat ${backupFileBase}.in-progress.sql.gz",
122 "zcat ${backupFileBase}.prev.sql.gz | grep '<test>ok</test>'",
123 )
124
125
126 with subtest("Initdb works"):
127 machine.succeed("sudo -u postgres initdb -D /tmp/testpostgres2")
128
129 machine.shutdown()
130 '';
131
132 };
133
134 mk-ensure-clauses-test = postgresql-name: postgresql-package: makeTest {
135 name = postgresql-name;
136 meta = with pkgs.lib.maintainers; {
137 maintainers = [ zagy ];
138 };
139
140 nodes.machine = {...}:
141 {
142 services.postgresql = {
143 enable = true;
144 package = postgresql-package;
145 ensureUsers = [
146 {
147 name = "all-clauses";
148 ensureClauses = {
149 superuser = true;
150 createdb = true;
151 createrole = true;
152 "inherit" = true;
153 login = true;
154 replication = true;
155 bypassrls = true;
156 };
157 }
158 {
159 name = "default-clauses";
160 }
161 ];
162 };
163 };
164
165 testScript = let
166 getClausesQuery = user: pkgs.lib.concatStringsSep " "
167 [
168 "SELECT row_to_json(row)"
169 "FROM ("
170 "SELECT"
171 "rolsuper,"
172 "rolinherit,"
173 "rolcreaterole,"
174 "rolcreatedb,"
175 "rolcanlogin,"
176 "rolreplication,"
177 "rolbypassrls"
178 "FROM pg_roles"
179 "WHERE rolname = '${user}'"
180 ") row;"
181 ];
182 in ''
183 import json
184 machine.start()
185 machine.wait_for_unit("postgresql")
186
187 with subtest("All user permissions are set according to the ensureClauses attr"):
188 clauses = json.loads(
189 machine.succeed(
190 "sudo -u postgres psql -tc \"${getClausesQuery "all-clauses"}\""
191 )
192 )
193 print(clauses)
194 assert clauses['rolsuper'], 'expected user with clauses to have superuser clause'
195 assert clauses['rolinherit'], 'expected user with clauses to have inherit clause'
196 assert clauses['rolcreaterole'], 'expected user with clauses to have create role clause'
197 assert clauses['rolcreatedb'], 'expected user with clauses to have create db clause'
198 assert clauses['rolcanlogin'], 'expected user with clauses to have login clause'
199 assert clauses['rolreplication'], 'expected user with clauses to have replication clause'
200 assert clauses['rolbypassrls'], 'expected user with clauses to have bypassrls clause'
201
202 with subtest("All user permissions default when ensureClauses is not provided"):
203 clauses = json.loads(
204 machine.succeed(
205 "sudo -u postgres psql -tc \"${getClausesQuery "default-clauses"}\""
206 )
207 )
208 assert not clauses['rolsuper'], 'expected user with no clauses set to have default superuser clause'
209 assert clauses['rolinherit'], 'expected user with no clauses set to have default inherit clause'
210 assert not clauses['rolcreaterole'], 'expected user with no clauses set to have default create role clause'
211 assert not clauses['rolcreatedb'], 'expected user with no clauses set to have default create db clause'
212 assert clauses['rolcanlogin'], 'expected user with no clauses set to have default login clause'
213 assert not clauses['rolreplication'], 'expected user with no clauses set to have default replication clause'
214 assert not clauses['rolbypassrls'], 'expected user with no clauses set to have default bypassrls clause'
215
216 machine.shutdown()
217 '';
218 };
219in
220 concatMapAttrs (name: package: {
221 ${name} = make-postgresql-test name package false;
222 ${name + "-clauses"} = mk-ensure-clauses-test name package;
223 }) postgresql-versions
224 // {
225 postgresql_11-backup-all = make-postgresql-test "postgresql_11-backup-all" postgresql-versions.postgresql_11 true;
226 }