###
### postgresql bindings for dbconfig-common
###
### all variables and functions fall under the namespace "dbc_foo" and
### "_dbc_foo", depending on whether or not they are "external"
###

# get some common functions
. /usr/share/dbconfig-common/internal/common

_dbc_psql_local_username(){
# determine proper local system user as whom to run commands.
# - if _dbc_asuser set and ident/localhost,
# - if $dbc_dbuser exists as a system account, use it
# - else ident/localhost: $dbc_dbadmin
# - all others: no change (root)
if [ "${_dbc_asuser:-}" ]; then
if [ "${dbc_authmethod_user:-}" = "ident" ] && [ ! "${dbc_dbserver:-}" ]; then
if [ "${dbc_dbuser:-}" ] && id $dbc_dbuser >/dev/null 2>&1; then
echo $dbc_dbuser
return 0
else
dbc_logline "warning: ident method specified but local account doesn't exist"
fi
fi
elif [ "${dbc_authmethod_admin:-}" = "ident" ] && [ ! "${dbc_dbserver:-}" ]; then
echo $dbc_dbadmin
return 0
fi
echo root
}

_dbc_psql_remote_username(){
# determine proper remote user for connection requests
# - if _dbc_asuser set: $dbc_dbuser
# - else: $dbc_dbadmin
if [ "${_dbc_asuser:-}" ]; then
echo $dbc_dbuser
else
echo $dbc_dbadmin
fi
}

_dbc_psql_cmd_setup(){
local localuser remoteuser remotepass
localuser=`_dbc_psql_local_username`
remoteuser=`_dbc_psql_remote_username`
_dbc_pgsql_tmpdir=`mktemp -dt dbconfig-common.psql_home.XXXXXX`
if [ $? -ne 0 ]; then return 1; fi
if [ "$remoteuser" = "$dbc_dbadmin" ]; then
remotepass="$dbc_dbadmpass"
else
remotepass="$dbc_dbpass"
fi
touch "${_dbc_pgsql_tmpdir}/.pgpass"
chmod 600 "${_dbc_pgsql_tmpdir}/.pgpass"
cat << EOF > "${_dbc_pgsql_tmpdir}/.pgpass"
*:*:*:$remoteuser:$remotepass
EOF
if [ "$localuser" ] && [ "$localuser" != "root" ]; then
chown -R "$localuser" "$_dbc_pgsql_tmpdir"
fi
}

_dbc_psql_cmd_args(){
local extra remoteuser

# if a dbserver is specified, use it
if [ "$dbc_dbserver" ]; then
extra="-h '$dbc_dbserver'";
# but if they specify a password, we should add the host explictly too
elif [ "${_dbc_asuser:-}" ] && [ "$dbc_dbpass" ]; then
extra="-h localhost";
elif [ "$dbc_dbadmpass" ]; then
extra="-h localhost";
fi
# add port settings if nonempty
if [ "$dbc_dbport" ]; then extra="$extra -p '$dbc_dbport'"; fi
# determine the database user name
extra="$extra -U '`_dbc_psql_remote_username`'"

echo $extra
}

_dbc_psql_cmd_cleanup(){
if [ "$_dbc_pgsql_tmpdir" ] && [ -d "$_dbc_pgsql_tmpdir" ]; then
rm -f "${_dbc_pgsql_tmpdir}/.pgpass"
rmdir "$_dbc_pgsql_tmpdir"
fi
}

_dbc_psql(){
local extra retval PGSSLMODE localuser
localuser=`_dbc_psql_local_username`
PGSSLMODE="prefer"
retval=0
_dbc_psql_cmd_setup
if [ "${dbc_ssl:-}" ]; then PGSSLMODE="require"; fi
extra=`_dbc_psql_cmd_args`
_dbc_debug "su -s /bin/sh $localuser -c \"env HOME='$_dbc_pgsql_tmpdir' PGPASSFILE='$_dbc_pgsql_tmpdir/.pgpass' PGSSLMODE='$PGSSLMODE' psql --set \"ON_ERROR_STOP=1\" -q $extra $*\" 2>&1"
dbc_error=`su -s /bin/sh $localuser -c "env HOME='$_dbc_pgsql_tmpdir' PGPASSFILE='$_dbc_pgsql_tmpdir/.pgpass' PGSSLMODE='$PGSSLMODE' psql --set "ON_ERROR_STOP=1" -q $extra $*" 2>&1` || retval=$?
_dbc_psql_cmd_cleanup
return $retval
}

_dbc_createdb(){
local extra extrasql retval PGSSLMODE localuser _dbc_asuser template
local _dbc_nodb
localuser=`_dbc_psql_local_username`
PGSSLMODE="prefer"
retval=0
_dbc_psql_cmd_setup
if [ "${dbc_ssl:-}" ]; then PGSSLMODE="require"; fi
extra=`_dbc_psql_cmd_args`
if [ "${dbc_pgsql_createdb_encoding:-}" ]; then
extrasql=" WITH TEMPLATE = \"template0\"";
extrasql="$extrasql ENCODING = '$dbc_pgsql_createdb_encoding'";
fi
_dbc_nodb="yes"
_dbc_pgsql_exec_command "CREATE DATABASE \"$dbc_dbname\"${extrasql:-}"
retval=$?
_dbc_nodb=""
_dbc_psql_cmd_cleanup
return $retval
}

_dbc_dropdb(){
local extra retval PGSSLMODE localuser _dbc_asuser
localuser=`_dbc_psql_local_username`
PGSSLMODE="prefer"
retval=0
_dbc_psql_cmd_setup
if [ "$dbc_ssl" ]; then PGSSLMODE="require"; fi
extra=`_dbc_psql_cmd_args`
_dbc_debug "su -s /bin/sh $localuser -c \"env HOME='$_dbc_pgsql_tmpdir' PGPASSFILE='$_dbc_pgsql_tmpdir/.pgpass' PGSSLMODE='$PGSSLMODE' dropdb $extra $*\" 2>&1"
dbc_error=`su -s /bin/sh $localuser -c "env HOME='$_dbc_pgsql_tmpdir' PGPASSFILE='$_dbc_pgsql_tmpdir/.pgpass' PGSSLMODE='$PGSSLMODE' dropdb $extra $*" 2>&1` || retval=$?
_dbc_psql_cmd_cleanup
return $retval
}

_dbc_createuser_majorversion(){
createuser --version | sed -e 's,.* ,,g' | sed -e 's,\..*,,g'
}

_dbc_createuser(){
local extra dbc_dbname retval PGSSLMODE localuser _dbc_asuser
localuser=`_dbc_psql_local_username`
PGSSLMODE="prefer"
retval=0
_dbc_psql_cmd_setup
if [ "$dbc_ssl" ]; then PGSSLMODE="require"; fi
extra=`_dbc_psql_cmd_args`
dbc_dbname="template1"
_dbc_pgsql_exec_command "CREATE USER \"$dbc_dbuser\" WITH PASSWORD '`dbc_pgsql_escape_str $dbc_dbpass`'" || retval=$?
_dbc_psql_cmd_cleanup
return $retval
}

_dbc_dropuser(){
local extra retval PGSSLMODE localuser _dbc_asuser
localuser=`_dbc_psql_local_username`
PGSSLMODE="prefer"
retval=0
_dbc_psql_cmd_setup
if [ "$dbc_ssl" ]; then PGSSLMODE="require"; fi
extra=`_dbc_psql_cmd_args`
_dbc_debug "su -s /bin/sh $localuser -c \"env HOME='$_dbc_pgsql_tmpdir' PGPASSFILE='$_dbc_pgsql_tmpdir/.pgpass' PGSSLMODE='$PGSSLMODE' dropuser $extra $*\" 2>&1"
dbc_error=`su -s /bin/sh $localuser -c "env HOME='$_dbc_pgsql_tmpdir' PGPASSFILE='$_dbc_pgsql_tmpdir/.pgpass' PGSSLMODE='$PGSSLMODE' dropuser $extra $*" 2>&1` || retval=$?
_dbc_psql_cmd_cleanup
return $retval
}

_dbc_pg_dump(){
local extra retval PGSSLMODE localuser _dbc_asuser dumpfile old_umask
dumpfile=$1
localuser=`_dbc_psql_local_username`
touch $dumpfile
chown $localuser $dumpfile
PGSSLMODE="prefer"
retval=0
_dbc_psql_cmd_setup
if [ "$dbc_ssl" ]; then PGSSLMODE="require"; fi
old_umask=`umask`
umask 0066
extra=`_dbc_psql_cmd_args`
extra="-f \"$dumpfile\" $extra"
_dbc_debug "su -s /bin/sh $localuser -c \"env HOME='$_dbc_pgsql_tmpdir' PGPASSFILE='$_dbc_pgsql_tmpdir/.pgpass' PGSSLMODE='$PGSSLMODE' pg_dump $extra $dbc_dbname\" 2>&1"
dbc_error=`su -s /bin/sh $localuser -c "env HOME='$_dbc_pgsql_tmpdir' PGPASSFILE='$_dbc_pgsql_tmpdir/.pgpass' PGSSLMODE='$PGSSLMODE' pg_dump $extra $dbc_dbname" 2>&1` || retval=$?
umask $old_umask
_dbc_psql_cmd_cleanup
return $retval
}

##
## check that we can actually connect to the specified postgres server
##
_dbc_pgsql_check_connect(){
local constat extra _dbc_asuser
constat="bad"
if ! _dbc_psql $extra template1 < /dev/null; then
dbc_logline "unable to connect to postgresql server"
return 1
fi
}

##
## execute a file with pgsql commands
##
## note this is done without passing any sensitive info on the cmdline
##
dbc_pgsql_exec_file(){
local l_sqlfile retval
l_sqlfile=$1

if [ ! "$l_sqlfile" ]; then
dbc_error="no file supplied to execute"
dbc_log="no file supplied to execute"
return 1
fi

_dbc_psql $dbc_dbname < "$l_sqlfile"
retval=$?
return $retval
}

##
## execute a specific pgsql command
##
## note this is done without passing any sensitive info on the cmdline,
## including the sql command itself
##
_dbc_pgsql_exec_command(){
local statement l_sqlfile l_dbname retval
retval=0
statement=$@
l_sqlfile=`dbc_mktemp dbconfig-common_sqlfile.XXXXXX`
if [ -z "${_dbc_nodb:-}" ]; then
l_dbname="$dbc_dbname"
fi

cat << EOF > "$l_sqlfile"
$statement
EOF
_dbc_psql $extra ${l_dbname:-} < "$l_sqlfile" || retval=$?
rm -f "$l_sqlfile"
return $retval
}

##
## check for the existance of a specified database
##
_dbc_pgsql_check_database(){
local dbc_dbname _dbc_asuser extra
dbc_dbname=$1
_dbc_psql $extra $dbc_dbname </dev/null 2>/dev/null
return $?
}

##
## check for access for a specific user
##
## this works by checking the grants for the user, so we can verify that
## not only does the user exist, but that it should be able to connect
##
dbc_pgsql_check_user(){
local dbc_dbname l_retval _dbc_asuser extra
dbc_dbname="template1"
_dbc_pgsql_exec_command "ALTER USER \"$dbc_dbuser\""
l_retval=$?
return $l_retval
}

###
### externally supplied functions
###
### included inline are some slightly modified / corrected comments from
### the respective original functions provided by wwwconfig-common, and
### comments of similar style for now functions
###
### all functions return non-zero on error
###

# File: pgsql-createdb.sh
# Description: Creates a database.
# Needs: $dbc_dbname
# $dbc_dbadmin
# Sets: $dbc_error = error message

dbc_pgsql_createdb(){
_dbc_sanity_check dbname dbadmin psql dbuser createdb || return 1
_dbc_pgsql_check_connect || return 1

dbc_logpart "creating database $dbc_dbname:"

if _dbc_pgsql_check_database "$dbc_dbname"; then
dbc_logline "already exists"
else
if _dbc_createdb $dbc_dbname ; then
dbc_logline "success"
dbc_logpart "verifying database $dbc_dbname exists:"
if ! _dbc_pgsql_check_database "$dbc_dbname"; then
dbc_logline "failed"
return 1
else
dbc_logline "success"
fi
else
dbc_logline "failed"
return 1
fi
fi

}

# File: pgsql-dropdb.sh
# Needs: $dbc_dbname - the database that user should have access to.
# $dbc_dbserver - the server to connect to.
# $dbc_dbadmin - the administrator name.
# $dbc_dbadmpass - the administrator password.
# Description: drops a database.
# Sets: $dbc_error = error message

dbc_pgsql_dropdb(){
_dbc_sanity_check dbname dbadmin dropdb || return 1
_dbc_pgsql_check_connect || return 1

dbc_logpart "dropping database $dbc_dbname:"

if _dbc_pgsql_check_database "$dbc_dbname"; then
if _dbc_dropdb "$dbc_dbname"; then
dbc_logline "success"
dbc_logpart "verifying database $dbc_dbname was dropped:"
if _dbc_pgsql_check_database "$dbc_dbname"; then
dbc_logline "failed"
else
dbc_logline "success"
fi
else
dbc_logline "does not exist"
fi
else
dbc_logline "database does not exist"
fi
}

# File: pgsql-createuser.sh
# Description: Creates or replaces a database user.
# Needs: $dbc_dbuser - the user name to create (or replace).
# $dbc_dballow - what hosts to allow. defaults to localhost/hostname
# $dbc_dbname - the database that user should have access to.
# $dbc_dbpass - the password to use.
# $dbc_dbserver - the server to connect to (defaults to localhost).
# $dbc_dbadmin - the administrator name.
# $dbc_dbadmpass - the administrator password.
# Sets: $dbc_error = error message

dbc_pgsql_createuser(){
local l_dballow dbc_dbname

dbc_dbname=template1

_dbc_sanity_check dbuser dbadmin createuser || return 1
_dbc_pgsql_check_connect || return 1

if [ ! "$dbc_dballow" ]; then
if [ ! "$dbc_dbserver" ] || [ "$dbc_dbserver" = "localhost" ]; then
l_dballow=localhost
else
l_dballow=`hostname -f`
fi
else
l_dballow="$dbc_dballow"
fi

dbc_logpart "creating postgres user $dbc_dbuser: "

if dbc_pgsql_check_user; then
dbc_logline "already exists"
if [ "$dbc_dbpass" ]; then
dbc_logpart "resetting password: "
if _dbc_pgsql_exec_command "ALTER USER \"$dbc_dbuser\" WITH PASSWORD '$dbc_dbpass'"; then
dbc_logline "success"
else
dbc_logline "failed"
return 1
fi
fi
elif _dbc_createuser; then
dbc_logline "success"
dbc_logpart "verifying creation of user:"
if ! dbc_pgsql_check_user ; then
dbc_logline "failed"
return 1
else
dbc_logline "success"
fi
else
dbc_logline "failed"
return 1
fi
}

# File: pgsql-dropuser.sh
# Needs: $dbc_dbuser - the user name to create (or replace).
# $dbc_dballow - what hosts to allow (defaults to %).
# $dbc_dbname - the database that user should have access to.
# $dbc_dbserver - the server to connect to.
# $dbc_dbadmin - the administrator name.
# $dbc_dbadmpass - the administrator password.
# Description: drops a database user.
# Sets: $dbc_error = error message

dbc_pgsql_dropuser(){
local l_dballow

_dbc_sanity_check dbuser dbname dbadmin dropuser || return 1
_dbc_pgsql_check_connect || return 1

if [ ! "$dbc_dballow" ]; then
if [ ! "$dbc_dbserver" ] || [ "$dbc_dbserver" = "localhost" ]; then
l_dballow=localhost
else
l_dballow=`hostname -f`
fi
else
l_dballow="$dbc_dballow"
fi

dbc_logpart "revoking access to database $dbc_dbname from $dbc_dbuser@$l_dballow:"
if ! dbc_pgsql_check_user; then
dbc_logline "access does not exist"
else
if _dbc_dropuser $dbc_dbuser 2>/dev/null; then
dbc_logline "success"
else
dbc_logline "failed"
fi
fi
}

##
## perform pg_dump
##
dbc_pgsql_dump(){
local dumpfile
dumpfile=$1
_dbc_sanity_check dbuser dbname dbadmin pg_dump || return 1
_dbc_pgsql_check_connect || return 1
_dbc_pg_dump $dumpfile
}

##
## basic installation check
##
dbc_pgsql_db_installed(){
test -n "`find /etc/init.d -name 'postgresql*'`"
}

##
## dbc_pgsql_escape_str: properly escape strings passed to pgsql queries
##
dbc_pgsql_escape_str(){
sed -e 's,\\,\\&,g' -e "s,',\\\\&,g" << EOF
$1
EOF
}