Syed Jahanzaib Personal Blog to Share Knowledge !

October 8, 2018

PAKRAD – Reseller,Dealer & Sub-Dealer base ISP Billing System

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

pakrad

PAK Radius” is a customized ISP billing system designed in Linux (Ubuntu 16/x64) using Freeradius v 2.2.8 as backend with PHP 7.0.28 as fronted Dynamic & responsive GUI panel. It’s developed to cater local desi market requirements.

It’s built on concept where ISP have Super Admin > Admin / Reseller / Dealer & Sub-Dealer base clientele. This system is continuously under development & new features / enhancements and improvements are begin made on a regular basis.

Some screenshots …

pakrad1

dashboard view.png

free user

 


Workflow ….

workflow.jpg

  • Admin cannot create users directly, Admin first creates reseller , assign him some services with desired rates, & transfer some amount in the reseller wallet/account,
  • Reseller cannot create users directly, Reseller first creates Dealer, assign him some services with desired rates, & transfer some amount in the dealer wallet/account,
  • Dealer can create his users upto the limit of his wallet/account, Dealer can also create his sub dealer and assign him required services and assign there rates to his sub-dealer.

Some major Features …

Exclusive.jpg

billing dept.png

  • Dynamic , responsive & appealing web design (advance responsive PHP designing)
  • With Open source codes, you cam modify it as per your requirements
  • Just one time license cost & you get Life time license
  • Unlimited number of users / NAS support
  • Generalized information on front panel for each manager, a glance window 
  • Specially customized designed for local Internet Cable Services providers , with TO THE POINT options only, no hanky panky
  • Purely Designed as a Reseller base system, Example – Admin > Reseller > Dealer / Sub-dealer base system
  • Profit calculation for view purposes
  • Free ID’s assignment for dealer for there Test purposes.
  • Different services rates assignment for different re-sellers / dealers / sub-dealers
  • Each Manager can view there dealer/sub-dealer billing easily.Good Financial modules for tracking all sort of transactions for dealer / sub-dealers
  • Cash base system for reseller/dealer/sub-dealer charging
  • `Get Back Cash` System to pull back cash sent to the managers
  • Dynamic day & night bandwidth configuration available , compatible with all version of mikrotik routers
  • Quota base packages configuration available
  • Prevention of user DELETE action to prevent any misuse, user can be disabled only!
  • Users unsuccessful dialing attempts for each manager
  • Users connecting devices information recorded in user table , example tplink/tenda etc
  • Reports for Package wise consumption
  • Good reporting section for Users usage reports / graphs reports / User charges reports 
  • Managers last login information
  • Email/SMS Alerts for various actions & multiple users email/sms alert example expiry / renewal / general notification sending to active users etc [currently its being done via bash scripts, but soon they will be added in GUI]
  • Strong Bug Free Back-end design, capable of connecting thousands of users in just few seconds ! 
  • Years of experiences compiled in one single package ~

Many other features added based on local operators feedback to suite local market requirements !


Demo link: 

demo.png


Regard’s
Syed Jahanzaib 

September 24, 2018

FREERADIUS WITH MIKROTIK – Part #20 – Enforcement of lowercase in username

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

uppwer lower logo

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 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 a generic FreeRADIUS Version 2.2.8 as a billing system in Ubuntu 16.04.3 LTS Server.
  • Freeradius is installed by apt-get default repository.
  • Mikrotik ver 6.43.x is being used as NAS.

Problem:

By default freeradius allows upper/lowercase in username, so If user configures  username in upper/lower mix case in his dialer/router then it will be logged same in RADACCT table. This is not a problem by design, but since we are using some external bash scripts to perform various operations like sending COA for bandwidth change on the fly/disconnection etc & the script is picking usernames from our user able which has all lowercase , the NAS does not recognize it for user who have uppercase defined.

Task:

We would like to restrict that all usernames must be entered in lowercase at user side , if not then reject the authentication to enforce our policy forcefully.

Solution:

Edit dialup.conf

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

& search following … Comment below lines, this code allows upper/lower case in user names …

# The default queries are case insensitive. (for compatibility with
# older versions of FreeRADIUS)
authorize_check_query = "SELECT id, username, attribute, value, op \
FROM ${authcheck_table} \
WHERE username = '%{SQL-User-Name}' \
ORDER BY id"
authorize_reply_query = "SELECT id, username, attribute, value, op \
FROM ${authreply_table} \
WHERE username = '%{SQL-User-Name}' \
ORDER BY id"

Now UN-COMMENT following …

# Use these for case sensitive usernames.
authorize_check_query = "SELECT id, username, attribute, value, op \
FROM ${authcheck_table} \
WHERE username = BINARY '%{SQL-User-Name}' \
ORDER BY id"
authorize_reply_query = "SELECT id, username, attribute, value, op \
FROM ${authreply_table} \
WHERE username = BINARY '%{SQL-User-Name}' \
ORDER BY id"

So after editing it would be something like …

case sensitive.PNG

Now restart freeradius service one time

service freeradius restart

After this all users authentication with uppercase will be rejected by freeradius. Use it with caution !

This is all done by default in v3…
Alan DeKok.


 

September 7, 2018

COA with Radclient workaround for RM 4.1 with Mikrotik 6.4x

Filed under: freeradius, Mikrotik Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 1:12 PM

dealing-with-dynamic-change-2

Scenario:

  • Dmasoftlab Radius Manager 4.1 with multiple services. Some of services have dynamic dynamic bandwidth scheduling for day & night. Example some services have double up mode for day , some for evening, and some for night.
  • Mikrotik 6.42.7 server with hotspot or pppoe authentication services for LAN users

Problem:

DMA Radius Manager 4.1 ‘s API functionality is broken for Mikrotik RouterOS newer versions. The 4.1 code is relying on modifying dynamic queues which had worked on 5.x version (& in some 6.2x series as well e.g: v6.29) . Any circumstances where that was doable were bugs that MikroTik has since fixed. And relying on bugs is generally a bad practice. This can be solved by using CoA instead of modifying dynamic queues which I have used in this post.

It is highly recommended that you must upgrade radius manager to latest 4.2 version which works good with new ROS.


Workaround for RM 4.1:

If for some reasons you want to stick with 4.1 version for whatsoever reason, example 4.2 version have some strict licensing policies so if still wants to use ROS latest series like 6.42.7 (as of writing this post)  , , & if you still wants to avail dynamic bandwidth changes on the fly for particular services , you can schedule following script which will run on hourly basis and will send bandwidth change request to mikrotik according to the service time.



Limitations of the Script:

  • This is a lab testing version of the script. You must modify and tune it for production use. Example the script is doing lots of sql queries, you can minimize it by creating single combined query to fetch all data from the tables, and then read values in next cmd from local file which will be much faster then querying from MySQL.
  • The service must have single time schedule. example from 08:00:00 to 20:00:00 , Multiple times for single service is not supported.
  • The time must consists of single day, it cannot overlap to next day,
  • Script will run as per cron schedule , despite you have selected specific days or not.
  • In lab I have configured it to run every hour , It will query services and its associated users. If the Start time matches , it will send bandwidth change request to the NAS, and if end time matches it will send user original package values to NAS. You can overcome repeating issue by adding additional column in the respective table and update it every time script runs which will check if it have already sent or not.
  • You should disable echoing the outputs, it will save some resources.

the Scheduler!

Either use @hourly in CRONTAB or make separate file under /etc/crond

Create new file name bw in /etc/crond/ with following contents

touch /etc/cron.d/bw
nano /etc/cron.d/bw

& add following line in it,

0 * * * * root /temp/bw.sh

Save & Exit …


the Script!

mkdir /temp
touch /temp/bw.sh
chmod +x /temp/bw.sh
nano /temp/bw.sh

& add following …

#!/bin/bash
# Following script is made specifically for Dmasoftlab radius manager 4.1.x for New Mikrotik 6.3x.x+ , 6.4x.x
# It will check "rm_specperbw" table, and if found any service entry,
# It will query that service and make list of users attached to this service,
# Then it will query next package , start/end time, and will perform actions accordingly
# Syed Jahanzaib
# Created: 6-SEP-2018
# Last Modified : 1-NOV-2018
# Tested on Ubuntu OS Only
#set -x
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
#################
# CHANGE these
SQLID="root"
SQLPASS="MYSQL_ROOT_PASS"
NAS_COA_PORT="1700"
DB="radius"
SRV="mysql"
#################

#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 "Bandwidth poller Last executed @ $currenttime by the CRON scheduler ... Powered by SYED.JAHANZAIB"
echo "- Script Start Time - $currenttime
"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
#Table which contain main users information
USER_TABLE="rm_users"
SRV_BW_DB="rm_services"
#Table which contains service name id which will be scanned for user and packages
DYN_BW_TABLE="rm_specperbw"
USER_SERVICE_TABLE="rm_services"
# Temp file where services/users list will be saved
TMP1="/tmp/bwsch_srv.txt"
TMP2="/tmp/bwsch_users.txt"
TMP3="/tmp/bwsch_users_final.txt"
RADCLIENT="/usr/local/bin/radclient"
> $TMP1
> $TMP2
> $TMP3
# Check if $SRV (in this case mysql) is running or not, if NOT, then exit the script
SRVSTATUS=$(pgrep $SRV | wc -l);
if [ "$SRVSTATUS" -ne 1 ];
then
echo "- $SRV service is down. Pleasec check your $srv service first.
- Exiting ...";
exit 1
else
echo "- INFO: $SRV service is accessible. Proceeding further ... OK"
fi
# Check if DB (in this case radius ) is accessible or not, if NOT, then exit the script
RESULT=`$CMD "SHOW DATABASES LIKE '$DB'"`
if [ "$RESULT" == "$DB" ]; then
echo "- INFO: $DB database exist. Proceeding further ... OK"
else
echo "- ERROR: $DB database does not exist! Sending EXIT signals ..."
exit 1
fi

# Look for services that have Dynamic bandwidth change (and remove duplicate entries as well becasue of multipel time definitiosn ins ingle service)
$CMD "use $DB; select srvid from $DYN_BW_TABLE" | sort -u >> $TMP1
TOTSRV=`cat $TMP1 | wc -l`
echo "- INFO: Total number of services with Dynamic bandwidth enabled = $TOTSRV / No.s ... OK"
if [ ! -s $TMP1 ]
then
endtime=$(date +%H:%M:%S)
echo "- WARNING: No SERVICES found to check for bandwdith changing in $DYN_BW_TABLE
- Script Ends Here

- EXITING peacefully...
- Script End Time - $endtime
"
exit 1
fi

# If required service found then look for Users
num=0
cat $TMP1 | while read srvid
do
num=$[$num+1]
SRVID=`echo $srvid |awk '{print $1}'`
$CMD "use $DB; select username from $USER_TABLE where srvid ='$SRVID';" >> $TMP2
done
TOTUSR=`cat $TMP2 | wc -l`
echo "- INFO: Total number of users with Dynamic bandwidth enabled = $TOTUSR / No.s ..."

# Remove duplicate users , If any (it was dueto the fact if the service have multiple time defined)
if [ ! -s $TMP2 ]
then
endtime=$(date +%H:%M:%S)
echo "- WARNING: No User found for bandwidth upgrade in DMA RADIUS MANAGER TABLE '$DYN_BW_TABLE' , Sending EXIT signals ...

- Script Ends Here...
- EXITING peacefully...
- Script End Time - $endtime
"
exit 1
fi

# Run loop forumla to run CMD for single or multi usernames
echo "- INFO: Checking for Dynamic Bandwidth Policies and implemnt change on the fly for online users , if any ...

"
num=0
cat $TMP2 | while read users
do
num=$[$num+1]
USERNAME=`echo $users |awk '{print $1}'`
SRVID=`$CMD "use $DB; select srvid from $USER_TABLE where username ='$USERNAME';"`
DN_ST=`$CMD "use $DB; select starttime from $DYN_BW_TABLE where srvid ='$SRVID';" |awk 'FNR == 1'`
DN_ET=`$CMD "use $DB; select endtime from $DYN_BW_TABLE where srvid ='$SRVID';" |awk 'FNR == 1'`

#######################
##### UP-GRADE SECTION
#######################
if [[ "$currenttime" > "$DN_ST" ]] && [[ "$currenttime" < "$DN_ET" ]]; then
#If time matches then take upgrade action
# If user is Online UPGRADE its package
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
if [ ! -z "$ACCTSESID" ]; then
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' ;"`
USER_IP=`$CMD "use $DB; select framedipaddress from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
dlrate_c=`$CMD "use $DB; select dlrate from $DYN_BW_TABLE where srvid ='$SRVID';" |awk 'FNR == 1'`
ulrate_c=`$CMD "use $DB; select ulrate from $DYN_BW_TABLE where srvid ='$SRVID';"|awk 'FNR == 1'`
ulrate=$(echo $(( $ulrate_c / 1024 )))k
dlrate=$(echo $(( $dlrate_c / 1024 )))k
DN_BWPKG="$ulrate/$dlrate"
echo "- UPGRADE ***** - $USERNAME / $USER_IP / $ACCTSESID is online, and eligible for package UPGRADE to new package $DN_BWPKG @ $currenttime ..."
#for pppoe, enable following line
echo User-Name=$USERNAME,Acct-Session-Id=$ACCTSESID,Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit=\"$DN_BWPKG\" | $RADCLIENT -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
#for hotspot, enable following line
#echo Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit=\"$DN_BWPKG\" | radclient -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
if [ -z "$ACCTSESID" ]; then
echo "- INFO: UPGRADE ***** - $USERNAME is eligible for package UPGRADE to new package $DN_BWPKG @ $currenttime BUT NOT ONLINE , no need to take action ..."
fi
fi
else
#######################
##### DOWNGRADE SECTION
#######################
# If package DOWNgrade time is matched in services & packages have not changed already, then do it now - zaib
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
if [ ! -z "$ACCTSESID" ]; then
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' ;"`
USER_IP=`$CMD "use $DB; select framedipaddress from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
dlrate_c=`$CMD "use $DB; select downrate from $USER_SERVICE_TABLE where srvid ='$SRVID';" |awk 'FNR == 1'`
ulrate_c=`$CMD "use $DB; select uprate from $USER_SERVICE_TABLE where srvid ='$SRVID';"|awk 'FNR == 1'`
ulrate=$(echo $(( $ulrate_c / 1024 )))k
dlrate=$(echo $(( $dlrate_c / 1024 )))k
DN_BWPKG="$ulrate/$dlrate"
echo "- INFO: DOWNGRADE ***** - $USERNAME / $USER_IP / $ACCTSESID is online, and eligible for package DOWNGRADE to old package $DN_BWPKG @ $currenttime ..."
#for pppoe, enable following line
echo User-Name=$USERNAME,Acct-Session-Id=$ACCTSESID,Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit=\"$DN_BWPKG\" | $RADCLIENT -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
#for hotspot, enable following line
#echo Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit=\"$DN_BWPKG\" | radclient -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
if [ -z "$ACCTSESID" ]; then
echo "- INFO: DOWNGRADE ***** - $USERNAME is eligible for package DOWNGRADE to OLD package $DN_BWPKG @ $currenttime BUT NOT ONLINE , no need to take action ..."
fi
fi
fi
done
endtime=$(date +%H:%M:%S)
echo "
- Script Ends Here
- EXITING peacefully ...
- Script End Time - $endtime
"
# Add Script end execution entry in the /var/log/syslog to see if the script got ended
currenttime=$(date +%H:%M:%S)
logger "Bandwidth poller script ended @ $currenttime ... Powered by SYED.JAHANZAIB"
# Script for dma 4.1.x COA for new Mikrotik 6.3+ - Designed by Syed Jahanzaib

Results:

radius bw poller result.PNG

 

Regard’s
Syed Jahanzaib

July 24, 2018

FREERADIUS WITH MIKROTIK – Part #19 – Restricting user by Service Type

Filed under: freeradius — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 2:30 PM

passport-control-3033049_960_720


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.

~ FreeRADIUS is a Dark Art ~

Regard's
Syed Jahanzaib~

Scenario:

We have a Mikrotik routerboard working as a HOTSPOT & PPPoE server for local users. Freeradius is configured as billing system.

Requirements:

We want to restrict users connection based on there service type.

Example

  1. User1 is allowed to connect from PPPoE Dialer Only,
  2. User2 is allowed to connect from HOTSPOT web login only,

Since we have our own freeradius build setup which has USERS table with various columns therefore we will manipulate this table and use the SQL famous IF statements in the AUTHORIZE section to fulfill our requirements.

the USERS table contains various information for users, Sample is attached below ,

srvtype.JPG

We will be using two columns to match user connection verification,

  1. SRVTYPE
  2. SRVTYPE_DESCR
  • SRVTYPE variable will be used to match if the requesting user have correct service type
  • if NOT matched then show SRVTYPE_DESCR variable which is actually a friendly version for display purpose in REPLY attribute 🙂

1# FREERADIUS AUTHORIZE Section Configuration

Edit `/etc/freeradius/sites-enabled/default` file

nano /etc/freeradius/sites-enabled/default

Use the following in AUTHORIZE section ,

if ("%{sql: select srvtype from users where username = '%{User-Name}'}" != "%{Service-Type}") {
update reply {
Reply-Message = "Error: %{User-Name} is allowed to connect from %{sql:SELECT srvtype_descr from users where username = '%{User-Name}';} only!"
}
update control {
Auth-Type := "Reject"
}
}

Test Ride …

From Hotspot ID, try to dial via pppoe dialer,

Sending Access-Reject of id 150 to 101.11.50.50 port 51555
Reply-Message = "Error: 2C:44:FD:68:C0:18 is allowed to connect from Hotspot only!"

Now using PPPOE account, try to login via hotspot login page,

Sending Access-Reject of id 145 to 101.11.50.50 port 52841
Reply-Message = "Error: test is allowed to connect from PPP Dialer only!"

ppp failed for hotspot id.JPG


 

July 17, 2018

FREERADIUS WITH MIKROTIK – Part #17 -Retrieve User Password via SMS

Filed under: freeradius — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 4:57 PM

password_en

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 a FREERADIUS server configured as AAA for local ppp users. All accounts username name/passwords are added in RADCHECK table as per Freeradius default scheme. Passwords are stored as Cleartext-Password As showed in the image below …

fr cleartext password in radcheck.JPG

We also have playSMS configured to facilitate various SMS base functions including user information , renewal, kicking etc with various controls and checks.


Requirements:

IF user sends sms to our billing system (to playsms server) with specific keywords like

forgotpass USERNAME

then the system should perform various checks like

  • Compare the sender number with the mobile number associate with USERNAME,
  • Check account status like IF account is active or expired either by date or quota or uptime,
  • If all replies are clear, then retrieve password from radcheck value, and send back to user as return reply.

in this example I am using dmasoftlab RADIUS Manager which provides its own builtin tool like `RMAUTH` which we will be using for some controls verification. Although DMA do provides its own method to retrieve password, but regardless of it, we are sharing idea which can be used to perform other functions as well too.

This is just for example purposes only, you can add remove your own controls.

Build your own solution, Sky is the only limit !

creativity-is-intelligence-having-fun-600x315.jpg

 


Bash Script ! forgotpass.sh

#!/bin/bash
#set -x
DATE=$(date '+%Y-%m-%d__%H-%M-%S')
# MYSQL USER NAME AND PASSWORD Variables
SQLUSER="root"
SQLPASS="SQLROOTPASS
SQLHOST="localhost"
SQLPORT="3306"
DB="radius"
export MYSQL_PWD=$SQLPASS
CURRENCY="PKR"
CMD="mysql -u$SQLUSER -p$SQLPASS -h$SQLHOST --port=$SQLPORT --skip-column-names -e"
RMAUTH=`/usr/local/bin/rmauth`

FOOTER="Powered by Syed Jahanzaib"
TMP="/tmp/forgotpass_incoming_sms__$DATE.sms"
> $TMP

# Strip user name and card number separate oterhwise playsms will treat both variables as one
echo $1 $2 > $TMP
USR=`cat $TMP | awk '{print $1}'`
SENDER_MOBILE=`cat $TMP | awk '{print $2}'`

# Check User variable if supplied or not
if [ -z "$USR" ]; then
echo "Username not supplied!"
exit 1
fi
# Check Mobile if supplied or not,
if [ -z "$SENDER_MOBILE" ]; then
echo "Mobile Number not supplied!"
exit 1
fi

#Check if user is in DB or not
IS_USER_VARIABLE_VALID=`$CMD "use $DB; SELECT username FROM rm_users WHERE username = '$USR';"`
if [ -z "$IS_USER_VARIABLE_VALID" ]; then
echo "Username not Found in the System!"
exit 1
fi

# Verify mobile number with sender number
USER_MOB_IN_DB=`$CMD "use $DB; SELECT mobile FROM rm_users WHERE username = '$USR'";`
if [ "$SENDER_MOBILE" != "$USER_MOB_IN_DB" ]; then
echo "Sender Mobile Number could not be verified with $USR account!"
exit 1
fi

IS_USER_ACTIVE=`/usr/local/bin/rmauth 127.0.0.1 $USR 1 |grep -c "Total traffic limit reached!"`
if [ "$IS_USER_ACTIVE" -eq 1 ];then
echo "Account quota limit have finished , please recharge it first!"
exit 1
fi

IS_USER_ACTIVE=`/usr/local/bin/rmauth 127.0.0.1 $USR 1 |grep -c "expired!"`
if [ "$IS_USER_ACTIVE" -eq 1 ];then
echo "Your account have expired , please recharge it first!"
exit 1
fi

#$CMD "use $DB; UPDATE rm_users SET password = MD5('$SENDER_MOBILE') WHERE username = '$USR';"
CUR_PASS=`$CMD "use $DB; select value from radcheck where attribute = 'Cleartext-Password' and username = '$USR';"`
echo "Your Password is = $CUR_PASS

$FOOTER"

* playSMS Command Setting

Copy the forgotpass.sh script in /var/lib/playsms/sms_command/1/ folder, then add command in playsms As showed in the image below …

PLAYSMS SETTING FOR FORGOT COMAMND.JPG


* RESULT:

pass forgoto result.jpg

(Note: Password is different as showed in the RADCHECK, ignore it, as it was changed later)


* PLAYSMS_LOGS


127.0.0.1 localhost 2018-07-17 16:34:38 PID5b4dd44e3d47b - L2 kannel__call # start load:/var/www/playsms/plugin/gateway/kannel/geturl.php
127.0.0.1 localhost 2018-07-17 16:34:38 PID5b4dd44e3d47b - L3 kannel__incoming # remote_addr:127.0.0.1 remote_host:localhost t:[2018-07-17 18:34:23] q:[+923333021909] a:[Forgotpass zaib] Q:[13013] smsc:[] smsc:[]
127.0.0.1 localhost 2018-07-17 16:34:38 PID5b4dd44e3d47b - L3 recvsms # isrecvsmsd:1 dt:2018-07-17 18:34:23 sender:+923333021909 m:Forgotpass zaib receiver:13013 smsc:
127.0.0.1 localhost 2018-07-17 16:34:38 PID5b4dd44e3d47b - L2 kannel__call # end load geturl
- - 2018-07-17 16:34:38 PID5b4019f3e7183 admin L3 recvsmsd # id:7861 dt:2018-07-17 18:34:23 sender:+923333021909 m:Forgotpass zaib receiver:13013 smsc:
- - 2018-07-17 16:34:38 PID5b4019f3e7183 admin L3 recvsms_process # dt:2018-07-17 18:34:23 sender:+923333021909 m:Forgotpass zaib receiver:13013 smsc:
- - 2018-07-17 16:34:38 PID5b4019f3e7183 admin L3 gateway_decide_smsc # SMSC supplied:[] configured:[] decided smsc:[]
- - 2018-07-17 16:34:38 PID5b4019f3e7183 admin L3 sms__command # command_exec:/var/lib/playsms/sms_command/1/forgotpass.sh 'zaib' '+923333021909'
- - 2018-07-17 16:34:38 PID5b4019f3e7183 admin L3 sms__command # command_output:Your Password is = mypass123 Powered by Syed Jahanzaib

 

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,

June 29, 2018

FREERADIUS WITH MIKROTIK – Part #15 – Dynamic NAS Clients

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

fre

rapid change.jpg

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 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:

In freeradius , we have to add NAS client entries either in clients.conf or in nas table to allow communication from NAS  with freeradius services (for AAA requests). This is good from security perspective to allow only specific IP addresses, BUT what if your NASes are spreaded across different location (geographically different places) and have dynamic IP addresses like DSL , 3G/4G etc.

As a workaround we can setup a vpn server on our central location and connect all remote NAS (es) to this vpn server but this requires additional configuration at server end and all client end’s as well.

Another workaround is to ALLOW all ip addresses to communicate with FR service which is really a BAD idea from security perspective 🙂 As ALAN once said:

Are you willing to let anyone on the net send RADIUS packets to your RADIUS server?

Another workaround is to allow only specific IP subnet range , for this you have to inquire about the IP range that ISP is assigning to that particular NAS & allow this range in your clients.conf .


1# Howto enable freeradius to inquire about NAS clients using SQL NAS table

To enable freeradius to read clients details from NAS table in SQL, We need to modify in sql.conf file …

Edit following file /etc/freeradius/sql.conf

nano /etc/freeradius/sql.conf file

Uncomment the following

readclients = yes

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

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

Now add one entry in this table & restart your Freeradius service.

mysql> select * from nas;
+---+---------------+------------+-------+------+------------+------+------+---------------+------+
| 1 | 101.11.11.255 | testmk | other | NULL | testing123 | NULL | NULL | RADIUS Client | 3799 |
+---+---------------+------------+-------+------+------------+------+------+---------------+------+
1 rows in set (0.00 sec)

This table contains data about your NASes (like mikrotik etc). It is more convenient to to maintain the NAS details in the database.

NOTE: Whenever you add / edit / remove any entry in clients.conf or NAS table, you must restart freeradius service by following cmd

service freeradius restart

 

You can use following NAS table also, adding just for reference purposes …

-- phpMyAdmin SQL Dump
-- version 4.5.4.1deb2ubuntu2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 29, 2018 at 03:25 PM
-- Server version: 5.7.21-0ubuntu0.16.04.1-log
-- PHP Version: 7.0.22-0ubuntu0.16.04.1

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 utf8mb4 */;

--
-- Database: `radius`
--

-- --------------------------------------------------------

--
-- Table structure for table `nas`
--

CREATE TABLE `nas` (
`id` int(10) NOT NULL,
`nasname` varchar(128) NOT NULL,
`shortname` varchar(32) DEFAULT NULL,
`type` varchar(30) DEFAULT 'other',
`ports` int(5) DEFAULT NULL,
`secret` varchar(60) NOT NULL DEFAULT 'secret',
`server` varchar(64) DEFAULT NULL,
`community` varchar(50) DEFAULT NULL,
`description` varchar(200) DEFAULT 'RADIUS Client',
`nas_coa_port` int(32) NOT NULL DEFAULT '3799'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `nas`
--

INSERT INTO `nas` (`id`, `nasname`, `shortname`, `type`, `ports`, `secret`, `server`, `community`, `description`, `nas_coa_port`) VALUES
(1, '10.0.0.3', 'ZAIB_CCR_GW', 'other', NULL, 'testing123', NULL, NULL, 'RADIUS Client', 3799);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `nas`
--
ALTER TABLE `nas`
ADD PRIMARY KEY (`id`),
ADD KEY `nasname` (`nasname`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `nas`
--
ALTER TABLE `nas`
MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
/*!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 */;


2# Adding NAS Clients entries in CLIENTS.CONF file

 

In /etc/freeradius/clients.conf use below format to allow either single ip, subnet, or Allow ANY IP (all all ip’s is is not recommended*)

# To allow specific NAS single IP only

# To allow ONLY specific NAS via clients.conf
client 92.168.10.1 {
secret = testing123
shortname = Mikrotik
}

# To allow specific SUBNET ip (example if remote NAS have dynamic public ip but the ip remains from specific subnet range)

client test_subnet_nas {
ipaddr = 192.168.10.0
secret = testing123
netmask = 24
}

To allow ANY ip to send request to freeradius server (not recommended)

# To allow ANY NAS client which is not recommended*
client 0.0.0.0/0 {
secret = testing123
shortname = Mikrotik
}

3# Allow NAS AAA Requests based on NAS-IDENTIFIER

In Some situations we would like to authenticate user only if its coming from SPECIFIC NAS only (not by ip, but by NAS-Identifier attribute).

Example if client request coming from NAS which have system identifiaction of ZAIB_CCR_GW , then process authentication request further ELSE REJECT !

First you need to allow NAS requests from ALL or Subnet range IP.

# To allow ANY NAS client which is not recommended*
client 0.0.0.0/0 {
secret = testing123
shortname = Mikrotik
}

Users Table Sample !

We have a user table which contains a column nas_id. We will add a SQL IF statement which will check the the connecting user NAS-Identifier & match it with users allowed nas_id in the user’s 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,
`macvendor` varchar(128) 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(20) NOT NULL,
`uptime_limit` varchar(20) NOT NULL,
`uptime_used` varchar(32) NOT NULL,
`owner` text NOT NULL,
`vlanid` varchar(32) NOT NULL,
`nas_id` varchar(32) DEFAULT NULL,
`createdon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `users`
--

Now we will add a USERS entry as sample …

INSERT INTO `users` (`id`, `username`, `password`, `firstname`, `lastname`, `email`, `mobile`, `cnic`, `srvname`, `srvid`, `expiration`, `mac`, `macvendor`, `bwpkg`, `pool`, `is_enabled`, `is_days_expired`, `is_qt_expired`, `is_uptime_expired`, `qt_total`, `qt_used`, `uptime_limit`, `uptime_used`, `owner`, `vlanid`, `nas_id`, `createdon`) VALUES
(1, 'zaib', 'zaib', 'OK', 'jahanzaib', 'aacableAThotmailDOTcom', '03333021909', '1234567890-1-1', '1mb', 9, '2018-01-04', '00:0C:29:B9:D8:A0', '', '1024k/1024k', 'public-pool', 1, 0, 0, 0, '0', '2933559', '0', '', 'xxxxxx', 'ether1-LAN-DUMMY', 'someinvalid_CCR_GW', '2018-06-29 11:06:52');

Now we will add the SQL IF statement that will actually check every incoming Authentication request for matching NAS-IDENTIFIER with nas_id column in users table.

Edit Default Sites-Enabled file,

nano /etc/freeradius/sites-enabled/default

& paste following in `Authorize` Section

if ("%{sql: select nas_id from users where username = '%{User-Name}'}" != "%{NAS-Identifier}") {
update reply {
Reply-Message = 'Error: You are not allowed to connect form this NAS ! Bingo - zaib'
}
update control {
Auth-Type := "Reject"
}
}

