#!/bin/bash
#
# Copyleft 2017 Clazzes.org & ITEG IT-Engineers GmbH
#
# Author: Christoph Lechleitner <christoph.lechleitner@iteg.at>
#
# see https://confluence.clazzes.org/x/GoDM
#

if [ -z "$*" ] ; then
  echo ""
  echo "ERROR: No postgres version specified."
  echo ""
  echo "Syntax:"
  echo "  $0 [--yes] version"
  echo ""
  cd /etc/postgresql
  echo "Ac. to the directories in /etc/postgresql the version candidates are:"
  ls -d 9.*
  PGVERSIONGUESS=$(ls -1d 9.* |sort |tail -1)
  echo ""
  echo "Our guess is you want to call   sudo -u postgres $0 $PGVERSIONGUESS"
  echo ""
  exit 1
fi

echo ""
echo "This script will apply changes to Postgres installation to"
echo "fix CVE-2017-7547 for existing and future postgres databases"
echo "in the 'main' cluster only of specified version only."
echo ""
echo "The author of this script takes no responsibility for your data whatsoever,"
echo "so continue at your own risk ;-)"
echo ""
echo "Feel free to review this script before you confirm it's risky work."
echo ""

if [ "$1" = "--yes" ] ; then
  shift
  echo "INFO: Skipping backcheck due to parameter --yes"
else
  echo -n "Enter 'Yes' to continue, or anything else to abort: "
  read YESORNO
  if [ "$YESORNO" != "Yes" ] ; then
    echo "ERROR: User failed to confirm execution. Aborting."
    echo ""
    exit 2
  fi
  echo ""
  echo "INFO: User confirmed execution."
fi
echo ""

if [ "$USER" != "postgres" ] ; then
  echo "ERROR: Not running as user 'postgres'. Aborting."
  echo ""
  if [ "$USER" = "root" ] ; then
    echo "Try to call   sudo -u postgres $0 version"
  else
    echo "Try to call   sudo sudo -u postgres $0 [--yes] version"
  fi
  echo ""
  exit 3
fi

echo "INFO: Running as user 'postgres'."
echo ""

PGVERSION=$1
shift
if [ -z "$PGVERSION" ] ; then
  echo "ERROR: No postgres verion specified as \$1. Aborting."
  echo ""
  cd /etc/postgresql
  echo "Ac. to the directories in /etc/postgresql the version candidates are:"
  ls -d 9.*
  PGVERSIONGUESS=$(ls -1d 9.* |sort |tail -1)
  echo ""
  echo "Our guess is you want to call   sudo -u postgres $0 $PGVERSIONGUESS"
  echo ""
  exit 4
fi

echo "INFO: Specified postgres version: $PGVERSION"
echo ""

PGOLD=$(echo "$PGVERSION" |egrep "9.[0-4]")
PGNEW=$(echo "$PGVERSION" |egrep "9.[5-9]")

if [ -n "$PGOLD" ] ; then
  echo "INFO: Detected 'old' postgres version, 9.[0-4]"         
else
  if [ -n "$PGNEW" ] ; then
    echo "INFO: Detected 'new' postgres version, 9.[5-9]"         
  else
    echo "ERROR: Detected no supported postgres version, as in 9.[0-9]"
    echo ""
    exit 5
  fi
fi
echo ""

if [ ! -f "/etc/postgresql/${PGVERSION}/main/postgresql.conf" ] ; then
  echo "ERROR: No file /etc/postgresql/${PGVERSION}/main/postgresql.conf, aborting."
  echo ""
  exit 6
fi

echo "INFO: /etc/postgresql/${PGVERSION}/main/postgresql.conf exists."
echo ""

# early checks done


# mv away existing /tmp/pg_fix_usermappings.sql

if [ -f /tmp/pg_fix_usermappings.sql ] ; then
  echo "INFO: Renaming existing /tmp/pg_fix_usermappings.sql"
  mv -v /tmp/pg_fix_usermappings.sql /tmp/pg_fix_usermappings.sql.bak
  echo ""
fi
if [ -f /tmp/pg_fix_usermappings.sql ] ; then
  echo "ERROR: Failed to rename existing /tmp/pg_fix_usermappings.sql"
  echo ""
  exit 11
fi

# prepare /tmp/pg_fix_usermappings.sql

echo "INFO: Creating /tmp/pg_fix_usermappings.sql"

echo "SET search_path = pg_catalog;

CREATE OR REPLACE VIEW pg_user_mappings AS
  SELECT
    U.oid       AS umid,
    S.oid       AS srvid,
    S.srvname   AS srvname,
    U.umuser    AS umuser,
    CASE WHEN U.umuser = 0 THEN
      'public'
    ELSE
      A.rolname
    END AS usename,
    CASE WHEN 
      (U.umuser <> 0 AND A.rolname = current_user AND (pg_has_role(S.srvowner, 'USAGE')
        OR has_server_privilege(S.oid, 'USAGE')))
      OR (U.umuser = 0 AND pg_has_role(S.srvowner, 'USAGE'))
      OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user)
    THEN U.umoptions
    ELSE NULL END AS umoptions
  FROM pg_user_mapping U
  LEFT JOIN pg_authid A ON (A.oid = U.umuser) 
  JOIN pg_foreign_server S ON (U.umserver = S.oid);" >/tmp/pg_fix_usermappings.sql

chmod ugo+rx /tmp/pg_fix_usermappings.sql


