Syed Jahanzaib Personal Blog to Share Knowledge !

July 12, 2018

FREERADIUS WITH MIKROTIK – Part #16 – Loosy workaround to disconnect missing users from the NAS

Filed under: freeradius — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 9:02 AM

search in croud

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s Click here to read more on FR tutorials …


Disclaimer! This is important!

Every Network is different , so one solution cannot be applied to all. Therefore try to understand the logic & create your own solution as per your network scenario. Just dont follow copy paste.

If anybody here thinks I am an expert on this stuff, I am NOT certified in anything Mikrotik/Cisco/Linux or Windows. However I have worked with some core networks and I read , research & try stuff all of the time. So I am not speaking/posting about stuff I am formerly trained in, I pretty much go with experience and what I have learned on my own. And , If I don’t know something then I read & learn all about it.

So , please don’t hold me/my-postings to be always 100 percent correct. I make mistakes just like everybody else. However – I do my best, learn from my mistakes and always try to help others.

Regard's
Syed Jahanzaib~

Scenario:

We have single NAS (Mikrotik) as pppoe server along with Freeradius as AAA server.
In NAS we have configured INTERIM UIPDATES set to 5 minutes therefore it sends accounting packets to the freeradius server after every 5 minutes. In Freeradius server web have a BASH script that closes the online sessions if the FR doesnt receive  accounting packets from the NAS for more then 10 minutes (to clear false sessions by considering that NAS is powered off).

We also have some users created locally in the NAS.


Problem:

Sometimes dueto communication disruption , Freeradius close the user session in radacct table considering the NAS is not reachable, BUT the user is still active on the NAS. When the communication restores & NAS sends the existing users accounting packets to the freeradius, they get discarded because the NAS connected user Account-Session-ID does not matches with the radacct table session id.

Freeradius will create new session (with acctstoptime is NULL value) only when

  • If new connection is made by user end , OR
  • If NAS connected user session ID is matched with the radacct Account Session ID

Workaround:

This is not a proper solution because it is limited to single NAS only, for multi NAS you should search freeradius mailing list for better approach.

Following is bash script which performs following action …

  1. Fetch NAS online active users in PPP/Active Connections, using password less ssh login from the Linux to Mikrotik,
  2. Fetch Freeradius online active users from RADACCT table (where acctstoptime value is NULL),
  3. Display difference between NAS and Freeradius Online users,
  4. If differentiated user is NAS local user, then donot take any action just move on,
  5. ELSE consider this user as RADIUS user which is online in NAS but offline in Radius RADACCT table, therefore KICK it , so that it can re-establish the new session and get re-inserted in radacct table properly.

You can modify it as per you local requirements.


Requirements:

  • Mikrotik as NAS with SSH enabled & RSA key imported so that ssh from Linux to mikrotik must work without password, explained  here , Make sure its working
  • Freeradius Server
  • Arithmetic function is performed by BC command, make sure you have it installed

the Script!

Schedule the script to run every 5 or above minutes, or as per local requirement,

#!/bin/bash
# BASH script to fetch online users list from Mikrotik NAS & compare it with local FR online users
# IF difference found, then kick the missed users from NAS, but if user is NAS local , then skip it
# This way the missing users will re-establishes there session and will be inserted properly in radacct table AGAIN ,
# This is just a workaround only, ITs not a proper solution, It also assumes you have single NAS only
# Created on : 11-JUL-2018
# Syed Jahanzaib / aacable at hotmnail dot com / https://aacable dot wordpress dot com

#set -x
# Setting various variables ...

# MYSQL related data
SQLID="root"
SQLPASS="SQLROOTPASSWORD"
DB="radius"
TBL_LOG="log"
export MYSQL_PWD=$SQLPASS
FOOTER="Script Ends Here. Thank you
Syed Jahanzaib / aacable at hotmail dot com"
# MIKROTIK related Data
MT_USER="admin"
MT_IP="10.10.0.1"
MT_PORT="10022"
MT_SECRET="RADIUS_INCOMING_SECRET"
MT_COA_PORT="3799"

# SSH commands for Mikrotik & Freeradius
MT_SSH_CMD="ssh $MT_USER@$MT_IP -p $MT_PORT"
FR_SSH_CMD="mysql -uroot --skip-column-names -s -e"

# Temporary holder for various data
MT_ACTIVE_USERS_LIST="/tmp/mt_active_users.txt"
MT_ACTIVE_USERS_NAME_ONLY_LIST="/tmp/mt_active_users_name_only_list.txt"
FR_ACTIVE_USERS_LIST="/tmp/fr_active_users_list.txt"
FR_MISSING_USERS_NAME_LIST="/tmp/fr_active_users_missing_list.txt"
> $MT_ACTIVE_USERS_LIST
> $MT_ACTIVE_USERS_NAME_ONLY_LIST
> $FR_ACTIVE_USERS_LIST
> $FR_MISSING_USERS_NAME_LIST

# Execute SSH commands & store data in temporary holders
echo "1- Getting list of Mikrotik Online users via ssh ..."
$MT_SSH_CMD "/ppp active print terse " | sed '/^\s*$/d' > $MT_ACTIVE_USERS_LIST
echo "2- Getting list of Freeradius Online users from 'RADACCT' table ..."
$FR_SSH_CMD "use radius; select username from radacct WHERE acctstoptime IS NULL;" > $FR_ACTIVE_USERS_LIST

# Run loop forumla to run CMD for single or multi usernames
echo "3- Running loop formula to fetch usernames only from the mikrotik PPP online users list ..."
num=0
cat $MT_ACTIVE_USERS_LIST | while read users
do
num=$[$num+1]
USERNAME=`echo $users |sed -n -e 's/^.*name=//p' | awk '{print $1}'`
echo "$USERNAME" >> $MT_ACTIVE_USERS_NAME_ONLY_LIST
done

# calculate and display Users from Mikrotik Active PPP list vs Freeradius Local Actvive Users List
MT_USERS_COUNT=`cat $MT_ACTIVE_USERS_LIST | wc -l`
FR_USERS_COUNT=`cat $FR_ACTIVE_USERS_LIST | wc -l`
USER_DIFFERENCE=`echo "($MT_USERS_COUNT)-($FR_USERS_COUNT)" |bc`
echo "
Result:
echo MIKROTIK ACTIVE USERS = $MT_USERS_COUNT
echo RADIUS ACTIVE USERS = $FR_USERS_COUNT
echo Freeradius Missing Users = $USER_DIFFERENCE
"
#comm -23 <(sort < $MT_ACTIVE_USERS_NAME_ONLY_LIST) <(sort < $FR_ACTIVE_USERS_LIST)

# Make separate list for users that are foung missing in Freeradius online users list
comm -23 <(sort < $MT_ACTIVE_USERS_NAME_ONLY_LIST) <(sort  $FR_MISSING_USERS_NAME_LIST

# Check if missing user is NAS local or FR user
echo "4- Running loop formula to check if missing users from FR are NAS local or radius users ...
"
cat $FR_MISSING_USERS_NAME_LIST | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk '{print $1}'`
REMOTE_OR_LOCAL=`cat $MT_ACTIVE_USERS_LIST |grep $USERNAME |awk '{print $2}'`
if [ "$REMOTE_OR_LOCAL" != "R" ]; then
echo "$USERNAME = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ..."
else
# IF user is a FR user, then consider his MISSED user & kick him so that he will reconnect & proper entries will be made in radius radacct table
echo "$USERNAME = This user is ON-LINE in NAS, but OFF-LINE in FR radacct table, kicking it so it will reconnect & session will be recreated properly **********"
#$MT_SSH_CMD "/ppp active remove [find name=$USERNAME]"
# KICK user via RADCLIENT / zaib
echo user-name=$USERNAME | radclient -x $MT_IP:$MT_COA_PORT disconnect $MT_SECRET
$FR_SSH_CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - Kicked from NAS dueto missing session in FR');"
fi
done

echo "$FOOTER"

OUTPUT:

root@zaibradius:/temp# ./test.sh
1- Getting list of Mikrotik Online users via ssh ...
2- Getting list of Freeradius Online users from 'RADACCT' table ...
3- Running loop formula to fetch usernames only from the mikrotik PPP online users list ...

Result:
echo MIKROTIK ACTIVE USERS = 959
echo RADIUS ACTIVE USERS = 945
echo Freeradius Missing Users = 14

4- Running loop formula to check if missing users from FR are NAS local or radius users ...

USER1 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER2 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER3 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER4 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER5 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER6 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER7 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER8 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER9 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER10 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER11 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER12 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER13 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER14 = This user is ON-LINE in NAS, but OFF-LINE in FR radacct table, kicking it so it will reconnect & session will be recreated properly **********

Sending Disconnect-Request of id 95 to 10.10.0.1 port 3799
User-Name = "USER14"
rad_recv: Disconnect-ACK packet from host 10.10.0.1 port 3799, id=95, length=36
NAS-Identifier = "MikroTik"
NAS-IP-Address = 10.10.0.1

Script Ends Here. Thank you
Syed Jahanzaib / aacable at hotmail dot com

FR stale session script result.JPG

.

Kick action entry will be logged in LOG table,

LOG ENTRY.JPG


Salam Alykum,

March 11, 2016

Mikrotik with Freeradius/mySQL # Part-1

Filed under: freeradius — Tags: , , , , , — Syed Jahanzaib / Pinochio~:) @ 3:42 PM

fre


 


Disclaimer! This is important!

Every Network is different , so one solution cannot be applied to all. Therefore try to understand logic & create your own solution as per your network scenario. Just dont follow copy paste.

If anybody here thinks I am an expert on this stuff, I am NOT certified in anything Mikrotik/Cisco/Linux or Windows. However I have worked with some core networks and I read , research & try stuff all of the time. So I am not speaking/posting about stuff I am formerly trained in, I pretty much go with experience and what I have learned on my own. And , If I don’t know something then I read & learn all about it.

So , please don’t hold me/my-postings to be always 100 percent correct. I make mistakes just like everybody else. However – I do my best, learn from my mistakes and always try to help others

Regard's
Syed Jahanzaib~

Personnel Note:

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not ROCKET SCIENCE as some PRO in the industry try to pose. You can do it as well, 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


Make your own Billing system in Linux with Freeradius 2.1.10 / MySQL 5.5.47
# Part-1

[This Guide will be updated with many further supporting posts)

The aim of writing this post was that there are number of radius products available on the internet with lots of features, each have some unique features. But this is also true that none of them is 100% perfect for every type of ISP. The reason is that every ISP/Network have different sort of local requirements and billing mode. If you ahve searched on google you will find that there are tons of guides for freeradius implementation, but most of them have either incomplete data , or difficult explanation, or does not meet the practical requirements of Desi ISP. Thats why I started this guide so that info that is not common on the net can be shared here. plus most important you can learn on your own using this baby step.

In this post I have made some quick guide to install a very basic level of billing system by using Freeradius/mysql on UBUNTU 12.4 [32bit]. Mikrotik is being used as NAS to connect user and freeradius will be used for authentication/accounting billing system.

Quick Code to get started.

Radius IP = 101.11.11.245
Mikrotik IP = 101.11.11.255

Let’s Rock …


 

First Update Ubuntu (12.4  32bit) and install the required modules

# Update Ubuntu First
apt-get update
# Install Required pre requisites modules
apt-get -y install apache2 mc wget make gcc mysql-server mysql-client curl
apt-get -y install phpmyadmin
apt-get install freeradius freeradius-mysql freeradius-utils

This may take some moments as average of 100+MB will be downloaded from the net and will be installed automatically. Sit back and relax.

After update/installation of components done, Proceed to MYSQL configuration below …

TIP: Use phpmyadmin, it will be much easier for you to add/edit/delete records from DB using its GUI …



MYSQL  CONFIGURATION:

Create Freeradius Database in MYSQL

Now create Freeradius Database in mySQL.

Login to mysql (use mysql root password that you entered in above steps)

mysql -uroot -pzaib1234
create database radius;
grant all on radius.* to radius@localhost identified by "zaib1234";

Import Freeradius Database Scheme in MYSQL ‘radius’ DB

Insert the freeradius database scheme using the following commands, Make sure to change the password ####

mysql -u root -pzaib1234 radius < /etc/freeradius/sql/mysql/schema.sql
mysql -u root -pzaib1234 radius < /etc/freeradius/sql/mysql/nas.sql

# For Ubuntu 18, use below...
# mysql -u root -pzaib1234 radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/schema.sql

Create new user in MYSQL radius database (For Testing Users)

User id = zaib
Password = zaib
Rate-Limit = 1024k/1024k

mysql -uroot -pzaib1234
use radius;
INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES ( NULL , 'zaib', 'Cleartext-Password', ':=', 'zaib');
INSERT INTO radreply (username, attribute, op, value) VALUES ('zaib', 'Mikrotik-Rate-Limit', '==', '1024k/1024k');
exit

Note:
You can skip the Framed-IP-Address part or modify it as per required.


FREERADIUS CONFIGURATION:

SQL.CONF

NAS SECTION:

We have to add a NAS entry either in radius NAS table, or in clients.conf so that this NAS will be allowed to send auth request to this freeradius

To enable NAS table via sql, we need to enable it in sql.conf file, follow below method …

Edit following file  /etc/freeradius/sql.conf

nano /etc/freeradius/sql.conf file

Change the password to zaib1234 (or whatever you set in mysql if required) and Uncomment the following

readclients = yes

So some portion of the file may look like following, after modifications

# Connection info:
server = "localhost"
#port = 3306
login = "radius"
password = "zaib1234"
readclients = yes

sql-mod

Save and Exit the file


/etc/freeradius/sites-enabled/default

Now edit the /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

Uncomment the sql option in the following sections

accounting

# See “Authorization Queries” in sql.conf

sql

session

# See “Authorization Queries” in sql.conf

sql

Post-Auth-Type

# See “Authorization Queries” in sql.conf

sql

[/sourcecode]

Save and Exit the file


RADIUSD.CONF

Now edit /etc/freeradius/radiusd.conf file

nano /etc/freeradius/radiusd.conf

#Uncomment the following option

$INCLUDE sql.conf

Save and exit the file


/etc/freeradius/sites-available/default

Last but no least , edit /etc/freeradius/sites-available/default

nano /etc/freeradius/sites-available/default

Search for LINE

#  See “Authorization Queries” in sql.conf

and UN-COMMENT the SQL word below it.

Example After modification

#  See “Authorization Queries” in sql.conf

sql

Save and exit.


ADDING ‘NAS’ [Mikrotik] in CLIENTS.CONF

To accept connectivity of Mikrotik with the Freeradius, we need to add the mikrotik IP and shared secret in clients.conf

Edit  /etc/freeradius/clients.conf

nano /etc/freeradius/clients.conf

and add following lines at bottom

client 101.11.11.255 {
secret          = 12345
shortname       = Mikrotik
}

Note: Change the IP /Secret according to your Mikrotik Network Scheme.

after any changes either to clients.conf or NAS table, you must restart the freeradius service in order to take changes effect, its a security measure


Last but not least, download mikrotik dictionary from

https://wiki.mikrotik.com/wiki/Manual:RADIUS_Client/vendor_dictionary

and copy it in /usr/share/freeradius folder

If freeradius is already running, stop it and restart it.


TESTING USER AUTHENTICATION ON FREERADIUS:

Now stop the free radius server

/etc/init.d/freeradius stop

and start in DEBUG mode so that we can monitor for any errors etc

freeradius -X

Now OPEN another TERMINAL/CONSOLE window and issue following command to TEST USER AUTHENTICATION

radtest zaib zaib localhost 1812 testing123

and you should ACCESS-ACCEPT MESSAGE as below …

root@ubuntu:~#  radtest zaib zaib localhost 1812 testing123

Sending Access-Request of id 38 to 127.0.0.1 port 1812
User-Name = "zaib"
User-Password = "zaib"
NAS-IP-Address = 101.11.11.245
NAS-Port = 1812
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=38, length=39
Mikrotik-Rate-Limit = "1024k/1024k"

mt

Another method

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

root@apnaradius:~# echo "User-Name = zaib, Password = zaib, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123
Received response ID 101, code 3, length = 56
Reply-Message = "zaib - Your account has expired. \r\n"

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

:~) Alhamdolillah


 

MIKROTIK SECTION:

I assumed you already have pppoe server configured and running.

Add Radius Entry as showed in the images below …

nas1

nas2


 

TEST FROM CLIENT WINDOWS PC:

Create pppoe dialer at client end, and test the user ID created in earlier steps.

c1

Once it will be connected, you can see entries in Mikrotik LOG / Active Users Session.
As showed in the image below …

ml1

and dynamic queue of 1mb will also be created (that we added in attributes section in radius/mysql)

queue


DISCONNECT Active ppp USER : COMMAND FROM RADIUS

If you want to disconnect a single active connected user , use following command (many other methods available as well)

echo user-name=zaib | radclient -x 101.11.11.255:1700 disconnect 12345

Result

discon command

dc

disconnect user

Another Method to disconnect ppp user on mikrotik via radclient with account session ID

First check active user Accounting Session ID in RADACCT table.

 mysql -uroot -pzaib1234 -s --skip-column-names -e "use radius; select acctsessionid from radacct where username ='zaib' AND acctstoptime is NULL;"

this way you will get account session id from radacct table,
Now issue disconnect command [You may fill up variables with actual values, following is an example only]

echo user-name=$USERNAME,Acct-Session-Id=$ACCTSESID | radclient -x $NAS disconnect $RADSECRET

Disconnect HOTSPOT user with acct session id and framed ip

#!/bin/bash
#set -x
SQLUSER="root"
SQLPASS="PASSWORD"
SQLHOST="localhost"
SQLPORT="3306"
DB="radius"
CMD="mysql -u$SQLUSER -p$SQLPASS -h$SQLHOST --port=$SQLPORT --skip-column-names -e"
NAS_IP=`$CMD "use $DB; select nasipaddress from radacct where username ='$USR' AND acctstoptime is NULL;"`
NAS_SECRET=`$CMD "use $DB; select secret from nas where nasname = '$NAS_IP' ;"`
NAS_COA_PORT="1700"
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USR' AND acctstoptime is NULL;"`
FRAMEDIP=`$CMD "use $DB; select framedipaddress from radacct where username ='$USR' AND acctstoptime is NULL;"`
echo user-name=$USR,Acct-Session-Id=$ACCTSESID,Framed-IP-Address="$FRAMEDIP" | /usr/local/bin/radclient -x $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET > /dev/null

Preventing Simultaneous Use by using simultaneous-Use attribute

To LIMIT USER SIMULTANEOUS SESSION: [command is phpMyadmin base format]

INSERT INTO `radcheck` (`id` ,`username` ,`attribute` ,`op` ,`value` )
VALUES (NULL , 'zaib', 'MD5-Password', ':=', MD5( 'zaib' ) ),
(NULL , 'zaib', 'Simultaneous-Use', ':=', '1');

NOTE: For sim-use i had to disable (comment) the “radutmp” entry in /etc/freeradius/sites-enabled/default .

ACCOUNTING SECTION
SESSION SECTION

Now modify the  /etc/freeradius/sql/mysql/dialup.conf file

nano /etc/freeradius/sql/mysql/dialup.conf

& UNCOMMENT following

# Uncomment simul_count_query to enable simultaneous use checking
simul_count_query = "SELECT COUNT(*) \
FROM ${acct_table1} \
WHERE username = '%{SQL-User-Name}' \
AND acctstoptime IS NULL"

NOTE:
YOU MUST RESTART FREERADIUS SERVER IN ORDER TO TAKE CHANGES EFFECT. SO DO IT.

Result of above attributes:

already


Add Calling-Station-Id attribute to restrict mac CALLED ID

If we want to restrict bind user name with specific mac address, first edit

nano /etc/freeradius/sites-enabled/default

and un comment following attribute “checkval“, Example is below …

checkvalsave and restart radius.

Now login to mysql , select radius database, and use below command to add user, with mac address.

INSERT INTO `radius`.`radcheck` (`id` ,`username` ,`attribute` ,`op` ,`value`)
VALUES (
NULL , 'zaib', 'Calling-Station-Id', ':=', '12:34:56:78:70:00'
);

If user uses different station to connect with this ID he will be rejected as showed in the image below …

phpadmin

 

reject-mac-wrong

 


Add Static IP Address and Pool in radreply group.

To Assign user FIX IP Address, use following …

INSERT INTO radreply ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Framed-IP-Address', '==', '1.2.3.4');

To Assign user IP from POOL, use following …

INSERT INTO radreply ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Framed-Pool', '==', '512k-pool');

 


Adding Expiration Date for user

If you want to Expire the Account after XX days, you can use following

INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Expiration', ':=', '13 Mar 2016');

In above Example User will expires on 13th March, 2016 at 00:00 [Midnight].

If you want to EXPIRE user at some other specific Time, use following format in time

INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Expiration', ':=', '13 Mar 2016 08:00');

ZAIB 🙂 GOT IT


Limit User Total Online time (Access by Period) Started from first login

If you want to start user online time (like in hours) but it should be calculated from first access, then use following.

edit the file /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

and add following under “authorize { section

accessperiod

so that it may look like below …

default

now edit file /etc/freeradius/modules/sqlcounter_expire_on_login

nano /etc/freeradius/modules/sqlcounter_expire_on_login

and add following

sqlcounter accessperiod {
counter-name = Max-Access-Period-Time
check-name = Access-Period
sqlmod-inst = sql
key = User-Name
reset = never
query = "SELECT IF(COUNT(radacctid>=1),(UNIX_TIMESTAMP() - IFNULL(UNIX_TIMESTAMP(AcctStartTime),0)),0) FROM radacct WHERE UserName = '%{%k}' AND AcctSessionTime >= 1 ORDER BY AcctStartTime LIMIT 1"
}

now add user attribute in radchceck table (Following is 1 hour Uptime limit example, and it will start after first login)

INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Access-Period', '=', '3600');

Once the time period is over, user will be disconnected.


 

Limit User Total Online time , Example one hour, which can be used in parts as well.

If we want to allow user one hour which user can use in parts as well, like ten minutes now, then next day he can use rest of his available time.  Use following

edit the file /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

and add following under “authorize { section

Max-All-Session

now edit file /etc/freeradius/modules/sqlcounter_expire_on_login

nano /etc/freeradius/modules/sqlcounter_expire_on_login

and add following

sqlcounter timelimit {
counter-name = Max-All-Session-Time
check-name = Max-All-Session
sqlmod-inst = sql
key = User-Name
reset = never
query = "SELECT SUM(AcctSessionTime) FROM radacct where UserName='%{%k}'"
}

Save and Exit.

Now add user attribute in radchceck table (Following is 1 hour Uptime limit example, which can be used in parts as well no first login applied here)

INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Max-All-Session', ':=', '3600');

QUOTA LIMIT FOR USER with CUSTOM MEANINGFUL REJECT REPLY MESSAGE

To limit user data volume limit (either daily, weekly or monthly) use below code.

edit the file /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

and add following under “authorize { section

totalbytecounter{
reject = 1
}
if(reject){
update reply {
Reply-Message := 'ZAIB-RADIUS-REPLY - You have reached your bandwidth limit'
}
reject
}

now edit file /etc/freeradius/modules/sqlcounter_expire_on_login

nano /etc/freeradius/modules/sqlcounter_expire_on_login

and add following

sqlcounter totalbytecounter {
                counter-name = Mikrotik-Total-Limit
                check-name = Mikrotik-Total-Limit
                reply-name = Mikrotik-Total-Limit
                sqlmod-inst = sql
                key = User-Name
                reset = never
                query = "SELECT ((SUM(AcctInputOctets)+SUM(AcctOutputOctets))) FROM radacct WHERE UserName='%{%k}'"
}

Save and Exit.

Now add user attribute in radchceck table (Following is 1 MB total data limit example, which can be used in parts as well )

Note: Value is in bytes, so use it accordingly

INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Mikrotik-Total-Limit', ':=', '1000000');

Once the user quota over, he will get access deny message, and in radius log, you can see following 🙂

radreply

Note:

There is a problem with above attribute. Radius will not AUTO disconnect user once he reaches his limit. he will continue to use his account. he will only be denied further login on his next login attempt.

[later I found that if you will add expiration check in radcheck section, NAS will auto DC the user, no need to disconnect the user manually 🙂 ) Look the next article which have EXPIRATION post.

Following is an workaround for it.

Make the following bash script. It will check for online users, and will check if those users have quota limit using ‘Mikrotik-Total-Limit’ attribute. Then it will check there usage against quota limit. If it will found above quota, it will simply disconnect users, else ignore. You can add this script in crontab to run every X minutes.

#!/bin/bash
#set -x
# HEADER -----------
# SCRIPT to fetch data of active radius users into file, then check there quota limit against there usage.
# if quota is over , disconnect them.
# Syed Jahanzaib / aacable@hotmail.com / https://aacable.wordpress.com
# 17-MAR-2016

# Setting FILE Variables
TMPFILE="/tmp/activeusers"
FINALFILE="/tmp/finalfile"

# Make list of ONLINE USERS using radwho command, very handy 🙂
radwho  | awk '{print $2}' | sed '1d' > $TMPFILE
# if you fail to configure radwho, then use following
# mysql -uroot -pSQLPASS --skip-column-names -e "use radius; SELECT username FROM radacct WHERE acctstoptime IS NULL;" | cut -f1 -d/ 

# Mikrotik NAS Details
NAS="101.11.11.255"
NASPORT="1700"
SECRET="12345"
CURDATE=`date`

# MYSQL user credentials
SQLUSER="root"
SQLPASS="zaib1234"

# Apply Formula to get QUOTA limit data for each user in $FINALFILE (EXCLUDING USER WHO DONT HAVE ANY QUOTA LIMIT USING MIKROTIK-TOTAL-LIMIT ATTRIBUTE)
num=0
cat $TMPFILE | while read users
do
num=$[$num+1]
ACTIVEID=`echo $users | awk '{print $1}'`
mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT username,value FROM radcheck WHERE attribute='Mikrotik-Total-Limit' AND username='$ACTIVEID';" > $FINALFILE
done

# Apply Formula to get username and QUOTA LIMIT from $FINALFILE and check there usage againts assigned quota
num=0
cat $FINALFILE | while read users
do
num=$[$num+1]
username=`echo $users | awk '{print $1}'`
QLIMIT=`echo $users | awk '{print $2}'`
QUSED=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT ((SUM(AcctInputOctets)+SUM(AcctOutputOctets))) FROM radacct WHERE UserName='$username'"`

# PRINT GENERAL INFO
echo "------ $CURDATE"
echo "$username QUOTA LIMIT= $QLIMIT"
echo "$username QUOTA USED= $QUSED"

# IF QUOTA IS ABOVE LIMIT, DISCONNECT USER USING RADCLIENT OR YOU CAN CHANGE THE USER SERVICE AS WELL 🙂 / zaib
if [ $QUSED -gt $QLIMIT ]
then
echo "QUOTA REACHED! Disconnecting $username from NAS $NAS"
echo user-name=$username | radclient -x $NAS:$NASPORT disconnect $SECRET

# ELSE JUST SHOW USER USED DATA WHICH IS IN LIMIT AT A MOMENT / zaib
else
echo "$username quote is under Limit"
echo "------"
fi
done

> $TMPFILE
> $FINALFILE
# SCRIPT END / Syed Jahanzaib

script-quota

Allah Shuker 🙂


BANDWIDTH CHANGE ON THE FLY – CHANGE OF AUTHORITY (COA) _for pppoe_

To change bandwidth speed for already connected users ON THE FLY , means without disconnecting him. Use following code. Its well tested with Freeradius 2.x and Mikrotik 6.34.2

Change the User Name / Rate Limit/ Mikrotik IP  and PORT/SECRET as per network.

echo User-Name := "zaib", Mikrotik-Rate-Limit = 512k/512k | radclient -x 101.11.11.255:1700 coa 12345

cOA


CHANGE BANDWIDTH PACKAGE TO LOWER AFTER DAILY QUOTA REACH

If you want to enforce FUP (fair usage policy) like if 1mb speed allowed user consumed X MB in a day, then his bandwidth package should DROP to lower speed, e.g: 512k for that day.

Add the COUNTER for daily counting

nano /etc/freeradius/modules/sqlcounter_expire_on_login


counter-name = Mikrotik-Total-Limit
check-name = Mikrotik-Total-Limit
reply-name = Mikrotik-Total-Limit
sqlmod-inst = sql
key = User-Name
reset = daily
query = "SELECT SUM(AcctInputOctets)+SUM(AcctOutputOctets) FROM radacct WHERE UserName='%{%k}'"
}

Now add the action for the above counter in sites-available (or enable) file

nano /etc/freeradius/sites-available/default


dailyquota {
reject = 1
}
if (reject) {
ok
update reply {
Mikrotik-Rate-Limit := "512k/512k"
Reply-Message := "You have reached your transfer limit. Limited bandwidth"
}
}

Get Online User Names

mysql -uroot -pSQLPASS --skip-column-names -e "use radius; SELECT username FROM radacct WHERE acctstoptime IS NULL;" | cut -f1 -d/ | sort | uniq -d

Sample of sites-enabled/default file

authorize {
### ZAIB Section-1 Start Here ##
preprocess
chap
mschap
digest
# If user name not found, print error
sql{
notfound = 1
}
if(notfound){
update reply {
Reply-Message = 'Username not found'
}
reject
}

# Check mac, if invalid, then give this user ip from expired-pool
checkval{
reject = 1
}
if(reject){
ok
update reply {
Reply-Message := "Incorrect MAC!"
Framed-Pool := "expired-pool"
Mikrotik-Rate-Limit := "1k/1k"
}
}

# If user is expired by date, then provide him from expired pool
expiration{
userlock = 1
}
if(userlock){
ok
update reply {
Reply-Message := 'Exp-Mod-Reply: Your account has expired.'
Framed-Pool := "expired-pool"
Mikrotik-Rate-Limit := "1k/1k"
}
pap
}
}

authenticate {
Auth-Type PAP {
pap
}
Auth-Type CHAP {
chap
}
Auth-Type MS-CHAP {
mschap
}
digest
unix
}

preacct {
preprocess
acct_unique
suffix
}

accounting {
detail
unix
sql
exec

}
session {
sql
}

### ZAIB Section-2 Start Here ## Default error
post-auth {
exec
Post-Auth-Type REJECT {
update reply {
Reply-Message = 'Wrong Password'
}
sql
attr_filter.access_reject
}
}
### ZAIB Section-2 ENDS Here ##

pre-proxy {
}

post-proxy {
eap
}

USERS file

DEFAULT Auth-Type := PAP

SIMULTANOUS-USE is ignored

in NAS type, make sure you select nas type to other if you are using Mikrotik, or else sim-use will not be checked on user login.


Reject Authentication based on RADGROUP

Create group name entry like disabled in radgroupcheck table,

radgroupcheck.JPG

now tag user name with this group name in radusergroup

radusergroup


Regard’s

Syed Jahanzaib

September 29, 2011

Howto Create HTTP File Sharing Server with Freeradius Backend + [Daloradius Frontend Optional]

Filed under: Linux Related, Mikrotik Related — Tags: , , , , , — Syed Jahanzaib / Pinochio~:) @ 2:06 PM

      

Following is a complete guide on howto setup Apache to use FreeRadius authentication module along with DALORADIUS as a front-end. You can also use this guide to create full featured RADIUS server for your MIKROTIK or any other NAS which have external RADIUS authentication support in it.
Also this guide will illustrate you howto configure DALORADIUS. which is an advanced RADIUS web management application aimed at managing hotspots and general-purpose ISP deployments. It features user management, graphical reporting, accounting, a billing engine. It  is basically an nice GUI Frontend to control FREERADIUS. Using DR, you can create single/batch users, hotspot tickets, create plans n packages etc etc.

In the end I will show you howto create a APACHE base file sharing server which will use FREERADIUS for authentication 🙂

Few months back , @ my friend’s cable.network , I installed  Mikrotik along with DMASOFTLAB RADIUS MANAGER which also uses FREERADIUS as backend authentication mechanism. They also had a 4 TB of windows IIS base FTP sharing server for Videos, Mp3, Games and etc for LAN users. All network was running on private ips, so setting authentication on sharing server so only valid users can access FTP was a headache, so for the time being I placed FTP server behind Mikrotik DMZ so that only pppoe dialer connected users can access them, BUT this topology had a negative impact on overall Mikrotik performance because huge amount of (JUNK FTP) irrelevant traffic was going through the router which was increasing overall load on MT, so I decided to overcome this problem by changing the sharing server operating system from Microsoft Windows to UBUNTU Linux, and then I placed  it on users subnet and then link this sharing server [apache] authentication with freeradius. This way I managed to solve the problem. This guide will show you how I exactly did this.

Here we go . . .

We will divide this article in two categories.

1) FREERADIUS + MYSQL + DALORADIUS

2) How to authenticate Apache 2 with Radius

[Please note that I am using UBUNTU 10.4 and ip address is 192.168.2.1, all packages are installed in this single box for testing purpose,  you can separate them as per your requirements]

1) Installing FREERADIUS Server along with MYSQL+DALORADIUS :

I prefer installing the whole pre-requisite LAMP package (lamp-server stands for Linux-Apache-MySQL-PHP server). First We install lamp-server using the command below:

sudo tasksel install lamp-server

(you will need to enter root password, which is “123”  in my case , to continue the installation)

Now Install freeradius package

sudo apt-get install freeradius

Install freeradius ldap authentication

sudo apt-get install freeradius-ldap

Install freeradius to run with mysql

sudo apt-get install freeradius-mysql

After finishing the above installations, restart the FreeRADIUS service

sudo /etc/init.d/freeradius restart

If you are using Ubuntu, remove /commend the IPV6 entry from /etc/hosts

nano /etc/hosts
# The following lines are desirable for IPv6 capable hosts
# ::1     localhost ip6-localhost ip6-loopback

Now, you can test the Radius Server using radtest package, the command will be as below:

radtest radius 123 localhost 1812 123
(you will see its result something like below)
Sending Access-Request of id 198 to 127.0.0.1 port 1812
User-Name = "radius"  User-Password = "123" NAS-IP-Address = 127.0.1.1
NAS-Port = 1812 rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=198, length=20
Which shows your RADIUS Server is in working condition. 

Now Download DALORADIUS which is hosted on sourceforge at the address of http://sourceforge.net/projects/daloradius/ and you may get the latest release from there ( I used 0.9.9) or use the wget command to download in any temp folder e.g /temp

mkdir /temp
cd /temp
wget http://citylan.dl.sourceforge.net/project/daloradius/daloradius/daloradius0.9-9/daloradius-0.9-9.tar.gz
tar -zxvf daloradius-0.9-9.tar.gz
mv daloradius-0.9-9.tar.gz daloradius
cp daloradius/ /var/www -R
chown www-data:www-data /var/www/daloradius -R chmod 644 /var/www/daloradius/library/daloradius.conf.php

MYSQL Database Setup

Now, we create the database for FreeRADIUS and an user account which will be used by FreeRADIUS to access into database. then import both freeradius and daloradius tables using this schema:
We will run the following command to Login MySQL Database

mysql -u root -p123
CREATE DATABASE radius;
grant all privileges on radius.* to 'radius'@'localhost';
quit

Now Import Daloradius mysql tables . . .

cd /var/www/daloradius/contrib/db/
mysql -u root -p radius < fr2-mysql-daloradius-and-freeradius.sql
mysql -u root -p radius < mysql-daloradius.sql

Database Connection SetupNow, simply adjust the MySQL database information in daloRADIUS’s config file.

cd /var/www/daloradius/library/
nano -w daloradius.conf.php
$configValues['FREERADIUS_VERSION'] = '2';
$configValues['CONFIG_DB_PASS'] = '123';
$configValues['CONFIG_DB_TBL_RADUSERGROUP'] = 'radusergroup';

Freeradius + mysql + daloradius Installation Complete.
Point your browser to

http://192.168.2.1/daloradius

Login to the management:

username: administrator
password: radius

Here you can add users / plans etc etc. Please see daloraidus web site for more support info. Add some test users so that you test it later when apache ask authentication.
You can also test this user with radtest.

radtest testuser testpassword localhost 1812 123

How to authenticate Apache 2 with RADIUS.

First we will install Apache radius module :

apt-get install libapache2-mod-auth-radius
a2enmod auth_radius

Now open /etc/apache2/apache2.conf and add the following lines to end of file,

AddRadiusAuth localhost:1812 123 5:3
AddRadiusCookieValid 1

Now For example we have mounted our 1 TB sharing Harddisk in /mnt/test and we want that if user try to open http://192.168.2.1/test in there browser , an Authentication Popup must appear to force user enter there valid radius user id password to further proceed, then Add the following lines in /etc/apache2/apache2.conf  in the end.

Alias /test /mnt/test
<Directory /mnt/test>
Options Indexes FollowSymlinks
AuthType Basic
AuthName "AA File Server Authentication"
AuthBasicAuthoritative Off
AuthBasicProvider radius
AuthRadiusAuthoritative on
AuthRadiusActive On
Require valid-user </Directory>


Now Point your browser to http://192.168.2.1/test and you will see authentication popup window , something like below image.


If the user supply wrong id password, he will see


If user enters valid id password, he will see the content of test folder.

Alhamdolillah 🙂
Allah Hafiz,

Regard’s
SYED JAHANZAIB

%d bloggers like this: