← Back
Editing: postgresql-lib.pl
# postgresql-lib.pl # Common PostgreSQL functions # XXX updating date field BEGIN { push(@INC, ".."); }; use WebminCore; &init_config(); do 'view-lib.pl'; if ($config{'plib'}) { $ENV{$gconfig{'ld_env'}} .= ':' if ($ENV{$gconfig{'ld_env'}}); $ENV{$gconfig{'ld_env'}} .= $config{'plib'}; } if ($config{'psql'} =~ /^(.*)\/bin\/psql$/ && $1 ne '' && $1 ne '/usr') { $ENV{$gconfig{'ld_env'}} .= ':' if ($ENV{$gconfig{'ld_env'}}); $ENV{$gconfig{'ld_env'}} .= "$1/lib"; } $pg_shadow_cols = "usename,usesysid,usecreatedb,usesuper,usecatupd,passwd,valuntil"; if ($module_info{'usermin'}) { # Login and password is set by user in Usermin, and the module always # runs as the Usermin user &switch_to_remote_user(); &create_user_config_dirs(); &set_login_pass(0, $userconfig{'login'}, $userconfig{'pass'}); %access = ( 'backup' => 1, 'restore' => 1, 'tables' => 1, 'cmds' => 1, ); $max_dbs = $userconfig{'max_dbs'}; $commands_file = "$user_module_config_directory/commands"; %displayconfig = %userconfig; } else { # Login and password is determined by ACL in Webmin %access = &get_module_acl(); if ($access{'user'} && !$use_global_login) { &set_login_pass( $access{'sameunix'}, $access{'user'}, $access{'pass'}); } else { &set_login_pass( $config{'sameunix'}, $config{'login'}, $config{'pass'}); } $max_dbs = $config{'max_dbs'}; $commands_file = "$module_config_directory/commands"; %displayconfig = %config; } foreach my $hba (split(/\t+/, $config{'hba_conf'})) { if ($hba =~ /\*|\?/) { ($hba) = glob($hba); } if ($hba && -r $hba) { $hba_conf_file = $hba; last; } } $cron_cmd = "$module_config_directory/backup.pl"; if (!$config{'nodbi'}) { # Check if we have DBD::Pg eval <<EOF; use DBI; \$driver_handle = DBI->install_driver("Pg"); EOF } # is_postgresql_running() # Returns 1 if yes, 0 if no, -1 if the login is invalid, -2 if there # is a library problem. When called in an array context, returns the full error # message too. sub is_postgresql_running { local $temp = &transname(); local $cmd = "e_path($config{'psql'}). &host_port_flags(). (!&supports_pgpass() ? " -u" : " -U $postgres_login"). " -c ''". " ".$config{'basedb'}; if ($postgres_sameunix && defined(getpwnam($postgres_login))) { $cmd = "su $postgres_login -c ".quotemeta($cmd); } $cmd = &command_with_login($cmd); if (&foreign_check("proc")) { &foreign_require("proc", "proc-lib.pl"); if (defined(&proc::close_controlling_pty)) { # Detach from tty if possible, so that the psql # command doesn't prompt for a login &proc::close_controlling_pty(); } } open(OUT, "$cmd 2>&1 |"); while(<OUT>) { $out .= $_; } close(OUT); unlink($temp); local $rv; if ($out =~ /setuserid:/i || $out =~ /no\s+password\s+supplied/i || $out =~ /no\s+postgres\s+username/i || $out =~ /authentication\s+failed/i || $out =~ /password:.*password:/i || $out =~ /database.*does.*not/i || $out =~ /user.*does.*not/i) { $rv = -1; } elsif ($out =~ /connect.*failed/i || $out =~ /could not connect to server:/) { $rv = 0; } elsif ($out =~ /lib\S+\.so/i) { $rv = -2; } else { $rv = 1; } return wantarray ? ($rv, $out) : $rv; } # get_postgresql_version([from-command]) sub get_postgresql_version { my ($fromcmd) = @_; return $postgresql_version_cache if (defined($postgresql_version_cache)); my $rv; if (!$fromcmd) { eval { local $main::error_must_die = 1; local $v = &execute_sql_safe($config{'basedb'}, 'select version()'); $v = $v->{'data'}->[0]->[0]; if ($v =~ /postgresql\s+([0-9\.]+)/i) { $rv = $1; } }; } if (!$rv || $@) { my $out = &backquote_command( "e_path($config{'psql'})." -V 2>&1 <$null_file"); $rv = $out =~ /\s([0-9\.]+)/ ? $1 : undef; } $postgresql_version_cache = $rv; return $rv; } sub can_drop_fields { return &get_postgresql_version() >= 7.3; } # list_databases() # Returns a list of all databases sub list_databases { local $force_nodbi = 1; local $t = &execute_sql_safe($config{'basedb'}, 'select datname from pg_database order by datname'); return sort { lc($a) cmp lc($b) } map { $_->[0] } @{$t->{'data'}}; } # supports_schemas(database) # Returns 1 if schemas are supported sub supports_schemas { 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"); return $t->{'data'}->[0]->[0] ? 1 : 0; } # list_tables(database) # Returns a list of tables in some database sub list_tables { if (&supports_schemas($_[0])) { local $t = &execute_sql_safe($_[0], 'select schemaname,tablename from pg_tables order by tablename'); return map { ($_->[0] eq "public" ? "" : $_->[0].".").$_->[1] } grep { $_->[1] !~ /^(pg|sql)_/ } @{$t->{'data'}}; } else { local $t = &execute_sql_safe($_[0], 'select tablename from pg_tables order by tablename'); return map { $_->[0] } grep { $_->[0] !~ /^(pg|sql)_/ } @{$t->{'data'}}; } } # list_types() # Returns a list of all available field types sub list_types { local $t = &execute_sql_safe($config{'basedb'}, 'select typname from pg_type where typrelid = 0 and typname !~ \'^_.*\' order by typname'); local @types = map { $_->[0] } @{$t->{'data'}}; push(@types, "serial", "bigserial") if (&get_postgresql_version() >= 7.4); return sort { $a cmp $b } &unique(@types); } # table_structure(database, table) # Returns a list of hashes detailing the structure of a table sub table_structure { if (&supports_schemas($_[0])) { # Find the schema and table local ($tn, $ns); if ($_[1] =~ /^(\S+)\.(\S+)$/) { $ns = $1; $tn = $2; } else { $ns = "public"; $tn = $_[1]; } $tn =~ s/^([^\.]+)\.//; 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"); local (@rv, $r); foreach $r (@{$t->{'data'}}) { local $arr; $arr++ if ($r->[2] =~ s/^_//); local $sz = $r->[4] - 4; if ($sz >= 65536 && $r->[2] =~ /numeric/i) { $sz = int($sz/65536).",".($sz%65536); } push(@rv, { 'field' => $r->[1], 'arr' => $arr ? 'YES' : 'NO', 'type' => $r->[4] < 0 ? $r->[2] : $r->[2]."($sz)", 'null' => $r->[5] =~ /f|0/ ? 'YES' : 'NO' } ); } # Work out which fields are the primary key if (&supports_indexes()) { local ($keyidx) = grep { $_ eq $_[1]."_pkey" || $_ eq "pk_".$_[1] } &list_indexes($_[0]); if ($keyidx) { local $istr = &index_structure($_[0], $keyidx); foreach my $r (@rv) { if (&indexof($r->{'field'}, @{$istr->{'cols'}}) >= 0) { $r->{'key'} = 'PRI'; } } } } return @rv; } else { # Just look by table name 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"); local (@rv, $r); foreach $r (@{$t->{'data'}}) { local $arr; $arr++ if ($r->[2] =~ s/^_//); local $sz = $r->[4] - 4; if ($sz >= 65536 && $r->[2] =~ /numeric/i) { $sz = int($sz/65536).",".($sz%65536); } push(@rv, { 'field' => $r->[1], 'arr' => $arr ? 'YES' : 'NO', 'type' => $r->[4] < 0 ? $r->[2] : $r->[2]."($sz)", 'null' => $r->[5] =~ /f|0/ ? 'YES' : 'NO' } ); } return @rv; } } # execute_sql(database, sql, [param, ...]) sub execute_sql { if (&is_readonly_mode()) { return { }; } &execute_sql_safe(@_); } # execute_sql_safe(database, sql, [param, ...]) sub execute_sql_safe { local $sql = $_[1]; local @params = @_[2..$#_]; if ($gconfig{'debug_what_sql'}) { # Write to Webmin debug log local $params; for(my $i=0; $i<@params; $i++) { $params .= " ".$i."=".$params[$i]; } &webmin_debug_log('SQL', "db=$_[0] sql=$sql".$params); } if ($sql !~ /^\s*\\/ && !$main::disable_postgresql_escaping) { $sql =~ s/\\/\\\\/g; } if ($driver_handle && $sql !~ /^\s*(create|drop)\s+database/ && $sql !~ /^\s*\\/ && !$force_nodbi) { # Use the DBI interface local $pid; local $cstr = "dbname=$_[0]"; $cstr .= ";host=$config{'host'}" if ($config{'host'}); $cstr .= ";port=$config{'port'}" if ($config{'port'}); local $sslmode = $config{'sslmode'}; $sslmode =~ s/_/-/g; $cstr .= ";sslmode=$sslmode" if ($sslmode); local @uinfo; if ($postgres_sameunix && (@uinfo = getpwnam($postgres_login))) { # DBI call which must run in subprocess pipe(OUTr, OUTw); if (!($pid = fork())) { &switch_to_unix_user(\@uinfo); close(OUTr); local $dbh = $driver_handle->connect($cstr, $postgres_login, $postgres_pass); if (!$dbh) { print OUTw &serialise_variable( "DBI connect failed : ".$DBI::errstr); exit(0); } $dbh->{'AutoCommit'} = 0; local $cmd = $dbh->prepare($sql); #foreach (@params) { # XXX dbd quoting is broken! # s/\\/\\\\/g; # } if (!$cmd->execute(@params)) { print OUTw &serialise_variable(&text('esql', "<tt>".&html_escape($sql)."</tt>", "<tt>".&html_escape($dbh->errstr)."</tt>")); $dbh->disconnect(); exit(0); } local (@data, @row); local @titles = @{$cmd->{'NAME'}}; while(@row = $cmd->fetchrow()) { push(@data, [ @row ]); } $cmd->finish(); $dbh->commit(); $dbh->disconnect(); print OUTw &serialise_variable( { 'titles' => \@titles, 'data' => \@data }); exit(0); } close(OUTw); local $line = <OUTr>; local $rv = &unserialise_variable($line); if (ref($rv)) { return $rv; } else { &error($rv || "$sql : Unknown DBI error"); } } else { # Just normal DBI call local $dbh = $driver_handle->connect($cstr, $postgres_login, $postgres_pass); $dbh || &error("DBI connect failed : ",$DBI::errstr); $dbh->{'AutoCommit'} = 0; local $cmd = $dbh->prepare($sql); if (!$cmd->execute(@params)) { &error(&text('esql', "<tt>".&html_escape($sql)."</tt>", "<tt>".&html_escape($dbh->errstr)."</tt>")); } local (@data, @row); local @titles = @{$cmd->{'NAME'}}; while(@row = $cmd->fetchrow()) { push(@data, [ @row ]); } $cmd->finish(); $dbh->commit(); $dbh->disconnect(); return { 'titles' => \@titles, 'data' => \@data }; } } else { # Check for a \ command my $break_f = 0 ; if ($sql =~ /^\s*\\l\s*$/) { # \l command to list encodings needs no special handling } elsif ($sql =~ /^\s*\\/ ) { $break_f = 1 ; if ($sql !~ /^\s*\\copy\s+/ && $sql !~ /^\s*\\i\s+/) { &error ( &text ( 'r_command', ) ) ; } } if (@params) { # Sub in ? parameters local $p; local $pos = -1; foreach $p (@params) { $pos = index($sql, '?', $pos+1); &error("Incorrect number of parameters in $_[1] (".scalar(@params).")") if ($pos < 0); local $qp = $p; if ($qp !~ /^[bB]'\d+'$/) { # Quote value, except for bits $qp =~ s/\\/\\\\/g; $qp =~ s/'/''/g; $qp =~ s/\$/\\\$/g; $qp =~ s/\n/\\n/g; $qp = $qp eq '' ? "NULL" : "'$qp'"; } $sql = substr($sql, 0, $pos).$qp.substr($sql, $pos+1); $pos += length($qp)-1; } } # Call the psql program local $cmd = "e_path($config{'psql'})." --html". &host_port_flags(). (!&supports_pgpass() ? " -u" : " -U $postgres_login"). " -c "."e_path($sql). " ".$_[0]; if ($postgres_sameunix && defined(getpwnam($postgres_login))) { $cmd = &command_as_user($postgres_login, 0, $cmd); } $cmd = &command_with_login($cmd); delete($ENV{'LANG'}); # to force output to english delete($ENV{'LANGUAGE'}); if ($break_f == 0) { # Running a normal SQL command, not one with a \ #$ENV{'PAGER'} = "cat"; if (&foreign_check("proc")) { &foreign_require("proc", "proc-lib.pl"); if (defined(&proc::close_controlling_pty)) { # Detach from tty if possible, so that the psql # command doesn't prompt for a login &proc::close_controlling_pty(); } } open(OUT, "$cmd 2>&1 |"); local ($line, $rv, @data); do { $line = <OUT>; } while($line =~ /^(username|password|user name):/i || $line =~ /(warning|notice):/i || $line !~ /\S/ && defined($line)); unlink($temp); if ($line =~ /^ERROR:\s+(.*)/ || $line =~ /FATAL.*:\s+(.*)/) { &error(&text('esql', "<tt>$sql</tt>", "<tt>$1</tt>")); } elsif (!defined($line)) { # Un-expected end of output .. &error(&text('esql', "<tt>$sql</tt>", "<tt>$config{'psql'} failed</tt>")); } else { # Read HTML-format output local $row; local @data; while($line = <OUT>) { if ($line =~ /^\s*<tr>/) { # Start of a row $row = [ ]; } elsif ($line =~ /^\s*<\/tr>/) { # End of a row push(@data, $row); $row = undef; } elsif ($line =~ /^\s*<(td|th)[^>]*>(.*)<\/(td|th)>/) { # Value in a row local $v = $2; $v =~ s/<br>/\n/g; push(@$row, &entities_to_ascii($v)); } } $rv = { 'titles' => shift(@data), 'data' => \@data }; } close(OUT); return $rv; } else { # Running a special \ command local ( @titles, @row, @data, $rc, $emsgf, $emsg ) ; $emsgf = &transname(); $rc = &system_logged ( "$cmd >$emsgf 2>&1"); $emsg = &read_file_contents($emsgf); &unlink_file($emsgf) ; if ($rc) { &error("<pre>$emsg</pre>"); } else { @titles = ( " Command Invocation " ) ; @row = ( " Done ( return code : $rc )" ) ; map { s/^\s+//; s/\s+$// } @row ; push ( @data, \@row ) ; return { 'titles' => \@titles, 'data' => \@data } ; } } } } # execute_sql_logged(database, command) sub execute_sql_logged { &additional_log('sql', $_[0], $_[1]); return &execute_sql(@_); } sub can_edit_db { if ($module_info{'usermin'}) { # Check access control list in configuration local $rv; DB: foreach $l (split(/\t/, $config{'access'})) { if ($l =~ /^(\S+):\s*(.*)$/ && ($1 eq $remote_user || $1 eq '*')) { local @dbs = split(/\s+/, $2); foreach $d (@dbs) { $d =~ s/\$REMOTE_USER/$remote_user/g; if ($d eq '*' || $_[0] =~ /^$d$/) { $rv = 1; last DB; } } $rv = 0; last DB; } } if ($rv && $config{'access_own'}) { # Check ownership on DB - first get login ID if (!defined($postgres_login_id)) { local $d = &execute_sql($config{'basedb'}, "select usesysid from pg_user where usename = ?", $postgres_login); $postgres_login_id = $d->{'data'}->[0]->[0]; } # Get database owner local $d = &execute_sql($config{'basedb'}, "select datdba from pg_database where datname = ?", $_[0]); if ($d->{'data'}->[0]->[0] != $postgres_login_id) { $rv = 0; } } return $rv; } else { # Check Webmin ACL local $d; return 1 if ($access{'dbs'} eq '*'); foreach $d (split(/\s+/, $access{'dbs'})) { return 1 if ($d && $d eq $_[0]); } return 0; } } # get_hba_config(version) # Parses the postgres host access config file sub get_hba_config { local $lnum = 0; open(HBA, "<".$hba_conf_file); while(<HBA>) { s/\r|\n//g; s/^\s*#.*$//g; if ($_[0] >= 7.3) { # New file format if (/^\s*(host|hostssl)\s+(\S+)\s+(\S+)\s+(\S+)\/(\S+)\s+(\S+)(\s+(\S+))?/) { # Host/cidr format push(@rv, { 'type' => $1, 'index' => scalar(@rv), 'line' => $lnum, 'db' => $2, 'user' => $3, 'address' => $4, 'cidr' => $5, 'auth' => $6, 'arg' => $8 } ); } elsif (/^\s*(host|hostssl)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)(\s+(\S+))?/) { # Host netmask format push(@rv, { 'type' => $1, 'index' => scalar(@rv), 'line' => $lnum, 'db' => $2, 'user' => $3, 'address' => $4, 'netmask' => $5, 'auth' => $6, 'arg' => $8 } ); } elsif (/^\s*local\s+(\S+)\s+(\S+)\s+(\S+)(\s+(\S+))?/) { push(@rv, { 'type' => 'local', 'index' => scalar(@rv), 'line' => $lnum, 'db' => $1, 'user' => $2, 'auth' => $3, 'arg' => $5 } ); } } else { # Old file format if (/^\s*host\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)(\s+(\S+))?/) { push(@rv, { 'type' => 'host', 'index' => scalar(@rv), 'line' => $lnum, 'db' => $1, 'address' => $2, 'netmask' => $3, 'auth' => $4, 'arg' => $6 } ); } elsif (/^\s*local\s+(\S+)\s+(\S+)(\s+(\S+))?/) { push(@rv, { 'type' => 'local', 'index' => scalar(@rv), 'line' => $lnum, 'db' => $1, 'auth' => $2, 'arg' => $4 } ); } } $lnum++; } close(HBA); return @rv; } # create_hba(&hba, version) sub create_hba { local $lref = &read_file_lines($hba_conf_file); push(@$lref, &hba_line($_[0], $_[1])); &flush_file_lines(); } # delete_hba(&hba, version) sub delete_hba { local $lref = &read_file_lines($hba_conf_file); splice(@$lref, $_[0]->{'line'}, 1); &flush_file_lines(); } # modify_hba(&hba, version) sub modify_hba { local $lref = &read_file_lines($hba_conf_file); splice(@$lref, $_[0]->{'line'}, 1, &hba_line($_[0], $_[1])); &flush_file_lines(); } # swap_hba(&hba1, &hba2) sub swap_hba { local $lref = &read_file_lines($hba_conf_file); local $line0 = $lref->[$_[0]->{'line'}]; local $line1 = $lref->[$_[1]->{'line'}]; $lref->[$_[1]->{'line'}] = $line0; $lref->[$_[0]->{'line'}] = $line1; &flush_file_lines(); } # hba_line(&hba, version) sub hba_line { if ($_[0]->{'type'} eq 'host' || $_[0]->{'type'} eq 'hostssl') { return join(" ", $_[0]->{'type'}, $_[0]->{'db'}, ( $_[1] >= 7.3 ? ( $_[0]->{'user'} ) : ( ) ), ($_[0]->{'cidr'} eq '' ? ( $_[0]->{'address'}, $_[0]->{'netmask'} ) : ( "$_[0]->{'address'}/$_[0]->{'cidr'}" )), $_[0]->{'auth'}, $_[0]->{'arg'} ? ( $_[0]->{'arg'} ) : () ); } else { return join(" ", 'local', $_[0]->{'db'}, ( $_[1] >= 7.3 ? ( $_[0]->{'user'} ) : ( ) ), $_[0]->{'auth'}, $_[0]->{'arg'} ? ( $_[0]->{'arg'} ) : () ); } } # split_array(value) sub split_array { if ($_[0] =~ /^\{(.*)\}$/) { local @a = split(/,/, $1); return @a; } else { return ( $_[0] ); } } # join_array(values ..) sub join_array { local $alpha; map { $alpha++ if (!/^-?[0-9\.]+/) } @_; return $alpha ? '{'.join(',', map { "'$_'" } @_).'}' : '{'.join(',', @_).'}'; } sub is_blob { return $_[0]->{'type'} eq 'text' || $_[0]->{'type'} eq 'bytea'; } # restart_postgresql() # HUP postmaster if running, so that hosts file changes take effect sub restart_postgresql { foreach my $pidfile (glob($config{'pid_file'})) { local $pid = &check_pid_file($pidfile); if ($pid) { &kill_logged('HUP', $pid); } } } # date_subs(filename) # Does strftime-style date substitutions on a filename, if enabled sub date_subs { local ($path) = @_; local $rv; if ($config{'date_subs'}) { eval "use POSIX"; eval "use posix" if ($@); local @tm = localtime(time()); &clear_time_locale(); $rv = strftime($path, @tm); &reset_time_locale(); } else { $rv = $path; } if ($config{'webmin_subs'}) { $rv = &substitute_template($rv, { }); } return $rv; } # execute_before(db, handle, escape, path, db-for-config) sub execute_before { local $cmd = $config{'backup_before_'.$_[4]}; if ($cmd) { $ENV{'BACKUP_FILE'} = $_[3]; local $h = $_[1]; local $out; local $rv = &execute_command($cmd, undef, \$out, \$out); if ($h && $out) { print $h $_[2] ? "<pre>".&html_escape($out)."</pre>" : $out; } return !$rv; } return 1; } # execute_after(db, handle, escape, path, db-for-config) sub execute_after { local $cmd = $config{'backup_after_'.$_[4]}; if ($cmd) { $ENV{'BACKUP_FILE'} = $_[3]; local $h = $_[1]; local $out; local $rv = &execute_command($cmd, undef, \$out, \$out); if ($h && $out) { print $h $_[2] ? "<pre>".&html_escape($out)."</pre>" : $out; } return !$rv; } return 1; } # make_backup_dir(directory) # Create a directory that PostgreSQL can backup into sub make_backup_dir { local ($dir) = @_; if (!-d $dir) { &make_dir($dir, 0755); if ($postgres_sameunix && defined(getpwnam($postgres_login))) { &set_ownership_permissions($postgres_login, undef, undef, $dir); } } } # quote_table(table) # Quotes a table name, including any schema sub quote_table { local @tn = split(/\./, $_[0]); return join(".", map { &pg_quote_ident($_) } @tn); } # quotestr(string) # Quotes a string for use in SQL statements sub quotestr { return &pg_quote_ident($_[0]); } # pg_quote_ident(name) # Quotes an identifier for use in SQL statements sub pg_quote_ident { my ($name) = @_; $name =~ s/"/""/g; return "\"$name\""; } # pg_quote_lit(string) # Quotes a literal for use in SQL statements sub pg_quote_lit { my ($value) = @_; $value =~ s/'/''/g; return "'$value'"; } # postgresql_command_unix_user([login]) # Returns the Unix user that PostgreSQL commands will run as. sub postgresql_command_unix_user { my ($login) = @_; $login ||= $postgres_login; return undef if (!$login || !$postgres_sameunix || $< != 0); my @uinfo = getpwnam($login); return @uinfo ? $login : undef; } # set_postgresql_command_file_permissions(file, [login]) # Makes a temp file readable by the Unix user that will run PostgreSQL commands. sub set_postgresql_command_file_permissions { my ($file, $login) = @_; my $user = &postgresql_command_unix_user($login); return 1 if (!$user); return &set_ownership_permissions($user, undef, 0600, $file); } # execute_sql_file(database, file, [user, pass], [unix-user]) # Executes some file of SQL statements, and returns the exit status and output sub execute_sql_file { local ($db, $file, $user, $pass, $unixuser) = @_; if (&is_readonly_mode()) { return (0, undef); } if (!defined($user)) { $user = $postgres_login; $pass = $postgres_pass; } local $cmd = "e_path($config{'psql'})." -f "."e_path($file). &host_port_flags(). (&supports_pgpass() ? " -U $user" : " -u"). " ".$db; if ($postgres_sameunix && defined(getpwnam($postgres_login))) { $cmd = &command_as_user($postgres_login, 0, $cmd); } elsif ($unixuser && $unixuser ne 'root' && $< == 0) { $cmd = &command_as_user($unixuser, 0, $cmd); } $cmd = &command_with_login($cmd, $user, $pass); local $out = &backquote_logged("$cmd 2>&1"); return ($out =~ /ERROR/i ? 1 : 0, $out); } # split_table(&titles, &checkboxes, &links, &col1, &col2, ...) # Outputs a table that is split into two parts sub split_table { local $mid = int((@{$_[2]}+1) / 2); local ($i, $j); print "<table width=100%><tr>\n"; foreach $s ([0, $mid-1], [$mid, @{$_[2]}-1]) { print "<td width=50% valign=top>\n"; # Header local @tds = $_[1] ? ( "width=5" ) : ( ); if ($s->[0] <= $s->[1]) { local @hcols; foreach $t (@{$_[0]}) { push(@hcols, $t); } print &ui_columns_start(\@hcols, 100, 0, \@tds); } for($i=$s->[0]; $i<=$s->[1]; $i++) { local @cols; push(@cols, "<a href='$_[2]->[$i]'>$_[3]->[$i]</a>"); for($j=4; $j<@_; $j++) { push(@cols, $_[$j]->[$i]); } if ($_[1]) { print &ui_checked_columns_row(\@cols, \@tds, "d", $_[1]->[$i]); } else { print &ui_columns_row(\@cols, \@tds); } } if ($s->[0] <= $s->[1]) { print &ui_columns_end(); } print "</td>\n"; } print "</tr></table>\n"; } # accepting_connections(db) # Returns 1 if some database is accepting connections, 0 if not sub accepting_connections { if (!defined($has_connections)) { $has_connections = 0; local @str = &table_structure($config{'basedb'}, "pg_catalog.pg_database"); foreach my $f (@str) { $has_connections = 1 if ($f->{'field'} eq 'datallowconn'); } } if ($has_connections) { $rv = &execute_sql_safe($config{'basedb'}, "select datallowconn from pg_database where datname = '$_[0]'"); if ($rv->{'data'}->[0]->[0] !~ /^(t|1)/i) { return 0; } } return 1; } # start_postgresql() # Starts the PostgreSQL database server. Returns an error message on failure # or undef on success. sub start_postgresql { if ($gconfig{'os_type'} eq 'windows' && &foreign_check("init")) { # On Windows, always try to sc start the pgsql- service &foreign_require("init", "init-lib.pl"); local ($pg) = grep { $_->{'name'} =~ /^pgsql-/ } &init::list_win32_services(); if ($pg) { return &init::start_win32_service($pg->{'name'}); } } local $temp = &transname(); local $rv = &system_logged("($config{'start_cmd'}) >$temp 2>&1"); local $out = `cat $temp`; unlink($temp); unlink($temp); if ($rv || $out =~ /failed|error/i) { return "<pre>$out</pre>"; } return undef; } # stop_postgresql() # Stops the PostgreSQL database server. Returns an error message on failure # or undef on success. sub stop_postgresql { if ($gconfig{'os_type'} eq 'windows' && &foreign_check("init")) { # On Windows, always try to sc stop the pgsql- service &foreign_require("init", "init-lib.pl"); local ($pg) = grep { $_->{'name'} =~ /^pgsql-/ } &init::list_win32_services(); if ($pg) { return &init::stop_win32_service($pg->{'name'}); } } if ($config{'stop_cmd'}) { local $out = &backquote_logged("$config{'stop_cmd'} 2>&1"); if ($? || $out =~ /failed|error/i) { return "<pre>$?\n$out</pre>"; } } else { local $pidcount = 0; foreach my $pidfile (glob($config{'pid_file'})) { local $pid = &check_pid_file($pidfile); if ($pid) { &kill_logged('TERM', $pid) || return &text('stop_ekill', "<tt>$pid</tt>", "<tt>$!</tt>"); $pidcount++; } } $pidcount || return &text('stop_epidfile', "<tt>$config{'pid_file'}</tt>"); } return undef; } # setup_postgresql() # Performs initial postgreSQL configuration. Returns an error message on failure # or undef on success. sub setup_postgresql { return undef if (!$config{'setup_cmd'}); local $temp = &transname(); local $rv = &system_logged("($config{'setup_cmd'}) >$temp 2>&1"); local $out = `cat $temp`; unlink($temp); if ($rv) { return "<pre>$out</pre>"; } return undef; } # list_indexes(db) # Returns the names of all indexes in some database sub list_indexes { local ($db) = @_; local (@rv, $r); local %tables = map { $_, 1 } &list_tables($db); if (&supports_schemas($db)) { local $t = &execute_sql_safe($db, "select schemaname,indexname,tablename from pg_indexes"); return map { ($_->[0] eq "public" ? "" : $_->[0].".").$_->[1] } grep { $tables{($_->[0] eq "public" ? "" : $_->[0].".").$_->[2]} } @{$t->{'data'}}; } else { local $t = &execute_sql_safe($db, "select indexname,tablename from pg_indexes"); return map { $_->[0] } grep { $tables{$t->[1]} } @{$t->{'data'}}; } } # index_structure(db, indexname) # Returns information on an index sub index_structure { local ($db, $index) = @_; local $info = { 'name' => $index }; if (&supports_schemas($db)) { local ($sn, $in); if ($index =~ /^(\S+)\.(\S+)$/) { ($sn, $in) = ($1, $2); } else { ($sn, $in) = ("public", $index); } local $t = &execute_sql_safe($db, "select schemaname,tablename,indexdef from pg_indexes where indexname = '$in' and schemaname = '$sn'"); local $r = $t->{'data'}->[0]; if ($r->[0] eq "public") { $info->{'table'} = $r->[1]; } else { $info->{'table'} = $r->[0].".".$r->[1]; } $info->{'create'} = $r->[2]; } else { local $t = &execute_sql_safe($db, "select tablename,indexdef from pg_indexes where indexname = '$index'"); local $r = $t->{'data'}->[0]; $info->{'table'} = $r->[0]; $info->{'create'} = $r->[1]; } # Parse create expression if ($info->{'create'} =~ /^create\s+unique/i) { $info->{'type'} = 'unique'; } if ($info->{'create'} =~ /using\s+(\S+)\s/) { $info->{'using'} = lc($1); } if ($info->{'create'} =~ /\((.*)\)/) { $info->{'cols'} = [ split(/\s*,\s*/, $1) ]; foreach my $c (@{$info->{'cols'}}) { $c =~ s/^"(.*)"$/$1/; } } return $info; } sub supports_indexes { return &get_postgresql_version() >= 7.3; } # list_views(db) # Returns the names of all views in some database sub list_views { local ($db) = @_; local (@rv, $r); if (&supports_schemas($db)) { local $t = &execute_sql_safe($db, "select schemaname,viewname from pg_views where schemaname != 'pg_catalog' and schemaname != 'information_schema'"); return map { ($_->[0] eq "public" ? "" : $_->[0].".").$_->[1] } @{$t->{'data'}}; } else { local $t = &execute_sql_safe($db, "select viewname from pg_indexes"); return map { $_->[0] } @{$t->{'data'}}; } } # view_structure(db, viewname) # Returns information about a view sub view_structure { local ($db, $view) = @_; local $info = { 'name' => $view }; if (&supports_schemas($db)) { local ($sn, $in); if ($view =~ /^(\S+)\.(\S+)$/) { ($sn, $in) = ($1, $2); } else { ($sn, $in) = ("public", $view); } local $t = &execute_sql_safe($db, "select schemaname,viewname,definition from pg_views where viewname = '$in' and schemaname = '$sn'"); local $r = $t->{'data'}->[0]; $info->{'query'} = $r->[2]; } else { local $t = &execute_sql_safe($db, "select viewname,definition from pg_views where viewname = '$index'"); local $r = $t->{'data'}->[0]; $info->{'query'} = $r->[1]; } $info->{'query'} =~ s/;$//; return $info; } sub supports_views { return &get_postgresql_version() >= 7.3; } # list_sequences(db) # Returns the names of all sequences in some database sub list_sequences { local ($db) = @_; local (@rv, $r); if (&supports_schemas($db)) { local $t = &execute_sql_safe($db, "select schemaname,relname from pg_statio_user_sequences"); return map { ($_->[0] eq "public" ? "" : $_->[0].".").$_->[1] } @{$t->{'data'}}; } else { local $t = &execute_sql_safe($db, "select relname from pg_statio_user_sequences"); return map { $_->[0] } @{$t->{'data'}}; } } # sequence_structure(db, name) # Returns details of a sequence sub sequence_structure { local ($db, $seq) = @_; local $info = { 'name' => $seq }; local $t = &execute_sql_safe($db, "select * from "."e_table($seq)); local $r = $t->{'data'}->[0]; local $i = 0; foreach my $c (@{$t->{'titles'}}) { $info->{$c} = $r->[$i++]; } return $info; } sub supports_sequences { return &get_postgresql_version() >= 7.4 ? 1 : &get_postgresql_version() >= 7.3 ? 2 : 0; } # Returns 1 if the postgresql server being managed is on this system sub is_postgresql_local { return $config{'host'} eq '' || $config{'host'} eq 'localhost' || $config{'host'} eq &get_system_hostname() || &to_ipaddress($config{'host'}) eq &to_ipaddress(&get_system_hostname()); } # backup_database(database, dest-path, format, [&only-tables], [run-as-user], # [compress-mode]) # Executes the pg_dump command to backup the specified database to the # given destination path. Returns undef on success, or an error message # on failure. sub backup_database { my ($db, $path, $format, $tables, $user, $compress) = @_; my $tablesarg = join(" ", map { " -t ".quotemeta('"'.$_.'"') } @$tables); my $writer; if ($compress == 0) { $writer = "cat >".quotemeta($path); } elsif ($compress == 1) { $writer = "gzip -c >".quotemeta($path); } elsif ($compress == 2) { $writer = "bzip2 -c >".quotemeta($path); } my $cmd = "e_path($config{'dump_cmd'}). &host_port_flags(). (!$postgres_login ? "" : &supports_pgpass() ? " -U $postgres_login" : " -u"). ($format eq 'p' ? "" : " -b"). $tablesarg. " -F$format $db | $writer"; if ($postgres_sameunix && defined(getpwnam($postgres_login))) { # Postgres connections have to be made as the 'postgres' Unix user $cmd = &command_as_user($postgres_login, 0, $cmd); } elsif ($user) { # Run as a specific Unix user $cmd = &command_as_user($user, 0, $cmd); } $cmd = &command_with_login($cmd); my $out = &backquote_logged("$cmd 2>&1"); if ($? || $out =~ /could not|error|failed/i) { return $out; } return undef; } # restore_database(database, source-path, only-data, clear-db, [&only-tables], # [login, password]) # Restores the contents of a PostgreSQL backup into the specified database. # Returns undef on success, or an error message on failure. sub restore_database { my ($db, $path, $only, $clean, $tables, $login, $pass) = @_; my $tablesarg = join(" ", map { " -t ".quotemeta('"'.$_.'"') } @$tables); $login ||= $postgres_login; $pass ||= $postgres_pass; my $cmd = "e_path($config{'rstr_cmd'}). &host_port_flags(). (!$login ? "" : &supports_pgpass() ? " -U $login" : " -u"). ($only ? " -a" : ""). ($clean ? " -c" : ""). $tablesarg. " -d $db "."e_path($path); if ($postgres_sameunix && defined(getpwnam($login))) { $cmd = &command_as_user($login, 0, $cmd); } $cmd = &command_with_login($cmd, $login, $pass); my $out = &backquote_logged("$cmd 2>&1"); if ($? || $out =~ /could not|error|failed/i) { return $out; } return undef; } # PostgreSQL versions below 7.3 don't support .pgpass, and version 8.0.* # don't allow it to be located via $HOME or $PGPASSFILE. sub supports_pgpass { local $ver = &get_postgresql_version(1); return $ver >= 7.3 && $ver < 8.0 || $ver >= 8.1; } # command_with_login(command, [user, pass]) # Given a command that talks to postgresql (like psql or pg_dump), sets up # the environment so that it can login to the database. Returns a modified # command to execute. sub command_with_login { local ($cmd, $user, $pass) = @_; if (!defined($user)) { $user = $postgres_login; $pass = $postgres_pass; } local $loginfile; if (&supports_pgpass()) { # Can use .pgpass file local $pgpass; if ($gconfig{'os_type'} eq 'windows') { # On Windows, the file is under ~/application data local $appdata = "$ENV{'HOME'}/application data"; &make_dir($appdata, 0755); local $postgresql = "$appdata/postgresql"; &make_dir($postgresql, 0755); $pgpass = "$postgresql/pgpass.conf"; } else { local $temphome = &transname(); &make_dir($temphome, 0755); $pgpass = "$temphome/.pgpass"; push(@main::temporary_files, $pgpass); $cmd = "HOME=$temphome $cmd"; } $cmd = "PGPASSFILE=$pgpass $cmd"; open(PGPASS, ">$pgpass"); print PGPASS "*:*:*:$user:$pass\n"; close(PGPASS); &set_ownership_permissions( $postgres_sameunix ? $user : undef, undef, 0600, $pgpass); } else { # Need to put login and password in temp file $loginfile = &transname(); open(TEMP, ">$loginfile"); print TEMP "$user\n$pass\n"; close(TEMP); $cmd .= " <$loginfile"; } return $cmd; } # host_port_flags() # Returns flags to set the correct host and post for postgreSQL CLI commands sub host_port_flags { local $sslmode = $config{'sslmode'}; $sslmode =~ s/_/-/g; if ($sslmode) { my @rv; push(@rv, "host=".$config{'host'}) if ($config{'host'}); push(@rv, "port=".$config{'port'}) if ($config{'port'}); push(@rv, "sslmode=".$sslmode) if ($sslmode); return @rv ? " '".join(" ", @rv)."'" : ""; } else { my $rv = ""; $rv .= " -h $config{'host'}" if ($config{'host'}); $rv .= " -p $config{'port'}" if ($config{'port'}); return $rv; } } # extract_grants(field) # Given a field from pg_class that contains grants either as a comma-separated # list or an array, return a list of tuples in user,grant format sub extract_grants { my ($f) = @_; my @rv; if (ref($f)) { @rv = map { [ split(/=/, $_, 2) ] } @$f; } else { $f =~ s/^\{//; $f =~ s/\}$//; @rv = map { [ split(/=/, $_, 2) ] } map { s/\\"/"/g; s/"//g; $_ } grep { /=\S/ } split(/,/, $f); } return @rv; } # delete_database_backup_job(db) # If there is a backup scheduled for some database, remove it sub delete_database_backup_job { my ($db) = @_; &foreign_require("cron"); my @jobs = &cron::list_cron_jobs(); my $cmd = "$cron_cmd $db"; my ($job) = grep { $_->{'command'} eq $cmd } @jobs; if ($job) { &lock_file(&cron::cron_file($job)); &cron::delete_cron_job($job); &unlock_file(&cron::cron_file($job)); } } # get_pg_shadow_table() # Returns the table containing users, and the list of columns (comma-separated) sub get_pg_shadow_table { if (&get_postgresql_version() >= 9.5) { my $cols = $pg_shadow_cols; $cols =~ s/usecatupd/'t'/g; return ("pg_user", $cols); } elsif (&get_postgresql_version() >= 9.4) { return ("pg_user", $pg_shadow_cols); } else { return ("pg_shadow", $pg_shadow_cols); } } # compression_format(file) # Returns 0 if uncompressed, 1 for gzip, 2 for compress, 3 for bzip2 or # 4 for zip sub compression_format { open(BACKUP, "<".$_[0]); local $two; read(BACKUP, $two, 2); close(BACKUP); return $two eq "\037\213" ? 1 : $two eq "\037\235" ? 2 : $two eq "PK" ? 4 : $two eq "BZ" ? 3 : 0; } # set_login_pass(same-unix, login, password) # Sets the credentials to be used for all SQL commands sub set_login_pass { my ($sameunix, $login, $pass) = @_; $postgres_sameunix = $sameunix; $postgres_login = $login; $postgres_pass = $pass; } 1;
Save File
Cancel