[Dbconfig-common-devel] dbconfig-common/internal common,NONE,1.1 mysql,NONE,1.1 pgsql,NONE,1.1
seanius@haydn.debian.org
seanius@haydn.debian.org
Update of /cvsroot/dbconfig-common/dbconfig-common/internal
In directory haydn:/org/alioth.debian.org/chroot/home/users/seanius/tmp/cvs-serv26038/internal
Added Files:
common mysql pgsql
Log Message:
big commit, this brings in all the latest code i've been working on.
support for postgresql is slightly more tested, and seems to work
on remote and local hosts. more decisions still need to be made regarding
authentication/connections methods, but things have gotten stable enough
to make a new release before trudging onwards :)
sean
--- NEW FILE: common ---
###
### common 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"
###
##
## dbc_logpart: log interal messages (without newline)
##
dbc_logpart(){
if [ "$dbc_debug" ]; then
echo -n "$@ " > /dev/stderr
fi
if [ "$dbc_log" ]; then
dbc_log="$dbc_log $@"
else
dbc_log="$@ "
fi
}
##
## dbc_logline: log interal messages
##
dbc_logline(){
if [ "$dbc_debug" ]; then
echo "$@." > /dev/stderr
fi
if [ "$dbc_log" ]; then
dbc_log="$dbc_log $@."
else
dbc_log="$@."
fi
}
##
## internal sanity check for certain important variables
##
_dbc_sanity_check(){
while [ "$1" ]; do
case "$1" in
"dbname")
if [ -z "$dbc_dbname" ]; then
dbc_error="No database name specified. Have
to know the name to create it."
dbc_logline "sanity check failed for dbc_dbname"
return 1
fi
;;
"dbadmin")
if [ -z "$dbc_dbadmin" ]; then
dbc_error="No database administrator specified."
dbc_logline "sanity check failed for dbc_dbadmin"
return 1
fi
;;
"dbuser")
if [ -z "$dbc_dbuser" ]; then
dbc_error="No database user specified."
dbc_logline "sanity check failed for dbc_dbuser"
return 1
fi
;;
"mysql")
if ! which mysql >/dev/null; then
dbc_error="No mysql client to execute. (have
you installed mysql-client?"
dbc_logline "sanity check failed for mysql"
return 1
fi
;;
"psql")
if ! which psql >/dev/null; then
dbc_error="No psql client to execute. (have
you installed postgresql-client?"
dbc_logline "sanity check failed for psql"
return 1
fi
;;
"createdb")
if ! which createdb >/dev/null; then
dbc_error="No pgsql createdb to execute. (have
you installed postgresql-client?"
dbc_logline "sanity check failed for createdb"
return 1
fi
;;
"dropdb")
if ! which dropdb >/dev/null; then
dbc_error="No pgsql dropdb to execute. (have
you installed postgresql-client?"
dbc_logline "sanity check failed for dropdb"
return 1
fi
;;
"createuser")
if ! which createuser >/dev/null; then
dbc_error="No pgsql createuser to execute. (have
you installed postgresql-client?"
dbc_logline "sanity check failed for createuser"
return 1
fi
;;
"dropuser")
if ! which dropuser >/dev/null; then
dbc_error="No pgsql dropuser to execute. (have
you installed postgresql-client?"
dbc_logline "sanity check failed for dropuser"
return 1
fi
;;
"pg_dump")
if ! which pg_dump >/dev/null; then
dbc_error="No pgsql pg_dump to execute. (have
you installed postgresql-client?"
dbc_logline "sanity check failed for pg_dump"
return 1
fi
;;
"mysqldump")
if ! which mysqldump >/dev/null; then
dbc_error="No mysqldump to execute. (have
you installed mysql-client?"
dbc_logline "sanity check failed for mysqldump"
return 1
fi
;;
*)
dbc_logline "don't know how to sanity check for $1"
return 1
;;
esac
shift
done
}
_dbc_psql(){
local extra
if [ "$dbserver" ]; then extra="$extra -h $dbserver"; fi
if [ "$dbport" ]; then extra="$extra -p $dbport"; fi
su - postgres -c "psql -q $extra $@"
}
_dbc_createdb(){
local extra
if [ "$dbserver" ]; then extra="$extra -h $dbserver"; fi
if [ "$dbport" ]; then extra="$extra -p $dbport"; fi
su - postgres -c "createdb -q $extra $@"
}
_dbc_dropdb(){
local extra
if [ "$dbserver" ]; then extra="$extra -h $dbserver"; fi
if [ "$dbport" ]; then extra="$extra -p $dbport"; fi
su - postgres -c "dropdb -q $extra $@"
}
_dbc_createuser(){
local extra
if [ "$dbserver" ]; then extra="$extra -h $dbserver"; fi
if [ "$dbport" ]; then extra="$extra -p $dbport"; fi
su - postgres -c "createuser -A -D -q $extra $@"
}
_dbc_dropuser(){
local extra
if [ "$dbserver" ]; then extra="$extra -h $dbserver"; fi
if [ "$dbport" ]; then extra="$extra -p $dbport"; fi
su - postgres -c "dropuser -q $extra $@"
}
##
## check that we can actually connect to the specified postgres server
##
_dbc_pgsql_check_connect(){
local constat
constat="bad"
_dbc_psql template1 < /dev/null && constat=good
if [ "$constat" = "bad" ]; then
dbc_error="Error when trying to connect to the postgresql
database. This error can occur if you have no database
to connect to, or if the password was incorrect. use:
dpkg-reconfigure -plow packagename to reconfigure."
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
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"
}
##
## execute a specific pgsql command
##
## note this is done without passing any sensitive info on the cmdline,
## including the sql command itself
##
_dbc_exec_command(){
local statement l_sqlfile
statement=$@
l_sqlfile=`mktemp -t dbconfig-common_sqlfile.XXXXXX`
cat << EOF > $l_sqlfile
$statement
EOF
_dbc_psql $dbc_dbname < $l_sqlfile
rm -f $l_sqlfile
}
##
## check for the existance of a specified database
##
_dbc_pgsql_check_database(){
local dbc_dbname
dbc_dbname=$1
_dbc_psql $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(){
_dbc_psql "template1 -A -c \"select usename from pg_shadow where usename='$dbc_dbuser'\"" | grep -q "^$dbc_dbuser\$"
return $?
}
###
### 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_status = {error, nothing, create}
# $dbc_error = error message (if $dbc_status = error)
dbc_pgsql_createdb(){
dbc_status=error
dbc_error=''
_dbc_sanity_check dbname dbadmin psql createdb || return 1
dbc_dbname='' _dbc_pgsql_check_connect || return 1
dbc_logpart "creating database $dbc_dbname:"
if _dbc_pgsql_check_database "$dbc_dbname"; then
dbc_logline "already exists"
dbc_status=nothing
else
if PGHOST="$dbc_dbserver" PGPORT="$dbc_dbport" PGUSER="$dbc_dbadmin" _dbc_createdb $dbc_dbname ; then
dbc_logline "success"
dbc_logpart "verifying database $dbc_dbname exists:"
if ! _dbc_pgsql_check_database "$dbc_dbname"; then
dbc_error="unable to create database $dbc_dbname."
dbc_logline "failed"
else
dbc_status=create
dbc_logline "success"
fi
else
dbc_error="unable to create database, pgsql exit status $?"
dbc_logline "failed"
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_status = {error, nothing, drop}
# $dbc_error = error message (if dbc_status = error).
dbc_pgsql_dropdb(){
dbc_status=error
dbc_error=""
_dbc_sanity_check dbname dbadmin dropdb || return 1
dbc_dbname='' _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"
dbc_error="could not drop database $dbc_dbname"
else
dbc_logline "success"
dbc_status=drop
fi
else
dbc_error="unable to drop database, dropdb exit code $?"
dbc_logline "does not exist"
fi
else
dbc_status=nothing
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_status = {error, create, nothing}
# $dbc_error = error message (if $dbc_status = error)
dbc_pgsql_createuser(){
local l_dballow
dbc_status=error
dbc_error=""
_dbc_sanity_check dbuser dbname dbadmin createuser || return 1
dbc_dbname='' _dbc_pgsql_check_connect || return 1
if [ ! "$dbc_dballow" ]; then
if [ ! "$dbc_dbserver" ]; then
l_dballow=localhost
else
l_dballow=`hostname`
fi
else
l_dballow="$dbc_dballow"
fi
dbc_logpart "granting access to database $dbc_dbname for $dbc_dbuser@$l_dballow:"
if _dbc_pgsql_check_user; then
dbc_status=nothing
dbc_logline "already exists"
elif dbc_dbname="" _dbc_createuser $dbc_dbuser; then
dbc_logline "success"
dbc_logpart "verifying creation of user:"
if ! _dbc_pgsql_check_user ; then
dbc_error="unable to grant privileges to user $dbc_dbuser."
dbc_logline "failed"
else
dbc_status=create
dbc_logline "success"
fi
else
dbc_error="unable to grant privileges to $dbc_dbuser, createuser error $?"
dbc_logline "failed"
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_status = {error, nothing, drop }
# $dbc_error = error message (if dbc_status = error).
dbc_pgsql_dropuser(){
local l_dballow
dbc_status=error
dbc_error=""
_dbc_sanity_check dbuser dbname dbadmin dropuser || return 1
dbc_dbname='' _dbc_pgsql_check_connect || return 1
if [ ! "$dbc_dballow" ]; then
if [ ! "$dbc_dbserver" ]; then
l_dballow=localhost
else
l_dballow=`hostname`
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_status="nothing"
dbc_logline "access does not exist"
else
if dbc_dbname='' _dbc_dropuser $dbc_dbuser 2>/dev/null; then
dbc_status=drop
dbc_logline "success"
else
dbc_error="unable to revoke privileges, pgsql error $?."
dbc_logline "failed"
fi
fi
}
--- NEW FILE: mysql ---
###
### mysql 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
##
## pass configuration options securely to the mysql client
##
_dbc_generate_mycnf(){
local mycnf l_date
mycnf=`mktemp -t dbconfig-common_my.cnf.XXXXXX`
[ ! "$mycnf" ] && return 1
l_date=`date`
cat << EOF > $mycnf
# temporary my.cnf generated for usage by dbconfig-common
# generated on $l_date
# if you're reading this, it probably means something went wrong and
# for some strange reason dbconfig-common was not able to clean up after itself.
# you can safely delete this file
[client]
user = '$dbc_dbadmin'
password = '$dbc_dbadmpass'
host = '$dbc_dbserver'
port = '$dbc_dbport'
socket = '/var/run/mysqld/mysqld.sock'
EOF
echo $mycnf
}
##
## check that we can actually connect to the specified mysql server
##
_dbc_mysql_check_connect(){
local constat mycnf
constat="bad"
mycnf=`_dbc_generate_mycnf $1`
mysql --defaults-extra-file="$mycnf" </dev/null && constat=good
rm -f $mycnf
if [ "$constat" = "bad" ]; then
dbc_error="Error when trying to connect to the mysql
database. This error can occur if you have no database
to connect to, or if the password was incorrect. use:
dpkg-reconfigure -plow packagename to reconfigure."
dbc_logline "unable to connect to mysql server"
return 1
fi
}
##
## execute a file with mysql commands
##
## note this is done without passing any sensitive info on the cmdline
##
dbc_mysql_exec_file(){
local l_sqlfile
l_sqlfile=$1
if [ ! "$l_sqlfile" ]; then
dbc_error="no file supplied to execute"
dbc_log="no file supplied to execute"
return 1
fi
mycnf=`_dbc_generate_mycnf`
mysql --defaults-extra-file="$mycnf" $dbc_dbname < $l_sqlfile
rm -f $mycnf
}
##
## execute a specific mysql command
##
## note this is done without passing any sensitive info on the cmdline,
## including the mysql command itself
##
dbc_mysql_exec_command(){
local statement l_sqlfile
statement=$@
l_sqlfile=`mktemp -t dbconfig-common_sqlfile.XXXXXX`
cat << EOF > $l_sqlfile
$statement
EOF
mycnf=`_dbc_generate_mycnf`
mysql --defaults-extra-file="$mycnf" $dbc_dbname < $l_sqlfile
rm -f $mycnf $l_sqlfile
}
##
## check for the existance of a specified database
##
_dbc_mysql_check_database(){
local dbc_dbname
dbc_dbname=$1
dbc_mysql_exec_command "SHOW DATABASES" 2>/dev/null | grep -q "^$dbc_dbname\$"
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_mysql_check_user(){
local l_dballow l_dbname
if [ ! "$dbc_dballow" ]; then
if [ ! "$dbc_dbserver" -o "$dbc_dbserver" = "localhost" ]; then
l_dballow=localhost
else
l_dballow=`hostname`
fi
else
l_dballow="$dbc_dballow"
fi
l_dbname=$dbc_dbname
dbc_dbname='' dbc_mysql_exec_command "SHOW GRANTS FOR '$dbc_dbuser'@'$l_dballow'" | grep -qi "GRANT ALL PRIVILEGES ON \`$l_dbname\`"
return $?
}
###
### 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: mysql-createdb.sh
# Description: Creates a database.
# Needs: $dbc_dbname
# $dbc_dbadmin
# Sets: $dbc_status = {error, nothing, create}
# $dbc_error = error message (if $dbc_status = error)
dbc_mysql_createdb(){
local ret l_dbname
dbc_status=error
dbc_error=''
_dbc_sanity_check dbname dbadmin mysql || return 1
_dbc_mysql_check_connect || return 1
dbc_logpart "creating database $dbc_dbname:"
if _dbc_mysql_check_database "$dbc_dbname"; then
dbc_logline "already exists"
dbc_status=nothing
else
l_dbname=$dbc_dbname
dbc_dbname=""
dbc_mysql_exec_command "CREATE DATABASE $l_dbname"
ret=$?
dbc_dbname=$l_dbname
if [ "$ret" = "0" ]; then
dbc_logline "success"
dbc_logpart "verifying database $dbc_dbname exists:"
if ! _dbc_mysql_check_database "$dbc_dbname"; then
dbc_logline "failed"
dbc_error="unable to create database $dbc_dbname."
else
dbc_logline "success"
dbc_status=create
fi
else
dbc_logline "failed"
dbc_error="unable to create database, mysql exit status $?"
fi
fi
}
# File: mysql-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_status = {error, nothing, drop}
# $dbc_error = error message (if dbc_status = error).
dbc_mysql_dropdb(){
dbc_status=error
dbc_error=""
_dbc_sanity_check dbname dbadmin mysql || return 1
_dbc_mysql_check_connect || return 1
dbc_logpart "dropping database $dbc_dbname:"
if _dbc_mysql_check_database "$dbc_dbname"; then
if dbc_mysql_exec_command "DROP DATABASE $dbc_dbname"; then
dbc_logline "success"
dbc_logpart "verifying database $dbc_dbname was dropped:"
if _dbc_mysql_check_database "$dbc_dbname"; then
dbc_logline "failed"
dbc_error="could not drop database $dbc_dbname"
else
dbc_logline "success"
dbc_status=drop
fi
else
dbc_error="unable to drop database, mysql exit code $?"
dbc_logline "does not exist"
fi
else
dbc_status=nothing
dbc_logline "database does not exist"
fi
}
# File: mysql-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_status = {error, create, nothing}
# $dbc_error = error message (if $dbc_status = error)
dbc_mysql_createuser(){
local l_dballow l_sqlfile l_dbname l_ret
dbc_status=error
dbc_error=""
_dbc_sanity_check dbuser dbname dbadmin mysql || return 1
_dbc_mysql_check_connect || return 1
if [ ! "$dbc_dballow" ]; then
if [ ! "$dbc_dbserver" -o "$dbc_dbserver" = "localhost" ]; then
l_dballow=localhost
else
l_dballow=`hostname`
fi
else
l_dballow="$dbc_dballow"
fi
dbc_logpart "granting access to database $dbc_dbname for $dbc_dbuser@$l_dballow:"
l_sqlfile=`mktemp -t dbconfig-common.sql.XXXXXX`
[ -f "$l_sqlfile" ] || return 1
cat << EOF > $l_sqlfile
GRANT ALL PRIVILEGES ON \`$dbc_dbname\`.* TO \`$dbc_dbuser\`@'$l_dballow' IDENTIFIED BY '$dbc_dbpass';
FLUSH PRIVILEGES;
EOF
if _dbc_mysql_check_user; then
dbc_status=nothing
dbc_logline "already exists"
# XXX
else
l_dbname=$dbc_dbname
dbc_dbname=""
dbc_mysql_exec_file "$l_sqlfile"
l_ret=$?
dbc_dbname=$l_dbname
if [ "$l_ret" = "0" ]; then
dbc_logline "success"
dbc_logpart "verifying access for $dbc_dbuser@$l_dballow:"
if ! _dbc_mysql_check_user ; then
dbc_error="unable to grant privileges to user $dbc_dbuser."
dbc_logline "failed"
else
dbc_status=create
dbc_logline "success"
fi
else
dbc_error="unable to grant privileges to $dbc_dbuser, mysql error $?"
dbc_logline "failed"
fi
fi
rm -f $l_sqlfile
}
# File: mysql-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_status = {error, nothing, drop }
# $dbc_error = error message (if dbc_status = error).
dbc_mysql_dropuser(){
local l_sqlfile l_dballow
dbc_status=error
dbc_error=""
_dbc_sanity_check dbuser dbname dbadmin mysql || return 1
_dbc_mysql_check_connect || return 1
if [ ! "$dbc_dballow" ]; then
if [ ! "$dbc_dbserver" -o "$dbc_dbserver" = "localhost" ]; then
l_dballow=localhost
else
l_dballow=`hostname`
fi
else
l_dballow="$dbc_dballow"
fi
dbc_logpart "revoking access to database $dbc_dbname from $dbc_dbuser@$l_dballow:"
if ! _dbc_mysql_check_user; then
dbc_status="nothing"
dbc_logline "access does not exist"
else
l_sqlfile=`mktemp -t dbconfig-common.sql.XXXXXX`
[ -f "$l_sqlfile" ] || return 1
cat << EOF > $l_sqlfile
REVOKE ALL PRIVILEGES ON \`$dbc_dbname\`.* FROM '$dbc_dbuser'@'$l_dballow';
FLUSH PRIVILEGES;
EOF
if dbc_dbname='' dbc_mysql_exec_file "$l_sqlfile" 2>/dev/null; then
dbc_status=drop
dbc_logline "success"
else
dbc_error="unable to revoke privileges, mysql error $?."
dbc_logline "failed"
fi
rm -f $l_sqlfile
fi
}
##
## perform mysqldump
##
dbc_mysql_dump(){
local mycnf
local db
_dbc_sanity_check dbname dbadmin mysql || return 1
_dbc_mysql_check_connect || return 1
mycnf=`_dbc_generate_mycnf`
mysqldump --defaults-extra-file="$mycnf" $dbc_dbname
}
--- NEW FILE: pgsql ---
###
### 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 extra
if [ "$dbc_dbserver" ]; then extra="$extra -h '$dbc_dbserver'"; fi
if [ "$dbc_dbport" ]; then extra="$extra -p '$dbc_dbport'"; fi
su - postgres -c "psql -q $extra $*"
}
_dbc_createdb(){
local extra
if [ "$dbc_dbserver" ]; then extra="$extra -h '$dbc_dbserver'"; fi
if [ "$dbc_dbport" ]; then extra="$extra -p '$dbc_dbport'"; fi
su - postgres -c "createdb -q -O $dbc_dbuser $extra $*"
}
_dbc_dropdb(){
local extra
if [ "$dbc_dbserver" ]; then extra="$extra -h '$dbc_dbserver'"; fi
if [ "$dbc_dbport" ]; then extra="$extra -p '$dbc_dbport'"; fi
su - postgres -c "dropdb -q $extra $*"
}
_dbc_createuser(){
local extra
if [ "$dbc_dbserver" ]; then extra="$extra -h '$dbc_dbserver'"; fi
if [ "$dbc_dbport" ]; then extra="$extra -p '$dbc_dbport'"; fi
su - postgres -c "createuser -A -D -q $extra $*"
}
_dbc_dropuser(){
local extra
if [ "$dbc_dbserver" ]; then extra="$extra -h '$dbc_dbserver'"; fi
if [ "$dbc_dbport" ]; then extra="$extra -p '$dbc_dbport'"; fi
su - postgres -c "dropuser -q $extra $*"
}
_dbc_pg_dump(){
local extra
if [ "$dbc_dbserver" ]; then extra="$extra -h '$dbc_dbserver'"; fi
if [ "$dbc_dbport" ]; then extra="$extra -p '$dbc_dbport'"; fi
su - postgres -c "pg_dump $extra $*"
}
##
## check that we can actually connect to the specified postgres server
##
_dbc_pgsql_check_connect(){
local constat
constat="bad"
_dbc_psql template1 < /dev/null && constat=good
if [ "$constat" = "bad" ]; then
dbc_error="Error when trying to connect to the postgresql
database. This error can occur if you have no database
to connect to, or if the password was incorrect. use:
dpkg-reconfigure -plow packagename to reconfigure."
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
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"
}
##
## 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
statement=$@
l_sqlfile=`mktemp -t dbconfig-common_sqlfile.XXXXXX`
cat << EOF > $l_sqlfile
$statement
EOF
_dbc_psql $dbc_dbname < $l_sqlfile
rm -f $l_sqlfile
}
##
## check for the existance of a specified database
##
_dbc_pgsql_check_database(){
local dbc_dbname
dbc_dbname=$1
_dbc_psql $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 extra dbc_dbname
dbc_dbname=template1 extra="-A" _dbc_pgsql_exec_command "select usename from pg_shadow where usename='$dbc_dbuser'" | grep -q "^$dbc_dbuser\$"
return $?
}
###
### 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_status = {error, nothing, create}
# $dbc_error = error message (if $dbc_status = error)
dbc_pgsql_createdb(){
dbc_status=error
dbc_error=''
_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"
dbc_status=nothing
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_error="unable to create database $dbc_dbname."
dbc_logline "failed"
else
dbc_status=create
dbc_logline "success"
fi
else
dbc_error="unable to create database, pgsql exit status $?"
dbc_logline "failed"
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_status = {error, nothing, drop}
# $dbc_error = error message (if dbc_status = error).
dbc_pgsql_dropdb(){
dbc_status=error
dbc_error=""
_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"
dbc_error="could not drop database $dbc_dbname"
else
dbc_logline "success"
dbc_status=drop
fi
else
dbc_error="unable to drop database, dropdb exit code $?"
dbc_logline "does not exist"
fi
else
dbc_status=nothing
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_status = {error, create, nothing}
# $dbc_error = error message (if $dbc_status = error)
dbc_pgsql_createuser(){
local l_dballow
dbc_status=error
dbc_error=""
_dbc_sanity_check dbuser dbname dbadmin createuser || return 1
_dbc_pgsql_check_connect || return 1
if [ ! "$dbc_dballow" ]; then
if [ ! "$dbc_dbserver" -o "$dbc_dbserver" = "localhost" ]; then
l_dballow=localhost
else
l_dballow=`hostname`
fi
else
l_dballow="$dbc_dballow"
fi
dbc_logpart "creating postgres user $dbc_dbuser: "
if _dbc_pgsql_check_user; then
dbc_status=nothing
dbc_logline "already exists"
elif _dbc_createuser $dbc_dbuser; then
dbc_logline "success"
dbc_logpart "verifying creation of user:"
if ! _dbc_pgsql_check_user ; then
dbc_error="unable to grant privileges to user $dbc_dbuser."
dbc_logline "failed"
else
dbc_status=create
dbc_logline "success"
fi
else
dbc_error="unable to grant privileges to $dbc_dbuser, createuser error $?"
dbc_logline "failed"
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_status = {error, nothing, drop }
# $dbc_error = error message (if dbc_status = error).
dbc_pgsql_dropuser(){
local l_dballow
dbc_status=error
dbc_error=""
_dbc_sanity_check dbuser dbname dbadmin dropuser || return 1
_dbc_pgsql_check_connect || return 1
if [ ! "$dbc_dballow" ]; then
if [ ! "$dbc_dbserver" -o "$dbc_dbserver" = "localhost" ]; then
l_dballow=localhost
else
l_dballow=`hostname`
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_status="nothing"
dbc_logline "access does not exist"
else
if _dbc_dropuser $dbc_dbuser 2>/dev/null; then
dbc_status=drop
dbc_logline "success"
else
dbc_error="unable to revoke privileges, pgsql error $?."
dbc_logline "failed"
fi
fi
}
##
## perform pg_dump
##
dbc_pgsql_dump(){
_dbc_sanity_check dbuser dbname dbadmin pg_dump || return 1
_dbc_pgsql_check_connect || return 1
_dbc_pg_dump $dbc_dbname
}