/usr/share/usermin/postgresql
Edit: /usr/share/usermin/postgresql/postgresql-lib.pl (36083B)
# 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 <
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 .= $_; }
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',
"".&html_escape($sql)."",
"".&html_escape($dbh->errstr).""));
$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 = ;
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', "".&html_escape($sql)."",
"".&html_escape($dbh->errstr).""));
}
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 = ;
} 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', "$sql", "$1"));
}
elsif (!defined($line)) {
# Un-expected end of output ..
&error(&text('esql', "$sql",
"$config{'psql'} failed"));
}
else {
# Read HTML-format output
local $row;
local @data;
while($line = ) {
if ($line =~ /^\s*/) {
# 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/
/\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("$emsg
");
}
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() {
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] ? "".&html_escape($out)."
" : $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] ? "".&html_escape($out)."
" : $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'";
}
# 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 "\n";
foreach $s ([0, $mid-1], [$mid, @{$_[2]}-1]) {
print "| \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, "$_[3]->[$i]");
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 " | \n";
}
print "
\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 "$out
";
}
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 "$?\n$out
";
}
}
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', "$pid",
"$!");
$pidcount++;
}
}
$pidcount || return &text('stop_epidfile',
"$config{'pid_file'}");
}
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 "$out
";
}
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;