Mikrotik with Freeradius/mySQL – Quota Limit # Part-7

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s

FREERADIUS WITH MIKROTIK – Part #2  – COA

FREERADIUS WITH MIKROTIK – Part #3 – Expiration

FREERADIUS WITH MIKROTIK – Part #4 – Auto Mac Binding

FREERADIUS WITH MIKROTIK – Part #5 – Stale Sessions

FREERADIUS  WITH MIKROTIK – Part # 6 – External Auth Script & RADPOSTAUTH

FREERADIUS WITH MIKROTIK – Part #7  – Quota Limit  < You are Here

---

Personnel Note:

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not an ROCKET SCIENCE. The only thing required is the ultimate passion to achieve the goal. And with the right search, reading, understanding logic’s, you can do all on your own. I strongly encourage to read the FR mailing list and Google

OS: Ubuntu 16.04.3 LTS / 64bit

---

Scenario:

Assuming, we have already configured freeradius and user can authenticate & all is working fine. Now we want to provide users with Quota limit. But we donot want to use RADACCT table to calculate there usage & we also don’t want to check there quota on REAL time using freeradius UNLAG query. we will simply cron a script that will run every 10 minutes and will check for quota used for each user from the USERS table. We will make a separate table where we will add the users names who are quote base to avoid re checking for same user. and in USERS table we will simply update there USED data record (in qt_used column in users table) using TRIGGER on radacct table. once they will cross limit, script will disconnect them, change there group, and remove entry from users_qt table.

---

USERS table Example.

Where all users information like name/mobile etc will be saved. Also it will contain a column qt_used which we will update dynamically using trigger on radacct table.

----------------------------------------------------------
-- Table structure for table `users`

CREATE TABLE `users` (
`id` int(10) NOT NULL,
`username` varchar(128) NOT NULL,
`password` varchar(32) NOT NULL,
`firstname` text NOT NULL,
`lastname` text NOT NULL,
`email` text NOT NULL,
`mobile` text NOT NULL,
`cnic` text NOT NULL,
`srvname` text NOT NULL,
`srvid` int(3) NOT NULL,
`expiration` date DEFAULT NULL,
`mac` varchar(30) NOT NULL,
`bwpkg` varchar(256) NOT NULL,
`pool` varchar(128) DEFAULT 'other',
`is_enabled` int(1) NOT NULL,
`is_days_expired` int(1) NOT NULL,
`is_qt_expired` int(1) NOT NULL,
`is_uptime_expired` int(1) NOT NULL,
`qt_total` varchar(32) NOT NULL,
`qt_used` varchar(32) NOT NULL,
`uptime_limit` varchar(32) NOT NULL,
`uptime_used` varchar(32) NOT NULL,
`owner` text NOT NULL,
`createdon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `username` (`username`) USING BTREE;

ALTER TABLE `users_qt`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

---

USERS Quota table Example!

If user is added in this product (username & qoota). then bash script will only check these users against there used quota (qt_used column in USERS table)

------------------------------------------------------------
-- Table structure for table `users_qt`
CREATE TABLE `users_qt` (
`id` int(11) NOT NULL,
`username` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users_qt`
ADD PRIMARY KEY (`id`);

---

LOG Table Example!

We will use this table to log any disconnect message.

--
-- Table structure for table `log`
--
CREATE TABLE `log` (
`id` int(11) NOT NULL,
`data` varchar(32) NOT NULL,
`msg` varchar(128) NOT NULL,
`datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--

---

Trigger for RADACCT

This trigger will simply update the qt_used column for all users (which are enabled, not expired, not over-quota already)

# By Syed Jahanzaib
# Last Modified 22-Jun-2018
#BEGIN
#SET @ctime = (select current_time());
#SET @srvid = (select srvid from users where username =New.Username);
#SET @st = (select free_qt_start_time from services where srvid=@srvid);
#SET @et = (select free_qt_end_time from services where srvid=@srvid);
#if (@ctime > @st)
#AND (@ctime < @et)
#then
#else
#insert into log (data,msg) values ('zaib','datetimenotmatched');
#END IF;
BEGIN
if (NEW.acctoutputoctets != OLD.acctoutputoctets)
OR (NEW.acctinputoctets != OLD.acctinputoctets)
THEN
SET @fout = (NEW.acctoutputoctets - OLD.acctoutputoctets);
SET @fin = (NEW.acctinputoctets - OLD.acctinputoctets);
UPDATE users set qt_used = qt_used + @fout + @fin WHERE username = New.username;
END IF;
END

---

Now insert a record in users_qt table for a user in this table. (quota limit 300 bytes testing only)

INSERT INTO 'users_qt' ('id', 'username') VALUES (NULL, 'zaib');

Now check the record.

root@radius:/temp# mysql -uroot -pPASS -s --skip-column-names -e "use radius; select * from users_qt;"
1 zaib 

Ok now we simply connect the user and will do some data download or browse , since we have defined very small bytes limit it will fill in seconds.

Now to check the over-quota user we will use a small bash script (which will be cron to run every 10 minutes)

---

BASH script to check over quota user

qc.sh

root@radius:/temp# cat qc.sh
#!/bin/bash
##set -x
SQLID="root"
SQLPASS="SQLPASS"
export MYSQL_PWD=$SQLPASS
DB="radius"
#Table which contain main users information
TBL="users"
#Table which contains users name which will be scanned for quota
QTDB="users_qt"
#Log table in which few actions will be logged
TBL_LOG="log"
#Rad user group in which we will update user profile like from 1mb to expired or likewise
GROUP="radusergroup"
#What is the Next pool name which will be
NEXTSRV="overquota"
# Temp file where user list will be saved
TMP="/tmp/qt_users.txt"
# NAS IP & port to send COA or disconnect pkt
NAS="10.0.0.1:3799"
# Radius Secret on NAS
RADSECRET="testing123"
CMD="mysql -u$SQLID --skip-column-names -s -e"
$CMD "use $DB; select username from $QTDB;" > $TMP
if [ ! -s $TMP ]
then
echo "No user found to check for QUOTA in table, exit"
exit 1
fi

# Run loop forumla to run CMD for single or multi usernames
num=0
cat $TMP | while read users
do
num=$[$num+1]
USERNAME=`echo $users |awk '{print $1}'`
TOTQT=`$CMD "use $DB; select qt_total from $TBL where username ='$USERNAME';"`
USEDQT=`$CMD "use $DB; select qt_used FROM $TBL WHERE username='$USERNAME';"`

# If quota exceeds then perform multiple actions
if [ "$USEDQT" -gt "$TOTQT" ]; then

# Pull account session id from radacct table, which will be used to send to NAS for user COA OR Disconnection
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
echo "Warning ! $USERNAME user quota reached limits. Allowed = $TOTQT Used = $USEDQT Disconnectig him now by sendin POD to NAS and adding LOG as well"

#Delete user from Quota check table, so it should not repeat checking every time for same user
$CMD "use $DB; delete from users_qt where username ='$USERNAME';"

# Update User group to NEXT Service , like expired pool
$CMD "use $DB; update $GROUP set groupname='$NEXTSRV' where username ='$USERNAME';"

# If user is Online , Kick him using session id with RADCLIENT
if [ ! -z "$ACCTSESID" ]; then
echo user-name=$USERNAME,Acct-Session-Id=$ACCTSESID | radclient -x $NAS disconnect $RADSECRET

#Add in LOG table about kicked action
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User Kickd dueto Quota limit exceeded');"
else
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User quota found exceeded, but could not found its online session in Radius');"
fi
fi
done

---

Done.

Now run the Quota checking script …

root@radius:/temp# ./qc.sh

Warning ! zaib user quota reached limits. Allowed = 300 Used = 400 Disconnectig him now by sending POD to NAS and adding LOG as well

Once script found user Over Quota, it will update the user group in RADGROUPREPLY  so user should get OVER QUOTA pool …

Entry in LOG table will be added …

(in above pic, user was not online but he reached the quota, therefore the log informed about this too 🙂 )

Ok lets test user with radclient.

echo "User-Name = zaib, Password = zaib, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth RADSECRET

RESPONSE:

Received response ID 218, code 2, length = 89
Reply-Message = "Exp-Mod-Reply: Your account has expired."
Framed-Pool = "expired-pool"
Mikrotik-Rate-Limit = "1k/1k"

Total approved auths: 1
Total denied auths: 0
Total lost auths: 0

---

 

Disclaimer:

I might be missing any part, message me if any one required further info in it.

In the end I would like to thanks Mr. Khazoum Yaghi to give me hints for the separate table for quota checking. I was stucked on the script which keep checking for over-quota users again and again but after adding user name entry in a separate table, the script will check users from this table only and if any user is over quota it will delete user entry from this table, so next time the script will run, it will not check that user because of missing entry in the quota table.

I am sure that there are more appropriate methods to achieve the task , but I just managed to go through it this way.

---

Regard’s

Syed Jahanzaib