check_psqlserver

#!/bin/bash
# ======================================================================
#
# !!! WORK IN PROGRESS !!! DO NOT USE YET !!!
#
# Check PSQL SERVER
#
# requirements:
# - psql client
#
# installation:
# - execute check_psqlserver -i
#
# ----------------------------------------------------------------------
# 2023-06-xx  v0.0  <axel.hahn@unibe.ch>
# ======================================================================


. $(dirname $0)/inc_pluginfunctions
self_APPNAME=$( basename $0 | tr [:lower:] [:upper:] )
self_APPVERSION=0.1

# --- set HOME
HOME=/etc/icinga2-passive-client
# HOME=/etc/icingaclient

# --- other vars...
cfgfile=$HOME/.psql.conf
myuser=icingamonitor

# new line
NL="
"

lastvalue=
out=" "

# ----------------------------------------------------------------------
# FUNCTIONS
# ----------------------------------------------------------------------

# uninstall database user
function _uninstall(){
    echo UNINSTALL ...
    su - postgres -c "psql -c \"DROP USER ${myuser};\""
    unset PGHOST
    unset PGUSER
    unset PGPASSWORD
}

# (re)install database user for monitoring
function _install(){
    echo INSTALLING ...
    local pwlength=64
    echo "- check psql connection..."
    su postgres -c "psql -V postgres"
    if [ $? -ne 0 ]; then
        echo "ERROR: psql connect without password failed."
        exit 1
    fi

    echo "- creating mysql user $myuser@localhost with random password ($pwlength chars)..."
    mypw=$( head /dev/urandom | tr -dc A-Za-z0-9 | head -c $pwlength )

    SQL1="CREATE USER ${myuser} WITH PASSWORD '${mypw}' CONNECTION LIMIT 5";
    SQL2="GRANT pg_monitor TO ${myuser};"
    # SQL2="GRANT MONITOR_QUERIES TO ${myuser};"
    su - postgres -c "psql -c \"${SQL1}\""
    if [ $? -ne 0 ]; then
        echo "ERROR: psql command to create user failed."
        exit 1
    fi
    echo "- grant ..."
    su - postgres -c "psql -c \"${SQL2}\""
    if [ $? -ne 0 ]; then
        echo "ERROR: psql command to grant permissions failed."
        # exit 1
    fi

    echo "- creating config file $cfgfile ... "
    cat  >$cfgfile <<EOF
#
# generated on $(date)
#
export PGUSER=${myuser}
export PGPASSWORD=${mypw}
export PGHOST=localhost
export PGDATABASE=postgres
EOF
    ls -l $cfgfile
    if [ $? -ne 0 ]; then
        echo "ERROR: creation of config file failed."
        exit 1
    fi
}

function _usage(){
    local _self=$( basename $0 )
    cat <<EOH
______________________________________________________________________

${self_APPNAME} :: v${self_APPVERSION}

THIS IS AN PRE ALPHA VERSION - DO NOT USE THIS

(c) Institute for Medical Education - Univerity of Bern
Licence: GNU GPL 3
______________________________________________________________________

USAGE:
  $_self [OPTIONS] -m METHOD

OPTIONS:
  -h  this help
  -i  install monitoring user (must be executed as root)
  -u  uninstall monitoring user (must be executed as root)

PARAMETERS:
  -m  method; valid methods are:
      dbstat          first implmenetation test

EXAMPLES:
  $_self -i
  $_self -m commands

EOH
}

function renderCounts(){
  local _query="$1"
  local _out=$( psql -c "${_query}")
  # out+=$( echo "DEBUG: _query=${_query}${NL}" )
  # echo "DEBUG: _out="; echo "${_out}"

  local _iCounter=0
  typeset -i _iCounter

  local _header
  _header=$( echo "${_out}" | head -1 | tr -d ' ')
  local _data
  _data=$( echo "${_out}" | head -3 | tail -1 | tr -d ' ')

  # echo "DEBUG: _header=${_header}"
  IFS="|"
  read -ra aCols <<< "$_header"
  read -ra aVals <<< "$_data"
  for sColumn in ${aCols[*]}
  do
    local value=${aVals[$_iCounter]}
    # echo "DEBUG: $sColumn = $value"
    out+=$( printf "%25s: %10s \n" "${sColumn}" "${value}${NL}")
    ph.perfadd "${sColumn}" "${value}"
    _iCounter+=1
  done
}
# ----------------------------------------------------------------------
# MAIN
# ----------------------------------------------------------------------

bOptInstall=$(   ph.hasParamoption "i" "$@")
bOptUninstall=$( ph.hasParamoption "u" "$@")
bOptHelp=$(      ph.hasParamoption "h" "$@")

if [ $bOptHelp -eq 1 -o $# -lt 1 ]; then
    _usage
    exit 0
fi

# --- check required tools
# ph.require mysql

# --- install
if [ $bOptInstall -eq 1 -a "$( whoami )" = "root" ]; then
    if [ -f $cfgfile ]; then
        ph.status "SKIP installation. config file already exists: $cfgfile."
        ph.exit
    fi
    HOME=/root
    export HOME

    _uninstall
    _install

    ph.status "Installation was done"
    ph.exit
fi

# --- uninstall
if [ $bOptUninstall -eq 1 -a "$( whoami )" = "root" ]; then
    HOME=/root

    _uninstall
    rm -f $cfgfile

    ph.status "Uninstalled."
    ph.exit
fi


# --- check installation
grep $myuser $cfgfile >/dev/null 2>/dev/null
if [ $? -ne 0 ]; then
    ph.abort "PSQL access not possible yet. You need to install the monitoring user first: as root execute `basename $0` -i"
fi

# ----------------------------------------------------------------------

sMode=$(ph.getValueWithParam '' m "$@")

case "${sMode}" in

    "dbstat")
        descr="statistics over all databases"
        renderCounts "select \
            sum(xact_commit)   as commit,   \
            sum(xact_rollback) as rollback, \
            sum(blks_read)     as blkread,  \
            sum(blks_hit)      as blkhit    \
            from pg_stat_database "
          ;;
    "queries")
        # psql -c "select * from pg_stat_activity "
        # psql -c "select * from pg_stat_statements " -> av psql v11
        ;;

    *)
        echo ERRROR: [${sMode}] is an INVALID mode
        _usage
        ph.abort

esac

ph.status "Pgsql $sMode :: $descr"
echo "$out"

ph.exit

# ----------------------------------------------------------------------