#!/bin/bash
#
# PostgreSQL Tools.
#
# For suggestion, bug reports, please contact Pierre-Yves Landuré <pierre-yves@landure.fr>


SCRIPT_NAME="$(command basename ${0})"

# Print this script help.
function usage {
  command echo "
This tool ease PostgreSQL server administration from command line.

Usage :

  ${SCRIPT_NAME} create-db database_name [ "allowed hosts" ]
  ${SCRIPT_NAME} create-domain-db www.domain-name.com [ "allowed hosts" ]
  ${SCRIPT_NAME} create-privileged-user username [ "allowed hosts" ]
  ${SCRIPT_NAME} create-user username [ "allowed hosts" ]

  * create-db : Create a database and its user.
  * create-domain-db : Create a database for domain
  * create-privileged-user : Create a user that can create databases.
  * create-user : Create a user without priviledges.
"
  exit 1
} # usage



# Get the absolute path for a file or directory.
#
# @param string $path A relative path.
#
# @return ${REALPATH} A absolute path.
REALPATH=""
function realpath {
  command test ${#} -ne 1 && exit 1
  REALPATH=$(/bin/readlink -f "${1}")
} # realpath



# Check if a binary is present
#
# @param string $binary The binary to check.
# @param string $package The package the binary come from.
#
# @return Exit with error if the binary is missing.
function check_binary {
  command test ${#} -ne 2 && exit 1

  # Test the binary presence.
  if [ -z "$(which "${1}")" ]; then
    echo "Error : '${1}' is missing. Please install package '${2}'."
    exit 1
  fi
} # check_binary



# Check if MySQL connection is working
#
# @param string $mysql_host The MySQL host.
# @param string $mysql_user The MySQL user.
# @param string $mysql_password The MySQL password.
# @param string $mysql_db The MySQL DB.
#
# @return Exit with error if connection to MySQL fail.
function check_mysql {
  command test ${#} -ne 4 && exit 1

  # Test the MySQL connection.
  if ! command mysql --execute="SELECT 1" \
            --host="${1}" --user="${2}" --password="${3}" \
            "${4}" 2>&1 > /dev/null; then
    echo "Error : Unable to connect to MySQL. Please provide valid MySQL connection parameters."
    exit 1
  fi
} # check_mysql



# Get the absolute path for pg_hba.conf file..
#
# @return ${PG_HBA_CONF} The path to pg_hba.conf.
PG_HBA_CONF=""
function pg_hba_conf {
  PG_HBA_CONF="$(command find '/etc/postgresql' -name 'pg_hba.conf' \
      | command head -n 1)"
} # pg_hba_conf



# Get the IP v4 address of the host.
#
# @param string A hostname.
#
# @return ${HOST_IPV4} The IP v4 of the given host.
HOST_IPV4=""
function host_ipv4 {
  HOST_IPV4=""

  command test ${#} -ne 1 && exit 1

  HOST_IPV4="$(command ping -c 1 "$1" \
        | command head -n 1 \
        | command sed -e 's/[^(]*(\([^)]*\).*/\1/')"
} # host_ipv4



# Get the IP v6 address of the host.
#
# @param string A hostname.
#
# @return ${HOST_IPV6} The IP v6 of the given host.
HOST_IPV6=""
function host_ipv6 {
  HOST_IPV6=""

  command test ${#} -ne 1 && exit 1

  HOST_IPV6="$(command ping6 -c 1 "$1" \
        | command head -n 1 \
        | command sed -e 's/[^(]*(\([^)]*\).*/\1/')"
} # host_ipv6



# Download a file from the given URL.
#
# @param string $url The URL of the file to download.
#
# @return ${DOWNLOAD_FILE} The path to the downloaded file.
DOWNLOAD_FILE=""
function download_file {
  command test ${#} -ne 1 && exit 1

  # Download a file.
  DOWNLOAD_FILE="$(command mktemp)"
  command wget --quiet "${1}" \
      --output-document="${DOWNLOAD_FILE}"

  if [ ! -s "${DOWNLOAD_FILE}" ]; then
    command rm "${DOWNLOAD_FILE}"
    echo "Error : Unable to download file from '${1}'."
    exit 1
  fi
} # download_file



# Download and uncompress a tgz file from the given URL.
#
# @param string $url The URL of the file to download.
#
# @return ${DOWNLOAD_TGZ} The path to the extracted content.
DOWNLOAD_TGZ=""
function download_tgz {
  command test ${#} -ne 1 && exit 1

  download_file "${1}"

  # Untar the downloaded file and place it at its final location.
  DOWNLOAD_TGZ="$(command mktemp -d)"
  command tar --directory "${DOWNLOAD_TGZ}" -xzf "${DOWNLOAD_FILE}"
  command rm "${DOWNLOAD_FILE}"

  if [ $(command ls -1 --all "${DOWNLOAD_TGZ}" | command wc --lines) -eq 0 ]; then
    echo "Error : unable to untar file downloaded from '${1}'."
    exit 1
  fi
} # download_tgz



# Check for binaries presence
check_binary "basename" "coreutils"
check_binary "dirname" "coreutils"
check_binary "mktemp" "mktemp"
check_binary "sed" "sed"
check_binary "psql" "postgresql-client"
check_binary "apg" "apg"
check_binary "wget" "wget"

# Check if at least one args given.
command test ${#} -eq 0 && usage

case "${1}" in

  create-domain-db )
    # Check if valid number of arguments given.
    command test ${#} -lt 2 && usage

    DOMAIN="${2}"
    ALLOWED_HOSTS="${3}"

    PGSQL_DB=$(echo "${DOMAIN}" | command tr '[a-z].-' '[A-Z]__')

    ${0} create-db "${PGSQL_DB}" "${ALLOWED_HOSTS}"

    exit 0
    ;;



  create-db )
    # Check if valid number of arguments given.
    command test ${#} -lt 2 && usage

    PGSQL_HOST="localhost"
    PGSQL_DB="${2}"
    ALLOWED_HOSTS="${3}"
    ROOT_PASSWORD="${4}"

    PGSQL_USER=$(command echo "${PGSQL_DB}" \
    | command tr '[A-Z]' '[a-z]')
    PGSQL_PASSWORD=$(command apg -q -a  0 -n 1 -M NCL)

    # Fetch the pg_hba.conf path.
    pg_hba_conf


    COMMAND="command createuser --no-superuser \\
          --no-createdb --no-createrole --login '${PGSQL_USER}';
        command psql -c \"ALTER USER ${PGSQL_USER} PASSWORD '${PGSQL_PASSWORD}'\";
        command createdb --encoding UTF8 \\
          --owner '${PGSQL_USER}' '${PGSQL_DB}';"

    command su 'postgres' -c "${COMMAND}"


    if [ -e "${PG_HBA_CONF}" ]; then
      for ALLOWED_HOST in ${ALLOWED_HOSTS}; do

        host_ipv4 "${ALLOWED_HOST}"
        if [ -n "${HOST_IPV4}" ]; then
          echo "
  # Allow connection to ${PGSQL_DB} from ${ALLOWED_HOST} :
  host    ${PGSQL_DB}    ${PGSQL_USER}    ${HOST_IPV4}/32    md5" \
              >> "${PG_HBA_CONF}"
        fi

        host_ipv6 "${ALLOWED_HOST}"
        if [ -n "${HOST_IPV6}" ]; then
          echo "
  # Allow connection to ${PGSQL_DB} from ${ALLOWED_HOST} :
  host    ${PGSQL_DB}    ${PGSQL_USER}    ${HOST_IPV6}/128    md5" \
              >> "${PG_HBA_CONF}"
        fi

      done
    fi

    command /etc/init.d/postgresql reload

    command echo "# Les paramètres d'accès à votre base de données sont :
# Hôte
PGSQL_HOST=localhost
# Nom de la base de données
PGSQL_DB=${PGSQL_DB}
# Identifiant
PGSQL_USER=${PGSQL_USER}
# Mot de passe
PGSQL_PASSWORD=${PGSQL_PASSWORD}"
    exit 0
    ;;



  create-privileged-user )
    # Check if valid number of arguments given.
    command test ${#} -lt 2 && usage

    PGSQL_HOST="localhost"
    PGSQL_USER="${2}"
    ALLOWED_HOSTS="${3}"
    ROOT_PASSWORD="${4}"

    PGSQL_PASSWORD=$(command apg -q -a  0 -n 1 -M NCL)

    # Fetch the pg_hba.conf path.
    pg_hba_conf


    COMMAND="command createuser --no-superuser \\
          --createdb --no-createrole --login '${PGSQL_USER}';
        command psql -c \"ALTER USER ${PGSQL_USER} PASSWORD '${PGSQL_PASSWORD}'\";"

    command su 'postgres' -c "${COMMAND}"


    if [ -e "${PG_HBA_CONF}" ]; then
      for ALLOWED_HOST in ${ALLOWED_HOSTS}; do

        host_ipv4 "${ALLOWED_HOST}"
        if [ -n "${HOST_IPV4}" ]; then
          echo "
# Allow connection to all databases from ${ALLOWED_HOST} :
host    all    ${PGSQL_USER}    ${HOST_IPV4}/32    md5" \
              >> "${PG_HBA_CONF}"
        fi

        host_ipv6 "${ALLOWED_HOST}"
        if [ -n "${HOST_IPV6}" ]; then
          echo "
# Allow connection to all databases from ${ALLOWED_HOST} :
host    all    ${PGSQL_USER}    ${HOST_IPV6}/128    md5" \
              >> "${PG_HBA_CONF}"
        fi

      done
    fi

    command /etc/init.d/postgresql reload

    command echo "# Les paramètres d'accès de l'utilisateur privilégié sont :
# Hôte
PGSQL_HOST=localhost
# Identifiant
PGSQL_USER=${PGSQL_USER}
# Mot de passe
PGSQL_PASSWORD=${PGSQL_PASSWORD}"

    exit 0
    ;;



  create-user )
    # Check if valid number of arguments given.
    command test ${#} -lt 2 && usage

    PGSQL_HOST="localhost"
    PGSQL_USER="${2}"
    ALLOWED_HOSTS="${3}"
    ROOT_PASSWORD="${4}"

    PGSQL_PASSWORD=$(command apg -q -a  0 -n 1 -M NCL)

    # Fetch the pg_hba.conf path.
    pg_hba_conf


    COMMAND="command createuser --no-superuser \\
          --no-createdb --no-createrole --login '${PGSQL_USER}';
        command psql -c \"ALTER USER ${PGSQL_USER} PASSWORD '${PGSQL_PASSWORD}'\";"

    command su 'postgres' -c "${COMMAND}"


    if [ -e "${PG_HBA_CONF}" ]; then
      for ALLOWED_HOST in ${ALLOWED_HOSTS}; do

        host_ipv4 "${ALLOWED_HOST}"
        if [ -n "${HOST_IPV4}" ]; then
          echo "
# Allow connection to template1 from ${ALLOWED_HOST} :
host    template1    ${PGSQL_USER}    ${HOST_IPV4}/32    md5" \
              >> "${PG_HBA_CONF}"
        fi

        host_ipv6 "${ALLOWED_HOST}"
        if [ -n "${HOST_IPV6}" ]; then
          echo "
# Allow connection to template1 from ${ALLOWED_HOST} :
host    template1    ${PGSQL_USER}    ${HOST_IPV6}/128    md5" \
              >> "${PG_HBA_CONF}"
        fi

      done
    fi

    command /etc/init.d/postgresql reload

    command echo "# Les paramètres d'accès de l'utilisateur privilégié sont :
# Hôte
PGSQL_HOST=localhost
# Identifiant
PGSQL_USER=${PGSQL_USER}
# Mot de passe
PGSQL_PASSWORD=${PGSQL_PASSWORD}"

    exit 0
    ;;



  * )
    echo "Error : '${1}' is not a valid action."
    usage
    ;;
esac

exit 0