2 # Common PostgreSQL functions
3 # XXX updating date field
5 BEGIN { push(@INC, ".."); };
9 $ENV{$gconfig{'ld_env'}} .= ':' if ($ENV{$gconfig{'ld_env'}});
10 $ENV{$gconfig{'ld_env'}} .= $config{'plib'};
12 if ($config{'psql'} =~ /^(.*)\/bin\/psql$/ && $1 ne '' && $1 ne '/usr') {
13 $ENV{$gconfig{'ld_env'}} .= ':' if ($ENV{$gconfig{'ld_env'}});
14 $ENV{$gconfig{'ld_env'}} .= "$1/lib";
17 if ($module_info{'usermin'}) {
18 # Login and password is set by user in Usermin, and the module always
19 # runs as the Usermin user
20 &switch_to_remote_user();
21 &create_user_config_dirs();
22 $postgres_login = $userconfig{'login'};
23 $postgres_pass = $userconfig{'pass'};
24 $postgres_sameunix = 0;
25 %access = ( 'backup' => 1,
29 $max_dbs = $userconfig{'max_dbs'};
30 $commands_file = "$user_module_config_directory/commands";
31 %displayconfig = %userconfig;
34 # Login and password is determined by ACL in Webmin
35 %access = &get_module_acl();
36 if ($access{'user'} && !$use_global_login) {
37 $postgres_login = $access{'user'};
38 $postgres_pass = $access{'pass'};
39 $postgres_sameunix = $access{'sameunix'};
42 $postgres_login = $config{'login'};
43 $postgres_pass = $config{'pass'};
44 $postgres_sameunix = $config{'sameunix'};
46 $max_dbs = $config{'max_dbs'};
47 $commands_file = "$module_config_directory/commands";
48 %displayconfig = %config;
50 foreach my $hba (split(/\t+/, $config{'hba_conf'})) {
51 if ($hba =~ /\*|\?/) {
54 if ($hba && -r $hba) {
55 $hba_conf_file = $hba;
59 $cron_cmd = "$module_config_directory/backup.pl";
61 if (!$config{'nodbi'}) {
62 # Check if we have DBD::Pg
65 \$driver_handle = DBI->install_driver("Pg");
69 # is_postgresql_running()
70 # Returns 1 if yes, 0 if no, -1 if the login is invalid, -2 if there
71 # is a library problem. When called in an array context, returns the full error
73 sub is_postgresql_running
75 local $temp = &transname();
76 local $host = $config{'host'} ? "-h $config{'host'}" : "";
77 $host .= " -p $config{'port'}" if ($config{'port'});
78 local $cmd = "e_path($config{'psql'}).
79 (!&supports_pgpass() ? " -u" : " -U $postgres_login").
80 " -c '' $host $config{'basedb'}";
81 if ($postgres_sameunix && defined(getpwnam($postgres_login))) {
82 $cmd = "su $postgres_login -c ".quotemeta($cmd);
84 $cmd = &command_with_login($cmd);
85 if (&foreign_check("proc")) {
86 &foreign_require("proc", "proc-lib.pl");
87 if (defined(&proc::close_controlling_pty)) {
88 # Detach from tty if possible, so that the psql
89 # command doesn't prompt for a login
90 &proc::close_controlling_pty();
93 open(OUT, "$cmd 2>&1 |");
94 while(<OUT>) { $out .= $_; }
98 if ($out =~ /setuserid:/i || $out =~ /no\s+password\s+supplied/i ||
99 $out =~ /no\s+postgres\s+username/i || $out =~ /authentication\s+failed/i ||
100 $out =~ /password:.*password:/i || $out =~ /database.*does.*not/i ||
101 $out =~ /user.*does.*not/i) {
104 elsif ($out =~ /connect.*failed/i || $out =~ /could not connect to server:/) {
107 elsif ($out =~ /lib\S+\.so/i) {
113 return wantarray ? ($rv, $out) : $rv;
116 # get_postgresql_version([from-command])
117 sub get_postgresql_version
119 local ($fromcmd) = @_;
120 local $main::error_must_die = 1;
121 return $postgresql_version_cache if (defined($postgresql_version_cache));
125 local $v = &execute_sql_safe($config{'basedb'},
127 $v = $v->{'data'}->[0]->[0];
128 if ($v =~ /postgresql\s+([0-9\.]+)/i) {
134 local $out = &backquote_command("e_path($config{'psql'})." -V 2>&1 <$null_file");
135 $rv = $out =~ /\s([0-9\.]+)/ ? $1 : undef;
137 $postgresql_version_cache = $rv;
143 return &get_postgresql_version() >= 7.3;
147 # Returns a list of all databases
150 local $force_nodbi = 1;
151 local $t = &execute_sql_safe($config{'basedb'}, 'select * from pg_database order by datname');
152 return sort { lc($a) cmp lc($b) } map { $_->[0] } @{$t->{'data'}};
155 # supports_schemas(database)
156 # Returns 1 if schemas are supported
159 local $t = &execute_sql_safe($_[0], "select a.attname FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'pg_tables' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid and a.attname = 'schemaname' order by attnum");
160 return $t->{'data'}->[0]->[0] ? 1 : 0;
163 # list_tables(database)
164 # Returns a list of tables in some database
167 if (&supports_schemas($_[0])) {
168 local $t = &execute_sql_safe($_[0], 'select schemaname,tablename from pg_tables where tablename not like \'pg_%\' and tablename not like \'sql_%\' order by tablename');
169 return map { ($_->[0] eq "public" ? "" : $_->[0].".").$_->[1] } @{$t->{'data'}};
172 local $t = &execute_sql_safe($_[0], 'select tablename from pg_tables where tablename not like \'pg_%\' and tablename not like \'sql_%\' order by tablename');
173 return map { $_->[0] } @{$t->{'data'}};
178 # Returns a list of all available field types
181 local $t = &execute_sql_safe($config{'basedb'}, 'select typname from pg_type where typrelid = 0 and typname !~ \'^_.*\' order by typname');
182 local @types = map { $_->[0] } @{$t->{'data'}};
183 push(@types, "serial", "bigserial") if (&get_postgresql_version() >= 7.4);
184 return sort { $a cmp $b } &unique(@types);
187 # table_structure(database, table)
188 # Returns a list of hashes detailing the structure of a table
191 if (&supports_schemas($_[0])) {
192 # Find the schema and table
194 if ($_[1] =~ /^(\S+)\.(\S+)$/) {
202 $tn =~ s/^([^\.]+)\.//;
203 local $t = &execute_sql_safe($_[0], "select a.attnum, a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef FROM pg_class c, pg_attribute a, pg_type t, pg_namespace ns WHERE c.relname = '$tn' and ns.nspname = '$ns' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid and a.attname not like '%pg.dropped%' and c.relnamespace = ns.oid order by attnum");
205 foreach $r (@{$t->{'data'}}) {
207 $arr++ if ($r->[2] =~ s/^_//);
208 local $sz = $r->[4] - 4;
209 if ($sz >= 65536 && $r->[2] =~ /numeric/i) {
210 $sz = int($sz/65536).",".($sz%65536);
212 push(@rv, { 'field' => $r->[1],
213 'arr' => $arr ? 'YES' : 'NO',
214 'type' => $r->[4] < 0 ? $r->[2]
216 'null' => $r->[5] =~ /f|0/ ? 'YES' : 'NO' } );
219 # Work out which fields are the primary key
220 if (&supports_indexes()) {
221 local ($keyidx) = grep { $_ eq $_[1]."_pkey" ||
223 &list_indexes($_[0]);
225 local $istr = &index_structure($_[0], $keyidx);
226 foreach my $r (@rv) {
227 if (&indexof($r->{'field'},
228 @{$istr->{'cols'}}) >= 0) {
238 # Just look by table name
239 local $t = &execute_sql_safe($_[0], "select a.attnum, a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = '$_[1]' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid order by attnum");
241 foreach $r (@{$t->{'data'}}) {
243 $arr++ if ($r->[2] =~ s/^_//);
244 local $sz = $r->[4] - 4;
245 if ($sz >= 65536 && $r->[2] =~ /numeric/i) {
246 $sz = int($sz/65536).",".($sz%65536);
248 push(@rv, { 'field' => $r->[1],
249 'arr' => $arr ? 'YES' : 'NO',
250 'type' => $r->[4] < 0 ? $r->[2]
252 'null' => $r->[5] =~ /f|0/ ? 'YES' : 'NO' } );
258 # execute_sql(database, sql, [param, ...])
261 if (&is_readonly_mode()) {
264 &execute_sql_safe(@_);
267 # execute_sql_safe(database, sql, [param, ...])
271 local @params = @_[2..$#_];
272 if ($gconfig{'debug_what_sql'}) {
273 # Write to Webmin debug log
275 for(my $i=0; $i<@params; $i++) {
276 $params .= " ".$i."=".$params[$i];
278 &webmin_debug_log('SQL', "db=$_[0] sql=$sql".$params);
280 if ($sql !~ /^\s*\\/) {
283 if ($driver_handle &&
284 $sql !~ /^\s*(create|drop)\s+database/ && $sql !~ /^\s*\\/ &&
286 # Use the DBI interface
288 local $cstr = "dbname=$_[0]";
289 $cstr .= ";host=$config{'host'}" if ($config{'host'});
290 $cstr .= ";port=$config{'port'}" if ($config{'port'});
292 if ($postgres_sameunix &&
293 (@uinfo = getpwnam($postgres_login))) {
294 # DBI call which must run in subprocess
296 if (!($pid = fork())) {
297 &switch_to_unix_user(\@uinfo);
299 local $dbh = $driver_handle->connect($cstr,
300 $postgres_login, $postgres_pass);
302 print OUTw &serialise_variable(
303 "DBI connect failed : ".$DBI::errstr);
306 $dbh->{'AutoCommit'} = 0;
307 local $cmd = $dbh->prepare($sql);
308 #foreach (@params) { # XXX dbd quoting is broken!
311 if (!$cmd->execute(@params)) {
312 print OUTw &serialise_variable(&text('esql',
313 "<tt>".&html_escape($sql)."</tt>",
314 "<tt>".&html_escape($dbh->errstr)."</tt>"));
319 local @titles = @{$cmd->{'NAME'}};
320 while(@row = $cmd->fetchrow()) {
321 push(@data, [ @row ]);
326 print OUTw &serialise_variable(
327 { 'titles' => \@titles,
332 local $line = <OUTr>;
333 local $rv = &unserialise_variable($line);
338 &error($rv || "$sql : Unknown DBI error");
342 # Just normal DBI call
343 local $dbh = $driver_handle->connect($cstr,
344 $postgres_login, $postgres_pass);
345 $dbh || &error("DBI connect failed : ",$DBI::errstr);
346 $dbh->{'AutoCommit'} = 0;
347 local $cmd = $dbh->prepare($sql);
348 if (!$cmd->execute(@params)) {
349 &error(&text('esql', "<tt>".&html_escape($sql)."</tt>",
350 "<tt>".&html_escape($dbh->errstr)."</tt>"));
353 local @titles = @{$cmd->{'NAME'}};
354 while(@row = $cmd->fetchrow()) {
355 push(@data, [ @row ]);
360 return { 'titles' => \@titles,
365 # Check for a \ command
367 if ($sql =~ /^\s*\\l\s*$/) {
368 # \l command to list encodings needs no special handling
370 elsif ($sql =~ /^\s*\\/ ) {
372 if ($sql !~ /^\s*\\copy\s+/ &&
373 $sql !~ /^\s*\\i\s+/) {
374 &error ( &text ( 'r_command', ) ) ;
379 # Sub in ? parameters
382 foreach $p (@params) {
383 $pos = index($sql, '?', $pos+1);
384 &error("Incorrect number of parameters in $_[1] (".scalar(@params).")") if ($pos < 0);
386 if ($qp !~ /^[bB]'\d+'$/) {
387 # Quote value, except for bits
392 $qp = $qp eq '' ? "NULL" : "'$qp'";
394 $sql = substr($sql, 0, $pos).$qp.substr($sql, $pos+1);
395 $pos += length($qp)-1;
399 # Call the psql program
400 local $host = $config{'host'} ? "-h $config{'host'}" : "";
401 $host .= " -p $config{'port'}" if ($config{'port'});
402 local $cmd = "e_path($config{'psql'})." --html".
403 (!&supports_pgpass() ? " -u" : " -U $postgres_login").
404 " -c "."e_path($sql)." $host $_[0]";
405 if ($postgres_sameunix && defined(getpwnam($postgres_login))) {
406 $cmd = &command_as_user($postgres_login, 0, $cmd);
408 $cmd = &command_with_login($cmd);
410 delete($ENV{'LANG'}); # to force output to english
411 delete($ENV{'LANGUAGE'});
413 # Running a normal SQL command, not one with a \
414 #$ENV{'PAGER'} = "cat";
415 if (&foreign_check("proc")) {
416 &foreign_require("proc", "proc-lib.pl");
417 if (defined(&proc::close_controlling_pty)) {
418 # Detach from tty if possible, so that the psql
419 # command doesn't prompt for a login
420 &proc::close_controlling_pty();
423 open(OUT, "$cmd 2>&1 |");
424 local ($line, $rv, @data);
427 } while($line =~ /^(username|password|user name):/i ||
428 $line =~ /(warning|notice):/i ||
429 $line !~ /\S/ && defined($line));
431 if ($line =~ /^ERROR:\s+(.*)/ || $line =~ /FATAL.*:\s+(.*)/) {
432 &error(&text('esql', "<tt>$sql</tt>", "<tt>$1</tt>"));
434 elsif (!defined($line)) {
435 # Un-expected end of output ..
436 &error(&text('esql', "<tt>$sql</tt>",
437 "<tt>$config{'psql'} failed</tt>"));
440 # Read HTML-format output
443 while($line = <OUT>) {
444 if ($line =~ /^\s*<tr>/) {
448 elsif ($line =~ /^\s*<\/tr>/) {
453 elsif ($line =~ /^\s*<(td|th)[^>]*>(.*)<\/(td|th)>/) {
457 push(@$row, &entities_to_ascii($v));
460 $rv = { 'titles' => shift(@data),
467 # Running a special \ command
468 local ( @titles, @row, @data, $rc, $emsgf, $emsg ) ;
470 $emsgf = &transname();
471 $rc = &system_logged ( "$cmd >$emsgf 2>&1");
472 $emsg = &read_file_contents($emsgf);
473 &unlink_file($emsgf) ;
475 &error("<pre>$emsg</pre>");
478 @titles = ( " Command Invocation " ) ;
479 @row = ( " Done ( return code : $rc )" ) ;
480 map { s/^\s+//; s/\s+$// } @row ;
481 push ( @data, \@row ) ;
482 return { 'titles' => \@titles, 'data' => \@data } ;
488 # execute_sql_logged(database, command)
489 sub execute_sql_logged
491 &additional_log('sql', $_[0], $_[1]);
492 return &execute_sql(@_);
497 if ($module_info{'usermin'}) {
498 # Check access control list in configuration
500 DB: foreach $l (split(/\t/, $config{'access'})) {
501 if ($l =~ /^(\S+):\s*(.*)$/ &&
502 ($1 eq $remote_user || $1 eq '*')) {
503 local @dbs = split(/\s+/, $2);
505 $d =~ s/\$REMOTE_USER/$remote_user/g;
506 if ($d eq '*' || $_[0] =~ /^$d$/) {
515 if ($rv && $config{'access_own'}) {
516 # Check ownership on DB - first get login ID
517 if (!defined($postgres_login_id)) {
518 local $d = &execute_sql($config{'basedb'}, "select usesysid from pg_user where usename = ?", $postgres_login);
519 $postgres_login_id = $d->{'data'}->[0]->[0];
522 local $d = &execute_sql($config{'basedb'}, "select datdba from pg_database where datname = ?", $_[0]);
523 if ($d->{'data'}->[0]->[0] != $postgres_login_id) {
532 return 1 if ($access{'dbs'} eq '*');
533 foreach $d (split(/\s+/, $access{'dbs'})) {
534 return 1 if ($d && $d eq $_[0]);
540 # get_hba_config(version)
541 # Parses the postgres host access config file
545 open(HBA, $hba_conf_file);
551 if (/^\s*(host|hostssl)\s+(\S+)\s+(\S+)\s+(\S+)\/(\S+)\s+(\S+)(\s+(\S+))?/) {
553 push(@rv, { 'type' => $1,
554 'index' => scalar(@rv),
563 elsif (/^\s*(host|hostssl)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)(\s+(\S+))?/) {
564 # Host netmask format
565 push(@rv, { 'type' => $1,
566 'index' => scalar(@rv),
575 elsif (/^\s*local\s+(\S+)\s+(\S+)\s+(\S+)(\s+(\S+))?/) {
576 push(@rv, { 'type' => 'local',
577 'index' => scalar(@rv),
587 if (/^\s*host\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)(\s+(\S+))?/) {
588 push(@rv, { 'type' => 'host',
589 'index' => scalar(@rv),
597 elsif (/^\s*local\s+(\S+)\s+(\S+)(\s+(\S+))?/) {
598 push(@rv, { 'type' => 'local',
599 'index' => scalar(@rv),
612 # create_hba(&hba, version)
615 local $lref = &read_file_lines($hba_conf_file);
616 push(@$lref, &hba_line($_[0], $_[1]));
620 # delete_hba(&hba, version)
623 local $lref = &read_file_lines($hba_conf_file);
624 splice(@$lref, $_[0]->{'line'}, 1);
628 # modify_hba(&hba, version)
631 local $lref = &read_file_lines($hba_conf_file);
632 splice(@$lref, $_[0]->{'line'}, 1, &hba_line($_[0], $_[1]));
636 # swap_hba(&hba1, &hba2)
639 local $lref = &read_file_lines($hba_conf_file);
640 local $line0 = $lref->[$_[0]->{'line'}];
641 local $line1 = $lref->[$_[1]->{'line'}];
642 $lref->[$_[1]->{'line'}] = $line0;
643 $lref->[$_[0]->{'line'}] = $line1;
647 # hba_line(&hba, version)
650 if ($_[0]->{'type'} eq 'host' || $_[0]->{'type'} eq 'hostssl') {
651 return join(" ", $_[0]->{'type'}, $_[0]->{'db'},
652 ( $_[1] >= 7.3 ? ( $_[0]->{'user'} ) : ( ) ),
653 ($_[0]->{'cidr'} eq '' ?
654 ( $_[0]->{'address'},
655 $_[0]->{'netmask'} ) :
656 ( "$_[0]->{'address'}/$_[0]->{'cidr'}" )),
658 $_[0]->{'arg'} ? ( $_[0]->{'arg'} ) : () );
661 return join(" ", 'local', $_[0]->{'db'},
662 ( $_[1] >= 7.3 ? ( $_[0]->{'user'} ) : ( ) ),
664 $_[0]->{'arg'} ? ( $_[0]->{'arg'} ) : () );
671 if ($_[0] =~ /^\{(.*)\}$/) {
672 local @a = split(/,/, $1);
680 # join_array(values ..)
684 map { $alpha++ if (!/^-?[0-9\.]+/) } @_;
685 return $alpha ? '{'.join(',', map { "'$_'" } @_).'}'
686 : '{'.join(',', @_).'}';
691 return $_[0]->{'type'} eq 'text' || $_[0]->{'type'} eq 'bytea';
694 # restart_postgresql()
695 # HUP postmaster if running, so that hosts file changes take effect
696 sub restart_postgresql
698 if (open(PID, $config{'pid_file'})) {
699 ($pid = <PID>) =~ s/\r|\n//g;
701 &kill_logged('HUP', $pid) if ($pid);
705 # date_subs(filename)
706 # Does strftime-style date substitutions on a filename, if enabled
709 if ($config{'date_subs'}) {
711 eval "use posix" if ($@);
712 local @tm = localtime(time());
713 &clear_time_locale();
714 local $rv = strftime($_[0], @tm);
715 &reset_time_locale();
723 # execute_before(db, handle, escape, path, db-for-config)
726 local $cmd = $config{'backup_before_'.$_[4]};
728 $ENV{'BACKUP_FILE'} = $_[3];
731 local $rv = &execute_command($cmd, undef, \$out, \$out);
733 print $h $_[2] ? "<pre>".&html_escape($out)."</pre>" : $out;
740 # execute_after(db, handle, escape, path, db-for-config)
743 local $cmd = $config{'backup_after_'.$_[4]};
745 $ENV{'BACKUP_FILE'} = $_[3];
748 local $rv = &execute_command($cmd, undef, \$out, \$out);
750 print $h $_[2] ? "<pre>".&html_escape($out)."</pre>" : $out;
757 # make_backup_dir(directory)
758 # Create a directory that PostgreSQL can backup into
763 &make_dir($dir, 0755);
764 if ($postgres_sameunix && defined(getpwnam($postgres_login))) {
765 &set_ownership_permissions($postgres_login, undef, undef, $dir);
772 local @tn = split(/\./, $_[0]);
773 return join(".", map { "\"$_\"" } @tn);
781 # execute_sql_file(database, file, [user, pass], [unix-user])
782 # Executes some file of SQL statements, and returns the exit status and output
785 local ($db, $file, $user, $pass, $unixuser) = @_;
786 if (&is_readonly_mode()) {
789 if (!defined($user)) {
790 $user = $postgres_login;
791 $pass = $postgres_pass;
793 local $cmd = "e_path($config{'psql'})." -f "."e_path($file).
794 (&supports_pgpass() ? " -U $user" : " -u").
795 ($config{'host'} ? " -h $config{'host'}" : "").
796 ($config{'port'} ? " -h $config{'port'}" : "").
798 if ($postgres_sameunix && defined(getpwnam($postgres_login))) {
799 $cmd = &command_as_user($postgres_login, 0, $cmd);
801 elsif ($unixuser && $unixuser ne 'root' && $< == 0) {
802 $cmd = &command_as_user($unixuser, 0, $cmd);
804 $cmd = &command_with_login($cmd, $user, $pass);
805 local $out = &backquote_logged("$cmd 2>&1");
806 return ($out =~ /ERROR/i ? 1 : 0, $out);
809 # split_table(&titles, &checkboxes, &links, &col1, &col2, ...)
810 # Outputs a table that is split into two parts
813 local $mid = int((@{$_[2]}+1) / 2);
815 print "<table width=100%><tr>\n";
816 foreach $s ([0, $mid-1], [$mid, @{$_[2]}-1]) {
817 print "<td width=50% valign=top>\n";
820 local @tds = $_[1] ? ( "width=5" ) : ( );
821 if ($s->[0] <= $s->[1]) {
823 foreach $t (@{$_[0]}) {
826 print &ui_columns_start(\@hcols, 100, 0, \@tds);
829 for($i=$s->[0]; $i<=$s->[1]; $i++) {
831 push(@cols, "<a href='$_[2]->[$i]'>$_[3]->[$i]</a>");
832 for($j=4; $j<@_; $j++) {
833 push(@cols, $_[$j]->[$i]);
836 print &ui_checked_columns_row(\@cols, \@tds, "d", $_[1]->[$i]);
839 print &ui_columns_row(\@cols, \@tds);
842 if ($s->[0] <= $s->[1]) {
843 print &ui_columns_end();
847 print "</tr></table>\n";
850 # accepting_connections(db)
851 # Returns 1 if some database is accepting connections, 0 if not
852 sub accepting_connections
854 if (!defined($has_connections)) {
855 $has_connections = 0;
856 local @str = &table_structure($config{'basedb'},
857 "pg_catalog.pg_database");
858 foreach my $f (@str) {
859 $has_connections = 1 if ($f->{'field'} eq 'datallowconn');
862 if ($has_connections) {
863 $rv = &execute_sql_safe($config{'basedb'}, "select datallowconn from pg_database where datname = '$_[0]'");
864 if ($rv->{'data'}->[0]->[0] !~ /^(t|1)/i) {
872 # Starts the PostgreSQL database server. Returns an error message on failure
873 # or undef on success.
876 if ($gconfig{'os_type'} eq 'windows' && &foreign_check("init")) {
877 # On Windows, always try to sc start the pgsql- service
878 &foreign_require("init", "init-lib.pl");
879 local ($pg) = grep { $_->{'name'} =~ /^pgsql-/ }
880 &init::list_win32_services();
882 return &init::start_win32_service($pg->{'name'});
885 local $temp = &transname();
886 local $rv = &system_logged("($config{'start_cmd'}) >$temp 2>&1");
887 local $out = `cat $temp`; unlink($temp);
889 if ($rv || $out =~ /failed|error/i) {
890 return "<pre>$out</pre>";
896 # Stops the PostgreSQL database server. Returns an error message on failure
897 # or undef on success.
900 if ($gconfig{'os_type'} eq 'windows' && &foreign_check("init")) {
901 # On Windows, always try to sc stop the pgsql- service
902 &foreign_require("init", "init-lib.pl");
903 local ($pg) = grep { $_->{'name'} =~ /^pgsql-/ }
904 &init::list_win32_services();
906 return &init::stop_win32_service($pg->{'name'});
909 if ($config{'stop_cmd'}) {
910 local $out = &backquote_logged("$config{'stop_cmd'} 2>&1");
911 if ($? || $out =~ /failed|error/i) {
912 return "<pre>$?\n$out</pre>";
917 open(PID, $config{'pid_file'});
918 ($pid = <PID>) =~ s/\r|\n//g;
920 $pid || return &text('stop_epidfile', "<tt>$config{'pid_file'}</tt>");
921 &kill_logged('TERM', $pid) ||
922 return &text('stop_ekill', "<tt>$pid</tt>", "<tt>$!</tt>");
928 # Performs initial postgreSQL configuration. Returns an error message on failure
929 # or undef on success.
932 return undef if (!$config{'setup_cmd'});
933 local $temp = &transname();
934 local $rv = &system_logged("($config{'setup_cmd'}) >$temp 2>&1");
935 local $out = `cat $temp`;
938 return "<pre>$out</pre>";
944 # Returns the names of all indexes in some database
949 local %tables = map { $_, 1 } &list_tables($db);
950 if (&supports_schemas($db)) {
951 local $t = &execute_sql_safe($db, "select schemaname,indexname,tablename from pg_indexes");
952 return map { ($_->[0] eq "public" ? "" : $_->[0].".").$_->[1] }
953 grep { $tables{($_->[0] eq "public" ? "" : $_->[0].".").$_->[2]} }
957 local $t = &execute_sql_safe($db, "select indexname,tablename from pg_indexes");
958 return map { $_->[0] } grep { $tables{$t->[1]} } @{$t->{'data'}};
962 # index_structure(db, indexname)
963 # Returns information on an index
966 local ($db, $index) = @_;
967 local $info = { 'name' => $index };
968 if (&supports_schemas($db)) {
970 if ($index =~ /^(\S+)\.(\S+)$/) {
971 ($sn, $in) = ($1, $2);
974 ($sn, $in) = ("public", $index);
976 local $t = &execute_sql_safe($db, "select schemaname,tablename,indexdef from pg_indexes where indexname = '$in' and schemaname = '$sn'");
977 local $r = $t->{'data'}->[0];
978 if ($r->[0] eq "public") {
979 $info->{'table'} = $r->[1];
982 $info->{'table'} = $r->[0].".".$r->[1];
984 $info->{'create'} = $r->[2];
987 local $t = &execute_sql_safe($db, "select tablename,indexdef from pg_indexes where indexname = '$index'");
988 local $r = $t->{'data'}->[0];
989 $info->{'table'} = $r->[0];
990 $info->{'create'} = $r->[1];
993 # Parse create expression
994 if ($info->{'create'} =~ /^create\s+unique/i) {
995 $info->{'type'} = 'unique';
997 if ($info->{'create'} =~ /using\s+(\S+)\s/) {
998 $info->{'using'} = lc($1);
1000 if ($info->{'create'} =~ /\((.*)\)/) {
1001 $info->{'cols'} = [ split(/\s*,\s*/, $1) ];
1007 sub supports_indexes
1009 return &get_postgresql_version() >= 7.3;
1013 # Returns the names of all views in some database
1018 if (&supports_schemas($db)) {
1019 local $t = &execute_sql_safe($db, "select schemaname,viewname from pg_views where schemaname != 'pg_catalog' and schemaname != 'information_schema'");
1020 return map { ($_->[0] eq "public" ? "" : $_->[0].".").$_->[1] }
1024 local $t = &execute_sql_safe($db, "select viewname from pg_indexes");
1025 return map { $_->[0] } @{$t->{'data'}};
1029 # view_structure(db, viewname)
1030 # Returns information about a view
1033 local ($db, $view) = @_;
1034 local $info = { 'name' => $view };
1035 if (&supports_schemas($db)) {
1037 if ($view =~ /^(\S+)\.(\S+)$/) {
1038 ($sn, $in) = ($1, $2);
1041 ($sn, $in) = ("public", $view);
1043 local $t = &execute_sql_safe($db, "select schemaname,viewname,definition from pg_views where viewname = '$in' and schemaname = '$sn'");
1044 local $r = $t->{'data'}->[0];
1045 $info->{'query'} = $r->[2];
1048 local $t = &execute_sql_safe($db, "select viewname,definition from pg_views where viewname = '$index'");
1049 local $r = $t->{'data'}->[0];
1050 $info->{'query'} = $r->[1];
1053 $info->{'query'} =~ s/;$//;
1060 return &get_postgresql_version() >= 7.3;
1063 # list_sequences(db)
1064 # Returns the names of all sequences in some database
1069 if (&supports_schemas($db)) {
1070 local $t = &execute_sql_safe($db, "select schemaname,relname from pg_statio_user_sequences");
1071 return map { ($_->[0] eq "public" ? "" : $_->[0].".").$_->[1] }
1075 local $t = &execute_sql_safe($db, "select relname from pg_statio_user_sequences");
1076 return map { $_->[0] } @{$t->{'data'}};
1080 # sequence_structure(db, name)
1081 # Returns details of a sequence
1082 sub sequence_structure
1084 local ($db, $seq) = @_;
1085 local $info = { 'name' => $seq };
1087 local $t = &execute_sql_safe($db, "select * from "."e_table($seq));
1088 local $r = $t->{'data'}->[0];
1090 foreach my $c (@{$t->{'titles'}}) {
1091 $info->{$c} = $r->[$i++];
1097 sub supports_sequences
1099 return &get_postgresql_version() >= 7.4 ? 1 :
1100 &get_postgresql_version() >= 7.3 ? 2 : 0;
1103 # Returns 1 if the postgresql server being managed is on this system
1104 sub is_postgresql_local
1106 return $config{'host'} eq '' || $config{'host'} eq 'localhost' ||
1107 $config{'host'} eq &get_system_hostname() ||
1108 &to_ipaddress($config{'host'}) eq &to_ipaddress(&get_system_hostname());
1111 # backup_database(database, dest-path, format, [&only-tables])
1112 # Executes the pg_dump command to backup the specified database to the
1113 # given destination path. Returns undef on success, or an error message
1117 local ($db, $path, $format, $tables) = @_;
1118 local $tablesarg = join(" ", map { " -t ".quotemeta($_) } @$tables);
1119 local $cmd = "e_path($config{'dump_cmd'}).
1120 (!$postgres_login ? "" :
1121 &supports_pgpass() ? " -U $postgres_login" : " -u").
1122 ($config{'host'} ? " -h $config{'host'}" : "").
1123 ($format eq 'p' ? "" : " -b").
1125 " -F$format -f "."e_path($path)." $db";
1126 if ($postgres_sameunix && defined(getpwnam($postgres_login))) {
1127 $cmd = &command_as_user($postgres_login, 0, $cmd);
1129 $cmd = &command_with_login($cmd);
1130 local $out = &backquote_logged("$cmd 2>&1");
1131 if ($? || $out =~ /could not|error|failed/i) {
1137 # restore_database(database, source-path, only-data, clear-db, [&only-tables])
1138 # Restores the contents of a PostgreSQL backup into the specified database.
1139 # Returns undef on success, or an error message on failure.
1140 sub restore_database
1142 local ($db, $path, $only, $clean, $tables) = @_;
1143 local $tablesarg = join(" ", map { " -t ".quotemeta($_) } @$tables);
1144 local $cmd = "e_path($config{'rstr_cmd'}).
1145 (!$postgres_login ? "" :
1146 &supports_pgpass() ? " -U $postgres_login" : " -u").
1147 ($config{'host'} ? " -h $config{'host'}" : "").
1148 ($only ? " -a" : "").
1149 ($clean ? " -c" : "").
1151 " -d $db "."e_path($path);
1152 if ($postgres_sameunix && defined(getpwnam($postgres_login))) {
1153 $cmd = &command_as_user($postgres_login, 0, $cmd);
1155 $cmd = &command_with_login($cmd);
1156 local $out = &backquote_logged("$cmd 2>&1");
1157 if ($? || $out =~ /could not|error|failed/i) {
1163 # PostgreSQL versions below 7.3 don't support .pgpass, and version 8.0.*
1164 # don't allow it to be located via $HOME or $PGPASSFILE.
1167 local $ver = &get_postgresql_version(1);
1168 return $ver >= 7.3 && $ver < 8.0 ||
1172 # command_with_login(command, [user, pass])
1173 # Given a command that talks to postgresql (like psql or pg_dump), sets up
1174 # the environment so that it can login to the database. Returns a modified
1175 # command to execute.
1176 sub command_with_login
1178 local ($cmd, $user, $pass) = @_;
1179 if (!defined($user)) {
1180 $user = $postgres_login;
1181 $pass = $postgres_pass;
1184 if (&supports_pgpass()) {
1185 # Can use .pgpass file
1187 if ($gconfig{'os_type'} eq 'windows') {
1188 # On Windows, the file is under ~/application data
1189 local $appdata = "$ENV{'HOME'}/application data";
1190 &make_dir($appdata, 0755);
1191 local $postgresql = "$appdata/postgresql";
1192 &make_dir($postgresql, 0755);
1193 $pgpass = "$postgresql/pgpass.conf";
1196 local $temphome = &transname();
1197 &make_dir($temphome, 0755);
1198 $pgpass = "$temphome/.pgpass";
1199 $ENV{'HOME'} = $temphome;
1201 $ENV{'PGPASSFILE'} = $pgpass;
1202 open(PGPASS, ">$pgpass");
1203 print PGPASS "*:*:*:$user:$pass\n";
1205 &set_ownership_permissions(
1206 $postgres_sameunix ? $user : undef,
1207 undef, 0600, $pgpass);
1210 # Need to put login and password in temp file
1211 $loginfile = &transname();
1212 open(TEMP, ">$loginfile");
1213 print TEMP "$user\n$pass\n";
1215 $cmd .= " <$loginfile";