Radius | Disconnect users after service change / user disable

Note for self

In older version of radius manager, when OP disables any user account or change user service package , those changes doesnt take effect until the user disconnect/reconnect. Sometimes user remains connected for days. Back in those days , we made a workaround by creating a mysql trigger and script combination. 

Assuming you have fully functional radius working, along with root access to DB. Save this trigger.sql and import it in mysql radius DB.

Example: mysql -uroot -pMYPASS radius < trigger.sql

Following trigger will check for rm_users table changes, and if it found any changes in the users disable/enable or srvid changes, it will add entry in the rm_kickuserstable & our schedule script will pick the data from there and will act accordingly …

MYSQL > kickuser_trigger

-- Host: localhost Database: radius
-- Server version 5.5.54-0ubuntu0.12.04.1
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
/!50003 CREATE/ /!50017 DEFINER=root@localhost/ /*!50003 TRIGGER kickuser_trigger BEFORE UPDATE ON rm_users
FOR EACH ROW BEGIN
IF NEW.enableuser <> OLD.enableuser THEN
INSERT INTO rm_kickuser (datetime, username, msg) VALUES (NOW(), new.username, new.enableuser);
END IF;
IF NEW.srvid <> OLD.srvid THEN
INSERT INTO rm_kickuser (datetime, username, msg) VALUES (NOW(), new.username, new.srvid);
END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
---- Dumping routines for database 'radius'
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2021-07-02 8:24:05

rm_kickuser TABLE

Save following & import it in radius db 

Example: mysql -uroot -pMYPASS radius < rm_kickuser_SQL_DB_Creation.sql

root@radius-zaib:/temp# cat rm_kickuser_SQL_DB_Creation.sql

-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 13, 2016 at 10:32 AM
-- Server version: 5.5.46
-- PHP Version: 5.3.10-1ubuntu3.21
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `radius`
--
-- --------------------------------------------------------
--
-- Table structure for table `rm_kickuser`
--
CREATE TABLE IF NOT EXISTS `rm_kickuser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL,
`username` varchar(64) NOT NULL,
`msg` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
--
-- Dumping data for table `rm_kickuser`
--
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

the Script !

Now create the script & schedule it to run every 1 minutes

kick_user.sh

#!/bin/bash
#set -x
# Following script is made specifically for Dmasoftlab radius manager 4.1.x
# When OP disables any user or change service, it will kick the user so that either disconnects, or his package changes on reconnect
# it requires custom trigger on rm_users table, this script will be schedule to run every minute
# Created: 25-MARCH-2019
# Tested on Ubuntu OS Only
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
#################
# CHANGE these
HOSTNAME=`hostname`
SQLID="root"
SQLPASS="MYPASSWORD"
NAS_COA_PORT="1700"
DB="radius"
SRV="mysql"
USR_TABLE="rm_kickuser"
MNT="1"
RADCLIENT="/usr/local/bin/radclient"
#################
#DATE TIME FUNCTIONS
currenttime=$(date +%H:%M:%S)
# Add Script start execution entry in the /var/log/syslog to see if the script got executed or not
logger "Kick Disabled/Enabled & Service Change - User poller script Started @ $currenttime by the CRON scheduler ... Powered by SYED.JAHANZAIB"
echo "- Script Start Time - $currenttime"
echo "- Checking Disabled/Enabled Users in $USR_TABLE table ..."
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
#Table which contain main users information
TMPUSRINFO=/tmp/_users_list.txt
TEMP="/temp"

# Checking if /temp folder is previously present or not . . .
{
if [ ! -d "$TEMP" ]; then
echo
echo "- INFO: $TEMP folder not found, Creating it now to store logs ..."
mkdir $TEMP
else
echo -e "- INFO: $TEMP folder is already present to store logs."
echo
fi
}

KICKUSER_LIST_FILE=$TEMP/kick_users_list.txt
SYSLOG="/var/log/syslog"
> $TMPUSRINFO
# Check if table exists
if [ $($CMD \
"select count(*) from information_schema.tables where \
table_schema='$DB' and table_name='$USR_TABLE';") -eq 1 ]; then
echo "- INFO: $USR_TABLE Table exists ..."
else
echo "- WARNING: $USR_TABLE Table does not exists ..."
fi
# pull user record
$CMD "use $DB; select username from $USR_TABLE WHERE datetime >= NOW() - INTERVAL $MNT MINUTE;" >> $TMPUSRINFO
if [ ! -s $TMPUSRINFO ]
then
endtime=$(date +%H:%M:%S)

echo "
- INFO: No User to KICK found in DMA RADIUS MANAGER TABLE '$USR_TABLE' , Sending EXIT signals ...

- Script Ends Here...
- EXITING peacefully...
- Script End Time - $endtime
"
exit 1
fi
# Apply Count Loop Formula while deleting first line which have junk text
num=0
cat $TMPUSRINFO | while read users
do
num=$[$num+1]
username=`echo $users | awk '{print $1}'`
USER_IP=`echo $users | awk '{print $2}'`
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$username' AND acctstoptime is NULL;"`
NAS_IP=`$CMD "use $DB; select nasipaddress from radacct where username ='$username' AND acctstoptime is NULL;"`
NAS_SECRET=`$CMD "use $DB; select secret from nas where nasname = '$NAS_IP' ;"`
if [ -z "$ACCTSESID" ]; then
echo "User Found to KICK: USER: $username , BUT USER IS NOT ONLINE, no action is requiroed ..."
else
# Print Info on screen
echo " User Found to KICK: USER: $username , IP: $USER_IP, ID: $ACCTSESID, NAS: $NAS_IP @ $currenttime ... KICKING him now ..."
logger " User Found to KICK: USER: $username , IP: $USER_IP, ID: $ACCTSESID, NAS: $NAS_IP @ $currenttime ... KICKING him now ..."
echo " User Found to KICK: USER: $username , IP: $USER_IP, ID: $ACCTSESID, NAS: $NAS_IP @ $currenttime ... KICKING him now ..." >> $KICKUSER_LIST_FILE
#in below cmd, I am using SSH base method to kick the user because there were some issues in routing & NAS was not accepting radclient packets, you may use the radclient method which is better approach
ssh -p 22 admin@192.168.0.1 /ppp active remove [find name=$username]
#for pppoe , use below
#echo user-name=$username | radclient -x $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET
#for hotspot, enable following line
#echo Framed-IP-Address=$USER_IP | radclient -x -c 1 $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET
fi
done
# once done, we should delete the tmp files to clear the garbage
rm $TMPUSRINFO

ADDING CRON Entry

*** Schedule the script to run every minute

crontab -e

*/1 * * * * /temp/kickuser.sh > /dev/null 2>&1

Result:

root@radius-zaib:/temp# ./kickuser.sh

- Script Start Time - 08:43:58
- Checking Disabled/Enabled Users in rm_kickuser table ...
- INFO: /temp folder is already present to store logs.
- INFO: rm_kickuser Table exists ...
- INFO: No User to KICK found in DMA RADIUS MANAGER TABLE 'rm_kickuser' , Sending EXIT signals ...

- Script Ends Here...
- EXITING peacefully...
- Script End Time - 08:43:58