summaryrefslogtreecommitdiffstats
path: root/t/020_create_sql_remove.t
blob: d208b99bfcb5920306130d539d82e49e4c889896 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
# We create a cluster, execute some basic SQL commands, drop it again, and
# check that we did not leave anything behind.

use strict; 

use File::Temp qw(tempdir);
use POSIX qw/dup2/;
use Time::HiRes qw/usleep/;

use lib 't';
use TestLib;
use PgCommon;

use Test::More tests => 147 * @MAJORS;

$ENV{_SYSTEMCTL_SKIP_REDIRECT} = 1; # FIXME: testsuite is hanging otherwise

sub check_major {
    my $v = $_[0];
    note "Running tests for $v";

    # create cluster
    my $xlogdir = tempdir("/tmp/$v.xlog.XXXXXX", CLEANUP => 1);
    rmdir $xlogdir; # recreated by initdb
    if ($v > 8.2) {
        my $start_command = $v >= 14 ? "$v *main *5432 *online" : # initdb --no-instructions in 14+
            ($v >= 11 and not $PgCommon::rpm) ? "pg_ctlcluster" : "pg_ctl"; # CLUSTER_START_COMMAND supported in initdb 11+
        like_program_out 'root', "pg_createcluster $v main --start -- -X $xlogdir", 0, qr/$start_command/,
            "pg_createcluster $v main";
    } else { # 8.2 does not have -X yet
        like_program_out 'root', "pg_createcluster $v main --start", 0, qr/pg_ctl/,
            "pg_createcluster $v main";
        system "mv /var/lib/postgresql/$v/main/pg_xlog $xlogdir";
        system "ln -s $xlogdir /var/lib/postgresql/$v/main/pg_xlog";
    }

    # check that a /var/run/postgresql/ pid file is created
    my @contents = ('.s.PGSQL.5432', '.s.PGSQL.5432.lock', "$v-main.pid", "$v-main.pg_stat_tmp");
    pop @contents if ($v < 8.4 or $v >= 15); # remove pg_stat_tmp
    unless ($PgCommon::rpm and $v < 9.4) {
        ok_dir '/var/run/postgresql/', [@contents],
            'Socket and pid file are in /var/run/postgresql/';
    } else {
        ok_dir '/var/run/postgresql/', [grep {/main/} @contents], 'Pid File is in /var/run/postgresql/';
    }

    # check that the xlog/wal symlink was created
    my $first_xlog = $v >= 9.0 ? "000000010000000000000001" : "000000010000000000000000";
    my @expectdir = ($first_xlog, "archive_status");
    push @expectdir, "summaries" if ($v >= 17);
    ok_dir $xlogdir, [@expectdir],
        "xlog/wal directory $xlogdir was created";

    # check pg_hba.conf auth methods
    my $local_method = $v >= 9.1 ? 'peer' :
                      ($v >= 8.4 ? 'ident' :
                                   'sameuser'); # actually "ident sameuser", but the test is lazy
    my $host_method = $v >= 14 ? 'scram-sha-256' : 'md5';
    my (%local_methods, %host_methods);
    open my $fh, "/etc/postgresql/$v/main/pg_hba.conf";
    while (<$fh>) {
        $local_methods{$1} = 1 if (/^local.*\s(\S+)/);
        $host_methods{$1} = 1 if (/^host.*\s(\S+)/);
    }
    close $fh;
    is_deeply [keys %local_methods], [$local_method], "local method in pg_hba.conf is $local_method";
    is_deeply [keys %host_methods], [$host_method], "host method in pg_hba.conf is $host_method";

    # verify that exactly one postgres master is running
    my @pm_pids = pidof ('postgres');
    is $#pm_pids, 0, 'Exactly one postgres master process running';

    # check environment
    my %safe_env = qw/LC_ALL 1 LC_CTYPE 1 LANG 1 PWD 1 PGLOCALEDIR 1 PGSYSCONFDIR 1 PG_GRANDPARENT_PID 1 PG_OOM_ADJUST_FILE 1 PG_OOM_ADJUST_VALUE 1 SHLVL 1 PGDATA 1 _ 1/;
    my %env = pid_env 'postgres', $pm_pids[0];
    foreach (keys %env) {
        fail "postgres has unsafe environment variable $_" unless exists $safe_env{$_};
    }

    # activate external_pid_file
    PgCommon::set_conf_value $v, 'main', 'postgresql.conf', 'external_pid_file', '';

    # add variable to environment file, restart, check if it's there
    open E, ">>/etc/postgresql/$v/main/environment" or 
        die 'could not open environment file for appending';
    print E "PGEXTRAVAR1 = 1 # short one\nPGEXTRAVAR2='foo bar '\n\n# comment";
    close E;
    is_program_out 0, "pg_ctlcluster $v main restart", 0, '',
        'cluster restarts with new environment file';

    @pm_pids = pidof ('postgres');
    is $#pm_pids, 0, 'Exactly one postgres master process running';
    %env = pid_env 'postgres', $pm_pids[0];
    is $env{'PGEXTRAVAR1'}, '1', 'correct value of PGEXTRAVAR1 in environment';
    is $env{'PGEXTRAVAR2'}, 'foo bar ', 'correct value of PGEXTRAVAR2 in environment';

    # Now there should not be an external PID file any more, since we set it
    # explicitly
    unless ($PgCommon::rpm and ($v < 9.4 or $v >= 15)) {
        ok_dir '/var/run/postgresql', [grep {! /pid/} @contents],
            'Socket and stats dir, but not PID file in /var/run/postgresql/';
    } else {
        ok_dir '/var/run/postgresql', ["$v-main.pg_stat_tmp"], 'Only stats dir in /var/run/postgresql/';
    }

    # verify that the correct client version is selected
    like_program_out 'postgres', 'createdb --version', 0, qr/^createdb \(PostgreSQL\) $v/,
        'pg_wrapper+createdb selects version number of cluster';

    # we always want to use the latest version of "psql", though.
    my $max_version = $ALL_MAJORS[-1];
    if ($v < 9.2) {
        # if version is older than 9.2 pick v14 at most
        $max_version = (grep { $_ <= 14 } @ALL_MAJORS)[-1];
    }
    like_program_out 'postgres', 'psql --version', 0, qr/^psql \(PostgreSQL\) $max_version/,
        "pg_wrapper+psql selects version $max_version";

    my $default_log = "/var/log/postgresql/postgresql-$v-main.log";

    # verify that the cluster is displayed
    my $ls = `pg_lsclusters -h`;
    $ls =~ s/\s+/ /g;
    $ls =~ s/\s*$//;
    is $ls, "$v main 5432 online postgres /var/lib/postgresql/$v/main $default_log",
	'pg_lscluster reports online cluster on port 5432';

    # verify that the log file is actually used
    ok !-z $default_log, 'log file is actually used';

    # verify configuration file permissions
    my $postgres_uid = (getpwnam 'postgres')[2];
    my @st = stat "/etc/postgresql";
    is $st[4], $postgres_uid, '/etc/postgresql is owned by user "postgres"';
    my @st = stat "/etc/postgresql/$v";
    is $st[4], $postgres_uid, 'version configuration directory file is owned by user "postgres"';
    my @st = stat "/etc/postgresql/$v/main";
    is $st[4], $postgres_uid, 'configuration directory file is owned by user "postgres"';

    # verify data file permissions
    my @st = stat "/var/lib/postgresql/$v";
    is $st[4], $postgres_uid, 'version data directory file is owned by user "postgres"';
    my @st = stat "/var/lib/postgresql/$v/main";
    is $st[4], $postgres_uid, 'data directory file is owned by user "postgres"';

    # verify log file permissions
    my @logstat = stat $default_log;
    is $logstat[2], 0100640, 'log file has 0640 permissions';
    is $logstat[4], $postgres_uid, 'log file is owned by user "postgres"';
    is $logstat[5], (getgrnam 'adm')[2], 'log file is owned by group "adm"';

    # check default log file configuration; when not specifying -l with
    # pg_createcluster, we should not have a 'log' symlink
    ok !-e "/etc/postgresql/$v/main/log", 'no log symlink by default';
    ok !-z $default_log, "$default_log is the default log if log symlink is missing";
    like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/^$v\s+main.*$default_log\n$/;

    # verify that log symlink works
    is ((exec_as 'root', "pg_ctlcluster $v main stop"), 0, 'stopping cluster');
    usleep $delay;
    truncate "$default_log", 0; # empty log file
    my $p = (PgCommon::cluster_data_directory $v, 'main') . '/mylog';
    symlink $p, "/etc/postgresql/$v/main/log";
    is ((exec_as 'root', "pg_ctlcluster $v main start"), 0, 
        'restarting cluster with nondefault log symlink');
    ok !-z $p, "log target is used as log file";
    ok -z $default_log, "default log is not used";
    like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/^$v\s+main.*$p\n$/;
    is ((exec_as 'root', "pg_ctlcluster $v main stop"), 0, 'stopping cluster');
    usleep $delay;
    truncate "$default_log", 0; # empty log file

    # verify that explicitly configured log file trumps log symlink
    PgCommon::set_conf_value ($v, 'main', 'postgresql.conf', 
        ($v >= '8.3' ? 'logging_collector' : 'redirect_stderr'), 'on');
    PgCommon::set_conf_value $v, 'main', 'postgresql.conf', 'log_filename', "$v#main.log";
    is ((exec_as 'root', "pg_ctlcluster $v main start"), 0, 
        'restarting cluster with explicitly configured log file');
    ok -z $default_log, "default log is not used";
    ok !-z $p, "log symlink target is used for startup message";
    my $pg_log = $v >= 10 ? 'log' : 'pg_log'; # log directory in PGDATA changed in PG 10
    my @l = glob ((PgCommon::cluster_data_directory $v, 'main') .  "/$pg_log/$v#main.log*");
    is $#l, 0, 'exactly one log file';
    ok (-e $l[0] && ! -z $l[0], 'custom log is actually used');
    SKIP: { skip "no logging_collector in $v", 2 if ($v < 8.3);
    like_program_out 'postgres', 'pg_lsclusters -h', 0, qr/^$v\s+main.*$v#main.log\n$/;
    }

    # clean up
    PgCommon::disable_conf_value ($v, 'main', 'postgresql.conf', 
        ($v >= '8.3' ? 'logging_collector' : 'redirect_stderr'), '');
    PgCommon::disable_conf_value $v, 'main', 'postgresql.conf', 'log_filename', '';
    unlink "/etc/postgresql/$v/main/log";

    # check that log creation does not escalate privileges
    program_ok 'root', "pg_ctlcluster $v main stop", 0, 'stopping cluster';
    unlink $default_log;
    symlink "/etc/postgres-hack", $default_log;
    program_ok 'root', "pg_ctlcluster $v main start", 1, 'starting cluster with rouge /var/log/postgresql symlink fails';
    ok !-f "/etc/postgres-hack", "/etc/postgres-hack was not created";
    unlink $default_log;
    program_ok 'root', "pg_ctlcluster $v main start", 0, 'restarting cluster';

    # verify that processes do not have an associated terminal
    unlike_program_out 0, 'ps -o tty -U postgres h', 0, qr/tty|pts/,
        'postgres processes do not have an associated terminal';

    # verify that SSL is enabled (which should work for user postgres in a
    # default installation)
    my $ssl = config_bool (PgCommon::get_conf_value $v, 'main', 'postgresql.conf', 'ssl');
    my $ssl_linked = `ldd $PgCommon::binroot$v/bin/postgres | grep libssl`;
    my ($os, $osversion) = os_release();
    if ($PgCommon::rpm) {
        isnt $ssl_linked, '', 'Server is linked with SSL support';
        is $ssl, undef, 'SSL is disabled in postgresql.conf';
    } elsif ($v <= 9.1 and (($os eq 'debian' and ($osversion eq 'unstable' or $osversion > 9)) or # stretch had 1.0 and 1.1
                            ($os eq 'ubuntu' and $osversion > 18.04))) { # bionic had 1.0 and 1.1
        is $ssl_linked, '', 'Server is linked without SSL support (old version with only OpenSSL 1.0 support)';
        is $ssl, undef, 'SSL is disabled in postgresql.conf';
    } else {
        isnt $ssl_linked, '', 'Server is linked with SSL support';
        is $ssl, 1, 'SSL is enabled in postgresql.conf';
    }

    # Create user nobody, a database 'nobodydb' for him, check the database list
    my $outref;
    is ((exec_as 'nobody', 'psql -l 2>/dev/null', $outref), 2, 'psql -l fails for nobody');
    is ((exec_as 'postgres', 'createuser nobody -D -R -S'), 0, 'createuser nobody');
    is ((exec_as 'postgres', 'createdb -O nobody nobodydb'), 0, 'createdb nobodydb');
    is ((exec_as 'nobody', 'psql -ltA|grep "|" | cut -f1-3 -d"|"', $outref), 0, 'psql -ltA succeeds for nobody');
    is ($$outref, 'nobodydb|nobody|UTF8
postgres|postgres|UTF8
template0|postgres|UTF8
template1|postgres|UTF8
', 'psql -ltA output');

    # Then fill nobodydb with some data.
    is ((exec_as 'nobody', 'psql nobodydb -c "create table phone (name varchar(255) PRIMARY KEY, tel int NOT NULL)" 2>/dev/null'), 
	0, 'SQL command: create table');
    is ((exec_as 'nobody', 'psql nobodydb -c "insert into phone values (\'Bob\', 1)"'), 0, 'SQL command: insert into table values');
    is ((exec_as 'nobody', 'psql nobodydb -c "insert into phone values (\'Alice\', 2)"'), 0, 'SQL command: insert into table values');
    is ((exec_as 'nobody', 'psql nobodydb -c "insert into phone values (\'Bob\', 3)"'), 1, 'primary key violation');

    # Check table contents
    is_program_out 'nobody', 'psql -tAc "select * from phone order by name" nobodydb', 0,
        'Alice|2
Bob|1
', 'SQL command output: select -tA';
    is_program_out 'nobody', 'psql -txc "select * from phone where name = \'Alice\'" nobodydb', 0,
        'name | Alice
tel  | 2

', 'SQL command output: select -tx';
    is_program_out 'nobody', 'psql -tAxc "select * from phone where name = \'Alice\'" nobodydb', 0,
        'name|Alice
tel|2
', 'SQL command output: select -tAx';

    sub create_extension ($$) {
        my ($v, $extension) = @_;
        return "psql -qc 'CREATE EXTENSION $extension' nobodydb" if ($v >= 9.1);
        return "createlang --cluster $v/main $extension nobodydb";
    }

    # Check PL/Perl untrusted
    my $fn_cmd = 'CREATE FUNCTION read_file() RETURNS text AS \'open F, \\"/etc/passwd\\"; \\$buf = <F>; close F; return \\$buf;\' LANGUAGE plperl';
    is ((exec_as 'nobody', create_extension($v, 'plperlu')), 1, 'CREATE EXTENSION plperlu fails for user nobody');
    is_program_out 'postgres', create_extension($v, 'plperlu'), 0, '', 'CREATE EXTENSION plperlu succeeds for user postgres';
    is ((exec_as 'nobody', "psql nobodydb -qc \"${fn_cmd}u;\""), 1, 'creating PL/PerlU function as user nobody fails');
    is ((exec_as 'postgres', "psql nobodydb -qc \"${fn_cmd};\""), 1, 'creating unsafe PL/Perl function as user postgres fails');
    is_program_out 'postgres', "psql nobodydb -qc \"${fn_cmd}u;\"", 0, '', 'creating PL/PerlU function as user postgres succeeds';
    like_program_out 'nobody', 'psql nobodydb -Atc "select read_file()"',
	0, qr/^root:/, 'calling PL/PerlU function';

    # Check PL/Perl trusted
    my $pluser = ($v >= '8.3') ? 'nobody' : 'postgres'; # pg_pltemplate allows non-superusers to install trusted languages in 8.3+
    is_program_out $pluser, create_extension($v, 'plperl'), 0, '', "CREATE EXTENSION plperl succeeds for user $pluser";
    is ((exec_as 'nobody', "psql nobodydb -qc \"${fn_cmd};\""), 1, 'creating unsafe PL/Perl function as user nobody fails');
    is_program_out 'nobody', 'psql nobodydb -qc "CREATE FUNCTION remove_vowels(text) RETURNS text AS \'\\$_[0] =~ s/[aeiou]/_/ig; return \\$_[0];\' LANGUAGE plperl;"',
	0, '', 'creating PL/Perl function as user nobody succeeds';
    is_program_out 'nobody', 'psql nobodydb -Atc "select remove_vowels(\'foobArish\')"',
	0, "f__b_r_sh\n", 'calling PL/Perl function';

    # Check PL/Python (untrusted)
    SKIP: {
    skip "No python2 support", 6 unless ($v <= 11 and $PgCommon::have_python2);
    is_program_out 'postgres', create_extension($v, 'plpythonu'), 0, '', 'CREATE EXTENSION plpythonu succeeds for user postgres';
    is_program_out 'postgres', 'psql nobodydb -qc "CREATE FUNCTION capitalize(text) RETURNS text AS \'import sys; return args[0].capitalize() + sys.version[0]\' LANGUAGE plpythonu;"',
	0, '', 'creating PL/Python function as user postgres succeeds';
    is_program_out 'nobody', 'psql nobodydb -Atc "select capitalize(\'foo\')"',
	0, "Foo2\n", 'calling PL/Python function';
    }

    # Check PL/Python3 (untrusted)
    if ($v >= '9.1') {
	is_program_out 'postgres', create_extension($v, 'plpython3u'), 0, '', 'CREATE EXTENSION plpython3u succeeds for user postgres';
	is_program_out 'postgres', 'psql nobodydb -qc "CREATE FUNCTION capitalize3(text) RETURNS text AS \'import sys; return args[0].capitalize() + sys.version[0]\' LANGUAGE plpython3u;"',
	    0, '', 'creating PL/Python3 function as user postgres succeeds';
	is_program_out 'nobody', 'psql nobodydb -Atc "select capitalize3(\'foo\')"',
	    0, "Foo3\n", 'calling PL/Python function';
    } else {
	pass "Skipping PL/Python3 test for version $v...";
	pass '...';
	pass '...';
	pass '...';
	pass '...';
	pass '...';
    }

    # Check PL/Tcl (trusted/untrusted)
    is_program_out 'postgres', create_extension($v, 'pltcl'), 0, '', 'CREATE EXTENSION pltcl succeeds for user postgres';
    is_program_out 'postgres', create_extension($v, 'pltclu'), 0, '', 'CREATE EXTENSION pltclu succeeds for user postgres';
    is_program_out 'nobody', 'psql nobodydb -qc "CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS \'if {\\$1 > \\$2} {return \\$1}; return \\$2\' LANGUAGE pltcl STRICT;"',
	0, '', 'creating PL/Tcl function as user nobody succeeds';
    is_program_out 'postgres', 'psql nobodydb -qc "CREATE FUNCTION tcl_max_u(integer, integer) RETURNS integer AS \'if {\\$1 > \\$2} {return \\$1}; return \\$2\' LANGUAGE pltclu STRICT;"',
	0, '', 'creating PL/TclU function as user postgres succeeds';
    is_program_out 'nobody', 'psql nobodydb -Atc "select tcl_max(3,4)"', 0,
        "4\n", 'calling PL/Tcl function';
    is_program_out 'nobody', 'psql nobodydb -Atc "select tcl_max_u(5,4)"', 0,
        "5\n", 'calling PL/TclU function';

    # fake rotated logs to check that they are cleaned up properly
    open L, ">$default_log.1" or die "could not open fake rotated log file";
    print L "old log .1\n";
    close L;
    open L, ">$default_log.2" or die "could not open fake rotated log file";
    print L "old log .2\n";
    close L;
    if (system "gzip -9 $default_log.2") {
        die "could not gzip fake rotated log";
    }

    # Check that old-style pgdata symbolic link still works (p-common 0.90+
    # does not create them any more, but they still need to work for existing
    # installations)
    is ((exec_as 'root', "pg_ctlcluster $v main stop"), 0, 'stopping cluster');
    my $datadir = PgCommon::get_conf_value $v, 'main', 'postgresql.conf', 'data_directory';
    symlink $datadir, "/etc/postgresql/$v/main/pgdata";

    # data_directory should trump the pgdata symlink
    PgCommon::set_conf_value $v, 'main', 'postgresql.conf', 'data_directory', '/nonexisting';
    like_program_out 0, "pg_ctlcluster $v main start", 1, 
        qr/\/nonexisting is not accessible/,
        'cluster fails to start with invalid data_directory and valid pgdata symlink';

    # if only pgdata symlink is present, it is authoritative
    PgCommon::disable_conf_value $v, 'main', 'postgresql.conf', 'data_directory', 'disabled for test';
    is_program_out 0, "pg_ctlcluster $v main start", 0, '',
        'cluster restarts with pgdata symlink';

    # check properties of backend processes
    pipe RH, WH;
    my $psql = fork;
    if (!$psql) {
	close WH;
	my @pw = getpwnam 'nobody';
	change_ugid $pw[2], $pw[3];
	open(STDIN, "<& RH");
	dup2(POSIX::open('/dev/null', POSIX::O_WRONLY), 1);
	exec 'psql', '-Xq', '-vPROMPT1=', 'nobodydb' or die "could not exec psql process: $!";
    }
    close RH;
    select WH; $| = 1; # make unbuffered

    open my $pidfile, "/var/lib/postgresql/$v/main/postmaster.pid";
    my $master_pid = <$pidfile>;
    chomp $master_pid;
    close $pidfile;

    my $client_pid;
    while (!$client_pid) {
	usleep $delay;
	$client_pid = `ps --user postgres hu | grep 'postgres.*: nobody nobodydb' | grep -v grep | awk '{print \$2}'`;
	($client_pid) = ($client_pid =~ /(\d+)/); # untaint
    }

    # OOM score adjustment under Linux: postmaster gets bigger shields for >=
    # 9.0, but client backends stay at default; this might not work in
    # containers with restricted privileges, so skip the check there
    my $adj;
    my $detect_virt = system 'systemd-detect-virt --container --quiet'; # from systemd
    open F, "/proc/$master_pid/oom_score_adj";
    $adj = <F>;
    chomp $adj;
    close F;
    if ($v >= '9.0' and not $PgCommon::rpm) {
        SKIP: {
            skip 'skipping postmaster OOM killer adjustment in container', 1 if $detect_virt == 0;
            cmp_ok $adj, '<=', -500, 'postgres master has OOM killer protection';
        }
    } else {
        is $adj, 0, 'postgres master has no OOM adjustment';
    }

    open F, "/proc/$client_pid/oom_score_adj";
    $adj = <F>;
    chomp $adj;
    close F;
    is $adj, 0, 'postgres client backend has no OOM adjustment';

    # test process title update
    like_program_out 0, "ps h $client_pid", 0, qr/ idle\s*$/, 'process title is idle';
    print WH "BEGIN;\n";
    usleep $delay;
    like_program_out 0, "ps h $client_pid", 0, qr/idle in transaction/, 'process title is idle in transaction';

    close WH;
    kill 15, $psql;
    waitpid $psql, 0;

    # log file gets re-created by pg_ctlcluster
    is ((exec_as 0, "pg_ctlcluster $v main stop"), 0, 'stopping cluster');
    unlink $default_log;
    is ((exec_as 0, "pg_ctlcluster $v main start"), 0, 'starting cluster as postgres works without a log file');
    ok (-e $default_log && ! -z $default_log, 'log file got recreated and used');

    # create tablespaces
    my $spc1 = tempdir("/tmp/$v.spc1.XXXXXX", CLEANUP => 1);
    my $spc2 = tempdir("/tmp/$v.spc2.XXXXXX", CLEANUP => 1);
    is (mkdir ("$spc2/PG_99_fakedirectory"), 1, 'creating a directory in spc2');
    chown $postgres_uid, 0, $spc1, $spc2, "$spc2/PG_99_fakedirectory";
    is_program_out 'postgres', "psql -qc \"CREATE TABLESPACE spc1 LOCATION '$spc1'\"", 0, '', 'creating tablespace spc1';
    is_program_out 'postgres', "psql -qc 'CREATE TABLE tbl1 (x int) TABLESPACE spc1'", 0, '', 'creating a table in spc1';
    SKIP: {
        skip "Non-empty tablespaces not supported before 9.0", 4 if ($v < 9.0);
        is_program_out 'postgres', "psql -qc \"CREATE TABLESPACE spc2 LOCATION '$spc2'\"", 0, '', 'creating tablespace spc2';
        is_program_out 'postgres', "psql -qc 'CREATE TABLE tbl2 (x int) TABLESPACE spc2'", 0, '', 'creating a table in spc2';
    }

    # check apt config
    is_program_out 0, "grep -Eo 'postgresql.[0-9.*-]+' /etc/apt/apt.conf.d/02autoremove-postgresql", 0,
        "postgresql.*-$v\n", "Correct apt NeverAutoRemove config";

    # stop server, clean up, check for leftovers
    ok ((system "pg_dropcluster $v main --stop") == 0,
	'pg_dropcluster removes cluster');

    is (-e $xlogdir, undef, "xlog/wal directory $xlogdir was deleted");
    ok_dir $spc1, [], "tablespace spc1 was emptied";
    ok_dir $spc2, [qw(PG_99_fakedirectory)], "tablespace spc2 was emptied";

    is_program_out 0, "grep -Eo 'postgresql.[0-9.*-]+' /etc/apt/apt.conf.d/02autoremove-postgresql", 1,
        "", "Correct apt NeverAutoRemove config";

    check_clean;
}

foreach (@MAJORS) { 
    check_major $_;
}

# vim: filetype=perl