scripts/sc
f51155cf
 #!/bin/sh
1291221b
 #
 # $Id$
 #
 # sc: ser control; tool for maintaining ser's databases
 #
 
c55b0dfd
 # ser's FIFO server
 SER_FIFO=/tmp/ser_fifo
 # period in which stats are reprinted
 WATCH_PERIOD=2
 
1291221b
 # SQL config
c430ed7b
 SQL_DB=ser
b11ee5d8
 SQL_HOST=localhost
c430ed7b
 SQL_USER=ser
1291221b
 
 # the read-only user for whom password may be stored here
 RO_USER=serro
 RO_PW=47serro11
 
 # binaries
 GENHA1='gen_ha1'
 MYSQL='mysql'
 SER='sr'
 
60cbe72d
 # ACL name verification
 VERIFY_ACL=1
c430ed7b
 ACL_GROUPS="local ld int voicemail free-pstn"
 
 # expiration time for alias table
 if [ -z "$FOREVER" ]; then 
 	FOREVER='2020-05-28 21:32:15'
 fi
60cbe72d
 
b9b54261
 #### SQL names
 
 # Usr Loc Table
 USRLOC=location
1291221b
 USER_COLUMN=user
a9aa2f53
 CALLID_COLUMN=callid
b9b54261
 
 # subscriber table
 TABLE=subscriber
1291221b
 REALM_COLUMN=realm
 HA1_COLUMN=HA1
 HA1B_COLUMN=HA1B
 PASSWORD_COLUMN=password
 SUBSCRIBER_COLUMN='user_id'
 EMAIL_COLUMN=email_address
b9b54261
 SUB_CREATED_COLUMN=datetime_created
 SUB_MODIFIED_COLUMN=datetime_modified
d461e02f
 PHP_LIB_COLUMN=phplib_id
b9b54261
 
7f4e06d1
 # acl table
 ACL_TABLE=grp
 ACL_USER_COLUMN=user
 ACL_GROUP_COLUMN=grp
b9b54261
 ACL_MODIFIED_COLUMN=last_modified
 
 # aliases table
 A_TABLE=aliases
 A_USER_COLUMN=user
 A_CONTACT_COLUMN=contact
 A_EXPIRES_COLUMN=expires
 A_Q_COLUMN=q
 A_CALLID_COLUMN=callid
 A_CSEQ_COLUMN=cseq
 A_LAST_MODIFIED_COLUMN=last_modified
1291221b
 
b9b54261
 #===================================================================
1291221b
 
 
 usage() {
f242a837
 CMD=`basename $0`
 if [ "0$VERIFY_ACL" -eq 1 ] ; then
 	EXTRA_TEXT="ACL privileges are: $ACL_GROUPS"
 fi
 cat <<EOF
0223256d
 sc $Revision$
f242a837
 usage: 
            < subscribers >
  $CMD add <name> <password> <email> ... add a new subscriber (*)
  $CMD mail <name> ..................... send an email to a user
  $CMD rm <name> ....................... delete a user (*)
  $CMD alias show [<alias>] ............ show aliases
  $CMD alias rm <alias> ................ remove an alias
  $CMD alias add <alias> <uri> ......... show aliases
            < access control lists >
  $CMD acl show [<user>] ............... show user membership
  $CMD acl grant <user> <group> ........ grant user membership (*)
  $CMD acl revoke <user> [<group>] ..... grant user membership(s) (*)
            < usrloc >
a58a2d73
  $CMD dul <table> <name> .............. delete user's UsrLoc entries
  $CMD show ............................ show online users
291ac2d3
  $CMD showdb [<name>] ................. show online users flushed in DB
f242a837
  $CMD passwd <user> <passwd> .......... change user's password (*)
  $CMD perm <user> <uri> ............... introduce a permanent UrLoc entry
            < server health >
  $CMD monitor ......................... show internal status
f51155cf
  $CMD ps .............................. show runnig processes 
  $CMD fifo ............................ send raw commands to FIFO
f242a837
 
    commands labeled with (*) will prompt for a MySQL password
    if the variable PW is set, the password will not be prompted"
 
      $EXTRA_TEXT
 
 EOF
1291221b
 }
 
b9b54261
 #params: none
 # output: PW
 prompt_pw() {
60cbe72d
 	if [ -z "$PW" ] ; then
     	read -s -p "MySql Password: " PW
     	echo
 	fi
b9b54261
 }
 