# enable system table mods in postgres.conf

if [ -f "/etc/postgresql/${PGVERSION}/main/postgresql.conf.bak" ] ; then
  echo ""
  echo "ERROR: There is a file"
  echo "  /etc/postgresql/${PGVERSION}/main/postgresql.conf.bak"
  echo ""
  echo "Aborting"
  echo ""
  echo "Suggestion:"
  echo "  sudo diff /etc/postgresql/9.6/main/postgresql.conf.bak /etc/postgresql/9.6/main/postgresql.conf"
  echo "  # review changes"
  echo "  sudo mv /etc/postgresql/9.6/main/postgresql.conf.bak /etc/postgresql/9.6/main/postgresql.conf"
  echo ""
  exit 21
fi

echo "INFO: Saving /etc/postgresql/${PGVERSION}/main/postgresql.conf to"
echo "      /etc/postgresql/${PGVERSION}/main/postgresql.conf.bak"
echo ""
cp -av /etc/postgresql/${PGVERSION}/main/postgresql.conf /etc/postgresql/${PGVERSION}/main/postgresql.conf.bak || exit 22
echo ""

echo "INFO: Patching /etc/postgresql/${PGVERSION}/main/postgresql.conf with allow_system_table_mods=true"
echo ""

echo "" >>/etc/postgresql/${PGVERSION}/main/postgresql.conf
echo "allow_system_table_mods = true" >>/etc/postgresql/${PGVERSION}/main/postgresql.conf || exit 23

# restart postgres

echo "INFO: Restarting postgres ${PGVERSION}"
echo ""
/etc/init.d/postgresql stop ${PGVERSION} || exit 31
sleep 3
/etc/init.d/postgresql start ${PGVERSION} || exit 32

echo ""
echo "INFO: Waiting for postgresql claims itself up ..."
echo ""

export PGSTATUSONLINE=""
I=0
while [ -z "${PGSTATUSONLINE}"  -a "$I" -lt 60 ]
do
  ((I += 1))
  echo "Waiting for PostgreSQL to come up ($I seconds so far) ..." |tee -a ${LOGFILE}
  sleep 1
  PGSTATUS=$(LANG="C" /etc/init.d/postgresql status ${PGVERSION} |egrep -i "^${PGVERSION}\/main .* online" 2>&1)
  echo "Postgres status: ${PGSTATUS}"
  PGSTATUSONLINE=$(echo "${PGSTATUS}" |egrep -i "^${PGVERSION}\/main .* online")
done
if [ -z "${PGSTATUSONLINE}" ] ; then
  echo "FATAL: PostgreSQL failed to come up within $I seconds."
  echo ""
  exit 33
fi
echo ""

echo "INFO: Postgresql claims itself up, sleeping another bit ..."
echo ""
sleep 5


# avoid postgres user claiming missing access to current directory

cd /tmp


# enable changes to template0

echo "INFO: Allowing connections to template0"
echo ""
if [ -n "$PGOLD" ] ; then
  echo "UPDATE pg_database SET datallowconn = true WHERE datname = 'template0';" |psql --cluster ${PGVERSION}/main || exit 41
else
  echo "ALTER DATABASE template0 WITH ALLOW_CONNECTIONS true;" |psql --cluster ${PGVERSION}/main postgres || exit 42
fi
echo ""


# apply pg_fix_usermappings.sql to all databases

DBLIST=$(LANG="C" psql --cluster ${PGVERSION}/main -l |cut -f2 -d' ' |egrep '^[a-z0-9_]+$')
#|egrep -v '^(postgres)$')
if [ -z "$DBLIST" ] ; then
  echo "ERROR: Could not detect list of databases to save."
  echo ""
  exit 51
fi

for DBNAME in $DBLIST
do
  echo "INFO: Applying /tmp/pg_fix_usermappings.sql to database $DBNAME"
  echo ""
  cat /tmp/pg_fix_usermappings.sql |psql --cluster ${PGVERSION}/main $DBNAME || echo "WARNING: Continuing despite failure to apply /tmp/pg_fix_usermappings.sql to database $DBNAME"
  # we do not exit on failure
  echo ""
done


# disable changes to template0

echo "INFO: Disallowing connections to template0"
echo ""
if [ -n "$PGOLD" ] ; then
  echo "UPDATE pg_database SET datallowconn = false WHERE datname = 'template0';" |psql --cluster ${PGVERSION}/main || exit 61
else
  echo "ALTER DATABASE template0 WITH ALLOW_CONNECTIONS false;" |psql --cluster ${PGVERSION}/main postgres || exit 62
fi
echo ""


# disable system table mods in postgres.conf

echo "INFO: Restoring /etc/postgresql/${PGVERSION}/main/postgresql.conf.bak to"
echo "      /etc/postgresql/${PGVERSION}/main/postgresql.conf"
echo ""
mv /etc/postgresql/${PGVERSION}/main/postgresql.conf.bak /etc/postgresql/${PGVERSION}/main/postgresql.conf || exit 71


# restart postgres

echo "INFO: Finally restarting patched postgres ${PGVERSION}"
echo ""
/etc/init.d/postgresql stop ${PGVERSION} || exit 81
sleep 3
/etc/init.d/postgresql start ${PGVERSION} || exit 82

echo ""
echo "INFO: Not waiting for postgresql to claim itself up again."
echo ""

echo "INFO: Your postgres ${PGVERSION} installatioin might be safer now ;-)"
echo ""