#!/bin/sh
# $Id: setup 643 2016-08-03 22:40:45Z bhockney $
# Webfwlog (C) 2003-2016 by Bob Hockney <zeus@ix.netcom.com>
#
# PostgreSQL setup script for webfwlog
#

DB="PostgreSQL"
PROG="psql"

SERVER="localhost"
PORT="5432"
ADMIN_USER="postgres"
ADMIN_PASS=""
USER="webfwlog"
PASS="password"
DATABASE="webfwlog"
WFWL_SCHEMA="webfwlog"
USE_ULOG=0
ULOG_SCHEMA="ulogd"
ULOG_TABLE="ulog"
ULOG_IP_ADDR=""
ULOG_TABLE_IS_VIEW=0
ULOG_CREATE_VIEW=""
ULOG_VIEW=""
ULOG_VIEW_SCHEMA=""
ULOG_ADD_LOCAL_HOSTNAME=0
ULOG_TABLE_SPLIT=0
ULOGD_USER="ulogd"
USE_SNORT=0
SNORT_SCHEMA="public"
SNORT_SCHEMA_VIEW="webfwlog"
SNORT_TABLE="event"
SNORT_VIEW="webfwlog_snort"

HAVE_DATABASE=0
HAVE_SCHEMAS=0
HAVE_WFWL_SCHEMA=0
HAVE_WFWL_REPORTS=0
HAVE_WFWL_HOSTNAMES=0
HAVE_WFWL_SERVICES=0
HAVE_ULOG_SCHEMA=0
HAVE_ULOG_TABLE=0
HAVE_ULOG_LOCAL_HOSTNAME=0
HAVE_SNORT_VIEW_SCHEMA=0
HAVE_SNORT_VIEW=0
HAVE_SNORT_TABLE=0
COPY_WFWL_TABLES=0
GRANTS=0
ADD_EXAMPLES=0
EXAMPLES_DIR="../examples"
OUTFILE="pgsql_setup"

if (printf "")
then
	ECHO="printf %b"
else
	echo
	echo "The printf program could not be found."
	echo "printf is required in order to run setup."
	echo
	exit 1
fi

GREP="grep"
AWK="awk"
SED="sed"
CUT="cut"
CAT="cat"
TR="tr"
WC="wc"
LS="ls"

SCRIPT=""

if test -d scripts
then :
else
	$ECHO "Scripts directory not found\n"
	$ECHO "Please change the working directory to the directory containing setup\n"
	exit 1 
fi

# test client program existance
if ($PROG --version) > /dev/null 2>&1
then :
else
	$ECHO "Client program $PROG not found\n"
	$ECHO "Exiting...\n"
	exit 1
fi

get_param () {
	read N
	if test -n "$N"
	then
		RET="$N"
	else
		RET="$1"
	fi
	$ECHO "$RET"
	return 0
}

get_yes_or_no () {
	read N
	if test -z "$N"
	then
		RET=1
	else
		N=`$ECHO "$N" | $SED 's/^[Yy].*/y/'`
		if test "$N" = "y"
		then
			RET=1
		else
			RET=0
		fi
	fi
	$ECHO "$RET"
	return 0
}

test_indexes () {
	$ECHO "$1\n" | while read N
	do
		if test $HAVE_SCHEMAS -eq 1
		then
			N=`$ECHO "$N" | $SED -e 's/^[^|]*| *\([^ ]*\).*$/\1/'`
		else
			N=`$ECHO "$N" | $SED -e 's/^ *\([^ ]\).*$/\1/'`
		fi
		if test -z "$N"; then continue; fi;

		if test "$2" = "$N"
		then
			$ECHO "0"
			return
		fi
	done

	return
}

# Test for existence of index name on table, and returns command to add it
# if it doesn't exist
#
# Call with table_name index_name [index_name ...]
add_indexes () {
	if test $HAVE_SCHEMAS
	then
		S="`$ECHO "$1"|cut -d. -f1`."
		T="`$ECHO "$1"|cut -d. -f2`"
	else
		S=""
		T="$1"
	fi
	DEF="`$PROG -tc "\di $S" 2> /dev/null`" > /dev/null 2>&1
	shift
	for x in $@
	do
		if test -z "`test_indexes "$DEF" "$x"`"
		then
			case "$x" in
				webfwlog_code)
					$ECHO "CREATE UNIQUE INDEX $x ON ${S}$T (code);\n"
				;;
				${T}_ip_saddr | ${T}_ip_daddr)
					$ECHO "CREATE INDEX $x ON ${S}$T (${x#${T}_}$ULOG_IP_ADDR);\n"
				;;
				${T}_?port)
					$ECHO "CREATE INDEX $x ON ${S}$T ($x);\n"
				;;
				*)
					$ECHO "CREATE INDEX $x ON ${S}$T (${x#${T}_});\n"
				;;
			esac
		fi
	done

	return 0;

}

# Test for existence of index name on table, and returns command to drop it
# if it does exist
#
# Call with schema_name index_name [index_name ...]
drop_indexes () {
	if test $HAVE_SCHEMAS
	then
		S="${1}."
	else
		S=""
	fi
	DEF=`$PROG -tc "\di $S" 2> /dev/null` > /dev/null 2>&1
	shift
	for x in $@
	do
		if test -n "`test_indexes "$DEF" "$x"`"
		then
			$ECHO "DROP INDEX ${S}$x;\n"
		fi
	done

	return 0;

}

$ECHO "
Welcome to the webfwlog setup program for $DB.  You will be prompted
to answer some questions and for input needed to set up your $DB server
for use with webfwlog.  You will also be prompted before any changes are made.

PREREQUISITES

Before running this script you should have your $DB server installed and
running, and you will need to have login credentials for an admin user
for $DB such as 'postgres' or 'pgsql'.

Also, if you want to use webfwlog with database logs you need to set up your
logging program to use your $DB server before setting up webfwlog.  See the
documentation for your logging program for details on setting up it up for
$DB.


First, the location of the $DB server and the admin user login
credentials.  If the server is not on localhost then enter the host name or
IP address where the $DB server is running.
\n"

# get database server location
# get admin user credentials
$ECHO "Enter location of $DB server (<cr>=$SERVER): "
SERVER="`get_param "$SERVER"`"

if test "$SERVER" != "localhost"
then
	$ECHO "Enter port for $DB server (<cr>=$PORT): "
	PORT="`get_param "$PORT"`"
fi

$ECHO "Enter $DB admin user (<cr>=$ADMIN_USER): "
ADMIN_USER="`get_param "$ADMIN_USER"`"

$ECHO "Enter $DB admin user password (<cr>=<blank>): "
stty -echo
ADMIN_PASS="`get_param "$ADMIN_PASS"`"
stty echo
$ECHO "\n"

# test database server existence and version
if test "$SERVER" != "localhost"
then
	export PGHOST=$SERVER
	export PGPORT=$PORT
fi
export PGUSER=$ADMIN_USER
export PGPASSWORD=$ADMIN_PASS

if VERSION=`$PROG -t -c "SELECT version() AS version" template1`
then :
else
	$ECHO "\n"
	$ECHO "Could not get $DB server version\n"
	$ECHO "Is the server running on $SERVER?\n"
	$ECHO "Are the credentials for $DB admin user \`$ADMIN_USER\` correct?\n"
	$ECHO "Exiting...\n\n"
	exit 1
fi

VERSION=`$ECHO "$VERSION" |$SED -e 's/^ *PostgreSQL \([^ ]*\) .*$/\1/'`

$ECHO "\n"
$ECHO "$DB server version $VERSION found on $SERVER!\n"

MAJOR=`$ECHO "$VERSION\n" | $CUT -d. -f1`
MINOR=`$ECHO "$VERSION\n" | $CUT -d. -f2`
EXTRA=`$ECHO "$VERSION\n" | $CUT -d. -f3 | $SED -e 's/\(^[0-9]\{1,\}\).*/\1/'` 

if test $MAJOR -lt 7 || test $MAJOR -eq 7 -a $MINOR -lt 1
then
	$ECHO "\n"
	$ECHO "$DB version >= 7.1 is required for use with webfwlog\n"
	$ECHO "Exiting...\n\n"
	exit 1
fi

# query re: data layout desired (database and table names)
if ($PROG -c "SELECT count(*) FROM pg_catalog.pg_namespace" template1) > /dev/null 2>&1
then
	HAVE_SCHEMAS=1
fi

$ECHO "
Enter the database you want to use with webfwlog.  If the database does not
exist it will be created.  Note that if you are using webfwlog with database
logs the same database must be used for webfwlog's tables and for the logs.
\n"
if test $HAVE_SCHEMAS -eq 0
then
$ECHO "
If the database exists and existing webfwlog tables are found they will be
used, otherwise new webfwlog tables will be created in this database.
\n"
fi

$ECHO "Enter database for webfwlog (<cr>=$DATABASE): "
DATABASE="`get_param "$DATABASE"`"

if ($PROG -c "SELECT 1;" $DATABASE) > /dev/null 2>&1
then 
	$ECHO "\n"
	$ECHO "Using existing database $DATABASE for webfwlog\n"
	HAVE_DATABASE=1
else
	$ECHO "\n"
	$ECHO "Creating new database $DATABASE for webfwlog\n"
	HAVE_DATABASE=0
fi

export PGDATABASE=$DATABASE

if test $HAVE_SCHEMAS -eq 1
then
$ECHO "
Enter the schema you want to use for the webfwlog tables.  Your $DB
server supports schemas and it is recomended to put the webfwlog tables in a
separate schema such as 'webfwlog'.  If the schema does not exist it will be
created.  If the schema exists and existing webfwlog tables are found they
will be used, otherwise new webfwlog tables will be created in this database.
\n"
	$ECHO "Enter the schema to use for the webfwlog tables (<cr>=$WFWL_SCHEMA): "
	WFWL_SCHEMA="`get_param "$WFWL_SCHEMA"`"

	if test $HAVE_DATABASE -eq 1 -a -n "`$PROG -tc "SELECT * FROM pg_catalog.pg_namespace WHERE nspname='$WFWL_SCHEMA'" 2> /dev/null`"
	then 
		$ECHO "\n"
		$ECHO "Using existing schema $WFWL_SCHEMA for webfwlog tables\n"
		HAVE_WFWL_SCHEMA=1
	else
		$ECHO "\n"
		$ECHO "Creating new schema $WFWL_SCHEMA for webfwlog tables\n"
		HAVE_WFWL_SCHEMA=0
	fi
fi

if test $HAVE_SCHEMAS -eq 1
then
	WFWL_REPORTS="${WFWL_SCHEMA}.reports"
	WFWL_HOSTNAMES="${WFWL_SCHEMA}.hostnames"
	WFWL_SERVICES="${WFWL_SCHEMA}.services"
else
	WFWL_REPORTS="reports"
	WFWL_HOSTNAMES="hostnames"
	WFWL_SERVICES="services"
