Syed Jahanzaib Personal Blog to Share Knowledge !

February 20, 2018

FREERADIUS WITH MIKROTIK – Part #13 – Detecting user device vendor based on MAC address

Filed under: freeradius — Tags: , , , , , — Syed Jahanzaib / Pinochio~:) @ 11:52 AM

fre2

mac-address

1- identify vendor from mac

 

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 based billing system in place. in RADACCT table, we have a trigger that fire-up after successfull connection made from user device & it inserts user device MAC address in radcheck table for MAC validation & restriction.

* Requirement:

For better management , control & reporting purposes we want to add more checks and controls by adding user calling device VENDOR name in an additional column so that we can have idea what devices are most common in our network, or to detect any device that is prohibited by the company SOP or policy example commercial grade routers to minimize misuse of our residential services. Based on this reporting  we can prohibit connecting these devices as well if required.

To fulfill this we will do following

  1. Create a TRIGGER on RADACCT that will executes after INSERT record (like when user will connect to system successfully)
  2. Create DB and upload vendors mac address data so that we can query for vendor name locally (alternate approach is to simple use any Perl or bash script to look up vendor name dynamically on the fly form the internet)

 


OK before creating TRIGGER we must learn or understand our USER table where mac related will be added or update. One example for such table is showed below …

root@radius:/temp# mysql -uroot -pSQLPASS -e "use radius; describe users;"

+-------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(128) | NO | UNI | NULL | |
| password | varchar(32) | NO | | NULL | |
| firstname | text | NO | | NULL | |
| lastname | text | NO | | NULL | |
| email | text | NO | | NULL | |
| mobile | text | NO | | NULL | |
| cnic | text | NO | | NULL | |
| srvname | text | NO | | NULL | |
| srvid | int(3) | NO | | NULL | |
| expiration | date | YES | | NULL | |
| mac | varchar(30) | NO | | NULL | |
| macvendor | varchar(128) | NO | | NULL | |
| bwpkg | varchar(256) | NO | | NULL | |
| pool | varchar(128) | YES | | other | |
| is_enabled | int(1) | NO | | NULL | |
| is_days_expired | int(1) | NO | | NULL | |
| is_qt_expired | int(1) | NO | | NULL | |
| is_uptime_expired | int(1) | NO | | NULL | |
| qt_total | varchar(32) | NO | | NULL | |
| qt_used | varchar(20) | NO | | NULL | |
| uptime_limit | varchar(20) | NO | | NULL | |
| uptime_used | varchar(32) | NO | | NULL | |
| owner | text | NO | | NULL | |
| vlanid | varchar(32) | NO | | NULL | |
| createdon | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------------+--------------+------+-----+-------------------+-----------------------------+

In this post we have interest in two columns named mac and macvendor


Now we will create TRIGGER with our so called magical code 😉

1- TRIGGER for radacct table

CREATE TRIGGER `chk_mac_after_insert` AFTER INSERT ON `radacct`
 FOR EACH ROW BEGIN
# Check if user mac is already added in radcheck table,
SET @mac = (SELECT count(*) from radcheck where username=New.username and attribute='Calling-Station-ID');
# If there is no entry for mac, then update mac in radcheck table, so that in future only this mac will be able to connect with that username
IF (@mac = 0) THEN 
INSERT into radcheck (username,attribute,op,value) values (NEW.username,'Calling-Station-ID',':=',NEW.callingstationid); 
# add mac in users table for general record purpose
UPDATE users SET mac = NEW.callingstationid where username = NEW.username;
# trim mac for first 3 strings to detect vendor company
SET @mactrim = (select LEFT(mac, 8) from users where username=New.username);
# get vendor name from mac db table
SET @macvendor1 = (select vendor from macdb where oui=@mactrim);
# Update vendor name in user table
UPDATE users SET macvendor=@macvendor1 where username = NEW.username;
END IF;
END
#SYED Jahanzaib - 
2- triger

2- Create MAC Address Database for VENDOR Lookup

This is a bit debatable part, I prefer to have local database for vendor mac addresses so that all lookup should be done locally rather then using any API for mac lookup. But if you want to prefer using internet base lookup , then you can use perl, bash or any other method to do lookup one example for internet lookup is as follows …

curl http://api.macvendors.com/70-54-D2-16-A5-D9
#OUTPUT

PEGATRON CORPORATION

OK lets move forward to create local DB for mac and vendor lookup.

  • Install required modules
apt-get install python-mysqldb python-minimal
  • Download the MAC address OUI from the IEEE site.
cd /tmp
wget http://standards.ieee.org/develop/regauth/oui/oui.txt
  • Once download is done, Create script that will insert mac records into separate table named ​mactable
touch mactosql.sh
chmod +x touch mactosql.sh

Now edit file

nano mactosql.sh

Paste following

# 09-07-2016
# ouiUpdate, grabs the iee oui list and adds it to a table in mysql database.
# Provides no syntax checking of the input file, use with caution
# based on load_iou_to_pgsql.py https://gist.github.com/pwldp/dee6d5f9868a1dd1d076
# Usage:
# python ouiUpdateMysql.py

import MySQLdb
import re
import urllib
import sys

# Globals
# Database Handle, again, no need to change them; Only Declarations
DB = ''
DB_CURSOR = ''
OUI_URL = "http://standards.ieee.org/develop/regauth/oui/oui.txt"
OUI_FILE = "oui.txt"

# Database Related Globals
DB_NAME='radius'
DB_USERNAME='root'
DB_PASSWORD='SQL_PASSWORD'
DB_TABLE='macdb'
DB_HOST='localhost'

def main():
global DB
global DB_CURSOR
global DB_TABLE
# Download oui.txt Comment out the two lines below if you do not want to download the file and use a offline copy
#####
# print "Downloading ",OUI_URL
# urllib.urlretrieve(OUI_URL, OUI_FILE)
#####
##connect to db
try:
## Opening connection to Database with pre-configured Database, Host, User and Password
DB = MySQLdb.connect(host=DB_HOST, user=DB_USERNAME, passwd=DB_PASSWORD, db=DB_NAME)
DB_CURSOR = DB.cursor()
except:
sys.exit('Unable to Open Connection to Database')

# Drop table and start from new
DB_CURSOR.execute("DROP TABLE IF EXISTS ouiList")
# Create table as per requirement
sql ="""CREATE TABLE IF NOT EXISTS ouiList (
id int(6) NOT NULL AUTO_INCREMENT,
oui varchar(8) DEFAULT NULL,
vendor varchar(100) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB"""

# make the new table
DB_CURSOR.execute(sql)

