perdition-postgresql 2.0-1ubuntu1 / usr / sbin / perditiondb_postgresql_makedb

#!/bin/bash
######################################################################
# perditiondb_postgresql_makedb                          December 2000
# Horms                                             horms@verge.net.au
#
# Sample programme to create a perdition database in a PostgreSQL 
# RDMS
#
# Adapted from a similar script for MySQL by 
# Frederic Delchambre <dedel@freegates.be>
#
# perdition
# Mail retrieval proxy server
# Copyright (C) 1999-2005  Horms
# 
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; either version 2 of the
# License, or (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software Foundation,
# Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA.
#
######################################################################


DEFAULT_DBNAME=dbPerdition
DEFAULT_DBUSER=perdition
DEFAULT_DBTABLE=tblPerdition
DEFAULT_DBSERVER=localhost
DEFAULT_DBUID=$(id -u)

quit () {
  stty echo
}

trap quit HUP
trap quit TERM
trap quit STOP


echo
echo "Perdition information will be sotored in a separeate"
echo "database within the PostgreSQL RDBMS. The default name for"
echo "this database is $DEFAULT_DBNAME. If you want to use this"
echo "hit return, otherwise enter the name for the database."
echo -n "Enter database name [$DEFAULT_DBNAME]: " >&2
read dbname
if [ -z "$dbname" ]; then
  dbname="$DEFAULT_DBNAME"
fi

echo
echo "Perdition information will be stored in table in the"
echo "$dbname database. The default name for this table is"
echo "$DEFAULT_DBTABLE. If you want to use this hit return,"
echo "otherwise enter the name for the table."
echo -n "Enter table name [$DEFAULT_DBTABLE]: " >&2
read dbtable
if [ -z "$dbtable" ]; then
  dbtable="$DEFAULT_DBTABLE"
fi

echo
echo "A PostgreSQL user, other than root should be used to access"
echo "the $dbname database. The default name for this user"
echo "is $DEFAULT_DBUSER. If you want to use this name, hit return,"
echo "otherwise enter the name for the user."
echo -n "Enter database user [$DEFAULT_DBUSER]: " >&2
read dbuser
if [ -z "$dbuser" ]; then
  dbuser="$DEFAULT_DBUSER"
fi

while [ 1 ]; do
  echo -n "Enter a numeric userid for the $dbuser user [$DEFAULT_DBUID]: " >&2
  read dbuid
  if [ -z "$dbuid" ]; then
    dbuid="$DEFAULT_DBUID"
  fi
  if echo $dbuid | grep "[^0-9]" > /dev/null; then
    echo "Userid is contains non-numbers, please try again." >& 2
  else
    break
  fi
done

while [ 1 ]; do
  echo -n "Enter a password for the $dbuser user: " >&2
  stty -echo
  read dbpw
  stty echo
  echo
  if [ -z "$dbpw" ]; then
    echo "Password is empty, please try again." >& 2
    continue
  fi
  echo -n "Enter password again to verify: " >&2
  stty -echo
  read dbpw2
  stty echo
  echo
  if [ "$dbpw" != "$dbpw2" ]; then
    echo "Passwords do not match, please try again." >& 2
    continue
  fi
  break
done

if [ -z "$(type -path destroyuser)" ]; then
  oldcmds="n"
else
  oldcmds="y"
fi

if [ "$oldcmds" != "y" ]; then
  echo
  echo "PostgreSQL should already be running on a host. The"
  echo "default host is $DEFAULT_DBSERVER. To use this host"
  echo "hit return. Otherwise enter a host to connect to."
  echo -n "Enter host name [$DEFAULT_DBSERVER]: "
  read dbserver
  if [ -z "$dbserver" ]; then
    dbserver="$DEFAULT_DBSERVER"
  fi
fi


echo
echo   "Database name:            $dbname"
echo   "Database user id:         $dbuid"
echo   "Database table:           $dbtable"
echo   "Database user:            $dbuser"
if [ "$oldcmds" != "n" ]; then
  echo "Postgress Version:        < 7.0"
else
  echo "Database server:          $dbserver"
  echo "Postgress Version:        >= 7.0"
fi
echo -n "Proceed (May destroy existing data in database) [y/n]? " >&2
read answer
if [ "$answer" != "y" -a "$answer" != "Y" ]; then
  exit 0
fi


echo
echo "The system user postgres password should already be set."
echo "It is needed to add a PostgreSQL perdition user that will"
echo "own the perdition database. Please enter the postgres user's"
echo "password at the prompt"


if [ "$oldcmds" != "n" ]; then
  echo
  su - postgres -c "\
    echo y | createuser -i $dbuid -U -d $dbuser > /dev/null ; \
    psql -q template1 -c \" \
  	  update pg_shadow set passwd='$dbpw',\
	  valuntil='Fri Jan 30 22:00:00 2037 PST' \
	  where usename='$dbuser';\""
  if [ $? != 0 ]; then
    echo "Error creating user $dbuser. Bailing"
    exit 1
  fi
else
  echo -n "Password: " >&2
  stty -echo
  read rootpw
  stty echo
  echo
  echo
  echo -n "Dropping user $dbuser... "
  echo $rootpw | dropuser --username="postgres" --password --quiet "$dbuser" \
    --host="$dbserver" >& /dev/null
  echo "Done"
  
  echo -n "Adding user $dbuser... "
  echo -e "$dbpw\n$dbpw\n$dbpw\n$rootpw" | \
    createuser --no-createdb --no-adduser --sysid="$dbuid" --pwprompt \
      --username=postgres --password "$dbuser" --host="$dbserver" > /dev/null
  if [ $? != 0 ]; then
    echo "Error creating user $dbuser. Bailing"
    exit 1
  fi
  echo "Done"
fi

echo -n "Droping database $dbname... "
if [ "$oldcmds" != "n" ]; then
  echo -e "$dbuser\n$dbpw" | destroydb -u $dbname &> /dev/null
else
  echo -e "$rootpw" | \
    dropdb --username="postgres" --quiet --password "$dbname" \
      --host="$dbserver" >& /dev/null
fi
echo " Done"


echo -n "Creating database $dbname..."
if [ "$oldcmds" != "n" ]; then
  echo -e "$dbuser\n$dbpw" | createdb -u $dbname > /dev/null
else
  echo $dbpw | createdb --host="$dbserver" --username=postgres $dbname \
    > /dev/null
fi
if [ $? != 0 ]; then
  echo
  echo "Error, Bailing" >&2
  exit 1
fi
echo " Done"


echo -n "Dropping table $dbtable in database $dbname..."
if [ "$oldcmds" != "n" ]; then
  psql -q -u  -d $dbname >& /dev/null  << _EOF_
$dbuser
$dbpw
drop table $dbtable;
_EOF_
else 
  psql -h "$dbserver" -q -u  -d $dbname >& /dev/null  << _EOF_
$dbuser
$dbpw
drop table $dbtable;
_EOF_
fi
echo " Done"


echo -n "Creating table $dbtable in database $dbname..."
if [ "$oldcmds" != "n" ]; then
  psql -q -u  -d $dbname > /dev/null << _EOF_
$dbuser
$dbpw
create table $dbtable (
  "user" char(16) not null primary key, 
  "servername" char(255) not null, 
  "port" char(8) default null
);
_EOF_
else
  psql -h "$dbserver" -q -u  -d $dbname > /dev/null << _EOF_
$dbuser
$dbpw
create table $dbtable (
  "user" char(16) not null primary key, 
  "servername" char(255) not null, 
  "port" char(8) default null
);
_EOF_
fi
if [ $? != 0 ]; then
  echo "Error creating $dbtable in $dbname. Bailing"
  exit 1
fi
echo " Done"


echo
echo
echo "You may now add entries to $dbtable in $dbname."
echo "To connect to $dbname use:"
echo
echo "psql -u \"$dbname\""
echo
echo "At the Username prompt enter $dbuser"
echo
echo "To insert rows into $dbtable use the following once"
echo "logged into $dbname"
echo
echo "insert into $dbtable values (\"user\", \"servername\", \"port\");"
echo "where: "
echo "  user:       name of user. Up to 16 characters. May not be NULL."
echo "  servername: name of server for user. Up to 256 characters. May not be NULL."
echo "  port:       port to connect to on server. May be NULL."