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
|
# We create a cluster, execute some basic SQL commands, drop it again, and
# check that we did not leave anything behind.
use strict;
use lib 't';
use TestLib;
use lib '/usr/share/postgresql-common';
use PgCommon;
use Test::More tests => 58 * ($#MAJORS+1);
sub check_major {
my $v = $_[0];
# create cluster
ok ((system "pg_createcluster $v main --start >/dev/null") == 0,
"pg_createcluster $v main");
# check that a /var/run/postgresql/ pid file is created for 8.0+
if ($v ge '8.0') {
ok_dir '/var/run/postgresql/', ['.s.PGSQL.5432', '.s.PGSQL.5432.lock', "$v-main.pid"],
'Socket and pid file are in /var/run/postgresql/';
} else {
ok_dir '/var/run/postgresql', ['.s.PGSQL.5432', '.s.PGSQL.5432.lock'],
'Socket is in /var/run/postgresql/';
}
# verify that pg_autovacuum is running if it is available
my $pg_autovacuum = get_program_path 'pg_autovacuum', $v;
if ($pg_autovacuum) {
like ((ps 'pg_autovacuum'), qr/$pg_autovacuum/, 'pg_autovacuum is running');
} else {
is ((ps 'pg_autovacuum'), '', "No pg_autovacuum available for version $v");
}
# verify that exactly one postmaster is running
my @pm_pids = pidof (($v ge '8.2') ? 'postgres' : 'postmaster');
is $#pm_pids, 0, 'Exactly one postmaster process running';
# check environment
my %safe_env = qw/LC_ALL 1 LC_CTYPE 1 LANG 1 PWD 1 PGLOCALEDIR 1 PGSYSCONFDIR 1 SHLVL 1 PGDATA 1 _ 1/;
my %env = pid_env $pm_pids[0];
foreach (keys %env) {
fail "postmaster has unsafe environment variable $_" unless exists $safe_env{$_};
}
# activate external_pid_file for 8.0+
if ($v ge '8.0') {
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 'postgres', "pg_ctlcluster $v main restart", 0, '',
'cluster restarts with new environment file';
@pm_pids = pidof (($v ge '8.2') ? 'postgres' : 'postmaster');
is $#pm_pids, 0, 'Exactly one postmaster process running';
%env = pid_env $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
ok_dir '/var/run/postgresql', ['.s.PGSQL.5432', '.s.PGSQL.5432.lock'],
'Socket, but not PID file in /var/run/postgresql/';
# verify that the correct client version is selected
like_program_out 'postgres', 'psql --version', 0, qr/^psql \(PostgreSQL\) $v/,
'pg_wrapper selects version number of cluster';
# verify that the cluster is displayed
my $ls = `pg_lsclusters -h`;
$ls =~ s/\s*$//;
is $ls, "$v main 5432 online postgres /var/lib/postgresql/$v/main /var/log/postgresql/postgresql-$v-main.log",
'pg_lscluster reports online cluster on port 5432';
# verify that the postmaster does not have an associated terminal
unlike_program_out 0, 'ps -o tty -U postgres h', 0, qr/tty|pts/,
'postmaster processes do not have an associated terminal';
# verify that SSL is enabled (which should work for user postgres in a
# default installation)
if ($v ge '8.0') {
my $ssl = config_bool (PgCommon::get_conf_value $v, 'main', 'postgresql.conf', 'ssl');
is $ssl, 1, 'SSL is enabled';
} else {
pass 'Skipping SSL test for versions before 8.0';
}
# 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 ' . (($v ge '8.1') ? '-R -s' : '-A')), 0, 'createuser nobody');
is ((exec_as 'postgres', 'createdb -O nobody nobodydb'), 0, 'createdb nobodydb');
is ((exec_as 'nobody', 'psql -ltA', $outref), 0, 'psql -ltA succeeds for nobody');
if ($v ge '8.1') {
is ($$outref, 'nobodydb|nobody|UTF8
postgres|postgres|UTF8
template0|postgres|UTF8
template1|postgres|UTF8
', 'psql -ltA output');
} else {
is ($$outref, 'nobodydb|nobody|UNICODE
template0|postgres|UNICODE
template1|postgres|UNICODE
', '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';
# Check PL/Perl (trusted)
is_program_out 'postgres', 'createlang plperl nobodydb', 0, '', 'createlang plperl succeeds for user postgres';
is_program_out 'postgres', 'createlang plperlu nobodydb', 0, '', 'createlang plperlu succeeds for user postgres';
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)
is_program_out 'postgres', 'createlang plpythonu nobodydb', 0, '', 'createlang plpythonu succeeds for user postgres';
is_program_out 'postgres', 'psql nobodydb -qc "CREATE FUNCTION capitalize(text) RETURNS text AS \'return args[0].capitalize()\' LANGUAGE plpythonu;"',
0, '', 'creating PL/Python function as user postgres succeeds';
is_program_out 'nobody', 'psql nobodydb -Atc "select capitalize(\'foo\')"',
0, "Foo\n", 'calling PL/Python function';
# Check pg_maintenance
if ($pg_autovacuum || $v ge '8.1') {
like_program_out 0, 'pg_maintenance', 0, qr/^Skipping.*\n$/, 'pg_maintenance skips autovacuumed cluster';
} else {
like_program_out 0, 'pg_maintenance', 0, qr/^Doing.*\n$/, 'pg_maintenance handles non-autovacuumed cluster';
}
like_program_out 0, 'pg_maintenance --force', 0, qr/^Doing.*\n$/,
'pg_maintenance --force always handles cluster';
# Drop database and user again.
sleep 1;
is ((exec_as 'nobody', 'dropdb nobodydb', $outref, 0), 0, 'dropdb nobodydb', );
is ((exec_as 'postgres', 'dropuser nobody', $outref, 0), 0, 'dropuser nobody');
# stop server, clean up, check for leftovers
ok ((system "pg_dropcluster $v main --stop") == 0,
'pg_dropcluster removes cluster');
check_clean;
}
foreach (@MAJORS) {
check_major $_;
}
# vim: filetype=perl
|