# parsing oui.txt data and adding to table
print "Parsing data..."
with open(OUI_FILE) as infile:
print "Inserting data into table, please wait..."
for line in infile:
#do_something_with(line)
if re.search("(hex)", line):
try:
mac,vendor = line.strip().split("(hex)")
except:
mac = vendor = ''
#print line.strip().split("(hex)")
#print mac.strip().replace("-",":").lower(), vendor.strip()
if mac!='' and vendor!='':
sql = "INSERT INTO ouiList "
sql+= "(oui,vendor) "
sql+= "VALUES ("
sql+= "'%s'," % mac.strip().replace("-",":").lower()
sql+= "'%s'" % vendor.strip().replace("'","`")
sql+= ")"
#print sql
try:
DB_CURSOR.execute(sql)
DB.commit()
except Exception, e:
DB.rollback()
print "Not inserted because error: "

# count the number of lines in the table
sql = ("SELECT count(id) FROM ouiList WHERE 1")
DB_CURSOR.execute(sql)
result = DB_CURSOR.fetchone()
if result:
print 'Completed: Total lines in table is: {0}'.format(result[0])
else:
print "Unable to count lines in table"

infile.close()
DB_CURSOR.close()
DB.close()
#

if __name__=="__main__":
main()

SAVE & EXIT. Now execute it.

python mactosql.sh

If no error occurs you will be seeing a new table named macdb with all vendors names as well 🙂

db import successfull.JPG

 

to get trimmed OUI, use following

sourcecode]mysql -uroot -pSQLPASS –skip-column-names -s -e “use radius; select LEFT(mac, 8) from users;”/sourcecode]


Now connect any user as normal, and see the mysql.log file

2018-02-20T06:41:04.593739Z 24 Query INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress, acctstartdelay, acctstopdelay, xascendsessionsvrkey) VALUES ('81100003', '702d22ac0a080f57', 'zaib', '', '101.11.11.253', '9', 'Ethernet', '2018-02-20 11:41:04', NULL, '0', 'RADIUS', '', '', '0', '0', 'service1', '00:0C:29:B9:D8:A0', '', 'Framed-User', 'PPP', '192.168.50.255', '0', '0', '')

# As soon we receive entry for INSERT, TRIGGER will fire-up, see below log

2018-02-20T06:41:04.594676Z 24 Query SET @mac = (SELECT count(*) from radcheck where username=New.username and attribute='Calling-Station-ID')

2018-02-20T06:41:04.594871Z 24 Query INSERT into radcheck (username,attribute,op,value) values (NEW.username,'Calling-Station-ID',':=',NEW.callingstationid)
2018-02-20T06:41:04.595020Z 24 Query UPDATE users SET mac = NEW.callingstationid where username = NEW.username

2018-02-20T06:41:04.595151Z 24 Query SET @mactrim = (select LEFT(mac, 8) from users where username=New.username)
2018-02-20T06:41:04.595256Z 24 Query SET @macvendor1 = (select vendor from macdb where oui=@mactrim)

2018-02-20T06:41:04.607786Z 24 Query UPDATE users SET macvendor=@macvendor1 where username = NEW.username

& FINALLY we will see records in USERS table that will be displayed on FRONTEND 🙂

1- identify vendor from mac

Advertisements

February 19, 2018

FREERADIUS WITH MIKROTIK – Part #12 – Happy Hours !

Filed under: freeradius — Tags: , — Syed Jahanzaib / Pinochio~:) @ 11:40 AM

fre2

happy hours

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


* SCENARIO:

We have a full working freeradius based billing system which have multiple Quota base services assigned to users. All users accounting is stored in radacct table as usual, but we have a separate column named qt_used in users table which is updates via radacct trigger when accounting updates arrives from the NAS. This column is checked by an external script every 10 minutes & if it finds qt_used value above then qt_total then it simply change user group to EXPIRED group & disconnects users from NAS by sending COA / POD.

* TASK:

Our bandwidth remains empty in night therefore we want to provide some extra benefits to quota base users by BYPASSING quota limit & counting in specific late night hours.

Example: There should be no QUOTA counting for all quota base users from 00:00 (midnight) till 8am (morning).

* Possible SOLUTION !

Like always, this may not be best optimal solution to achieve the task, but still it may work. Be sure took for other better approach rather then this sol.

Following is our USERS table

mysql> describe users;
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(128) | NO | UNI | NULL | |
| password | varchar(32) | NO | | NULL | |
| firstname | text | NO | | NULL | |
| lastname | text | NO | | NULL | |
| email | text | NO | | NULL | |
| mobile | text | NO | | NULL | |
| cnic | text | NO | | NULL | |
| srvname | text | NO | | NULL | |
| srvid | int(3) | NO | | NULL | |
| expiration | date | YES | | NULL | |
| mac | varchar(30) | NO | | NULL | |
| bwpkg | varchar(256) | NO | | NULL | |
| is_enabled | int(1) | NO | | NULL | |
| is_days_expired | int(1) | NO | | NULL | |
| is_qt_expired | int(1) | NO | | NULL | |
| is_uptime_expired | int(1) | NO | | NULL | |
| qt_used | varchar(20) | NO | | NULL | |
| uptime_used | varchar(32) | NO | | NULL | |
| owner | text | NO | | NULL | |
| vlanid | varchar(32) | NO | | NULL | |
| createdon | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
22 rows in set (0.00 sec)

& following is our services table

mysql> describe services;
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| srvid | int(10) | NO | PRI | NULL | auto_increment |
| srvname | varchar(128) | NO | MUL | NULL | |
| descr | varchar(128) | YES | | other | |
| enabled | varchar(1) | NO | | NULL | |
| expdays | varchar(3) | NO | | NULL | |
| dlimit | varchar(30) | NO | | NULL | |
| ulimit | varchar(30) | NO | | NULL | |
| qt_enabled | tinyint(1) | NO | | NULL | |
| tot_qt | int(128) | NO | | NULL | |
| free_quota_enabled | tinyint(4) | NO | | NULL | |
| free_qt_start_time | time | NO | | NULL | |
| free_qt_end_time | time | NO | | NULL | |
| ippool | varchar(30) | NO | | NULL | |
| createdon | datetime | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
14 rows in set (0.00 sec)

* MYSQL TRIGGER on RADACCT table for AFTER UPDATE ACTION. [ This is our magical code 😛 ]

Ok, now we will create a TRIGGER on radacct table for AFTER UPDATE action.
[apply it in mysql shell on radius table]

BEGIN
SET @ctime = (select current_time());
SET @srvid = (select srvid from users where username =New.Username);
SET @st = (select free_qt_start_time from services where srvid=@srvid);
SET @et = (select free_qt_end_time from services where srvid=@srvid);
if (@ctime > @st)
OR (@ctime < @et)
then
UPDATE users set qt_used = qt_used + NEW.acctoutputoctets WHERE username = New.username;
# below section ELSE is not required, I just added it for ON THE FLY troubleshooting]
else
insert into log (data,msg) values ('zaib','happy_hours_time_not_matched');
END IF;
END
# Syed Jahanzaib / aacable at hotmail dot com

Current time is 11:00{am}, inspect the mysql.log & you will find following (quota will not update because time doesnt matches)