fi

# query re: existing webfwlog tables
if test $HAVE_DATABASE -ne 0
then
	if ($PROG -c "SELECT count(*) FROM $WFWL_REPORTS;") > /dev/null 2>&1
	then 
		$ECHO "Using existing table $WFWL_REPORTS for webfwlog report definitions\n"
		HAVE_WFWL_REPORTS=1
	fi

	if ($PROG -c "SELECT count(*) FROM $WFWL_HOSTNAMES;") > /dev/null 2>&1
	then 
		$ECHO "Using existing table $WFWL_HOSTNAMES for webfwlog hostnames cache\n"
		HAVE_WFWL_HOSTNAMES=1
	fi

	if ($PROG -c "SELECT count(*) FROM $WFWL_SERVICES;") > /dev/null 2>&1
	then 
		$ECHO "Using existing table $WFWL_SERVICES for webfwlog services cache\n"
		HAVE_WFWL_SERVICES=1
	fi
fi

# test existing webfwlog reports table for last accessed/last saved
# reset indexes and column defaults on all existing webfwlog tables
# create webfwlog database/tables if they do not exist
if test $HAVE_DATABASE -eq 0
then
	SCRIPT="${SCRIPT}--\n-- Create new database $DATABASE\n--\nCREATE DATABASE $DATABASE;\n--\n"
fi

SCRIPT="${SCRIPT}\\\\connect $DATABASE $ADMIN_USER\n"

if test $HAVE_SCHEMAS -eq 1 -a $HAVE_WFWL_SCHEMA -eq 0
then
	SCRIPT="${SCRIPT}--\n-- Create new schema $WFWL_SCHEMA\n--\nCREATE SCHEMA $WFWL_SCHEMA;\n"
fi

if test $HAVE_WFWL_REPORTS -eq 0
then
	$ECHO "Creating new table $WFWL_REPORTS for webfwlog report definitions\n"
	SCRIPT="$SCRIPT`$AWK "{ if (NR != 1) print | \\\"$SED 's/\\\$WFWL_SCHEMA.reports/$WFWL_REPORTS/'\\\" }" scripts/wfwl_reports_create`\n"
fi

if test $HAVE_WFWL_HOSTNAMES -eq 0
then
	$ECHO "Creating new table $WFWL_HOSTNAMES for webfwlog hostnames cache\n"
	SCRIPT="$SCRIPT`$AWK "{ if (NR != 1) print | \\\"$SED 's/\\\$WFWL_SCHEMA.hostnames/$WFWL_HOSTNAMES/'\\\" }" scripts/wfwl_hostnames_create`\n"
fi

if test $HAVE_WFWL_SERVICES -eq 0
then
	$ECHO "Creating new table $WFWL_HOSTNAMES for webfwlog hostnames cache\n"
	SCRIPT="$SCRIPT`$AWK "{ if (NR != 1) print | \\\"$SED 's/\\\$WFWL_SCHEMA.services/$WFWL_SERVICES/'\\\" }" scripts/wfwl_services_create`\n"
fi

R="$WFWL_REPORTS"

if test $HAVE_WFWL_REPORTS -ne 1 || ($PROG -c "SELECT last_saved, last_accessed FROM $R;") > /dev/null 2>&1
then :
else
	SCRIPT="$SCRIPT`$AWK "{ if (NR != 1) print | \\\"$SED 's/\\\$WFWL_SCHEMA.reports/$R/'\\\" }" scripts/wfwl_reports_addcol`\n"
fi

# check reports table for unique constraint on code column
if test $HAVE_WFWL_REPORTS -eq 1
then
	SCRIPT_ADD="`add_indexes "$WFWL_REPORTS" "webfwlog_code"`"
	if test -n "$SCRIPT_ADD" 
	then
		SCRIPT="${SCRIPT}--\n-- Add unique constraint on code column of reports table\n--\n"
		SCRIPT="${SCRIPT}$SCRIPT_ADD\n"
	fi
fi

if test $HAVE_SCHEMAS -eq 1
then
	W_SCHEMA="$WFWL_SCHEMA."
else
	W_SCHEMA=""
fi

# drop / add functions
SCRIPT="${SCRIPT}--\n-- Add / replace postgresql inet address conversion functions\n--\n"

if ($PROG -c "SELECT ${W_SCHEMA}webfwlog_ntoa(0::int8);") > /dev/null 2>&1
then
	SCRIPT="${SCRIPT}DROP FUNCTION ${W_SCHEMA}webfwlog_ntoa (int8);\n"
fi
if ($PROG -c "SELECT ${W_SCHEMA}webfwlog_aton('1.2.3.4'::inet);") > /dev/null 2>&1
then
	SCRIPT="${SCRIPT}DROP FUNCTION ${W_SCHEMA}webfwlog_aton (inet);\n"
fi
if ($PROG -c "SELECT ${W_SCHEMA}webfwlog_aton('1.2.3.4'::text);") > /dev/null 2>&1
then
	SCRIPT="${SCRIPT}DROP FUNCTION ${W_SCHEMA}webfwlog_aton (text);\n"
fi
if ($PROG -c "SELECT ${W_SCHEMA}next_part('1.2.3'::text);") > /dev/null 2>&1
then
	SCRIPT="${SCRIPT}DROP FUNCTION ${W_SCHEMA}next_part (text);\n"
fi

SCRIPT="$SCRIPT`$AWK "{ if (NR != 1) print | \\\"$SED 's/\\\$WFWL_SCHEMA./$W_SCHEMA/g'\\\" }" scripts/wfwl_functions`\n"

# change column type to inet to support ipv6
if test $HAVE_WFWL_HOSTNAMES -eq 1 && test $MAJOR -gt 7 -o $MAJOR -eq 7 -a $MINOR -ge 4 \
	&& test -n "`$PROG -c "\d $WFWL_HOSTNAMES" $DATABASE|$GREP ' ip_addr .*bigint'`"
then
	SCRIPT="${SCRIPT}\n--\n-- Change ip_addr column type to inet to support IPv6\n--\n"
	SCRIPT="${SCRIPT}BEGIN;\n"
	SCRIPT="${SCRIPT}  ALTER TABLE $WFWL_HOSTNAMES ADD COLUMN ip_addr_inet inet;\n"
	SCRIPT="${SCRIPT}  UPDATE $WFWL_HOSTNAMES SET ip_addr_inet=$WFWL_SCHEMA.webfwlog_ntoa(ip_addr)::inet;\n"
	SCRIPT="${SCRIPT}  ALTER TABLE $WFWL_HOSTNAMES DROP COLUMN ip_addr;\n"
	SCRIPT="${SCRIPT}  ALTER TABLE $WFWL_HOSTNAMES RENAME COLUMN ip_addr_inet TO ip_addr;\n"
	SCRIPT="${SCRIPT}  ALTER TABLE $WFWL_HOSTNAMES ALTER COLUMN ip_addr SET NOT NULL;\n"
	SCRIPT="${SCRIPT}  CREATE UNIQUE INDEX ip_addr ON $WFWL_HOSTNAMES (ip_addr);\n"
	SCRIPT="${SCRIPT}  UPDATE $WFWL_HOSTNAMES SET ip_addr=ip_addr;\n"
	SCRIPT="${SCRIPT}COMMIT;\n"
	SCRIPT="${SCRIPT}VACUUM FULL $WFWL_HOSTNAMES;\n"
fi

# skip ulog and snort setup if creating new database
# webfwlog tables and data tables must be in same database
if test $HAVE_DATABASE -eq 1
then

# ulog
$ECHO "
If you want to use webfwlog with ulog data answer yes to the next question.
You will then be prompted for the schema (if required) and table name for your
logs.

NOTE: A ulog table must already exist before it can be set up for use with
webfwlog.  See the documentation of ulogd for more information on setting
up a ulog table.

If you do not want to use webfwlog with ulog then answer no to this question.
\n"

$ECHO "Do you want to set up webfwlog for use with ulog data? [Y/n]: "
USE_ULOG="`get_yes_or_no`"

if test $USE_ULOG -eq 1 -a $HAVE_SCHEMAS -eq 1
then
	$ECHO "
Enter the schema containing your ulog table.  Your $DB server
supports schemas and it is recomended to put the ulog table in a separate
schema such as 'ulogd'.  A ulog table must already exist before you can set
up webfwlog for use with ulog data.  See the documentation of ulogd for more
information on setting up a ulog table.
\n"
	$ECHO "Enter schema to use for the ulog tables (<cr>=$ULOG_SCHEMA): "
	ULOG_SCHEMA="`get_param "$ULOG_SCHEMA"`"

	if test $HAVE_DATABASE -eq 1 -a -n "`$PROG -tc "SELECT * FROM pg_catalog.pg_namespace WHERE nspname='$ULOG_SCHEMA' LIMIT 1"`"
	then 
		$ECHO "\n"
		$ECHO "Using existing schema $ULOG_SCHEMA for ulog\n"
		HAVE_ULOG_SCHEMA=1
	fi
fi

if test $HAVE_SCHEMAS -eq 1
then
	U_SCHEMA="$ULOG_SCHEMA."
else
	U_SCHEMA=""
fi

if test $USE_ULOG -eq 1
then
	until test $HAVE_ULOG_TABLE -eq 1
	do
		$ECHO "\n"
		$ECHO "Enter name of ulog table (<cr>=$ULOG_TABLE): "
		ULOG_TABLE="`get_param "$ULOG_TABLE"`"

		if test $HAVE_SCHEMAS -eq 1
		then
			ULOG="$ULOG_SCHEMA.$ULOG_TABLE"
		else
			ULOG="$ULOG_TABLE"
		fi

		if ($PROG -c "SELECT oob_time_sec, tcp_dport FROM $ULOG LIMIT 1;") > /dev/null 2>&1
		then 
			if ($PROG -c "SELECT ip_saddr_str FROM $ULOG LIMIT 1;") > /dev/null 2>&1
			then
				ULOG_IP_ADDR="_str"
			fi
			HAVE_ULOG_TABLE=1
		fi
	done

	#   test existing ulog table for local _hostname and update if needed.
	if test $HAVE_SCHEMAS -eq 1 -a -n \
		"`$PROG -tc "SELECT c.relkind FROM pg_catalog.pg_class c
						LEFT JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
					 WHERE n.nspname='$ULOG_SCHEMA' AND c.relname='$ULOG_TABLE' AND c.relkind='v';" $DATABASE 2>/dev/null`" \
		-o $HAVE_SCHEMAS -eq 0 -a -n \
		"`$PROG -tc "SELECT c.relkind FROM pg_class c
					 WHERE c.relname='$ULOG_TABLE' AND c.relkind='v';" $DATABASE`"
	then
		ULOG_TABLE_IS_VIEW=1
		ULOG_CREATE_VIEW="`$PROG -c "\d+ ${U_SCHEMA}$ULOG_TABLE"|$SED -e 's/\n//'|$AWK "{ if (NR > 3) print }" - \
									|$GREP -v '|'|$TR -ds "\n" " "|$SED -e 's/^View definition: *//'`"
		ULOG_VIEW="$ULOG_TABLE"
		ULOG_VIEW_SCHEMA="$ULOG_SCHEMA"
		ULOG="`$ECHO "$ULOG_CREATE_VIEW"|$SED -e 's/^.* FROM (* *\([^ ]*\).*$/\1/'`"
		if test $HAVE_SCHEMAS -eq 1
		then
			ULOG_SCHEMA="`$ECHO "$ULOG"|$CUT -d. -f1`"
			ULOG_TABLE="`$ECHO "$ULOG"|$CUT -d. -f2`"
		else
			ULOG_TABLE="$ULOG"
		fi
	fi

	if test $ULOG_TABLE_IS_VIEW -eq 1
	then
		$ECHO "\nRelation ${U_SCHEMA}$ULOG_VIEW is a view\n"
		$ECHO "Underlying table is $ULOG\n"
		$ECHO "\nChecking underlying table $ULOG ... "
	else
		$ECHO "\nUsing ulog table $ULOG\n"
		$ECHO "\nChecking table $ULOG ... "
	fi

	if ($PROG -c "SELECT local_hostname FROM $ULOG LIMIT 1;") > /dev/null 2>&1
	then
		HAVE_ULOG_LOCAL_HOSTNAME=1
	fi

	if ! ($PROG -c "SELECT tcp_dport FROM $ULOG LIMIT 1") > /dev/null 2>&1
	then
		ULOG_TABLE_SPLIT=1
	fi

	SCRIPT_ADD="`drop_indexes $ULOG_SCHEMA saddr daddr udp_s_service udp_d_service tcp_s_service tcp_d_service \
					saddr_$ULOG_TABLE daddr_$ULOG_TABLE udp_s_$ULOG_TABLE udp_d_$ULOG_TABLE tcp_s_$ULOG_TABLE tcp_d_$ULOG_TABLE`" 
	if test -n "$SCRIPT_ADD"
	then
		SCRIPT="${SCRIPT}--\n-- Drop old index names on $ULOG_TABLE\n--\n$SCRIPT_ADD\n"
	fi

	I="${ULOG_TABLE}_ip_saddr ${ULOG_TABLE}_ip_daddr ${ULOG_TABLE}_ip_protocol ${ULOG_TABLE}_oob_time_sec" 

	if ($PROG -c "SELECT oob_family FROM $ULOG LIMIT 1") > /dev/null 2>&1
	then
		I="${I} ${ULOG_TABLE}_oob_family"
	fi

	if test $ULOG_TABLE_SPLIT -eq 0
	then
		I="${I} ${ULOG_TABLE}_tcp_sport ${ULOG_TABLE}_tcp_dport ${ULOG_TABLE}_udp_sport ${ULOG_TABLE}_udp_dport"
	fi

	SA="`add_indexes $ULOG $I`"
	if test -n "$SA"; then SCRIPT_ADD="$SA\n"; fi


	if test $ULOG_TABLE_SPLIT -eq 1
	then
		SA="`add_indexes $ULOG_SCHEMA.tcp tcp_sport tcp_dport`"
		if test -n "$SA"; then SCRIPT_ADD="${SCRIPT_ADD}$SA\n"; fi
		SA="`add_indexes $ULOG_SCHEMA.udp udp_sport udp_dport`"
		if test -n "$SA"; then SCRIPT_ADD="${SCRIPT_ADD}$SA\n"; fi
	fi

	if test -n "$SCRIPT_ADD" 
	then
		SCRIPT="${SCRIPT}--\n-- Add indexes on Ulog table $ULOG\n--\n$SCRIPT_ADD"
	fi

	$ECHO "Done!\n"

	if test $HAVE_ULOG_LOCAL_HOSTNAME -eq 0
	then
		$ECHO "
By default, ulog does not record the name of the logging host, but this can
be logged with the LOCAL plugin of ulogd into the local_hostname column, which
will be added if you answer yes to the following question.
\n"
	$ECHO "Do you want to add a local_hostname column? [Y/n]: "
	ULOG_ADD_LOCAL_HOSTNAME="`get_yes_or_no`"
	fi

	if test $ULOG_ADD_LOCAL_HOSTNAME -eq 1
	then
		SCRIPT="${SCRIPT}--\n-- Add local_hostname column to table $ULOG\n--\nBEGIN;\n  ALTER TABLE $ULOG ADD COLUMN local_hostname character varying(32);\n"
		if test $ULOG_TABLE_IS_VIEW -eq 1
		then
			ULOG_CREATE_VIEW="`$ECHO "$ULOG_CREATE_VIEW"|$SED -e "s/^\(SELECT .*\)\( FROM .*\)/CREATE VIEW ${U_SCHEMA}$ULOG_VIEW AS\n  \1, local_hostname\2/"`"
			SCRIPT="${SCRIPT}  --\n  -- Update view ${U_SCHEMA}$ULOG_VIEW for local_hostname column\n  --\n  DROP VIEW ${U_SCHEMA}$ULOG_VIEW;\n"
			SCRIPT="${SCRIPT}  $ULOG_CREATE_VIEW\n"
		fi
		SCRIPT="${SCRIPT}COMMIT;\n"
	fi

	SCRIPT="${SCRIPT}--\n-- Run VACUUM ANALYZE on Ulog table(s)\n--\nVACUUM ANALYZE $ULOG;\n"

	if test $ULOG_TABLE_SPLIT -eq 1
	then
		A=""
		T="`$PROG -c "\dt $U_SCHEMA"`"
		for x in tcp udp icmp icmpv6 mac hwhdr sctp
		do
			if test $HAVE_SCHEMAS -eq 1
			then
				N="`$ECHO "$T" | $AWK   "{ if (NR >3) print }" | $GREP -v '^(' | $SED -e 's/^[^|]*| *\([^ ]*\).*$/\1/'`"
			else
				N="`$ECHO "$T" | $AWK   "{ if (NR >3) print }" | $GREP -v '^(' | $SED -e 's/^ *\([^ ]\).*$/\1/'`"
			fi
			if test -n "`$ECHO "$N" | $GREP "$x"`"
			then
				A="${A} ${U_SCHEMA}$x"
			fi
		done
		for x in $A
		do
			SCRIPT="${SCRIPT}VACUUM ANALYZE $x;\n"
		done
	fi

fi # if test $USE_ULOG -eq 1

# snort
$ECHO "
If you want to use webfwlog with snort data answer yes to the next question.
You will then be prompted for the schema (if required) for your logs.

NOTE: Snort tables must already exist before it they can be set up for use
with webfwlog.  See the documentation of snort for more information on setting
up snort tables.

If you do not want to use webfwlog with snort then answer no to this question.
\n"

$ECHO "Do you want to set up webfwlog for use with snort data? [Y/n]: "
USE_SNORT="`get_yes_or_no`"

if test $USE_SNORT -eq 1
then
	if test $HAVE_SCHEMAS -eq 1
	then
		$ECHO "
Enter the schema you want to use for your snort view for the use of webfwlog.
It is recomended to put the snort view in the same schema as the webfwlog tables.
The schema will be created if it does not exist.
\n"

		$ECHO "Enter schema where you want the snort view for webfwlog (<cr>=$SNORT_SCHEMA_VIEW): "
		SNORT_SCHEMA_VIEW="`get_param "$SNORT_SCHEMA_VIEW"`"

		if test $HAVE_DATABASE -eq 1 -a -n "`$PROG -tc "SELECT * FROM pg_catalog.pg_namespace WHERE nspname='$SNORT_SCHEMA_VIEW' LIMIT 1"`"
		then 
			$ECHO "\n"
			$ECHO "Using existing schema $SNORT_SCHEMA_VIEW for snort view\n"
			HAVE_SNORT_VIEW_SCHEMA=1

		SNORT_VIEW="$SNORT_SCHEMA_VIEW.$SNORT_VIEW"
		fi
	else
		SNORT_VIEW="$SNORT_VIEW"
	fi

	until test $HAVE_SNORT_TABLE -eq 1
	do
		if test $HAVE_SCHEMAS -eq 1
		then
			$ECHO "\nEnter schema containing your snort tables (<cr>=$SNORT_SCHEMA): "
			SNORT_SCHEMA="`get_param "$SNORT_SCHEMA"`"
			SNORT="$SNORT_SCHEMA.$SNORT_TABLE"
		else
			SNORT=$SNORT_TABLE
		fi

		if ($PROG -c "SELECT count(*) FROM $SNORT;") > /dev/null 2>&1
		then 
			$ECHO "\nUsing snort table $SNORT\n"
			HAVE_SNORT_TABLE=1
		else
			$ECHO "\n\aSnort table $SNORT NOT found.\nSnort tables must exist before setting up for use with webfwlog\n"
		fi
	done

	if ($PROG -c "SELECT count(*) FROM $SNORT_VIEW;") > /dev/null 2>&1
	then
		$ECHO "\nReplacing existing snort view $SNORT_VIEW\n"
		HAVE_SNORT_VIEW=1
	else
		$ECHO "\nCreating snort view $SNORT_VIEW\n"
	fi

	SCRIPT="${SCRIPT}--\n-- Add snort view for webfwlog\n--\nBEGIN;\n" 

	if test $HAVE_SCHEMAS -eq 1 -a $HAVE_SNORT_VIEW_SCHEMA -eq 0 \
		-a "$SNORT_SCHEMA_VIEW" != "$WFWL_SCHEMA" -a "$SNORT_SCHEMA_VIEW" != "$ULOG_SCHEMA"
	then 
		SCRIPT="${SCRIPT}CREATE SCHEMA $SNORT_SCHEMA_VIEW;\n"
	fi

	if test $HAVE_SCHEMAS -eq 1
	then
		SCRIPT="${SCRIPT}SET search_path = $SNORT_SCHEMA_VIEW,$SNORT_SCHEMA,\"\$user\",public;\n" 
	fi

	if test $HAVE_SNORT_VIEW -eq 1
	then
		SCRIPT="${SCRIPT}DROP VIEW $SNORT_VIEW;\n" 
	fi

	SCRIPT="$SCRIPT`$AWK "{ if (NR != 1) print | \\\"$SED 's/\\\$SNORT_VIEW/$SNORT_VIEW/g'\\\" }" scripts/snort_view`\n"
	SCRIPT="${SCRIPT}COMMIT;\n" 

	SCRIPT="${SCRIPT}--\n-- Run VACUUM ANALYZE on Snort table(s)\n--\nVACUUM ANALYZE $SNORT;\n"

	A=""
	if test $HAVE_SCHEMAS -eq 1
	then
		T="`$PROG -c "\dt ${SNORT_SCHEMA}."`"
	else
		T="`$PROG -c "\dt"`"
	fi
	for x in sensor iphdr tcphdr udphdr icmphdr signature sig_class
	do
		if test $HAVE_SCHEMAS -eq 1
		then
			N="`$ECHO "$T" | $AWK   "{ if (NR >3) print }" | $GREP -v '^(' | $SED -e 's/^[^|]*| *\([^ ]*\).*$/\1/'`"
		else
			N="`$ECHO "$T" | $AWK   "{ if (NR >3) print }" | $GREP -v '^(' | $SED -e 's/^ *\([^ ]\).*$/\1/'`"
		fi
		if test -n "`$ECHO "$N" | $GREP "^$x$"`"
		then
			A="${A} ${SNORT_SCHEMA}.$x"
		fi
	done
	for x in $A
	do
		SCRIPT="${SCRIPT}VACUUM ANALYZE $x;\n"
	done

fi # if test $USE_SNORT -eq 1

fi # if test $HAVE_DATABASE -eq 1

# set rights
if test $ULOG_TABLE_IS_VIEW -eq 1 -a $ULOG_ADD_LOCAL_HOSTNAME -eq 1 -o $HAVE_SNORT_VIEW -eq 1 
then
	GRANTS=1;
	$ECHO "\n
Your ulog table is a view and you elected to add a local_hostname column,
or you are replacing your snort view. This requires that you reset your
$DB grants to give SELECT privileges to the webfwlog user on the
recreated view.
\n"
else
$ECHO "\n
Answer yes to the following question set the rights for the $DB
user you want to use with webfwlog.  You must do this the first time you set up
webfwlog, and it is recommended to do this if you are upgrading from a previous
version of webfwlog.  You can also use this to change the password for the
$DB user for webfwlog.  Be sure to update your webfwlog.conf file
after setting or changing the webfwlog user's password.
\n"

$ECHO "Do you want to set the $DB user rights? [Y/n]: "
GRANTS="`get_yes_or_no`"
$ECHO "\n"
fi

if test $GRANTS -eq 1
then
	# get webfwlog user credentials
	$ECHO "Enter $DB user for webfwlog (<cr>=$USER): "
	USER="`get_param "$USER"`"

	$ECHO "Enter password for $DB user for webfwlog (<cr>=$PASS): "
	PASS="`get_param "$PASS"`"

	if ($PROG -c "ALTER USER $USER;" template1) > /dev/null 2>&1
	then
		SCRIPT="${SCRIPT}--\n-- Set password for $DB user $USER\n--\n"
		SCRIPT="${SCRIPT}ALTER USER $USER WITH PASSWORD '$PASS';\n"
	else
		SCRIPT="${SCRIPT}--\n-- Create $DB user $USER\n--\n"
		SCRIPT="${SCRIPT}CREATE USER $USER WITH PASSWORD '$PASS';\n"
	fi

	SCRIPT="${SCRIPT}--\n-- Add grants for webfwlog tables\n--\n"
	if test $HAVE_SCHEMAS -eq 1
	then
		SCRIPT="${SCRIPT}GRANT USAGE ON SCHEMA $WFWL_SCHEMA TO $USER;\n"
		SCRIPT="${SCRIPT}GRANT TEMPORARY ON DATABASE $DATABASE TO $USER;\n"
	fi

	SCRIPT="${SCRIPT}GRANT SELECT, INSERT, UPDATE, DELETE ON $WFWL_REPORTS   TO $USER;\n"
	SCRIPT="${SCRIPT}GRANT SELECT, INSERT, UPDATE, DELETE ON $WFWL_HOSTNAMES TO $USER;\n"
	SCRIPT="${SCRIPT}GRANT SELECT, INSERT, UPDATE, DELETE ON $WFWL_SERVICES  TO $USER;\n"

	if test $USE_ULOG -eq 1
	then
		SCRIPT="${SCRIPT}--\n-- Add grants for ulog\n--\n"
		if test $HAVE_SCHEMAS -eq 1
		then
			SCRIPT="${SCRIPT}GRANT USAGE ON SCHEMA $ULOG_SCHEMA TO $USER;\n"
		fi
		if test $ULOG_TABLE_IS_VIEW -eq 1
		then
			SCRIPT="${SCRIPT}GRANT SELECT ON ${U_SCHEMA}$ULOG_VIEW to $USER;\n"			
		else
			SCRIPT="${SCRIPT}GRANT SELECT ON $ULOG to $USER;\n"
		fi
	fi
	
	if test $USE_SNORT -eq 1
	then
		SCRIPT="${SCRIPT}--\n-- Add grants for snort\n--\n"
		if test $HAVE_SCHEMAS -eq 1
		then
			SCRIPT="${SCRIPT}GRANT USAGE ON SCHEMA $SNORT_SCHEMA_VIEW TO $USER;\n"
		fi
		SCRIPT="${SCRIPT}GRANT SELECT ON $SNORT_VIEW to $USER;\n"
	fi

	$ECHO "\n"
	$ECHO "Using $DB user \`$USER\`\n"
fi

# query regarding import of definitions
create_def () {
	$CAT $1 | while read N 
	do
		LINE=`$ECHO "$N" |$SED 's/^ *#.*//'`
		if test -z "$LINE"; then return; fi
		KEY=`$ECHO "$LINE" | $CUT -d= -f1`
		VALUE=`$ECHO "$LINE" | $CUT -d= -f2`
		LEN=`$ECHO "$VALUE"|wc -c`
		LEN=`expr $LEN - 2` 
		$ECHO "s:`$ECHO "$KEY"|$WC -c`:\"$KEY\";s:$LEN:$VALUE;"
	done;
}

get_def () {
	$ECHO "`$GREP ^description $1|$CUT -d= -f2|$SED 's/\"//g'`\n"
	$ECHO "a:`$CAT $1 |$GREP -v "^ *#"|$GREP -v "^$"|$WC -l|$SED -e 's/^[[:space:]]*\([0-9]*\)[[:space:]]*$/\1/'`:{"
	$ECHO "`create_def $1`"}
}

$ECHO "
You can populate the webfwlog reports table with report definitions in the
examples directory by answering yes below.  Existing definitions will not
be overwritten.
\n"
$ECHO "Do you want to use populate the reports table with example reports? [Y/n]: "
ADD_EXAMPLES="`get_yes_or_no`"

if test $ADD_EXAMPLES -eq 1
then
	if test -d $EXAMPLES_DIR
	then
		$ECHO "\nCreating INSERT statements for example reports ... "
		SCRIPT="${SCRIPT}--\n-- Insert example report definitions\n--\n"
		SCRIPT="${SCRIPT}CREATE TEMPORARY TABLE examples AS SELECT * FROM $WFWL_REPORTS WHERE false;\n"
		for x in `$LS $EXAMPLES_DIR`
		do
			if test -f $EXAMPLES_DIR/$x
			then
				DEF=`get_def $EXAMPLES_DIR/$x`
				SCRIPT="${SCRIPT}INSERT INTO examples (code, description, definition, last_saved, last_accessed)\nVALUES ('$x', '`$ECHO "$DEF"|head -n1`', '`$ECHO "$DEF"|$AWK "{ if (NR != 1) print }"`', extract(EPOCH FROM TIMESTAMP '`date`'), extract(EPOCH FROM TIMESTAMP '`date`'));\n"
			fi
		done
		SCRIPT="${SCRIPT}INSERT INTO $WFWL_REPORTS (code, description, definition, last_saved, last_accessed)\n  SELECT s.code, s.description, s.definition, s.last_saved, s.last_accessed\n  FROM examples s LEFT JOIN $WFWL_REPORTS r ON r.code=s.code\n  WHERE r.code IS NULL;\n"
		SCRIPT="${SCRIPT}DROP TABLE examples;\n"
		$ECHO "done!\n"
	else
		$ECHO "Could not find examples directory $EXAMPLES_DIR\n"
	fi
fi

$ECHO "\n"
$ECHO "$DB server location:     $SERVER\n"
if test "$SERVER" != "localhost"
then
	$ECHO "$DB server port:         $PORT\n"
fi
$ECHO "$DB server version:      $VERSION\n"
$ECHO "$DB admin user:          $ADMIN_USER\n"
#$ECHO "$DB admin user password: $ADMIN_PASS\n"
if test $GRANTS -eq 1
then
	$ECHO "$DB webfwlog user:       $USER\n"
	$ECHO "$DB user password:       $PASS\n"
fi
$ECHO "Database:                       $DATABASE\n"
if test $HAVE_SCHEMAS -eq 1
then
	$ECHO "Webfwlog schema:                $WFWL_SCHEMA\n"
fi
if test $USE_ULOG -eq 1
then
	if test $HAVE_SCHEMAS -eq 1
	then
		$ECHO "Ulog schema:                    $ULOG_SCHEMA\n"
	fi
	$ECHO "Ulog table:                     $ULOG_TABLE\n"
fi
if test $ULOG_TABLE_IS_VIEW -eq 1
then
	$ECHO "Ulog view schema:               $ULOG_VIEW_SCHEMA\n"
	$ECHO "Ulog view:                      $ULOG_VIEW\n"
fi
if test $USE_SNORT -eq 1
then
	$ECHO "Snort table:                    $SNORT\n"
	$ECHO "Snort view:                     $SNORT_VIEW\n"
fi
OUTPUT="V"
until test "$OUTPUT" = "q" -o "$OUTPUT" = "Q"
do
	$ECHO "
===========================================================================
The setup script for $DB is now complete.  You can do the following:

- View the script
- Save the script to a file
- Run the script
- Quit

You will be returned to this menu when done
\n"
	$ECHO "What do you want to do? [V/s/r/q]: "
	OUTPUT="`get_param "$OUTPUT"`"

	if test "$OUTPUT" = "v" -o "$OUTPUT" = "V"
	then
		$ECHO "\n" 
		$ECHO "$SCRIPT\n"
		$ECHO "Press CR to continue ..."
		read N
	fi
	
	if test "$OUTPUT" = "s" -o "$OUTPUT" = "S"
	then
		$ECHO "Enter filename (<cr>=$OUTFILE): "
		OUTFILE="`get_param "$OUTFILE"`"
		if test -d `dirname "$OUTFILE"` -a -w `dirname "$OUTFILE"` && touch "$OUTFILE"
		then
			$ECHO "$SCRIPT" > "$OUTFILE"
			$ECHO "\nWrote $DB script $OUTFILE\n"
		else
			$ECHO "\nCannot write to $OUTFILE\n"
			$ECHO "Press CR to continue ..."
			read N
		fi
	fi
	
	if test "$OUTPUT" = "r" -o "$OUTPUT" = "R"
	then
		if ($ECHO "$SCRIPT\n" | $PROG template1)
		then
			$ECHO "\n$DB script completed successfully!\n"
			$ECHO "Press CR to continue ..."
			read N
		else
			$ECHO "\n$DB script failed!\n"
			$ECHO "Press CR to continue ..."
			read N
		fi
	fi
done

exit 0

# vim:set ts=4:
