stacks-web 1.35-1ubuntu1 / usr / sbin / stacks-setup-database

#!/bin/bash
set -u

USAGE="sudo stacks-setup-database [-u <username>] [-DELETE] <dbname>..."

HELP="usage: $USAGE

This script sets up a MySQL database suitable for loading Stacks data, which can
be viewed at http://localhost/stacks.

In the simplest case, give the name of a single new database to create and it
will be made in MySQL as dbname_radtags.

In addition to creating the database, the user running sudo will be given write
access to the new database.  If you are creating the database for another user
you can specify an alternative account name with the -u flag; eg. '-u joe'.
If the user joe does not yet exist in MySQL, a random password will be set for
joe and also placed in /home/joe/.my.cnf so that joe, and only joe, will immediately
will be able to write to the dbname_radtags database.

Note that all users on the system who can see the web interface will see all
Stacks databases, and furthermore can access them directly (read-only) in MySQL
by using the well-known username/password stacksweb/stacksweb.  If this is a
problem and you want to hide databases from certain users then you'll need to
configure the security manually and edit /etc/stacks/constants.php.

This script is part of the Debian stacks-web package and is not a standard Stacks
feature.
"

mode=create
no_user_set=0
user_name=
sudo_user=

#I'll be good and use OPTARGS
while getopts ":hNu:D:" opt; do
  case $opt in
    h)
      echo "$HELP"
      exit 0
      ;;
    N)
      no_user_set=1
      ;;
    D)
      #Ugly, but...
      if [ "$OPTARG" = ELETE ] ; then
	  mode=delete
	  no_user_set=1
      else
	  echo "Invalid option: -D$OPTARG"
	  exit 1
      fi
      ;;
    u)
      user_name="$OPTARG"
      ;;
    \?)
      echo "Invalid option: -$OPTARG"
      exit 1
      ;;
    :)
      echo "Option -$OPTARG requires an argument."
      exit 1
      ;;
  esac
done
shift $(( $OPTIND - 1 ))

# Function to run a SQL command or else take input from stdin
mysql_do(){
    e= ; [ "$#" = 1 ] && e=e
    #echo DEBUG: "$@"
    mysql --defaults-file=/etc/mysql/debian.cnf -BN$e "$@"
}

if [ "$*" = "" ] ; then
    echo "Please give the name of the Stacks database(s) you wish to $mode,"
    echo "or use -h to get help."

    if [ `id -u` = 0 ] ; then
	echo "Stacks databases on this machine:"
	mysql_do 'SHOW DATABASES' | grep '_radtags$' | sed 's/^/ /'
    else
	echo "Stacks databases accessible to `id -nu`:"
	mysql -BNe 'SHOW DATABASES' | grep '_radtags$' | sed 's/^/ /'
    fi

    exit 1
fi

# Ensure we are running as root and work out who the controlling sudoer was:
if [ `id -u` != 0 ] ; then
    echo "Only root can administer MySQL via this script.  Please use sudo."
    exit 1
fi

if [ 0`id -u "${SUDO_USER:-}" 2>/dev/null` -gt 0 ] ; then
    sudo_user="$SUDO_USER"
fi

#echo "You ran this as user $SUDO_USER."


# Work out what user to give access to.
if ! [ "$no_user_set" = 1 ] ; then
    if [ "$user_name" = "" ] ; then
	if [ "$sudo_user" = "" ] ; then
	    echo "You need to specify a user for the database using the -u flag, or -N for no user setup."
	else
	    user_name="$sudo_user"
	fi
    fi
    # Note: If you specify -u and -N together the -N wins.

    #Ensure the user is real, even though sudo_user has already been checked.
    user_home=`getent passwd "$user_name"  | cut -d: -f6`
    user_grp=`getent passwd "$user_name"  | cut -d: -f4`
    if [ "$user_home" = "" ] ; then
	echo "User $user_name is not a valid account on this system."
	exit 1
    fi

    # See if $user_name is known to MySQL
    # Need to do this before any GRANTs
    user_in_mysql=$(mysql_do "SELECT User FROM mysql.user WHERE User = '$user_name'" | sort -u)

fi

# Then, create/delete the database(s)
# Note there is no sanitising of the DB name - this script does not pretend
# to be hardened against malicious usage should you permit non-admins to run it
# under sudo.
dbcreate(){
    # Set [ db_name db_in_mysql no_user_set user_name ] before
    # calling this routine.
    if [ "$db_in_mysql" = "" ] && [ "$no_user_set" = 1 ] ; then
	echo "Making database $db_name without adding any write permissions."
    elif [ "$db_in_mysql" = "" ] ; then
	echo "Making database $db_name and granting permissions for $user_name."
    elif [ "$no_user_set" = 1 ] ; then
	echo "Database $db_name already exists.  Nothing to do!"
	return
    else
	echo "Database $db_name already exists.  Ensuring $user_name has write permission."
    fi

    if [ "$db_in_mysql" = "" ] ; then
	if ! mysql_do "CREATE DATABASE \`$db_name\`" ; then
	    echo Aborting.
	    exit 1
	fi

	# Now load in the standard SQL
	mysql_do -D "$db_name" < /usr/share/stacks/sql/stacks.sql
    fi

    if ! [ "$no_user_set" = 1 ] ; then
	# Now ensure that user can write to it
	mysql_do "GRANT all ON \`$db_name\`.* TO '$user_name'@localhost"
    fi

    # In all cases ensure that stacksweb can read from it
    mysql_do "GRANT select ON \`$db_name\`.* TO 'stacksweb'@localhost"
}

dbdelete()
{
    if [ "$db_in_mysql" = "" ] ; then
	echo "Database $db_name not found in MySQL"
	return
    else
	echo "Removing database $db_name and cleaning up privileges."
	if ! mysql_do "DROP DATABASE \`$db_name\`" ; then
	    echo Aborting.
	    exit 1
	fi
	mysql_do "DELETE FROM mysql.db WHERE db = '$db_name'"
	mysql_do "DELETE FROM mysql.tables_priv WHERE db = '$db_name'"
	mysql_do "FLUSH PRIVILEGES"
    fi
}

for db in "$@" ; do
    db_name="$db"_radtags

    db_in_mysql=$(mysql_do "SHOW DATABASES" | grep -x -- "$db_name")

    #dbcreate or dbdelete
    #Yes, this is a symbolic function call with pass-by-global parameters. Meh.
    db$mode
done

# Come up with a random password, then set it if there was no user account
# there previously.
random_pass=`head -c300 /dev/urandom | md5sum | head -c 24`

# Set a user for default access if they are new to MySQL.
# Allow this to work in root-squash mode on NFS by using sudo -u "$user_name"
# for all file accesses under /home.
if ! [ "$no_user_set" = 1 ] ; then
    if [ "$user_in_mysql" != "$user_name" ] && \
       sudo -u "$user_name" [ ! -e "$user_home"/.my.cnf ] ; then

	# Nope, set the user up
	echo "Setting random password and writing $user_home/.my.cnf"
	mysql_do "set password for '$user_name'@'localhost' = PASSWORD('$random_pass')"

	echo $'[client]\nuser='"$user_name"$'\npassword='"$random_pass" | \
	   sudo -u "$user_name" sh -c 'cat>"$1"' -- "$user_home"/.my.cnf
	#chown "$user_name:$user_grp" "$user_home"/.my.cnf
	sudo -u "$user_name" chmod 600 "$user_home"/.my.cnf
    else
	echo "User is already in MySQL or has .my.cnf, so password will not be changed."
    fi
fi

#In all cases after creating databases, ensure that stacksweb can connect...
if ! [ "$mode" = delete ] ; then
    echo "Ensuring user stacksweb can connect to MySQL"
    mysql_do "set password for 'stacksweb'@'localhost' = PASSWORD('stacksweb')"
fi

echo DONE