249cc934
 # process output from FIFO server; if everything is ok
 # skip the first "ok" line and proceed to returned 
 # parameters
 filter_fl()
 {
 #	tail +2
 	
 	awk 'BEGIN {line=0;IGNORECASE=1;}
 		{line++}
 		line==1 && /^200 ok/ { next }
 		{ print }'
 }
 
f51155cf
 
 fifo_cmd()
291ac2d3
 {
f51155cf
 	if [ "$#" -lt 1 -o "$#" -gt 3 ]; then
 		echo "ERROR: fifo_cmd takes 1..3 parameters and not $#"
 		exit
 	fi
291ac2d3
 	name=ser_receiver_$$
 	path=/tmp/$name
 	if [ ! -w $SER_FIFO ]; then
 		echo "Error opening ser's FIFO $SER_FIFO"
 		echo "Make sure you have line fifo=$SER_FIFO in your config"
 		exit 1
 	fi
 	mkfifo $path
 	if [ $? -ne 0 ] ; then
 		echo "error opening read fifo $path"
 		exit 1
 	fi
 
1485fbbb
 	# start reader now so that it is ready for replies
 	# immediately after a request was sent out
249cc934
 	cat < $path | filter_fl &
1485fbbb
 
 	# issue request
f51155cf
 	if [ "$#" -eq 1 ] ; then
1485fbbb
 		cat > $SER_FIFO <<EOF 
f51155cf
 :$1:$name
291ac2d3
 
f51155cf
 EOF
 	elif [ "$#" -eq 2 ] ; then 
 		cat > $SER_FIFO <<EOF
 :$1:$name
e0068c86
 $2
 
 EOF
f51155cf
 	elif [ "$#" -eq 3 ] ; then 
 		cat > $SER_FIFO <<EOF
 :$1:$name
291ac2d3
 $2
f51155cf
 $3
291ac2d3
 
 EOF
f51155cf
 	fi
1485fbbb
 	# wait for the reader to complete
 	wait
291ac2d3
 	rm $path
 }
 
 
c55b0dfd
 # $1 = name $2=path $3=attempt
 print_stats() {
90b0b10d
 
764d6fd0
 echo "[cycle #: $3; if constant make sure server lives and fifo is on]"
90b0b10d
 
249cc934
 cat < $2 | filter_fl &
90b0b10d
 cat > $SER_FIFO <<EOF
1a232d1c
 :version:$1
 
249cc934
 EOF
 wait
 
 cat < $2 | filter_fl &
 cat > $SER_FIFO << EOF
90b0b10d
 :uptime:$1
aaa07aa9
 
90b0b10d
 EOF
1485fbbb
 wait
90b0b10d
 echo
 
 echo Transaction Statistics
249cc934
 cat < $2 | filter_fl &
c55b0dfd
 cat > $SER_FIFO <<EOF
 :t_stats:$1
aaa07aa9
 
c55b0dfd
 EOF
1485fbbb
 wait
90b0b10d
 echo
 
 echo Stateless Server Statistics
249cc934
 cat < $2 | filter_fl &
90b0b10d
 cat > $SER_FIFO <<EOF
 :sl_stats:$1
aaa07aa9
 
90b0b10d
 EOF
1485fbbb
 wait
90b0b10d
 echo
 
 echo UsrLoc Stats
249cc934
 cat < $2 | filter_fl &
90b0b10d
 cat > $SER_FIFO <<EOF
 :ul_stats:$1
aaa07aa9
 
90b0b10d
 EOF
1485fbbb
 wait
c55b0dfd
 }
 
b9b54261
 
 # input: sql query, optional mysql command-line params
 sql_query() {
 	# if password not yet queried, query it now
 	if [ -z "$PW" ] ; then
 		read -s -p "MySql Password: " PW >&2
 		echo >&2
 	fi
 	$MYSQL $2 -h $SQL_HOST -u $SQL_USER "-p$PW" -e "$1 ;" $SQL_DB
 }
 
 # input: sql query, optional mysql command-line params
 sql_ro_query() {
 	$MYSQL $2 -h $SQL_HOST -u $RO_USER "-p$RO_PW" \
 		-e "$1 ;" $SQL_DB
 }
 
 
 ser_alias() {
 	case $1 in 
 		show)
 			if [ $# -eq 2 ] ; then
 				CLAUSE=" WHERE $A_USER_COLUMN='$2' "
 			elif [ $# -ne 1 ] ; then
 				usage
 				exit 1
 			fi
 			QUERY="select * FROM $A_TABLE $CLAUSE ; "
 			sql_ro_query "$QUERY"
 			;;
 		add)
 			if [ $# -ne 3 ] ; then
 				usage
 				exit 1
 			fi
 			QUERY="insert into $A_TABLE \
 				($A_USER_COLUMN, $A_CONTACT_COLUMN, $A_EXPIRES_COLUMN, \
 				$A_Q_COLUMN, $A_CALLID_COLUMN, $A_CSEQ_COLUMN, \
 				$A_LAST_MODIFIED_COLUMN) \
c430ed7b
 				values ( '$2', '$3', '$FOREVER', \
b9b54261
 				'1.00', 'call-id-for-ever', '1',
 				now() );"
 			sql_query "$QUERY"
 			;;
 		rm)
             if [ $# -ne 2 ] ; then
                 usage
                 exit 1
             fi
 
             QUERY="delete from $A_TABLE where \
                 $A_TABLE.$A_USER_COLUMN='$2' "
 			sql_query "$QUERY"
 
             ;;
 
 		*)
 			usage
 			exit 1
 			;;
 	esac
 }
 