2018-02-19T06:18:26.797015Z 51 Query UPDATE radacct SET framedipaddress = '192.168.50.255', acctsessiontime = '1841', acctinputoctets = '0' > 32 | '122655', acctoutputoctets = '0' > 32 | '145069' WHERE acctsessionid = '8100000e' AND username = 'zaib' AND nasipaddress = '101.11.11.253'
2018-02-19T16:18:26.797819Z 51 Query SET @ctime = (select current_time())
2018-02-19T16:18:26.797889Z 51 Query SET @srvid = (select srvid from users where username =New.Username)
2018-02-19T16:18:26.798009Z 51 Query SET @st = (select free_qt_start_time from services where srvid=@srvid)
2018-02-19T16:18:26.798092Z 51 Query SET @et = (select free_qt_end_time from services where srvid=@srvid)
2018-02-19T16:18:26.798165Z 51 Query insert into log (data,msg) values ('zaib','happy_hours_time_not_matched)

.

& if the radacct updates came in between happy hours then we will observe following (Quota will update accordingly)

2018-02-19T06:19:31.818111Z 98 Query UPDATE radacct SET framedipaddress = '192.168.50.255', acctsessiontime = '1906', acctinputoctets = '0' << 32 | '130274', acctoutputoctets = '0' << 32 | '161901' WHERE acctsessionid = '8100000e' AND username = 'zaib' AND nasipaddress = '101.11.11.253'
2018-02-19T06:19:31.818500Z 98 Query SET @ctime = (select current_time())
2018-02-19T06:19:31.818574Z 98 Query SET @srvid = (select srvid from users where username =New.Username)
2018-02-19T06:19:31.818680Z 98 Query SET @st = (select free_qt_start_time from services where srvid=@srvid)
2018-02-19T06:19:31.818758Z 98 Query SET @et = (select free_qt_end_time from services where srvid=@srvid)
2018-02-19T06:19:31.818833Z 98 Query UPDATE users set qt_used = qt_used + NEW.acctoutputoctets WHERE username = New.username

 


Regard's
Syed Jahanzaib ~

.

January 30, 2018

January 24, 2018

January 18, 2018

FREERADIUS WITH MIKROTIK – Part #9 – Restrict user auth from allowed VLAN only

Filed under: freeradius — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 8:48 AM

FreeRadius Core info

restrcited vlan.jpg

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

Disclaimer:
This solution was designed for some specific situation described below. Surely there might be better ways, but sometimes dueto my laziness & dumbness, I select whichever gives required results. You may fine tune it, or modify it as required. I always recommends to read the FR mailing list for any query.

It’s a weird world we live in.
EVERY HUMAN BEING IS BORN FREE BUT YET EVERYWHERE HE IS IN CHAINS … ~

Syed Jahanzaib ~


Scenario:

OP have single Mikrotik CCR as NAS (pppoe server) and Freeradius as billing system. VLAN(s) are configured on Cisco switch for each reseller/dealer area with proper Trunking configured from SW to CCR. OP have few reseller/franchise managers, like Dealer-A, Dealer-B. They can create there own users in freeradius using customized fronted designed in php. All dealers can view/add/edit there own users only.

Sometimes it happens that Dealer-A creates User-ID and provide it to a user/friend who is sitting in Dealer-B network, therefore from Billing perspective its a Loss for Dealer-B.


Requirement:

To prevent users roaming. Every user must be allowed to connect from there respective dealer network Only. [That is ‘allowed-vlan]. User’s MAC address validation is also in place but we know that mac can be cloned easily.


Solution:

p4.jpg

We have a user table which contains a column vlanid. We will add a SQL IF statement which will check the the connecting user NAS-Port-Id & match it with users allowed vlanid in the user’s table.

Users Table Example:

mysql> describe users;
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(128) | NO | UNI | NULL | |
| password | varchar(32) | NO | | NULL | |
| firstname | text | NO | | NULL | |
| lastname | text | NO | | NULL | |
| email | text | NO | | NULL | |
| mobile | text | NO | | NULL | |
| cnic | text | NO | | NULL | |
| srvname | text | NO | | NULL | |
| srvid | int(3) | NO | | NULL | |
| expiration | date | YES | | NULL | |
| mac | varchar(30) | NO | | NULL | |
| bwpkg | varchar(256) | NO | | NULL | |
| pool | varchar(128) | YES | | other | |
| is_enabled | int(1) | NO | | NULL | |
| is_days_expired | int(1) | NO | | NULL | |
| is_qt_expired | int(1) | NO | | NULL | |
| is_uptime_expired | int(1) | NO | | NULL | |
| qt_total | varchar(32) | NO | | NULL | |
| qt_used | varchar(20) | NO | | NULL | |
| uptime_limit | varchar(20) | NO | | NULL | |
| uptime_used | varchar(32) | NO | | NULL | |
| owner | text | NO | | NULL | |
| vlanid | varchar(32) | NO | | NULL | |
| createdon | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------------+--------------+------+-----+-------------------+-----------------------------+

mysql> select * from users;
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+------------+-------------------+-------------+----------------------+------------+-----------------+---------------+-------------------+----------+---------+--------------+-------------+--------+-------------+---------------------+
| id | username | password | firstname | lastname | email | mobile | cnic | srvname | srvid | expiration | mac | bwpkg | pool | is_enabled | is_days_expired | is_qt_expired | is_uptime_expired | qt_total | qt_used | uptime_limit | uptime_used | owner | vlanid | createdon |
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+------------+-------------------+-------------+----------------------+------------+-----------------+---------------+-------------------+----------+---------+--------------+-------------+--------+-------------+---------------------+
| 1 | zaib | zaib | OK | jahanzaib | aacableAThotmail.com | 03333021909 | 1234567890-1-1 | | 0 | 2018-01-04 | 00:0C:29:B9:D8:A0 | 1024k/1024k | public-pool | 1 | 0 | 0 | 0 | 300 | 38980 | | | manager1 | ether1-LAN | 2018-01-17 16:45:05 |

Now we will add the SQL IF statement that will actually check every incoming Authentication request for matching vlan with users table.

Edit Default Sites-Enabled file,

nano /etc/freeradius/sites-enabled/default

& paste following in `Authorize` Section

if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") {
update reply {
Reply-Message = 'Error: You are not allowed to connect form this VLAN !'
}
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 vlan which does not matches the vlanid in user’s table he will get denied with the message.

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

rad_recv: Access-Request packet from host 101.11.11.253 port 34175, id=11, length=119
Service-Type = Framed-User
Framed-Protocol = PPP
NAS-Port = 14
NAS-Port-Type = Ethernet
User-Name = "zaib"
Calling-Station-Id = "00:0C:29:B9:D8:A0"
Called-Station-Id = "testppp"
NAS-Port-Id = "ether1-LAN"  # < < < <  Look at this, this is the VLAN id user is coming from, this will be matched with the vlanid we add in the users table
User-Password = "zaib"
NAS-Identifier = "Win"
NAS-IP-Address = 101.11.11.253
# Executing section authorize from file /etc/freeradius/sites-enabled/default
+group authorize {
++[preprocess] = ok

++? if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}")
sql_xlat
expand: %{User-Name} -> zaib
sql_set_user escaped user --> 'zaib'
expand: select vlanid from users where username = '%{User-Name}' -> select vlanid from users where username = 'zaib'
rlm_sql (sql): Reserving sql socket id: 29
sql_xlat finished
rlm_sql (sql): Released sql socket id: 29
expand: %{sql: select vlanid from users where username = '%{User-Name}'} -> ether1-LAN-DUMMY
expand: %{NAS-Port-Id} -> ether1-LAN
? Evaluating ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") -> TRUE
++? if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") -> TRUE
++if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") {
+++update reply {
+++} # update reply = noop
+++update control {
+++} # update control = noop
++} # if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") = noop

Sending delayed reject for request 0
Sending Access-Reject of id 11 to 101.11.11.253 port 34175

# Meaningful Reply Message by zaib
Reply-Message = "Error: You are not allowed to connect form this VLAN !"

Waking up in 4.9 seconds.
Cleaning up request 0 ID 11 with timestamp +31
Ready to process requests.

.

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


 

p4-2.jpg

January 15, 2018

Mikrotik with Freeradius/mySQL – Trimming & Archiving RADACCT # Part-8

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

fre


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

FREERADIUS WITH MIKROTIK – Part #2  – COA

FREERADIUS WITH MIKROTIK – Part #3 – Expiration

FREERADIUS WITH MIKROTIK – Part #4 – Auto Mac Binding

FREERADIUS WITH MIKROTIK – Part #5 – Stale Sessions

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

FREERADIUS WITH MIKROTIK – Part #7  – Quota Limit

FREERADIUS WITH MIKROTIK – Part #8  – RADACCT – Trimming & Archiving < You are here


Personnel Note:

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

OS: Ubuntu 16.04.3 LTS / 64bit


Disclaimer:
There are some other neat-to-Perfect methods like archive users usage data from radacct table to the archive data , per user ONE row only.  This will take much lesser size , but I need to track some data therefore I duplicated the whole table. Else you can use TRIGGER on RADACCT too which can update other user table for the usage only. All depend son the scenario and requirements.



Scenario:

slow data.jpg

Radius accounting is stored in radacct table which contains the user usage related data . Over the period of time this data can take lot of space as time passes. It can also create performance related issues as well if you donot take care of it like slow or timeout queries because of hundreds of thousands of entries.

I received complains from few networks that radius is giving timeout because they were installed few years ago and there radacct table have grown enormously in size thus resulting ins slow queries and timeout in authentication specially in Radius Manager which uses external auth script to validate the user request.

Solution:

  • Use SSD disks (or RAID10), they are more performance oriented storage,
  • Adding more RAM is plus point for any database server.

We will create another table named  radacct_archive  and move accounting data older then 6 months from the radacct table to radacct_archive table.

This way queries will work faster dueto less data in radacct. Also we will delete data older then 12 months from `radacct_archive` table too so that it may not grow large later.

First we will clone the radacct table structure into new radacct_archive table.

# This is one time step.
mysql -uroot -pzaib1234 -s -e "use radius; create table radacct_archive LIKE radacct;"

Now you can create a bash script and schedule it to run DAILY in less busy timings like 5 am in the morning.

# --- Copy data from CURRENT radacct table to new radacct_archive table (for archive purposes)
mysql -uroot -pSQLPASS -s -e "use radius; INSERT INTO radacct_archive SELECT * FROM radacct WHERE acctstoptime > 0 AND date(acctstarttime) < (CURDATE() - INTERVAL 6 MONTH);"

# --- Now Delete data from CURRENT RADACCT table so that it should remain fit and smart ins size
mysql -uroot -pSQLPASS -s -e "use radius; DELETE FROM radacct WHERE acctstoptime > 0 AND date(acctstarttime) < (CURDATE() - INTERVAL 6 MONTH);"

# --- Now Delete data from RADACCT_ARCHIVE table so that it should not grow either more then we required i.e 1 Year - one year archived data is enough IMO
mysql -uroot -pSQLPASS -s -e "use radius; DELETE FROM radacct_archive WHERE date(acctstarttime) < (CURDATE() - INTERVAL 12 MONTH);"

Regard’s
Syed Jahanzaib

January 8, 2018

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

Filed under: freeradius, Linux Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 11:36 AM

FreeRadius Core info

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

FREERADIUS WITH MIKROTIK – Part #2  – COA

FREERADIUS WITH MIKROTIK – Part #3 – Expiration

FREERADIUS WITH MIKROTIK – Part #4 – Auto Mac Binding

FREERADIUS WITH MIKROTIK – Part #5 – Stale Sessions

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

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


Personnel Note:

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

OS: Ubuntu 16.04.3 LTS / 64bit


qt-logo.jpg

Scenario:

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


USERS table Example.

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

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

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

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

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

USERS Quota table Example!

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

------------------------------------------------------------
-- Table structure for table `users_qt`

CREATE TABLE `users_qt` (
`id` int(11) NOT NULL,
`username` varchar(32) NOT NULL,
`quota` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

LOG Table Example!

We will use this table to log any disconnect message.

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

ALTER TABLE `log`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;

Trigger for RADACCT

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

DELIMITER $$
CREATE TRIGGER `radacct_after_update` AFTER UPDATE ON `radacct` FOR EACH ROW BEGIN
#if (NEW.acctterminatecause ='Session-Timeout')
#then
#UPDATE users set is_days_expired ='1' WHERE username =OLD.username;
#UPDATE radusergroup set groupname ='expired' WHERE username =OLD.username;
#ELSE
if (NEW.acctoutputoctets > 1)
OR (NEW.acctinputoctets > 1)
then
UPDATE users set qt_used = qt_used + NEW.acctoutputoctets WHERE is_enabled ='1' AND is_qt_expired ='0' AND is_uptime_expired ='0' AND is_days_expired ='0';
END IF;
#END IF;
END
$$
DELIMITER ;

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

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

Now check the record.

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

1 zaib 300

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

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


BASH script to check over quota user.

qc_check.sh

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

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

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

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

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

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

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

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

Done.

Now run the Quota checking script …

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

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

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

group_updated

Entry in LOG table will be added …

over-qt.JPG

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

Ok lets test user with radclient.

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

RESPONSE:

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

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

 

Disclaimer:

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

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

There are some other proper methods as well, but I just managed to achieve my task this way.

Regard’s

Syed Jahanzaib

December 25, 2017

Freeradius External Authentication Script & log reject request only in radpostauth with customized REPLY message

Filed under: freeradius, Linux Related — Syed Jahanzaib / Pinochio~:) @ 11:49 PM

FreeRadius Core info

  1. FREERADIUS WITH MIKROTIK – Part #1
  2. FREERADIUS WITH MIKROTIK – Part #2 
  3. FREERADIUS WITH MIKROTIK – Part #3
  4. FREERADIUS WITH MIKROTIK – Part #4
  5. FREERADIUS WITH MIKROTIK – Part #5
  6. FREERADIUS EXTERNAL AUTH SCRIPT & RADPOSTAUTH – Part #6 >>> YOU ARE HERE

Note: This post is for reference purposes only. This post may change rapidly as at the time of writing , frequent changes are being made to adjust many parameters to fulfill local OP requirements. So you may see some display text difference as compared in the code itself … worry not 🙂 Just read and Enjoy, Pick your desired section.

Also you may want to read this reply came from ALAN (freeradius) when I asked on external auth script.

FreeRadius users mailing list (freeradius-users@lists.freeradius.org)

________________________________
From: Freeradius-Users <freeradius-users-bounces+aacable=hotmail.com@lists.freeradius.org> on behalf of Alan DeKok <aland@deployingradius.com>
Sent: Saturday, January 13, 2018 8:30:02 PM
To: FreeRadius users mailing list
Subject: Re: External Auth Script or local Auth

On Jan 13, 2018, at 8:40 AM, JAHANZAIB SYED <aacable@hotmail.com> wrote:
> I am using Freeradius ver 2.1.10

You should upgrade to 2.2.10. There’s just no reason to use a version which is almost 10 years old

> I have noticed that some commercial radius servers (with freeradius backend) using External script (php/perl or C code) to authenticate users. I just wanted to know that what are the additional benefits of using external auth script over freeradius own authentication (via rad-groups) ?

So that they can avoid GPL licensing issues.

> I made my own bash script that runs fine for authentication by checking user status in my_users table like expiry, disable/enable, quota, uptime etc , but for heavy load network like 20-30k users, what is recommended?

Use the features in FreeRADIUS. They work. They’re also MUCH faster than forking an external program.

i.e. FreeRADIUS can do 10’s of 1000’s of DB queries per second, and 10’s of 1000’s of authentications per second. When you use shell script, that number can drop by 10x to 100x.

On top of that, why re-implement features which already work? It doesn’t make any sense to write a shell script to do something, when FreeRADIUS can already do it.
Most people who are re-packaging FreeRADIUS are figuring this out. Some have taken the source, and hacked it up… at which point they have their own magic server that no one understands.
And a few years later, FreeRADIUS has more / better features than their hacked-up version, and they can no longer sell decent features to their customers.

Alan DeKok.

z@ib

Following is an BASH script which we can use to authenticate users in freeradius. Using external auth script have several benefits over traditional attributes. Example we can provide NAS with radreply based on various attributes in users table like expiration, quota, disabled/expired users & other crazy stuff 🙂

It took many hours for a duffer like me to accomplish this task. Some says BASH is not suitable for servers under heavy load, therefore you may look for C/C++ approach for faster fetching results. I have tested this with 1000 + loop auth requests & it worked fine without putting any load on the server & without any invalid  or missing any single request !

Regard’s
Syed Jahanzaib


Scenario:

  • OS: Ubuntu 12.4 Server Edition
  • FreeRADIUS Version 2.1.10, for host i686-pc-linux-gnu
  • Mysql Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (i686) using readline 6.2

>>> OP Requirements :

  1. If users not found, It should display error message in debug log & REJECT user request
  2. If user password does not match with the radcheck table entry, REJECT the user request
  3. If user is Disabled by OP, let him login with disabled-pool (for redirection purposes)
  4. If user is Expired, let him login with expired-pool (for redirection purposes)
  5. If user is logged in for the first time, his MAC should update in USERS table for AUTO MAC Binding purposes. So only this device should be able to connect in future
  6. If user try to login from other device, let him login with invalidmac-pool (for redirection purposes)
  7. Check for Over Quota users (not required at a moment)
  8. LOG requests in mysql RADPOSTAUTH table for REJECT actions

So in short we donot want to REJECT user login request if it matches above criteria.

Yes I know its a kind of weird stuff, but still its good for learning !

>>> Mysqle ‘USERS’ Table example

In mysql we have following users table [Just an example, its not fine tuned]

#### USERS TABLE
root@testradius:/temp# mysql -uroot -pROOTPASS -e "use radius; describe users;"
+------------+---------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(128) | NO | MUL | NULL | |
| password | varchar(32) | NO | | NULL | |
| firstname | text | NO | | NULL | |
| lastname | text | NO | | NULL | |
| email | text | NO | | NULL | |
| mobile | text | NO | | NULL | |
| cnic | text | NO | | NULL | |
| srvname | text | NO | | NULL | |
| srvid | int(11) | NO | | NULL | |
| expiration | datetime | YES | | NULL | |
| mac | varchar(30) | NO | | NULL | |
| bwpkg | varchar(1000) | NO | | NULL | |
| pool | varchar(128) | YES | | other | |
| enabled | varchar(1) | NO | | NULL | |
| owner | text | NO | | NULL | |
| createdon | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+---------------+------+-----+-------------------+-----------------------------+
17 rows in set (0.00 sec)

root@testradius:/temp# mysql -uroot -pROOTPASS -e "use radius; select * from users;"
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+---------------------+-------------------+-------------+----------------------+---------+--------+---------------------+
| id | username | password | firstname | lastname | email | mobile | cnic | srvname | srvid | expiration | mac | bwpkg | pool | enabled | owner | createdon |
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+---------------------+-------------------+-------------+----------------------+---------+--------+---------------------+
| 1 | zaib | | syed | jahanzaib | aacableAThotmail.com | 03333021909 | 1234567890-1-1 | | 0 | 2018-12-23 00:00:00 | 00:0C:29:35:F8:2F | 1024k/1024k | public-pool | 1 | galaxy | 2017-12-23 16:25:09 |
| 7 | test | | test firstname | test lastname | test@test.com | 13434234234 | 242342420424-42-2 | | 0 | 2017-12-24 00:00:00 | 00:0C:29:35:F8:2F | 2048k/2048k | public-multinet-pool | 1 | zaib | 2017-12-25 16:27:39 |
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+---------------------+-------------------+-------------+----------------------+---------+--------+---------------------+

FREERADIUS config to accommodate external authentication script [auth.sh] execution

We need to add following directives in FR config files in order to execute the bash script for auth purpose once the username/password is found ok by FR.


>>> Edit freeradius/USERS file in FR

nano /etc/freeradius/users

& Add following in the end of this file …

DEFAULT Auth-Type := PAP
Exec-Program-Wait = "/temp/auth.sh %{User-Name} %{User-Password} %{Calling-Station-Id}"

SAVE & EXIT !


>>> Now edit sites-enabled/default file …

nano /etc/freeradius/sites-enabled/default

& use following … [my working sample file, you must modify it as per your requirements]

authorize {
preprocess
chap
pap
mschap
digest
# suffix
# eap {
# ok = return
# }
files
### ZAIB Section-1 Start Here ##
sql{
notfound = 1
}
if(notfound){
update reply {
Reply-Message = 'Username not found'
}
reject
}
### ZAIB Section-1 Ends Here ##
# checkval
# expiration
# logintime
# pap
}

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

preacct {
preprocess
acct_unique
suffix
files
}

accounting {
detail
unix
radutmp
sql
exec
attr_filter.accounting_response
}

session {
# radutmp
sql
}

### ZAIB Section-2 Start Here ##
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
}

SAVE & EXIT !


 


AUTH.SH [bash script as external auth for FR]

Create auth.sh file

  • mkdir /temp
  • touch /temp/auth.sh
  • chmod+x /temp/auth.sh
  • nano /temp/auth.sh

& paste following data [after necessary modifications]

#!/bin/bash
# Bash script for Freeradius / external auth script
# By Syed Jahanzaib / aacable at hotmail dot com
# https://aacable.wordpress.com
# 22-DEC-2017
# Last modified on 27-DEC-2017
# You may want to disable few sections in it like user validation, password validation
# because both are now handled by FR with our custom reply message hurrahhhhh zaib.
#set -x
USERNAME=$1
PASS=$2
MAC=$3
MYSQL_USR="root"
MYSQL_PASS="ROOT-OR-SQL-PASS"
DB="radius"
TBL="users"
MSG=""
USRISVALID=`mysql -u$MYSQL_USR -p$MYSQL_PASS --skip-column-names -s -e "use $DB; select id from $TBL where username ='$USERNAME';" | tr "'" " "`
if [ -z "$USRISVALID" ];then
echo "$USERNAME >>>> User Not Found."
exit 1
fi
CHKPASS=`mysql -u$MYSQL_USR -p$MYSQL_PASS --skip-column-names -s -e "use $DB; select password from users where username='$USERNAME';"`
if [ "$PASS" != "$CHKPASS" ];then
echo "$USERNAME / $PASS >>> Incorrect Password."
exit 1
fi
ENORDIS=`mysql -u$MYSQL_USR -p$MYSQL_PASS -s -e "use $DB; select enabled from $TBL where username ='$USERNAME';" | tail -n 1`
if [ "$ENORDIS" == "0" ];then
echo 'Mikrotik-Rate-Limit="'1k/1k'",Framed-Pool="'disabled-pool'",Session-Timeout="'0'"'
exit 0
fi
CHECKMAC=`mysql -u$MYSQL_USR -p$MYSQL_PASS --skip-column-names -s -e "use $DB; select mac from $TBL where username ='$USERNAME';" | tail -n 1`
if [ -z "$CHECKMAC" ];then
mysql -u$MYSQL_USR -p$MYSQL_PASS -e "use $DB; UPDATE $TBL SET mac ='$MAC' WHERE users.username ='$USERNAME' LIMIT 1;"
fi
if [ "$MAC" != "$CHECKMAC" ];then
echo 'Mikrotik-Rate-Limit="'1k/1k'",Framed-Pool="'invalidmac-pool'",Session-Timeout="'0'"'
exit 0
fi
SECONDS=`mysql -u$MYSQL_USR -p$MYSQL_PASS -s -e "use $DB; select time_to_sec(TIMEDIFF (expiration, NOW() ) ) as secs from users where username ='$USERNAME';" | tail -n 1`
if [ "$SECONDS" -lt 0 ]; then
echo 'Mikrotik-Rate-Limit="'1k/1k'",Framed-Pool="'expired-pool'",Session-Timeout="'0'"'
exit 0
fi
POOL=`mysql -u$MYSQL_USR -p$MYSQL_PASS -s -e "use $DB; select pool as pool from $TBL where username ='$USERNAME';" | tail -n 1`
BWPKG=`mysql -u$MYSQL_USR -p$MYSQL_PASS -s -e "use $DB; select bwpkg as bwpkg from $TBL where username ='$USERNAME';" | tail -n 1`
echo 'Framed-Pool="'$POOL'",Session-Timeout="'$SECONDS'",Mikrotik-Rate-Limit="'$BWPKG'",'
exit 0
fi

EXAMPLE:

After all is configured properly, we can see the results as following.

>>> To test with the script it self

root@testradius:/temp# ./auth.sh MR.PONKA PASS 00:0C:29:35:F8:2F
MR.PONKA >>>> User Not Found... *****

root@testradius:/temp# ./auth.sh zaib PASS 00:0C:29:35:F8:2F
Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",

root@testradius:/temp# ./auth.sh zaib PASS 11:11:11:11:11:11
Mikrotik-Rate-Limit="1k/1k",Framed-Pool="invalidmac-pool",Session-Timeout="0"

root@testradius:/temp# ./auth.sh test PASS 00:0C:29:35:F8:2F
Mikrotik-Rate-Limit="1k/1k",Framed-Pool="expired-pool",Session-Timeout="0"

>>> To test from RADCLIENT ,

we can use the following radclient syntax to test username/pass/mac.

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

If user not found

Sending delayed reject for request 2
Sending Access-Reject of id 32 to 127.0.0.1 port 57901
 Reply-Message = "Username not found"
Waking up in 4.9 seconds.

If user is connecting from other device [mac binding scenario]

+- entering group post-auth {...}
Exec-Program output: Mikrotik-Rate-Limit="1k/1k",Framed-Pool="invalidmac-pool",Session-Timeout="0"
Exec-Program-Wait: value-pairs: Mikrotik-Rate-Limit="1k/1k",Framed-Pool="invalidmac-pool",Session-Timeout="0"
Exec-Program: returned: 0
++[exec] returns ok
Sending Access-Accept of id 113 to 127.0.0.1 port 50894
Mikrotik-Rate-Limit = "1k/1k"
Framed-Pool = "invalidmac-pool"
Session-Timeout = 0

If user account is expired…

+- entering group post-auth {...}
Exec-Program output: Mikrotik-Rate-Limit="1k/1k",Framed-Pool="expired-pool",Session-Timeout="0"
Exec-Program-Wait: value-pairs: Mikrotik-Rate-Limit="1k/1k",Framed-Pool="expired-pool",Session-Timeout="0"
Exec-Program: returned: 0
++[exec] returns ok
Sending Access-Accept of id 176 to 127.0.0.1 port 36544
Mikrotik-Rate-Limit = "1k/1k"
Framed-Pool = "expired-pool"
Session-Timeout = 0

& IF all is OK, & account is valid …. then user will be able to connect with his assigned profile present in the users table …

+- entering group post-auth {...}
Exec-Program output: Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",
Exec-Program-Wait: value-pairs: Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",
Exec-Program: returned: 0
++[exec] returns ok
Sending Access-Accept of id 0 to 127.0.0.1 port 37090
Framed-Pool = "public-pool"
Session-Timeout = 3020399
Mikrotik-Rate-Limit = "1024k/1024k"

RADPOSTAUTH mysql query section

Following is RADPOSTAUTH Table to accommodate modified message.

mysql>describe radpostauth;
+--------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | | | |
| pass | varchar(64) | NO | | | |
| mac | varchar(18) | NO | | NULL | |
| nasipaddress | varchar(16) | NO | | NULL | |
| reply | varchar(200) | NO | | | |
| authdate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| reason | varchar(100) | NO | | NULL | |
+--------------+--------------+------+-----+-------------------+-----------------------------+

MYSQL QUERY:

This is mysql query which will be executed on every login attempt. Either success or failed. Replace existing or modify as required.

cat /etc/freeradius/sql/mysql/dialup.conf
postauth_query = "INSERT into ${postauth_table} (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}')"

Now restart your freeradius service in DEBUG mode

freeradius -X

& issue following commands in other terminal window

# echo "User-Name = zaib1, Password = wrongpass, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123
Received response ID 30, code 3, length = 40
Reply-Message = "Username not found"

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

# echo "User-Name = zaib, Password = wrongpass, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123
Received response ID 77, code 3, length = 47
Reply-Message = "Wrong Password"

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

# echo "User-Name = zaib, Password = zaib, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123
Received response ID 121, code 2, length = 58
Framed-Pool = "public-pool"
Session-Timeout = 3020399
Mikrotik-Rate-Limit = "1024k/1024k"

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

Now look at the debug window

& you will get

# debug for USERNAME not found request

# debug for WRONG Username

Sending delayed reject for request 13
Sending Access-Reject of id 30 to 127.0.0.1 port 46089
Reply-Message = "Username not found"

# debug for WRONG Password

Sending Access-Reject of id 77 to 127.0.0.1 port 41764
Reply-Message = "Wrong Password"
Waking up in 4.9 seconds.
Cleaning up request 14 ID 77 with timestamp +666
Ready to process requests.

# debug for SUCCESSFUL request

[pap] login attempt with password "zaib"
[pap] Using clear text password "zaib"
[pap] User authenticated successfully
++[pap] returns ok
# Executing section post-auth from file /etc/freeradius/sites-enabled/default
+- entering group post-auth {...}
Exec-Program output: Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",
Exec-Program-Wait: value-pairs: Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",
Exec-Program: returned: 0
++[exec] returns ok
Sending Access-Accept of id 121 to 127.0.0.1 port 46954
Framed-Pool = "public-pool"
Session-Timeout = 3020399
Mikrotik-Rate-Limit = "1024k/1024k"
Finished request 15.
Going to the next request
Waking up in 4.9 seconds.
Cleaning up request 15 ID 121 with timestamp +675
Ready to process requests.

Now look @ the radpostauth table


Alhamdolillah !

 

November 1, 2017

Mikrotik with Freeradius/mySQL – Dealing with STALE sessions in FR – Part 5

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

~ Dealing with STALE session in Freeradius 2.x ~
! From the CORE of FREERADIUS !
By
Syed jahanzaib

FREERADIUS WITH MIKROTIK – Part #1

FREERADIUS WITH MIKROTIK – Part #2 

FREERADIUS WITH MIKROTIK – Part #3

FREERADIUS WITH MIKROTIK – Part #4

FREERADIUS WITH MIKROTIK – Part #5 > You are here

Part-6


Scenario:

Mikrotik as acting as NAS (pppoe server) along with Freeradius as AAA. in the event of light or hardware failure or in a situation where NAS cannot update the FR about user is being disconnected & not active any more, the FR will consider user ACTIVE in radacct table, therefore on next dialup attempt by the user (once every thing is restored), he will get access denied because

  1. There is Simltanous-Use attribute to prevent multiple login from same user
  2. user accctsoptime is NULL because FR have not receive any update from the NAS about user is not online any more.

To remove such stale sessions, there are various methods, you can make your own bash script & schedule it to run every x minutes (example every 5 minutes). Or you can use IF query in authorize session so that when user tries to reconnect & his sessions have NULL  then in this case the query should put stop entry in acctstoptime and allow user new login. or make a PHP program that can be scheuled to run every 5 minutes and then query the radacct session for users whose account update have not received from the NAS.

First you need to add lastupdate column in your RADACCT table (in radius db) . Infact all solutions posted in this guide relies on it. so add it

ALTER TABLE 'radacct' ADD 'lastupdate' TIMESTAMP NOT NULL AFTER 'xascendsessionsvrkey';

Solution#1 [Using PHP program, good if u want to encrypt your code, ELSE bash is good]

 

Create a PHP file name fr_stale.php in your /var/www (for ubuntu)

/var/www/fr_stale.php

<?php
date_default_timezone_set('Asia/Karachi');
include ("/var/www/db.php");
$check_stale = mysqli_query($db,"UPDATE `radacct` SET acctstoptime = NOW() WHERE acctstoptime IS NULL AND lastupdate < DATE_SUB(NOW(), INTERVAL 1 MINUTE)");
?>

Save & exit.

Create a database details file that will be called by above php file in order to get DB connection details.

/var/www/db.php

<?php
$db_username = 'radius';
$db_password = 'PASSWORD';
$db_name = 'radius';
$db_host = '127.0.0.1';
$db=mysqli_connect($db_host, $db_name, $db_password, $db_name);
$mysqli = new mysqli($db_host, $db_username, $db_password, $db_name);
// If unable to communicate with MYSQL, print ERROR
if ($mysqli->connect_error) {
die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}
?>>

Schedule it in CRON

*/5 * * * * /usr/bin/php  /var/www/fr_stale.php

Solution # 3
Using BASH in CRON

You can use following bash script to run every 5 minutes which will check for any stale session by matching last update time with current.

mysql -uroot -pSQLPASS -s --skip-column-names -e "use radius; UPDATE radacct SET acctstoptime = NOW() WHERE acctstoptime IS NULL AND lastupdate < DATE_SUB(NOW(), INTERVAL 2 MINUTE)";

Solution#3
This will update the radacct acctstoptime only if user will try to re-connect, tested it in old times]

Edit /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

in AUTHORIZE { section add following query

if (User-Name){
if("%{sql:UPDATE radacct set AcctStopTime=ADDDATE(AcctStartTime,INTERVAL AcctSessionTime SECOND), AcctTerminateCause='Clear-Stale Session' WHERE UserName='%{User-Name}' and CallingStationId='%{Calling-Station-Id}' and AcctStopTime is NULL}"){
}
}

Save & restart freeradius server.

This way if NAS goes out, the session will still show online in radacct table, but when user will relogin next time, his session on radacct table will update and new entry will be created.

 

October 31, 2017

Mikrotik with Freeradius/mySQL – Auto MAC Binding on 1st Login – Part 4

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

mac_auth_radius_mysql

~ Auto Mac Binding via EXEC / PHP in Freeradius 2.x ~
! From the CORE of FREERADIUS !
By
Syed jahanzaib

FREERADIUS WITH MIKROTIK – Part #1

FREERADIUS WITH MIKROTIK – Part #2 

FREERADIUS WITH MIKROTIK – Part #3

FREERADIUS WITH MIKROTIK – Part #4 > You are here 

There are others parts too, look at part-1 for listing, i will update only part-1 listing


Personnel Note:

[At end of this guide I used TRIGGER method to auto insert the mac address of user if there is no mac entry in the radcheck table for his username, Trigger are more efficient method in my opinion.]

This post is just for demonstration purposes. in production environment you should make your own module and add it in proper relevant places. This post contains just minimalist working config to begin with. Make sure to refine it in prd environment.

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not ROCKET SCIENCE. The only thing required is the ultimate passion to achieve the goal & with the proper googling , reading a LOT, understand logic’s, then you can do all on your own. Just wanted to break the image that most of professionals don’t like to share there knowledge. I strongly encourage to read the FR mailing list and Google


OP Requirements:

[ Sort of Wired one 😉 ]

We have a working Freeradius installation. All users can login to mikrotik which verify user account authentication requests via this radius. All working fine. Now OP wants to add Auto MAC binding feature so that when user first time login to NAS, his MAC should auto binds with his account, so next time if he tries to login from another workstation, he must get access denied.


Components used in this guide:

  • Ubuntu 12.4 / x86
  • Freeradius 2.1.10 [Default apt-get installation]
  • MySQL 5.5.47 [Default apt-get installation]

SOLUTION:

To fulfill such weird requirements, we have to use external program example PHP program (via exec) which will be executed when user gets connect successfully. It will then look in RADCHECK table for this specific user MAC address value name “Calling-Station-Id”. If it’s unable to find it, then it will add the entry so that next time user will login his MAC will be verified by the CHECKVAL module in freeradius to match the mac address. If there is mac address entry, it will simply ignore and process further , will also print message that “MAC Entry already found – z@iB”

First enable the CHECKVAL module in following file > /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

Search & uncomment the checkval module. Save & Exit.

Now edit EXEC module file by

nano /etc/freeradius/modules/exec

Remove all previous contents (if its lab testing otherwise be careful editing this file) & paste following

exec {
wait = yes
program = "/usr/bin/php /temp/checkmac.php %{User-Name} %{Calling-Station-Id}"
input_pairs = request
}

Save & Exit.

Now create the php program which will be executed by above module.

mkdir /temp
mkdir /temp/checkmac.php
touch /temp/checkmac.php
nano /temp/checkmac.php

and use following to paste make sure to modify relevant details …

>

checkmac.php contents

<?php
// PHP page to check if MAC is not aleady there for the user, then INSERT it for MAC VALIDATION,
// it will add mac for 1st time login user only
// Syed Jahanzaib / aacable at hotmail dot com
// https://aacable . wordpress . com
// 31-OCT-2017

$link = mysql_connect('localhost', 'root', 'MYSQL-ROOT-PASSWORD');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
// Default DB is radius
mysql_select_db('radius');
// Look for MAC entry for this user
$result=mysql_query("select * FROM radcheck WHERE `UserName`='$argv[1]' AND attribute='Calling-Station-Id' order by Username limit 1");
$val = mysql_num_rows($result);
if ($val > 0) {
printf ("MAC Entry already found by ZAiBBBBBBBBBBBBBBBB");
}
else {
printf ("Seems to be New User, adding its MAC address in table ...");
mysql_query("INSERT into radcheck (UserName, Attribute, op, Value) values ('$argv[1]', 'Calling-Station-Id', ':=', '$argv[2]')");
}
?>

TESTING ….

Start FR in debug mode by freeradius -X and try to login with the test ID from your workstation (or use the radtest or ntradping)


rad_recv: Access-Request packet from host 192.168.0.1 port 42449, id=45, length=188
Service-Type = Framed-User
Framed-Protocol = PPP
NAS-Port = 15728851
NAS-Port-Type = Ethernet
User-Name = "zaib"
Calling-Station-Id = "0C:84:DC:1E:0B:8D"
Called-Station-Id = "service1"
NAS-Port-Id = "ether10"
MS-CHAP-Challenge = 0x49c4549501e07fad5e6dae708bc815ed
MS-CHAP2-Response = 0x0100acaa712e29adad9abb681c5ef666e69300000000000000003cd5a092d7c816de798b7f5d09acba6f04eeed208cd6c19b
NAS-Identifier = "MIKROTIK"
NAS-IP-Address = 192.168.0.1
# Executing section authorize from file /etc/freeradius/sites-enabled/default
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
[mschap] Found MS-CHAP attributes. Setting 'Auth-Type = mschap'
++[mschap] returns ok
++[digest] returns noop
[suffix] No '@' in User-Name = "zaib", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
[exec] expand: %{User-Name} -> zaib
[exec] expand: %{Calling-Station-Id} -> 0C:84:DC:1E:0B:8D
Exec-Program output: Seems to be New User, adding its MAC address in table ...
Exec-Program-Wait: plaintext: Seems to be New User, adding its MAC address in table ...
Exec-Program: returned: 0
++[exec] returns ok

As you CAN SEE

“Exec-Program output: Seems to be New User, adding its MAC address in table …”

Now see the difference …

RADCHECK TABLE, Before Login …

1- before login

RADCHECK TABLE, After Login …

2- after login ok

When user will login again, radcheck table will be searched, if the mac found it will simply skip the add part and print the statement

[exec] expand: %{User-Name} -> zaib
[exec] expand: %{Calling-Station-Id} -> 0C:84:DC:1E:0B:8D
Exec-Program output: MAC Entry already found
Exec-Program-Wait: plaintext: MAC Entry already found
Exec-Program: returned: 0
++[exec] returns ok

& If the user will login from any other mac/workstation, he will be denied access.


Method #2
Trigger approach to add MAC address
🙂 ~ ZAIB

Use following TRIGGER on radacct table. It will add the MAC address for the user in RADCHECK table. (or you can modify it as well)

--
-- Triggers `radacct`
--
DELIMITER $$
CREATE TRIGGER `chk_mac_after_insert` AFTER INSERT ON `radacct` FOR EACH ROW BEGIN
SET @mac = (SELECT count(*) from radcheck where username=New.username and attribute='Calling-Station-ID');
IF (@mac = 0) THEN
INSERT into radcheck (username,attribute,op,value) values (NEW.username,'Calling-Station-ID',':=',NEW.callingstationid);
UPDATE users SET mac = NEW.callingstationid where username = NEW.username;
END IF;
END
$$
DELIMITER ;

trigger for mac add.JPG


Regard’s
Syed Jahanzaib ~

Older Posts »

%d bloggers like this: