The PostgreSQL advisory gave us some clues how to apply the security fix without dump/initdb/reload, but this sucks on a shared hosting server with several hundred databases :-)
Here’s some perl that iterates over all your databases and applies the fix, and also fixes up template0 in the recommended way. No warranty of fitness for purpose, blah blah, public domain, blah blah.
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
| #!/usr/bin/env perl
use DBI;
my $db = DBI->connect(q/dbi:Pg:dbname=template1/);
my $databases = $db->prepare(q{
SELECT datname FROM pg_database WHERE datname <> 'template0'
});
$databases->execute()
or die $DBI::errstr;
sub fix($) {
my $datname = shift;
#print "fixing $datname\n";
my $fixdb = DBI->connect("dbi:Pg:dbname=$datname");
$fixdb->begin_work;
my $fixinator = $fixdb->prepare(q{
UPDATE pg_proc SET proargtypes[3] = 'internal'::regtype
WHERE pronamespace = 11 AND pronargs = 5
AND proargtypes[2] = 'cstring'::regtype;
});
$fixinator->execute()
or die $DBI::errstr;
if ($fixinator->rows == 90) {
#print "fixed $datname ok\n";
$fixdb->commit();
} else {
print "warning! $datname not fixed\n";
$fixdb->rollback();
}
$fixdb->disconnect();
}
# do all but template0
while (@data = $databases->fetchrow_array()) {
#print @data[0] . "\n";
fix(@data[0])
}
# do template0
$db->do(q{
update pg_database set datallowconn = true where
datname = 'template0';
});
fix('template0');
$db->do("vacuum freeze");
$db->do(q{
update pg_database set datallowconn = false where
datname = 'template0';
});
$db->disconnect();
|