7f4e06d1
 acl() {
 	case $1 in
 		show)
 			if [ $# -eq 2 ] ; then
a61c6b27
 				is_user $2
 				if [ $? -ne 0 ] ; then
 					echo non-existent user
 					exit 1;
 				fi
7f4e06d1
 				CLAUSE=" WHERE $ACL_USER_COLUMN='$2' "
 			elif [ $# -ne 1 ] ; then
 				usage
 				exit 1
 			fi
 			QUERY="select * FROM $ACL_TABLE $CLAUSE ; "
b9b54261
 			sql_ro_query "$QUERY"
743653c8
 
7f4e06d1
 			;;
 
 		grant)
b9b54261
 			if [ $# -lt 3 ] ; then
7f4e06d1
 				usage
 				exit 1
 			fi
60cbe72d
 			prompt_pw
 			is_user $2
 			if [ $? -ne 0 ] ; then
 				echo non-existent user
 				exit 1
 			fi
b9b54261
 			SIP_USER="$2"
 			shift 2
 			while [ $# -gt 0 ] ; do
 
60cbe72d
 				if [ $VERIFY_ACL -eq 1 ] ; then
 					found=0
 					for i in $ACL_GROUPS ; do
 						if [ "$1" = "$i" ] ; then
 							found=1
 							break
 						fi
 					done	
 					if [ $found -eq 0 ] ; then
 						echo "Invalid privilege: $1 (ignored)"
 						shift
 						continue
 					fi
 				fi
 
b9b54261
         		QUERY="insert into $ACL_TABLE \
                 	($ACL_USER_COLUMN,$ACL_GROUP_COLUMN,$ACL_MODIFIED_COLUMN) \
                 	values ('$SIP_USER','$1', now());"
 				sql_query "$QUERY"
 				if [ $? -ne 0 ] ; then
 					echo "SQL Error"
 					exit 1
 				fi
 				shift
 			done
 
 			$0 acl show $SIP_USER
7f4e06d1
 
 			;;
 
 		revoke)
 			if [ $# -eq 3 ] ; then
 				CLAUSE=" and $ACL_GROUP_COLUMN='$3' "
 			elif [ $# -ne 2 ] ; then
 				usage
 				exit 1
 			fi	
 
 			QUERY="delete from $ACL_TABLE where \
 				$ACL_TABLE.$ACL_USER_COLUMN='$2' $CLAUSE"
b9b54261
 			sql_query "$QUERY"
 
 			$0 acl show $2
7f4e06d1
 
 			;;
 
 		*)
 			usage
 			exit 1
 			;;
 	esac
 }
 
 # params: user
 # output: false if exists, true otherwise
 is_user() {
b9b54261
 	QUERY="select count(*) from $TABLE \
d395e64b
 		where $SUBSCRIBER_COLUMN='$1' and $REALM_COLUMN='$SIP_DOMAIN';"
a61c6b27
 	CNT=`sql_ro_query "$QUERY" | grep -v ERROR | tail -n1`
7f4e06d1
 	if [ "0$CNT" -eq 0 ] ; then
 		false
 	else
 		true
 	fi
 
 }
 
 
 # params: user, password
 # output: HA1, HA1B
 credentials()
 {
d395e64b
 	HA1=`$GENHA1 $1 $SIP_DOMAIN $2`
7f4e06d1
 	if [ $? -ne 0 ] ; then
 		echo "HA1 calculation failed"
 		exit 1
 	fi
d395e64b
 	HA1B=`$GENHA1 "$1@$SIP_DOMAIN" $SIP_DOMAIN $2`
7f4e06d1
 	if [ $? -ne 0 ] ; then
 		echo "HA1B calculation failed"
 		exit 1
 	fi
 }
 
60cbe72d
 #================================================================
7f4e06d1
 
d395e64b
 if [ -z "$SIP_DOMAIN" ] ; then
 	echo "You need to set environment variable SIP_DOMAIN (e.g. to 'foobar.com') first"
 	echo
 	usage
 	exit 1
 fi
 
60cbe72d
 # if the script calls itself ...
 export PW
1291221b
 
 case $1 in
 
bee659af
 	passwd)
 		if [ $# -ne 3 ] ; then
 			usage
 			exit 1
 		fi
 		shift
b9b54261
 		credentials $1 $2
7f4e06d1
 		prompt_pw
b9b54261
 
7f4e06d1
 		is_user $1
bee659af
 		if [ $? -ne 0 ] ; then
7f4e06d1
 			echo non-existent user
bee659af
 			exit 1
 		fi
b9b54261
 		QUERY="update $TABLE \
bee659af
 			set $HA1_COLUMN='$HA1', $HA1B_COLUMN='$HA1B', $PASSWORD_COLUMN='$2' \
b9b54261
 			, $SUB_MODIFIED_COLUMN=now() \
d395e64b
 			WHERE $SUBSCRIBER_COLUMN='$1' and $REALM_COLUMN='$SIP_DOMAIN';"
b9b54261
 		sql_query "$QUERY"
bee659af
 		if [ $? -ne 0 ] ; then
 			echo "password change failed"
 		else
 			echo "password change succeeded"
 		fi
 
 		;;		
 			
 		
 
1291221b
 	add)
bee659af
 		if [ $# -ne 4 ] ; then
1291221b
 			usage
 			exit 1
 		fi
 		shift
b9b54261
 		credentials $1 $2
7f4e06d1
 		prompt_pw
         is_user $1
         if [ $? -eq 0 ] ; then
             echo user already exists
             exit 1
         fi
1291221b
 
b9b54261
 		QUERY="insert into $TABLE \
0c0d8d83
 				($SUBSCRIBER_COLUMN,$REALM_COLUMN,$HA1_COLUMN,\
d461e02f
 				$HA1B_COLUMN,$PASSWORD_COLUMN,$EMAIL_COLUMN, $SUB_CREATED_COLUMN,  \
 				$PHP_LIB_COLUMN ) \
 				values ('$1','$SIP_DOMAIN','$HA1','$HA1B','$2', '$3', now(), '$HA1' );";
b9b54261
 		sql_query "$QUERY"
1291221b
 		if [ $? -ne 0 ] ; then
 			echo "introducing a new user to the database failed"
 		else
 			echo "new user added"
 		fi
 
 		;;
 
f242a837
 	# add a permanent UsrLoc entry
 	perm)
 		if [ $# -ne 3 ] ; then
 			usage
 			exit 1
 		fi
 		prompt_pw
 		is_user $2
 		if [ $? -ne 0 ] ; then
 			echo non-existent user
 			exit 1;
 		fi
 		QUERY="insert into $USRLOC \
 			($A_USER_COLUMN, $A_CONTACT_COLUMN, $A_EXPIRES_COLUMN, \
 			$A_Q_COLUMN, $A_CALLID_COLUMN, $A_CSEQ_COLUMN, \
 			$A_LAST_MODIFIED_COLUMN) \
 			values ( '$2', '$3', '$FOREVER', \
 			'1.00', 'call-id-for-ever', '1',
 			now() );"
 			sql_query "$QUERY"
9ce71e1f
 		echo "Changes do not take effect until server restarted"
f242a837
 		;;
 
 
1291221b
 	dul)
291ac2d3
 		shift
1291221b
 		if [ $# -ne 2 ] ; then
 			usage
 			exit 1
 		fi
f51155cf
 		fifo_cmd ul_rm $1 $2
291ac2d3
 		exit $?
1291221b
 		;;
 
90b0b10d
 	monitor|console|moni|con)
c55b0dfd
 		name=ser_receiver_$$
 		path=/tmp/$name
 		if [ ! -w $SER_FIFO ]; then
 			echo "Error opening ser's FIFO $SER_FIFO"
 			echo "Make sure you have line fifo=$SER_FIFO in your config"
 			exit 1
 		fi
 		mkfifo $path
 		if [ $? -ne 0 ] ; then
 			echo "error opening read fifo $path"
 			exit 1
 		fi
cafca31b
 		trap "rm $path; clear; echo sc monitor ^C-ed; exit 1" 2
c55b0dfd
 		attempt=0
90b0b10d
 		clear
c55b0dfd
 		while [ 1 -eq 1 ]; do
 			attempt=`expr $attempt + 1`
90b0b10d
 			#clear
 			tput cup 0 0
c55b0dfd
 			print_stats $name $path $attempt
 			sleep $WATCH_PERIOD
 		done
 		rm $path
 		exit 0
 		;;
 
b01b59a3
 	mail)
 		if [ $# -ne 2 ] ; then
 			usage
 			exit 1
 		fi
 		shift
0c0d8d83
 		QUERY="select $TABLE.$EMAIL_COLUMN from $TABLE where  \
 			$TABLE.$SUBSCRIBER_COLUMN='$1'"
b9b54261
 		EA=`sql_ro_query "$QUERY" "-B" | grep -v ERROR | tail -n1`
0c0d8d83
 		if [ $? -ne 0 ] ; then
 			echo "MySql query failed"
 			exit 1
 		fi
b01b59a3
 		echo "Write email to $1: $EA now ..."
d395e64b
 		mail -s "Message from $SIP_DOMAIN SIP admin" $EA
b01b59a3
 		if [ $? -eq 0 ] ; then
 			echo message sent
 		else
 			echo sending message failed
 		fi
 
 		;;
 
291ac2d3
 	show)
e0068c86
 		shift
 	
 		if [ $# -eq 1 ] ; then
f51155cf
 			fifo_cmd ul_show_contact $USRLOC $1
e0068c86
 		else
f51155cf
 			fifo_cmd ul_dump
e0068c86
 		fi
291ac2d3
 		exit $?
 		;;
 
a58a2d73
 	online)
f51155cf
 		fifo_cmd ul_dump |grep aor| awk '{print $3}' | sort | sort -mu
a58a2d73
 		exit $?
 		;;
 
b01b59a3
 
291ac2d3
 	showdb|userdb)
a61c6b27
 		if [ $# -eq 2 ] ; then
 			is_user $2
 			if [ $? -ne 0 ] ; then
 				echo non-existent user
 				exit 1;
 			fi
 		elif [ $# -ne 1  ] ; then
1291221b
 			usage
 			exit 1
 		fi
 
 		shift
 
7f4e06d1
 		QUERY1="select $TABLE.$EMAIL_COLUMN from $TABLE where  \
 			$TABLE.$SUBSCRIBER_COLUMN='$1'"
 		QUERY2="select $USRLOC.* from $USRLOC where \
 			$USRLOC.$USER_COLUMN='$1' order by expires desc"
a9aa2f53
 		QUERY3="select $USRLOC.$USER_COLUMN, $TABLE.$EMAIL_COLUMN, $USRLOC.$CALLID_COLUMN \
7f4e06d1
 			from $TABLE, $USRLOC where  \
8945832c
 			$TABLE.$SUBSCRIBER_COLUMN=$USRLOC.$USER_COLUMN  order by $USRLOC.$USER_COLUMN" 
1291221b
 
 		if [ $# -eq 1 ] ; then
b9b54261
 			sql_ro_query "$QUERY1"
 			sql_ro_query "$QUERY2"
1291221b
 		else
b9b54261
 			sql_ro_query "$QUERY3"
1291221b
 		fi
743653c8
 		echo -n "Note: Due to usage of cache, server's list "
 		echo "may differ from DB list."
1291221b
 
 		;;
 
 	rm)
         if [ $# -ne 2 ] ; then
             usage
             exit 1
         fi
7f4e06d1
 		shift 
b9b54261
 		prompt_pw 
 
7f4e06d1
         is_user $1
         if [ $? -ne 0 ] ; then
             echo non-existent user
             exit 1
         fi
 
         QUERY="delete from $TABLE where $TABLE.$SUBSCRIBER_COLUMN='$1'"
b9b54261
 		sql_query "$QUERY"
60cbe72d
 
 		$0 acl revoke $1 > /dev/null 2>&1
 		$0 dul $1   > /dev/null 2>&1
1291221b
         ;;
 			
f51155cf
 	ps)
 		fifo_cmd ps
1291221b
 		;;
7f4e06d1
 
 	acl)
 		shift
 		acl "$@"
 		;;
b9b54261
 
 	alias)
 		shift
 		ser_alias "$@"
 		;;
f51155cf
 	
 	fifo)
 		shift
 		fifo_cmd "$@"
 		;;
1291221b
 		
 	*)
 		usage
 		exit 1
 		;;
 
 esac