#!/bin/sh

# This program is Copyright (c) 2020 SolarWinds Worldwide, LLC. All rights reserved.

echo
echo "SolarWinds DPM PostgreSQL installer v20211012"

set -u
POSIXLY_CORRECT=1
export POSIXLY_CORRECT
TERM=dumb
export TERM

unalias -a >/dev/null 2>&1 || true

EXISTS="command -v"
${EXISTS} ls >/dev/null 2>&1 || EXISTS=which
if ! ${EXISTS} ls >/dev/null 2>&1 ; then
	echo "command/which failed" >&2
	exit 1
fi

tempdir=""

reset_tempdir()
{
	test -n "${tempdir}" && test -d "${tempdir}" && rm -Rf "${tempdir}" >/dev/null 2>&1
	tempdir=""
}

abort()
{
	if [ $# -gt 0 ]; then
		echo >&2
		echo "$1" >&2
		echo >&2
	fi
	reset_tempdir
	exit 1
}

PROXY="auto"
socks5=""
SKIPCERTS=""
VCHOST="${PGHOST:-}"
VCUSER="${VCUSER:-vividcortex}"
UNPRIVILEGED=""
OVERWRITE=""
DATABASE=""

test "${VCUSER}" = "vividcortex" || abort "This script no longer allows changing username, it's always 'vividcortex'"

if [ -n "${SSL_NO_VERIFY_PEER:-}" ]; then
	SKIPCERTS=1
fi

OFFHOST=""
script_name="$(basename -- "$0")"
test "${script_name}" = "pg-offhost-setup" && OFFHOST=1
test "${script_name}" = "pg-onhost-setup" && OFFHOST=0

###############################################################################

getfile()
{
	failed=0
	if ${EXISTS} curl >/dev/null 2>&1 ; then
		if [ -z "${socks5}" ]; then
			curl -f -s "$1" > "$2" 2>/dev/null && return 0
			test -n "${SKIPCERTS}" && curl -k -f -s "$1" > "$2" 2>/dev/null && return 0
		else
			curl -f -s --socks5-hostname "${socks5}" "$1" > "$2" 2>/dev/null && return 0
			curl -f -s --socks5 "${socks5}" "$1" > "$2" 2>/dev/null && return 0
			curl -f -s -x "socks5://${socks5}" "$1" > "$2" 2>/dev/null && return 0
			test -n "${SKIPCERTS}" && curl -k -f -s --socks5-hostname "${socks5}" "$1" > "$2" 2>/dev/null && return 0
			test -n "${SKIPCERTS}" && curl -k -f -s --socks5 "${socks5}" "$1" > "$2" 2>/dev/null && return 0
			test -n "${SKIPCERTS}" && curl -k -f -s -x "socks5://${socks5}" "$1" > "$2" 2>/dev/null && return 0
		fi
		failed=1
	fi
	if ${EXISTS} wget >/dev/null 2>&1 ; then
		# depending on version, uses env vars
		wget -q -O - "$1" > "$2" 2>/dev/null && return 0
		test -n "${SKIPCERTS}" && wget --no-check-certificate -q -O - "$1" > "$2" 2>/dev/null && return 0
		failed=1
	fi
	if ${EXISTS} fetch >/dev/null 2>&1 ; then
		# fetch uses env vars and does not support socks5
		fetch -q -o - "$1" > "$2" 2>/dev/null && return 0
		test -n "${SKIPCERTS}" && fetch --no-verify-peer -q -o - "$1" > "$2" 2>/dev/null && return 0
		failed=1
	fi

	test "${failed}" = "0" && abort "No suitable tool for file download found"
}

###############################################################################

set_proxy()
{
	proxy="${PROXY}"

	if [ "${PROXY}" = "auto" ]; then
		# detect proxy
		detProxy=${HTTPS_PROXY:-''}
		test -n "${detProxy}" && echo "${detProxy}" | grep -v '://' >/dev/null 2>&1 && detProxy="https://${detProxy}"
		echo "${detProxy}" | grep '^http' >/dev/null 2>&1 || detProxy=${https_proxy:-''}
		test -n "${detProxy}" && echo "${detProxy}" | grep -v '://' >/dev/null 2>&1 && detProxy="https://${detProxy}"
		echo "${detProxy}" | grep '^http' >/dev/null 2>&1 || detProxy=${HTTP_PROXY:-''}
		test -n "${detProxy}" && echo "${detProxy}" | grep -v '://' >/dev/null 2>&1 && detProxy="http://${detProxy}"
		echo "${detProxy}" | grep '^http' >/dev/null 2>&1 || detProxy=${http_proxy:-''}
		test -n "${detProxy}" && echo "${detProxy}" | grep -v '://' >/dev/null 2>&1 && detProxy="http://${detProxy}"
		echo "${detProxy}" | grep '^http' >/dev/null 2>&1 || detProxy=${SOCKS_SERVER:-''}
		test -n "${detProxy}" && echo "${detProxy}" | grep -v '://' >/dev/null 2>&1 && detProxy="socks5://${detProxy}"
		echo "${detProxy}" | grep '^http\|^socks' >/dev/null 2>&1 || detProxy=${ALL_PROXY:-''}
		test -n "${detProxy}" && echo "${detProxy}" | grep -v '://' >/dev/null 2>&1 && detProxy="socks5://${detProxy}"
		echo "${detProxy}" | grep '^http\|^socks' >/dev/null 2>&1 || detProxy=${all_proxy:-''}
		test -n "${detProxy}" && echo "${detProxy}" | grep -v '://' >/dev/null 2>&1 && detProxy="socks5://${detProxy}"
		echo "${detProxy}" | grep '^http\|^socks' >/dev/null 2>&1 || detProxy=""
		proxy="${detProxy}"
	fi

	unset ALL_PROXY all_proxy HTTP_PROXY http_proxy HTTP_PROXY_AUTH HTTPS_PROXY https_proxy SOCKS_SERVER SOCKS_VERSION SSL_NO_VERIFY_PEER
	if [ -n "${proxy}" ]; then
		if echo "${proxy}" | grep "^\(socks5\|socks\)://" >/dev/null 2>&1 ; then
			# socks5 proxy
			socks5=$(echo "${proxy}" | sed 's/[^:]\+:\/\///')
			export SOCKS_SERVER="${socks5}"
			export SOCKS_VERSION=5
			export ALL_PROXY="${proxy}"
			export all_proxy="${proxy}"
		elif echo "${proxy}" | grep "^\(https\|http\)://" >/dev/null 2>&1 ; then
			# https/http proxy
			export HTTP_PROXY="${proxy}"
			export http_proxy="${proxy}"
			export HTTPS_PROXY="${proxy}"
			export https_proxy="${proxy}"
		else
			abort "Unsupported proxy URI: ${proxy}
Proxy URI must begin with 'https://', 'http://', 'socks5://' or 'socks://'.
E.g.: socks5://user:pass@127.0.0.1:1080"
		fi
		echo
		echo "proxy: ${proxy}"
	else
		echo
		echo "proxy: no"
	fi
}

###############################################################################

usage()
{
	EXTRA=""
	if [ "$#" -ge 1 ]; then
		EXTRA="

ERROR: $1"
	fi
	EXTRACMDLINE=""
	test "${OFFHOST}" != "0" && EXTRACMDLINE="${EXTRACMDLINE}\n  --on-host                  Setup on-host monitoring"
	test "${OFFHOST}" = "1" && EXTRACMDLINE="${EXTRACMDLINE} [now: off-host]"
	test "${OFFHOST}" != "1" && EXTRACMDLINE="${EXTRACMDLINE}\n  --off-host                 Setup off-host monitoring"
	test "${OFFHOST}" = "0" && EXTRACMDLINE="${EXTRACMDLINE} [now: on-host]"
	abort "Usage: ${script_name} --host PGHOST --overwrite

List of options:

  --help                     Display this help information
  --host [-h] 'PGHOST'       Database server host or socket directory
  --unprivileged [-u]        Force creation of a non-superuser vividcortex${EXTRACMDLINE}
  --overwrite [-o]           Drop and re-create vividcortex user and schema
  --proxy [-p] 'URI/auto/no' Configure HTTP/HTTPS/SOCKS5 proxy to 'URI'
                             'auto' (default) auto-detects proxy.

This script needs SUPERUSER/rds_superuser/cloudsqlsuperuser access to PGHOST.
Set ~/.pgpass or PGHOST/PGUSER/PGPASSWORD/PGDATABASE/PGPORT env vars as needed.

It will create a database user and set its password to the content of the
VCPASSWORD environment variable; password must be non-blank. It will also
create a 'vividcortex' schema in the default database and populate it with
monitoring functions.

In off-host setup mode, this script will also enable the pg_stat_statements
extension, which is needed for the extraction of query information. You can
read more about these requirements in the 'Off-Host Installation' docs page:
    https://docs.vividcortex.com/getting-started/off-host-installation/

--overwrite is mandatory.${EXTRA}"
}

###############################################################################

# 0 OK, 1 NOTICE, 2 WARNING, 3 ERROR
pgInternal()
{
	errComment=""
	test -n "$4" && errComment="

$4"
	RES=""
	if [ -s "${tempdir}/stderr" ]; then
		RES=`cat "${tempdir}/stderr"`
		if grep '^NOTICE' "${tempdir}/stderr" >/dev/null 2>&1 ; then
			if [ "$1" = "abortOnOutput" ] || [ "$1" = "abortOnNotice" ]; then
				abort "
PostgreSQL unexpected notice: ${RES}
trying to run: $2
using $3 user${errComment}"
			fi
			return 1
		elif grep '^WARNING' "${tempdir}/stderr" >/dev/null 2>&1 ; then
			if [ "$1" = "abortOnOutput" ] || [ "$1" = "abortOnNotice" ] || [ "$1" = "abortOnWarning" ]; then
				abort "
PostgreSQL unexpected warning: ${RES}
trying to run: $2
using $3 user${errComment}"
			fi
			return 2
		else
			if [ "$1" = "abortOnOutput" ] || [ "$1" = "abortOnNotice" ] || [ "$1" = "abortOnWarning" ] || [ "$1" = "abortOnError" ] || [ "$1" = "abortOnFatal" ]; then
				if [ "$1" = "abortOnFatal" ]; then
					if grep 'does not exist' "${tempdir}/stderr" >/dev/null 2>&1 ; then
						return 0
					fi
				fi
				abort "
PostgreSQL error: ${RES}
trying to run: $2
using $3 user${errComment}"
			fi
			return 3
		fi
	else
		RES=`cat "${tempdir}/stdout"`
		test -n "${RES}" && test "$1" = "abortOnOutput" && abort "
PostgreSQL unexpected output: ${RES}
trying to run: $2
using $3 user${errComment}"
		return 0
	fi
}

pgSuper()
{
	ARGS="--host=${VCHOST} --no-password --no-psqlrc --no-readline --no-align --tuples-only --quiet --command="
	psql ${ARGS}"$1" >"${tempdir}/stdout" 2>"${tempdir}/stderr"
	if [ "$#" -ge 3 ]; then
		pgInternal "$2" "$1" default "$3"
	elif [ "$#" -ge 2 ]; then
		pgInternal "$2" "$1" default ""
	else
		pgInternal "" "$1" default ""
	fi
	return $?
}

pg()
{
	ARGS="--host=${VCHOST} --username=${VCUSER} --dbname=${DATABASE} --no-password --no-psqlrc --no-readline --no-align --tuples-only --quiet --command="
	PGPASSWORD="${VCPASSWORD}" psql ${ARGS}"$1" >"${tempdir}/stdout" 2>"${tempdir}/stderr"
	if [ "$#" -ge 3 ]; then
		pgInternal "$2" "$1" ${VCUSER} "$3"
	elif [ "$#" -ge 2 ]; then
		pgInternal "$2" "$1" ${VCUSER} ""
	else
		pgInternal "" "$1" ${VCUSER} ""
	fi
	return $?
}

###############################################################################

${EXISTS} psql >/dev/null 2>&1 || abort "psql command not found"

# Note that we use the external getopt(1) here rather than internal getopts()
# because /bin/sh on some operating systems (e.g. FreeBSD) is not bash.
(getopt -T) >/dev/null 2>&1
exitcode=$?
if [ ${exitcode} -eq 4 ]; then
	# GNU version, supports long options
	GETARGS=$(getopt -o "kuoh:p:" -l "help,skip-certs,unprivileged,off-host,on-host,overwrite,host:,proxy:" -n "$0" -- "$@") 2>/dev/null
	test $? -eq 0 || usage "invalid command-line arg"
	eval set -- "${GETARGS}"
else
	# BSD, cannot validate
	eval set -- "$*"
fi


while [ $# -ne 0 ]; do
	case "$1" in
		--help)
			shift
			usage
			;;
		-k|--skip-certs)
			# Skip certs verification when using wget/curl/whatever.
			# Better leave this undoc.
			shift
			SKIPCERTS=1
			;;
		-h|--host)
			shift
			test -z "$1" && abort "--host requires a value"
			VCHOST="$1"
			shift
			;;
		-u|--unprivileged)
			shift
			UNPRIVILEGED=1
			;;
		--off-host)
			shift
			OFFHOST=1
			;;
		--on-host)
			shift
			OFFHOST=0
			;;
		-o|--overwrite)
			shift
			OVERWRITE=1
			;;
		-p|--proxy)
			shift
			if [ -n "$1" ]; then
				PROXY="$1"
				shift
				if [ "${PROXY}" = "no" ]; then
					PROXY=""
				fi
			fi
			;;
		--)
			shift
			break
			;;
		*)
			shift
			usage
			;;
	esac
done

test "${OFFHOST}" = "" && usage "One of --off-host and --on-host is required"

test -z "${VCHOST}" && usage "db host missing. Use command-line '--host' or env variable 'PGHOST'"

test -z "${OVERWRITE}" && usage

test -n "${VCPASSWORD=}" || abort "Please set the environment variable VCPASSWORD to the password you want to set for the 'vividcortex' user that this script will create."

# Download to temp folder
tempdir=$(mktemp -d /tmp/vividcortex.XXXXXX)
if [ $? -ne 0 ] || [ -z "${tempdir}" ] || [ ! -d "${tempdir}" ]; then
	tempdir=""
	abort "Unable to create temp folder under /tmp"
fi

set_proxy

echo -n "checking connection... "
# connectable?
pgSuper "SELECT 1,2,3"
if [ "${RES}" != "1|2|3" ]; then
	FIRSTERR="${RES}"
	export PGDATABASE=postgres
	pgSuper "SELECT 1,2,3"
fi
test "${RES}" = "1|2|3" || abort "Could not connect to PostgreSQL: ${FIRSTERR}
Make sure your pg_hba.conf allows access E.g. contains \"host all CURRENT_USER_NAME all md5\"
You may need to specify a password by setting the PGPASSWORD environment variable,
or a non-default database in PGDATABASE."
echo "OK"

echo -n "determining database... "
pgSuper "SELECT current_database()" abortOnNotice
DATABASE="${RES}"
test -z "${DATABASE}" && abort "Could not fetch current database"
echo "${DATABASE}"

SUPERUSER="1"
RDS_SUPERUSER=""
echo -n "checking privileges... "
pgSuper "SELECT current_user" abortOnNotice
test -z "${RES}" && abort "Could not determine current user"
PGUSER="${RES}"
pgSuper "SELECT 1 FROM pg_roles WHERE rolname=session_user AND rolsuper='t' LIMIT 1" abortOnError
if [ "${RES}" = "1" ]; then
	if [ "${UNPRIVILEGED}" = "1" ]; then
		SUPERUSER="0"
		echo "${PGUSER}, SUPERUSER, but vividcortex user won't be due to --unprivileged"
	else
		echo "${PGUSER}, SUPERUSER"
	fi
else
	SUPERUSER="0"
	pgSuper "SELECT rolname FROM pg_roles WHERE pg_has_role(oid,'usage') AND rolname IN('rds_superuser','cloudsqlsuperuser') LIMIT 1" abortOnError
	if [ "${RES}" != "" ]; then
		if [ "${UNPRIVILEGED}" = "1" ]; then
			if [ "${PGUSER}" = "rds_superuser" ] || [ "${PGUSER}" = "postgres" ]; then
				echo "${PGUSER}, but vividcortex user won't be due to --unprivileged"
			else
				echo "${PGUSER}"
			fi
		else
			RDS_SUPERUSER="${RES}"
			test "${PGUSER}" = "${RDS_SUPERUSER}" && echo "${PGUSER}" || echo "${PGUSER} (${RDS_SUPERUSER})"
		fi
	else
		abort "
Current user, ${PGUSER}, isn't a SUPERUSER nor has rds_superuser/cloudsqlsuperuser role."
	fi
fi

echo -n "checking version... "
pgSuper "SHOW server_version" abortOnNotice # "8.4.9.1-whatever", "10beta3", "10.b"
RES=`echo "${RES}" | sed 's/[^0-9\.].*$//' | sed -r 's/\.+$//'` # "8.4.9.1", "10", "10"
PGMAJMINPT=`echo "${RES}.0.0" | sed -r 's/^([0-9]+\.[0-9]+\.[0-9]+).*/\1/'` # "8.4.9", "10.0.0"
PGMAJOR=`echo "${PGMAJMINPT}" | sed 's/\..*//'` # "8", "10"
PGMINOR=`echo "${PGMAJMINPT}" | sed -r 's/^[^\.]+\.//' | sed -r 's/\..+//' | sed -e :a -e 's/^.$/0&/;ta'` # "04", "00"
PGPOINT=`echo "${PGMAJMINPT}" | sed -r 's/^[^\.]+\.[^\.]+\.//' | sed -r 's/\..+//' | sed -e :a -e 's/^.$/0&/;ta'` # "09", "00"
PGVER="${PGMAJOR}${PGMINOR}${PGPOINT}" # "80409", "100000"
if [ "${SUPERUSER}" = "0" ]; then
	PGFUNCVER=""
	test "${PGVER}" -ge "80400" && PGFUNCVER="84"
	test "${PGVER}" -ge "90000" && PGFUNCVER="90"
	test "${PGVER}" -ge "90200" && PGFUNCVER="92"
	test "${PGVER}" -ge "90400" && PGFUNCVER="94"
	test "${PGVER}" -ge "90600" && PGFUNCVER="96"
	test "${PGVER}" -ge "100000" && PGFUNCVER="100"
	test "${PGVER}" -ge "130000" && PGFUNCVER="130"
	if [ "${PGFUNCVER}" != "" ]; then
		echo "${RES} >= 8.4; compatible with stored functions v${PGFUNCVER}"
	else
		if [ "${OFFHOST}" = "1" ]; then
			abort "
PostgreSQL version ${RES} = ${PGMAJMINPT} < 8.4.0"
		else
			PGFUNCVER="84"
			echo "${RES} < 8.4; will attempt to use stored functions v${PGFUNCVER}"
		fi
	fi
	if [ "${PGFUNCVER}" != "" ]; then
		echo -n "downloading SQL stored functions... "
		getfile "https://docs.vividcortex.com/create-stat-functions-v${PGFUNCVER}.sql" "${tempdir}/funcs.tmp"
		test -s "${tempdir}/funcs.tmp" || abort "
Cannot download https://docs.vividcortex.com/create-stat-functions-v${PGFUNCVER}.sql"
		grep -v -i "CREATE SCHEMA vividcortex" "${tempdir}/funcs.tmp" > "${tempdir}/funcs.sql"
		echo "OK"
	fi
else
	echo "${RES}; will use SUPERUSER instead of stored functions"
fi

if [ "${OFFHOST}" = "1" ]; then
	echo -n "testing pg_stat_statements module... "
	if [ "${PGVER}" -ge "90100" ]; then
		pgSuper "SELECT 1 FROM pg_available_extensions WHERE name='pg_stat_statements' LIMIT 1" abortOnError
		test "${RES}" = "1" || abort "
pg_stat_statements not found in pg_available_extensions.
Please verify your postgres.conf contains the line: shared_preload_libraries = 'pg_stat_statements'"
	else
		abort "
off-host mode requires PostgreSQL 9.1 or newer"
	fi

	pgSuper "SHOW pg_stat_statements.track" || abort "
pg_stat_statements.track not found.
Please verify your postgres.conf contains the line: shared_preload_libraries = 'pg_stat_statements'
Original error: ${RES}"
	test -z "${RES}" && abort "
pg_stat_statements.track not found.
Please verify your postgres.conf contains the line: shared_preload_libraries = 'pg_stat_statements'"

	echo "OK"
fi

echo -n "dropping vividcortex schema and user... "
pgSuper "DROP SCHEMA IF EXISTS vividcortex CASCADE" abortOnError
pgSuper "REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM \"${VCUSER}\" CASCADE"
pgSuper "REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM \"${VCUSER}\" CASCADE"
pgSuper "REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM \"${VCUSER}\" CASCADE"
pgSuper "REVOKE ALL PRIVILEGES ON SCHEMA public FROM \"${VCUSER}\" CASCADE"
pgSuper "REVOKE ALL PRIVILEGES ON DATABASE ${DATABASE} FROM \"${VCUSER}\" CASCADE"
pgSuper "ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON SEQUENCES FROM \"${VCUSER}\" CASCADE"
pgSuper "ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON FUNCTIONS FROM \"${VCUSER}\" CASCADE"
pgSuper "ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON TABLES FROM \"${VCUSER}\" CASCADE"
pgSuper "REASSIGN OWNED BY \"${VCUSER}\" TO \"${PGUSER}\""
pgSuper "DROP ROLE IF EXISTS \"${VCUSER}\"" abortOnError "There's an existing \"${VCUSER}\" user that was created using a different parent role, and this script can't safely drop it.
You can re-run this script with the former parent role (see above), or remove the dependency, after carefully checking objects, with DROP OWNED BY \"${VCUSER}\";"
if [ $? -ge 3 ]; then
	FIRSTERR="${RES}"
	pgSuper "SELECT 1 FROM pg_roles WHERE rolname='${VCUSER}' LIMIT 1" abortOnError
	test "${RES}" = "1" && abort "
cannot drop ${VCUSER} role: ${FIRSTERR}"
fi
echo "OK"

echo -n "creating vividcortex user... "
if [ "${SUPERUSER}" = "1" ]; then
	pgSuper "CREATE ROLE \"${VCUSER}\" SUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN PASSWORD '${VCPASSWORD}'" abortOnOutput
else
	# Pivotal Greenplum 5.0.0-beta.4: NOTICE:  resource queue required -- using default resource queue "pg_default"
	pgSuper "CREATE ROLE \"${VCUSER}\" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN PASSWORD '${VCPASSWORD}'" abortOnError
	if [ "${RDS_SUPERUSER}" != "" ]; then
		pgSuper "GRANT ${RDS_SUPERUSER} TO \"${VCUSER}\"" abortOnError
	fi
	pgSuper "GRANT \"${VCUSER}\" TO \"${PGUSER}\"" abortOnError
	pgSuper "GRANT CONNECT ON DATABASE ${DATABASE} TO \"${VCUSER}\"" abortOnOutput
	pgSuper "GRANT USAGE ON SCHEMA public TO \"${VCUSER}\"" abortOnOutput
	if [ "${PGVER}" -ge "90000" ]; then
		pgSuper "GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"${VCUSER}\"" abortOnError
		pgSuper "GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO \"${VCUSER}\"" abortOnOutput
		pgSuper "GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO \"${VCUSER}\"" abortOnOutput
		pgSuper "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO \"${VCUSER}\"" abortOnOutput
		pgSuper "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO \"${VCUSER}\"" abortOnOutput
		# we don't support EXPLAIN on PostgreSQL < 9.0 anyway-- due to lack of JSON
	fi
fi
echo "OK"

echo -n "creating vividcortex schema... "
pgSuper "CREATE SCHEMA vividcortex AUTHORIZATION \"${VCUSER}\"" abortOnOutput
pgSuper "SELECT 1 FROM pg_namespace WHERE nspname='vividcortex' LIMIT 1" abortOnError
test "${RES}" = "1" || abort "Error: CREATE SCHEMA failed (superuser)"
pg "SELECT 1 FROM pg_namespace WHERE nspname='vividcortex' LIMIT 1" abortOnError
test "${RES}" = "1" || abort "Error: CREATE SCHEMA failed (user)"
pg "CREATE TABLE vividcortex.dummy (x integer CONSTRAINT pk PRIMARY KEY)" abortOnError
pg "SELECT 1 FROM pg_tables WHERE schemaname='vividcortex' AND tablename='dummy' AND tableowner='${VCUSER}' LIMIT 1" abortOnError
test "${RES}" = "1" || abort "Error: CREATE TABLE failed (pg_tables)"
pg "DROP TABLE vividcortex.dummy" abortOnError
echo "OK"

echo -n "testing vividcortex user... "
# basic connect
pg "SELECT 1,2,3" abortOnError
test "${RES}" = "1|2|3" || abort "Could not connect to PostgreSQL: ${RES}
Make sure your pg_hba.conf allows access E.g. contains \"host all ${VCUSER} all md5\""
# privileges
pg "SELECT 1 FROM pg_roles WHERE pg_has_role(oid,'usage') AND (rolsuper='t' OR rolname IN('rds_superuser','cloudsqlsuperuser')) LIMIT 1" abortOnError
if [ "${RES}" != "1" ]; then
	if [ "${RDS_SUPERUSER}" != "" ] || [ "${SUPERUSER}" = "1" ]; then
		abort "Error: CREATE ROLE didn't set ${VCUSER} to SUPERUSER/rds_superuser/cloudsqlsuperuser"
	fi
fi

if [ "${OFFHOST}" = "1" ]; then
	# Enable pg_stat_statements for current user if we'll work as superuser.
	# Otherwise do it for parent as we'll use functions with SECURITY DEFINER.
	pgExt="pg"
	test "${SUPERUSER}" = "1" || pgExt="pgSuper"
	if [ "${PGVER}" -ge "90100" ]; then
		${pgExt} "CREATE EXTENSION IF NOT EXISTS pg_stat_statements" abortOnError
		${pgExt} "SELECT COUNT(*) FROM pg_stat_statements" abortOnNotice
	else
		${pgExt} "SELECT COUNT(*) FROM pg_stat_statements" abortOnNotice "You may need to load pg_stat_statements.sql, see https://www.postgresql.org/docs/8.4/static/pgstatstatements.html"
	fi
	test "${RES}" = "0" && abort "
Unexpected empty result from SELECT COUNT(*) FROM pg_stat_statements"
fi
echo "OK"

if [ "${SUPERUSER}" = "0" ]; then
	echo -n "loading SQL stat functions... "
	funcList="get_processlist() get_lockmetrics_v2() get_blockers(array[0]) get_dbsizes() get_schemasizes() get_tablesizes() get_indexsizes()"
	if [ "${OFFHOST}" = "1" ]; then
		funcList="${funcList} get_stat_query('') *get_stat_query_pk(0,0,0) *get_stat_statements() *get_stat_statements_pk() get_stat_statements_grouped() get_stat_activity() qm_warmup() *qm_warmup_pk()"
		pgSuper "`cat \"${tempdir}/funcs.sql\"`" abortOnOutput
	else
		pgSuper "`sed '/off-host\smonitoring/q' \"${tempdir}/funcs.sql\"`" abortOnOutput
	fi
	pg "SELECT * FROM vividcortex.get_func_version()" abortOnNotice
	echo "${RES}" | grep "^PG${PGFUNCVER}-20[0-9]\+$" >/dev/null 2>&1 || echo "Expected get_func_version to return PG${PGFUNCVER}-20nn, and got ${RES}"
	echo "OK, ${RES}"
	echo -n "Testing functions... "
	for func in ${funcList}; do
		mandatory=`echo "${func}" | cut -c 1`
		if [ "${mandatory}" = "*" ]; then
			func=`echo "${func}" | cut -c 2-`
			pg "SELECT 1 FROM vividcortex.${func}" abortOnFatal
		else
			pg "SELECT 1 FROM vividcortex.${func}" abortOnNotice
		fi
	done
	echo "OK"
fi

reset_tempdir

echo
echo "Success"
echo
exit 0