Save & Exit.

Now reload Freeradius in Debug Mode (by freeradius -X) & monitor the debugging.

If user will connect from another NAS (which is not matched in with nas_id column in the USERS table , he will get denied with the message.

Note: For testing purposes . I have added dummy entry in user’s nas_id column.

###############################################
# Showing relevant data only for demo purposes - Syed Jahanzaib - 29-JUN-2018
###############################################
rad_recv: Access-Request packet from host 10.0.0.1 port 49453, id=150, length=124
Service-Type = Framed-User
Framed-Protocol = PPP
NAS-Port = 15729249
NAS-Port-Type = Ethernet
User-Name = "zaib"
Calling-Station-Id = "24:26:42:D4:BC:43"
Called-Station-Id = "service1"
NAS-Port-Id = "ether10"
User-Password = "zaib"
NAS-Identifier = "ZAIB_CCR_GW"
NAS-IP-Address = 10.0.0.1

# Executing section authorize from file /etc/freeradius/sites-enabled/default

++? if ("%{sql: select nas_id from users where username = '%{User-Name}'}" != "%{NAS-Identifier}")
sql_xlat
expand: %{User-Name} -> zaib
sql_set_user escaped user --> 'zaib'
expand: select nas_id from users where username = '%{User-Name}' -> select nas_id from users where username = 'zaib'
rlm_sql (sql): Reserving sql socket id: 25
sql_xlat finished
rlm_sql (sql): Released sql socket id: 25
expand: %{sql: select nas_id from users where username = '%{User-Name}'} -> ZAIB_CCR_GW1
expand: %{NAS-Identifier} -> ZAIB_CCR_GW
? Evaluating ("%{sql: select nas_id from users where username = '%{User-Name}'}" != "%{NAS-Identifier}") -> TRUE
++? if ("%{sql: select nas_id from users where username = '%{User-Name}'}" != "%{NAS-Identifier}") -> TRUE
++if ("%{sql: select nas_id from users where username = '%{User-Name}'}" != "%{NAS-Identifier}") {
+++update reply {
+++} # update reply = noop
+++update control {
+++} # update control = noop
++} # if ("%{sql: select nas_id from users where username = '%{User-Name}'}" != "%{NAS-Identifier}") = noop

Found Auth-Type = Reject
Auth-Type = Reject, rejecting user
Failed to authenticate the user.
Using Post-Auth-Type Reject

# Executing group from file /etc/freeradius/sites-enabled/default
+group REJECT {
++update reply {
++} # update reply = noop
[sql] expand: %{User-Name} -> zaib
[sql] sql_set_user escaped user --> 'zaib'

[sql] expand: INSERT into radpostauth (username, pass, mac, nasipaddress, reply, authdate, reason) values ('%{User-Name}', '%{User-Password:-Pap-Password}', '%{Calling-Station-Id}', '%{NAS-IP-Address}', '%{reply:Packet-Type}', NOW(), '%{reply:Reply-Message}') -> INSERT into radpostauth (username, pass, mac, nasipaddress, reply, authdate, reason) values ('zaib', 'zaib', '24:26:42:D4:BC:43', '10.0.0.1', 'Access-Reject', NOW(), 'Error: You are not allowed to connect form this NAS =21')
rlm_sql (sql) in sql_postauth: query is INSERT into radpostauth (username, pass, mac, nasipaddress, reply, authdate, reason) values ('zaib', 'zaib', '24:26:42:D4:BC:43', '10.0.0.1', 'Access-Reject', NOW(), 'Error: You are not allowed to connect form this NAS =21')
rlm_sql (sql): Reserving sql socket id: 24
rlm_sql (sql): Released sql socket id: 24
++[sql] = ok
[attr_filter.access_reject] expand: %{User-Name} -> zaib
attr_filter: Matched entry DEFAULT at line 11
++[attr_filter.access_reject] = updated
+} # group REJECT = updated
Delaying reject of request 3 for 1 seconds
Going to the next request
Waking up in 0.9 seconds.
Sending delayed reject for request 3
Sending Access-Reject of id 150 to 10.0.0.1 port 49453
Reply-Message = "Error: You are not allowed to connect form this NAS !"
Waking up in 4.9 seconds.
Cleaning up request 3 ID 150 with timestamp +104
Ready to process requests.

.

& if the users request matches , he will be granted access (off course after all other checks) 🙂


Regard’s
Syed Jahanzaib

 

June 27, 2018

FREERADIUS WITH MIKROTIK – Part #14 – Dynamic Bandwidth Change on the FLY using COA with radclient

Filed under: freeradius — Tags: , , , , , , — Syed Jahanzaib / Pinochio~:) @ 1:33 PM

fre

bandwidth

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 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.

This particular script was tested in Virtual environment only, therefore consider this posting as an reference only, donot use it in production environment.

Regard's
Syed Jahanzaib~

Scenario:

We have a generic FreeRADIUS Version 2.2.8 based billing system in Ubuntu 16.04.3 LTS Server. Users are authenticating to NAS (Mikrotik) which is using Freeradius as its AAA Server.


Requirement:

Currently users packages are flat 1mb,  2mb and so on. We would like to introduce different bandwidth for day and night for specific services. Upgrade/Downgrade of user package should be done by dynamically with COA, so that package changes must be done on the fly without disconnecting user.

I made  a simple bash script to perform the job, but that required different cron jobs to start/end the packages, also if the server was powered off on that particular time then schedule will be missed till next run. This was major PITA. Therefore I added few more checks and balances so that if the job misses its schedule time, it will execute it self BUT avoiding re-running of same action.

Its a bit complicated piece of BASH scripting , but so far doing its job.


Software / Hardware Components Used:

  • NAS: Mikrotik CCR1036 / Firmware: 6.42.1
  • Radius: Ubuntu 16.04.3 LTS Server Edition / 64bit
  • FreeRADIUS Version: 2.2.8 using apt-get installation

(more…)

June 26, 2018

BASH Script with time & single execution per day checks

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

 

repeat

Scenario:

We have a bash script that performs various functions related to Freeradius, including expiration checkup & various groups updates functions.

Problem:

We have schedules this script to run daily at 1700 hours, What will happen if server was powered off  at 1700 hours? Script will missed its schedule resulting in expired users account will not be disabled for that day and incorrect display of users status in front gui. Alternatively we can schedule it to run hourly but it will increase the server load because of repeated task , which is supposed to be executed only once per day

Requirements:

We need to make some checks and balances so that if script must have some intelligent IF ELSE criteria in order to check following steps …

  • Script is scheduled to run hourly, so that if it misses the 1700 hour it will be relaunched next hour,
  • If time is 1700 hours or above then execute the script,
  • If the script executes successfully , then save this result, and on next run (within current date) it should detect last run status and dont repeat the code to avid recurrence,
  • If the last run was not executed for any reason, & the time is 1700 or above then execute the script and save its run status in file,
  • Once the data changes, it should re-run the script only if the time is 1700 or above.

Solution:

the SCRIPT ! Sample Purpose only

#!/bin/bash
# Bash Script to make sure script runs at specific time, and should not run again for the same date
# record date in local file, to avoic repeating running the code in todays date, once the date is changed,
# then re-run the code and match time again and so on
# Syed Jahanzaib / 26-Jun-2018
# set -x

# Setting variables
DATE=$(date +%d-%m-%Y)
FULL_DATE=`date`
FILE=/temp/1.txt
touch $FILE
CURR_HOUR=$(date +%H)

# Set time for script execution
SCR_SCHEDULED_TIME="11"
H=$(date +'%-H')

CHK_GREP=`grep -c $DATE $FILE`
echo "Current Date time is $FULL_DATE"

# If script is executed successfully then dont re-run and exit now
if grep -q $DATE $FILE >/dev/null 2>&1
then
echo "It seems the script was executed successfully today $DATE, It will run on next date change.... Exiting now."
exit 1
fi

# Check if time is matched that is greater or equals to $SCR_SCHEDULED_TIME and also check if script hae ran successfully or not previously
echo "
Stage-1: Checking if current time is equals or greater then '$SCR_SCHEDULED_TIME hours' ..."
if [ "$CURR_HOUR" -ge "$SCR_SCHEDULED_TIME" ] && [ "$CHK_GREP" == "0" ]; then
echo "
Stage-2: Time matched that is equals or greater then $SCR_SCHEDULED_TIME"
# If all matches, then run the code ! and add time stamp in file to avoid repeatingo/re-running the following script code on next RUN
echo "Finally: All conditions time + this day first execution matched, Now running the script code ...."
date +%d-%m-%Y >> $FILE
exit 1
fi

# If time have not come like its before the $SCR_SCHEDULED_TIME, then give error and exit now
echo "
Warning !
Scheudled time is equals or greater then $SCR_SCHEDULED_TIME hours : Current hour is $CURR_HOUR
Time have not came yet ! wait for your turn ..."

This way I was able to achieve the task I was thiking of.


# Actual Script which performs various functions with time checkups etc

Just for personnel reference …

#!/usr/bin/env bash
####!/bin/sh
#set -x
#trap "set +x; set -x" DEBUG
# BASH base script to discopnnect users whose expiry is today & update there GROUPS as well and update log table.
# the simple logic can be applied for about any other task as well. I tried to make it as simple as it can be
# also check if the scrpit ran successfully then dont re-run today,
# By Syed Jahanzaib
# CREATED on : 16th July, 2015
# Modified on 25h June 2018

# Local Variables
# Mysql credentials
SQLID="root"
SQLPASS="ROOTPASS"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
DB="radius"
#Table which contain main users information
TBL="users"
#Rad user group in which we will update user profile like from 1mb to expired or likewise
GROUP="radusergroup"
NEXTSRV="expired"
TBL_LOG="log"

# Date Time Variables
DATE=$(date +%d-%m-%Y)
FULL_DATE=`date`
CURR_HOUR=$(date +%H)
TODAY=$(date +"%Y-%m-%d")
WEEK=`date -d "-1000 days" '+%Y-%m-%d'`
BEGIN="1970-01-01"
H=$(date +'%-H')
################################
## Set time for script execution
## IMPORTANT, donot use 0 in it
SCR_SCHEDULED_TIME="12"
################################

#Network Related
hostname=`hostname`
IP=`ip route get 8.8.8.8 | awk '{print $NF; exit}'`

# Gmail Data
GMAILID="MYGMAIL@gmail.com"
GMAILPASS="MYGMAILPASS"
SMTP="64.233.184.108:587"
ADMINMAIL1="aacableAThotmail.com"
COMPANY="ZAIB"
MAILSUB="$COMPANY INFO -$HOSTNAME-$IP- List of account expired on $TODAY"

##################################################
##################################################
# Temp folder and files setup for various actions
##################################################
##################################################
TEMP="temp"
# Temp holders to store users list
ALLEXPLIST=/$TEMP/all_time_expired_users_list.txt
TODEXPLIST=/$TEMP/only_today_expired_users_list.txt
FILE=/$TEMP/check_user_expiration_exec.txt

# Checking if /temp folder is previously present or not , if not create one ...
{
if [ ! -d "/$TEMP" ]; then
echo
mkdir /$TEMP
fi
}

# remove and recreate users list to avoid any Duplication or issue
{
if [ -f $ALLEXPLIST ]; then
rm $ALLEXPLIST
touch $ALLEXPLIST
fi
}
{
if [ -f $TODEXPLIST ]; then
rm $TODEXPLIST
touch $TODEXPLIST
fi
}
# File Holder to store last execution date
{
if [ ! -f $FILE ]; then
touch $FILE
fi
}

############################
############################
###### START thE script cOdE
############################
############################

CHK_GREP=`grep -c $DATE $FILE`
echo "Current Date time is $FULL_DATE
"
# If script is executed successfully in current date, then dont re-run and exit now
echo "Stage-1:
Checking if the script have already ran successfully for today, by getting current date from the $FILE"
if grep -q $DATE $FILE >/dev/null 2>&1
then
echo "
Result:
It seems the script was executed successfully today $DATE, It will run on next date change. Exiting now ..."
exit 1
fi

# Check if time is matched that is greater or equals to $SCR_SCHEDULED_TIME and also check if script hae ran successfully or not previously by getting date from $FILE
echo "
Stage-2: Checking if current hour is equals or greater then '$SCR_SCHEDULED_TIME hours' & previous run is not done yet ..."
if [ "$CURR_HOUR" -ge "$SCR_SCHEDULED_TIME" ] && [ "$CHK_GREP" == "0" ]; then
echo "
Stage-2:
Time matched that is equals or greater then $SCR_SCHEDULED_TIME"
# If all matches, then run the code ! and add time stamp in file to avoid repeatingo/re-running the following script code on next RUN
echo "Finally:
All conditions time + this day first execution matched, Now running the script code ..."
date +%d-%m-%Y >> $FILE

# Pull users that are expiring from beginning till TODAY, to avoid missing any part
$CMD "use $DB; select username from $TBL where expiration between '$BEGIN' AND '$TODAY';" |sort > $ALLEXPLIST
# Pull user list that are expiring today only, for email purposes
$CMD "use $DB; select username from $TBL where expiration ='$TODAY';" |sort > $TODEXPLIST

# IF no user found , show error and exit - zaib
CHK=`wc -m $ALLEXPLIST | awk {'print $1}'`
if [ "$CHK" -eq 0 ]
then
echo "No user found expiring today, exiting ..."
exit 1
fi

# Apply formula
num=0
cat $ALLEXPLIST |while read data
do
num=$[$num+1]
USERNAME=`echo $data`

# Update user status in RADgroup & users table so that he will rejected on next login
IS_EXPIRED=`$CMD "use $DB; select is_expired from users where username ='$USERNAME';"`
if [ "$IS_EXPIRED" = "N" ]; then
echo "user_IS EXIPRED is NO *************************************************************************************************"
$CMD "use $DB; update $GROUP set groupname='$NEXTSRV' where username='$USERNAME';"
$CMD "use $DB; update $TBL set is_expired='Y' where username='$USERNAME';"
$CMD "use $DB; update $TBL set is_days_expired='Y' where username='$USERNAME';"
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User reached Expiration, Group udpated.');"
fi

# CHECK ONLINE AND KICK
# Pull account session id from radacct table, which will be used to COA OR user disconnection
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
# If user is not Online , just give info that he is not online
if [ -z "$ACCTSESID" ]; then
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User reached Expiration, Group udpated but its already offline');"
echo "$USERNAME - User reached Expiration, Group udpated but its already offline."
else
# Kick user by getting his NAS ip, secret and other info and log
# Mikrotik NAS IP and Radport and Shared Secret
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';"`
NAS_COA_PORT=`$CMD "use $DB; select nas_coa_port from nas where nasname ='$NAS_IP';"`
# Disconnect users now using RADCLIENT with username adn Account session ID taken from radacct table
echo user-name=$USERNAME,Acct-Session-Id=$ACCTSESID | radclient -x $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET
# LOG into LOG TABLE in radius DB
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User reached Expiration, kicked & updated,.');"
echo "$USERNAME - User reached Expiration, kicked & updated."
fi
done

#TOT=`cat $TODEXPLIST | wc -l`
#echo "
#-----------
#-----------

#$COMAPNY _ List of TODAY $TODAY expired users, Total = $TOT"
#cat $TODEXPLIST

# Send Email to all admin - currently 4 users
sendemail -t $GMAILID -u "$MAILSUB" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$TODEXPLIST -o message-content-type=text
fi

Regard’s
Syed Jahanzaib

Older Posts »

%d bloggers like this: