Syed Jahanzaib Personal Blog to Share Knowledge !

November 8, 2019

Freeradius 3 with Mikrotik – Part-1 # Time to upgrade

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

freeradius-ldaps-e1380323478337

fr logo



Disclaimer! This is important!

Every Network is different , so one solution cannot be applied to all. Therefore try to understand logics & create or modify the solutions as per your network scenario. Never follow copy paste blindly, [unfortunately this has become our national culture]

My humble request is that kindly donot consider me as 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 try to help others

Regard's
Syed Jahanzaib~

This is another post about installation & configuration of ISP related billing system called Freeradius version 3.0.19. My aim is to let people know that creating your own Radius Billing system is not a ROCKET SCIENCE.

The only thing required is your ultimate passion to achieve the goal & with the right search, reading, understanding logic’s, you can do all on your own. I strongly encourage all to read the FR mailing list and Google search

For older version of Freeradius ver 2.x series, you may read below

https://aacable.wordpress.com/2016/03/11/mikrotik-with-freeradiusmysql-part-1/


Make your own Billing system in Linux with Latest version of Freeradius 3.0.19 / MySQL 5.7.27

by Syed Jahanzaib / aacable[at]hotmail[dot]com

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

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

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

Let’s Rock …

blog-header-collaboration-between-hardware-software-teams_0

Hardware Software components used in this post

Hardware:

  • Xeon 2Ghz CPU x 2
  • 64 GB RAM
  • 480GB SSD x 8 disks in RAID-10
  • Vmware ESXI 6.5 installed on bare metal hardware
  • VM guest created for Radius role with following config
    40 GB RAM / 12 CORES VCPU / 300 GB Disk Space

Software:

  • OS: Ubuntu 18.04.3 LTS Server Edition [Click here for Download Link]
  • FreeRADIUS: Version 3.0.19 [using apt-get with custom repository]
  • Mysql Version: mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper [using apt-get]
  • IP scheme

    Radius IP = 101.11.11.254
    Mikrotik IP = 101.11.11.255

 


 

Quick Tips for Ubuntu OS before rolling out FR

helpful_tips1

Network address configuration

18.04_beaver_wp_8192x4608_AW

In Ubuntu 18, network addresses are configured slightly differently as compared to earlier version of ubuntu series. To add / modify IP addresses, edit 50-cloud-init.yaml file

nano /etc/netplan/50-cloud-init.yaml

A working configuration file is attached as below. Make sure to follow syntaxes as defined. U18 is quite sensitive regarding this section.

# Ubuntu 18 Network Config file # Syed Jahanzaib
network:
ethernets:
# Interface Name
ens33:
# Interface IP Address
addresses:
- 101.11.11.254/24
gateway4: 101.11.11.1
nameservers:
addresses:
- 1.1.1.1
- 8.8.8.8
version: 2

<span style="color:var(--color-text);">


 

ROOT Login Access on local machine is not Allowed by Default on Fresh installation of Ubuntu 18

After fresh installation of Ubuntu 18 server edition, you cannot login with root ID by default. & to perform various functions, you may need ROOT access.

Therefore using your normal user account , issue below cmd’s to change root password in order to enable root login access

sudo passwd root

it will ask you to enter current user password, then it will ask to enter new root password two times. Do so and then you can change to use root user by using

# su

and it will ask you to enter root password, Enter the password that you setup in above steps. and you will be switched to ROOT.

Allow SSH Login for remote access using PUTTY or any other tool

permit-ssh-root-login-ubuntu

By default, Ubuntu 18 will not allow you to remotely login via SSH.

Edit file

nano /etc/ssh/sshd_config

now search for

PermitRootLogin prohibit-password

Change it to

PermitRootLogin yes

Save & Exit , Restart ssh service

service ssh restart

Another quick copy paste method to enable root ssh access

sudo sed -i 's/#PermitRootLogin prohibit-password/PermitRootLogin yes/' /etc/ssh/sshd_config
service ssh restart

NTP configuration for timezone configuration &

Automatic time update from the internet [for KHI/PK]

apt-get -y install ntp ntpdate
cp /usr/share/zoneinfo/Asia/Karachi /etc/localtime
sudo /etc/init.d/ntp restart

Disable iPV6

disable-ipv6-on-ubuntu

if ipv6 not required, its a good idea to disable it from beginning.

Use below CMD

echo "net.ipv6.conf.all.disable_ipv6=1" >> /etc/sysctl.conf
echo "net.ipv6.conf.default.disable_ipv6=1" /etc/sysctl.conf
sysctl -p

for further ensurity, now edit

nano /etc/default/grub

& change

FROM:
GRUB_CMDLINE_LINUX_DEFAULT=""
GRUB_CMDLINE_LINUX=""
TO:
GRUB_CMDLINE_LINUX_DEFAULT="ipv6.disable=1"
GRUB_CMDLINE_LINUX="ipv6.disable=1"

Save & exit & run below cmd to update GRUB

sudo update-grub



Installing components required for general operations including MYSQL as well

Install supporting tools like mysql / phpmyadmin etc

#During installation, it will ask you to enter PASSWORD, dont let it skip, be vigilant 

apt-get -y install apache2 mc wget make gcc mysql-server mysql-client curl
apt-get -y install phpmyadmin

NOTE# mySQL Empty Root Password 

Try to login to mysql with your defined password. In case if mysql is letting you in with any random or empty password, use this CMD to change mysql root password

Use these steps only if you have mysql login issue with empty/any password

mysql -uroot -pANYPASS
UPDATE mysql.user SET authentication_string= '' WHERE User='root';
UPDATE mysql.user SET plugin = '' WHERE user = 'root';
UPDATE mysql.user SET authentication_string=PASSWORD('zaib1234') WHERE User='root';
UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root';
exit

Don’t forget to restart mySQL service, this part wasted some time of mine

service mysql restart

fr logo with hatFR logo new .png

~ Installation & Configuration part ~
~ Freeradius version 3.0.19 ~

To install freeradius 3.0.19 via apt-get on ubuntu 18.4 server, first update the repository by using following

echo "deb http://packages.networkradius.com/releases/ubuntu-bionic bionic main" >> /etc/apt/sources.list

Save and exit, and issue below cmd

sudo apt-key adv --keyserver keys.gnupg.net --recv-key 0x41382202
sudo apt-get update

~

Now using apt-get install method , we will install FR ver 3.0.19

apt-get -y install freeradius freeradius-mysql freeradius-utils

Once installed, check the FR version, & it should be similar to this

freeradius -v

radiusd: FreeRADIUS Version 3.0.19 (git #ab4c76709), for host x86_64-pc-linux-gnu
FreeRADIUS Version 3.0.19
Copyright (C) 1999-2019 The FreeRADIUS server project and contributors
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE
You may redistribute copies of FreeRADIUS under the terms of the
GNU General Public License
For more information about these matters, see the file named COPYRIGHT

SQL Section:

Create DB in mysql

# Make sure to change password which you setup while installing mySQL

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

Now import sql schema file into the mysql

mysql -u root -pzaib1234 radius < /etc/freeradius/mods-config/sql/main/mysql/schema.sql

Now create SQL link

ln -s /etc/freeradius/mods-available/sql /etc/freeradius/mods-enabled/

FREERADIUS CONFIG Section

now edit /etc/freeradius/mods-enabled/sql

nano /etc/freeradius/mods-enabled/sql

& make modification like in SQL { section

change to below

sql {
dialect = "mysql"
driver = "rlm_sql_${dialect}"
mysql {
# there are few lines related to certs, delete them as we donot require them
warnings = auto
}

# Connection info:
server = "localhost"
port = 3306
login = "radius"
password = "zaib1234"
# Change password as you configured in initial steps/zaib
# Database table configuration for everything except Oracle
radius_db = "radius"

Save & exit.

Adding your NAS in mysql NAS table

TIP: Quick CMD to add NAS in table,

mysql -uroot -pzaib1234 -e "use radius; INSERT INTO nas (id, nasname, shortname, type, ports, secret, server, community, description) VALUES (NULL, '101.11.11.255', NULL, 'other', NULL, '123456', NULL, NULL, 'RADIUS Client');"

OUTPUT:

mysql> select * from nas;
+----+---------------+-----------------+-------+-------+--------+--------+-----------+---------------+
| id | nasname | shortname | type | ports | secret | server | community | description |
+----+---------------+-----------------+-------+-------+--------+--------+-----------+---------------+
| 1 | 101.11.11.255 | NULL  | other | 3799 | 123456 | | NULL | RADIUS Client |
+----+---------------+-----------------+-------+-------+--------+--------+-----------+---------------+
1 row in set (0.00 sec)


Create TEST USER for validating freeradius & mysql installation

We will now create a simple Test USER in mySQL RADIUS DB for verification / test purposes. It will have

  1. Username Password
  2. Mikrotik Rate Limit for Bandwidth Limitation, example 1mb/1mb
  3. Expiration date time
  4. Simultaneous Use set to 1 , to prevent Multiple login of same ID (it requires further modifications in DEFAULT section of sites enabled)
mysql -uroot -pzaib1234
use radius;
INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES ( NULL , 'zaib', 'Cleartext-Password', ':=', 'zaib');
INSERT INTO radreply (username, attribute, op, value) VALUES ('zaib', 'Mikrotik-Rate-Limit', '==', '1024k/1024k');
INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Expiration', ':=', '13 Jan 2029 11:00');
INSERT INTO radcheck (username,attribute,op,value) VALUES ('zaib', 'Simultaneous-Use', ':=', '1');
exit

effective-software-testing-tips-testers-on-the-go

Testing FREERADIUS connection using FR built-in tools

It’s time that we should test freeradius connectivity. in first Terminal, issue below cmd to start freeradius in DEBUG mode

service freeradius stop
freeradius -X

If FR configurall are configured correctly, then you will see something like below

Listening on auth address 127.0.0.1 port 18120 bound to server inner-tunnel
Listening on auth address * port 1812 bound to server default
Listening on acct address * port 1813 bound to server default
Listening on auth address :: port 1812 bound to server default
Listening on acct address :: port 1813 bound to server default
Listening on proxy address * port 48178
Listening on proxy address :: port 38713
Ready to process requests

If you see Ready to process requests  , than all good to Go, Else it will show you the particular errors and lines that you must FIX in order to proceed further.

Moving forward, Open second Terminal window, & try below cmd’s to test the user credentials/authentication

Test#1 using RADCLIENT tool with particular calling MAC address

echo "User-Name=zaib,User-Password=zaib,Calling-Station-Id=00:0C:29:71:60:DA" | radclient -s localhost:1812 auth testing123

Result:

Sent Access-Request Id 38 from 0.0.0.0:37282 to 127.0.0.1:1812 length 63
Received Access-Accept Id 38 from 127.0.0.1:1812 to 127.0.0.1:37282 length 26
Packet summary:
Accepted : 1
Rejected : 0
Lost : 0
Passed filter : 1
Failed filter : 0

Test#2 using RADTEST tool

radtest zaib zaib localhost 1812 testing123

Result:

Sent Access-Request Id 26 from 0.0.0.0:53486 to 127.0.0.1:1812 length 74
User-Name = "zaib"
User-Password = "zaib"
NAS-IP-Address = 101.11.11.254
NAS-Port = 1812
Message-Authenticator = 0x00
Cleartext-Password = "zaib"
Received Access-Accept Id 26 from 127.0.0.1:1812 to 127.0.0.1:53486 length 26
Session-Timeout = 289854175

Freeradius Debug Window:

Ready to process requests
(0) Received Access-Request Id 57 from 127.0.0.1:56913 to 127.0.0.1:1812 length 63
(0) User-Name = "zaib"
(0) User-Password = "zaib"
(0) Calling-Station-Id = "00:0C:29:71:60:DA"
(0) # Executing section authorize from file /etc/freeradius/sites-enabled/default
(0) authorize {
(0) [preprocess] = ok
(0) [chap] = noop
(0) [mschap] = noop
(0) suffix: Checking for suffix after "@"
(0) suffix: No '@' in User-Name = "zaib", looking up realm NULL
(0) suffix: No such realm "NULL"
(0) [suffix] = noop
(0) [files] = noop
(0) sql: EXPAND %{User-Name}
(0) sql: --> zaib
(0) sql: SQL-User-Name set to 'zaib'
rlm_sql (sql): Reserved connection (1)
(0) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id
(0) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'zaib' ORDER BY id
(0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'zaib' ORDER BY id
(0) sql: User found in radcheck table
(0) sql: Conditional check items matched, merging assignment check items
(0) sql: Cleartext-Password := "zaib"
(0) sql: Expiration := "Jan 13 2029 11:00:00 PKT"
(0) sql: Simultaneous-Use := 1
(0) sql: Calling-Station-Id := "00:0C:29:71:60:DA"
(0) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id
(0) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'zaib' ORDER BY id
(0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'zaib' ORDER BY id
(0) sql: User found in radreply table, merging reply items
(0) sql: Mikrotik-Rate-Limit == "1024k/1024k"
rlm_sql (sql): Reserved connection (2)
rlm_sql (sql): Released connection (2)
Need 5 more connections to reach 10 spares
rlm_sql (sql): Opening additional connection (6), 1 of 26 pending slots used
rlm_sql_mysql: Starting connect to MySQL server
rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 5.7.27-0ubuntu0.18.04.1, protocol version 10
(0) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority
(0) sql: --> SELECT groupname FROM radusergroup WHERE username = 'zaib' ORDER BY priority
(0) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'zaib' ORDER BY priority
(0) sql: User not found in any groups
rlm_sql (sql): Released connection (1)
(0) [sql] = ok
(0) if (notfound){
(0) if (notfound) -> FALSE
(0) expiration: Account will expire at 'Jan 13 2029 11:00:00 PKT'
(0) [expiration] = ok
(0) if (userlock){
(0) if (userlock) -> FALSE
(0) [pap] = updated
(0) if (&request:Calling-Station-Id != &control:Calling-Station-Id) {
(0) if (&request:Calling-Station-Id != &control:Calling-Station-Id) -> FALSE
(0) } # authorize = updated
(0) Found Auth-Type = PAP
(0) # Executing group from file /etc/freeradius/sites-enabled/default
(0) Auth-Type PAP {
(0) pap: Login attempt with password
(0) pap: Comparing with "known good" Cleartext-Password
(0) pap: User authenticated successfully
(0) [pap] = ok
(0) } # Auth-Type PAP = ok
(0) # Executing section session from file /etc/freeradius/sites-enabled/default
(0) session {
(0) sql: EXPAND %{User-Name}
(0) sql: --> zaib
(0) sql: SQL-User-Name set to 'zaib'
rlm_sql (sql): Reserved connection (3)
(0) sql: EXPAND SELECT COUNT(*) FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL
(0) sql: --> SELECT COUNT(*) FROM radacct WHERE username = 'zaib' AND acctstoptime IS NULL
(0) sql: Executing select query: SELECT COUNT(*) FROM radacct WHERE username = 'zaib' AND acctstoptime IS NULL
rlm_sql (sql): Released connection (3)
(0) [sql] = ok
(0) } # session = ok
(0) # Executing section post-auth from file /etc/freeradius/sites-enabled/default
(0) post-auth {
(0) if (session-state:User-Name && reply:User-Name && request:User-Name && (reply:User-Name == request:User-Name)) {
(0) if (session-state:User-Name && reply:User-Name && request:User-Name && (reply:User-Name == request:User-Name)) -> FALSE
(0) update {
(0) No attributes updated for RHS &session-state:
(0) } # update = noop
(0) sql: EXPAND .query
(0) sql: --> .query
(0) sql: Using query template 'query'
rlm_sql (sql): Reserved connection (4)
(0) sql: EXPAND %{User-Name}
(0) sql: --> zaib
(0) sql: SQL-User-Name set to 'zaib'
(0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, reply_msg, authdate, nasipaddress, mac) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%{reply:Reply-Message}', '%S', '%{NAS-IP-Address}', '%{Calling-Station-Id}')
(0) sql: --> INSERT INTO radpostauth (username, pass, reply, reply_msg, authdate, nasipaddress, mac) VALUES ( 'zaib', 'zaib', 'Access-Accept', '', '2019-11-07 15:58:05', '127.0.0.1', '00:0C:29:71:60:DA')
(0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, reply_msg, authdate, nasipaddress, mac) VALUES ( 'zaib', 'zaib', 'Access-Accept', '', '2019-11-07 15:58:05', '127.0.0.1', '00:0C:29:71:60:DA')
(0) sql: SQL query returned: success
(0) sql: 1 record(s) updated
rlm_sql (sql): Released connection (4)
(0) [sql] = ok
(0) [exec] = noop
(0) } # post-auth = ok
(0) Sent Access-Accept Id 57 from 127.0.0.1:1812 to 127.0.0.1:56913 length 0
(0) Session-Timeout = 289854115
(0) Finished request
Waking up in 4.9 seconds.
(0) Cleaning up request packet ID 57 with timestamp +2
Ready to process requests

Above output shows OK status. You can inspect any errors in the freeradius config or with the user credentials here like, invalid mac, expiratione etc


Workaround to open existing session in RADACCT

welcoem_back_500x143

When there is an disconnection between NAS & RADIUS, following situation will be observed

NAS:

User session will be online on NAS, but NAS will not send interim updates to the Radius because communication between NAS/Radius is lost

RADIUS:

Radius will not receive any interim updates from the NAS & his session will keep alive (acctstoptime IS NULL), But for some local requirements we scheduled a bash script which checks stale session checking last acct update time . Example if the radius doesn’t receives interim update for 10 minutes, it will close this user session in radacct table by setting acctstoptime to current date/time.

Now when the connectivity restore between the NAS & Radius, and since NAS has this user online, it will send Interim Update to the radius, BUT our radius have closed this session already earlier (via bash script), therefore this update packet will only update the old entry using unique acct ID, but acctstoptime will not be set to NULL.

This will create confusion for frontend, because when you will search online users by searching entries whose acctstoptime is NULL. this session will not appear which will create FALSE assumption on your frontend that this user is offline whereas this user is actually online in NAS.

To settle this , I made some dirty workaround by poking in freeradius SQL queries.conf  file. I am not aware if this trick is is already being used or not,but its working fine on my end.

Add below code

acctstoptime = NULL, \

in queries.conf

Edit file by

nano /etc/freeradius/mods-config/sql/main/mysql/queries.conf
interim-update {
#
# Update an existing session and calculate the interval
# between the last data we received for the session and this
# update. This can be used to find stale sessions.
#
query = "\
UPDATE ${....acct_table1} \
SET \
acctupdatetime = (@acctupdatetime_old:=acctupdatetime), \
acctupdatetime = FROM_UNIXTIME(\
%{integer:Event-Timestamp}), \
acctinterval = %{integer:Event-Timestamp} - \
UNIX_TIMESTAMP(@acctupdatetime_old), \
acctstoptime = NULL, \
framedipaddress = '%{Framed-IP-Address}', \
framedipv6address = '%{Framed-IPv6-Address}', \
framedipv6prefix = '%{Framed-IPv6-Prefix}', \
framedinterfaceid = '%{Framed-Interface-Id}', \
delegatedipv6prefix = '%{Delegated-IPv6-Prefix}', \
acctsessiontime = %{%{Acct-Session-Time}:-NULL}, \
acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' \
<< 32 | '%{%{Acct-Input-Octets}:-0}', \
acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \
<< 32 | '%{%{Acct-Output-Octets}:-0}' \
WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"

& as always reload freeradius

service freeradius reload


Prevent Multiple User login [ Simultaneous Login :=1 ]

To prevent multiple login for same user , we have to first enable SQL base session logging , so that our control can check the session details for already logged in user

Edit file

nano /etc/freeradius/sites-enabled/default

& then uncomment sql under session { section

# Session database, used for checking Simultaneous-Use. Either the radutmp
# or rlm_sql module can handle this.
# The rlm_sql module is *much* faster
session {
sql

Now we need to add UNLAG query in DEFAULT config to check for MAC address if control is defined in RADCHECK table

edit file

nano /etc/freeradius/sites-enabled/default

Under authorize { section, add below code

if (&request:Calling-Station-Id != &control:Calling-Station-Id) {
update reply {
Reply-Message := "Incorrect Mac"
}
reject
}

 

 

After every change in config files, reload freeradius by

service freeradius reload

Now you can simulate test using below CMD

RADCLIENT test with particular calling MAC address

echo "User-Name=zaib,User-Password=zaib,Calling-Station-Id=00:0C:29:71:60:DA" | radclient -s localhost:1812 auth testing123

Note: If there is no Calling-Station-ID defined in RADCHECK , test will success and in FREERADIUS Debug log, you will see line that failed to evaluate control. but user will be able to connect if no mac defined in radcheck.


Modifying RADPOSTAUTH section for recording user login attempts in sql table

To record all users login attempts with our customized RADPOSTAUTH table, we can following

First edit queries.conf in /etc/freeradius/mods-config/sql/main/mysql

nano /etc/freeradius/mods-config/sql/main/mysql/queries.conf

Goto End & search for section (usually its in the last)

#######################################################################
# Authentication Logging Queries
#######################################################################
# postauth_query - Insert some info after authentication
#######################################################################

Delete existing query & use this one


query = "\
INSERT INTO ${..postauth_table} \
(username, pass, reply, reply_msg, authdate, nasipaddress, mac) \
VALUES ( \
'%{SQL-User-Name}', \
'%{%{User-Password}:-%{Chap-Password}}', \
'%{reply:Packet-Type}', \
'%{reply:Reply-Message}', \
'%S', \
'%{NAS-IP-Address}', \
'%{Calling-Station-Id}')"
}

Save and Exit.

Modify RADPOSTAUTH table

Now modify the RADPOSTAUTH table in radius DB so that reply messages can be stored here in the way want it to be

mysql -uroot -pzaib1234
use radius;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
DROP TABLE IF EXISTS `radpostauth`;
CREATE TABLE `radpostauth` ( `id` int(11) NOT NULL, `username` varchar(64) NOT NULL DEFAULT '', `pass` varchar(64) NOT NULL DEFAULT '', `reply` varchar(256) NOT NULL DEFAULT '', `reply_msg` varchar(256) DEFAULT NULL, `authdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `nasipaddress` varchar(100) DEFAULT NULL, `mac` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `radpostauth` ADD PRIMARY KEY (`id`), ADD KEY `username` (`username`(32));
ALTER TABLE `radpostauth` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
exit

As always reload freeradius service after any modification in the configuration files using

service freeradius reload

Try to auth any user and then look into RADPOSTAUTH table & you will see entries like


mysql> select * from radpostauth;
+----+-----------+-------+---------------+-------------------------------------------+---------------------+---------------+-------------------+
| id | username | pass | reply | reply_msg | authdate | nasipaddress | mac |
+----+-----------+-------+---------------+-------------------------------------------+---------------------+---------------+-------------------+
| 14 | zaib | zaib | Access-Accept | | 2019-11-05 19:21:37 | 127.0.0.1 | 00:0C:29:35:F8:2F |
| 15 | zaib | zaib | Access-Accept | | 2019-11-05 19:21:50 | 127.0.0.1 | 00:0C:29:35:F8:21 |
| 16 | zaib | zaib | Access-Reject | | 2019-11-05 19:22:12 | 127.0.0.1 | 00:0C:29:35:F8:21 |
| 17 | zaib | zaib | Access-Reject | Incorrect Mac | 2019-11-05 19:23:51 | 127.0.0.1 | 00:0C:29:35:F8:21 |
| 18 | zaib | zaib | Access-Reject | Incorrect Mac | 2019-11-05 19:23:57 | 127.0.0.1 | 00:0C:29:35:F8:2F |
| 19 | zaib | zaib | Access-Accept | | 2019-11-05 19:24:14 | 127.0.0.1 | 11:11:11:11:11:11 |
| 20 | zaib | zaib | Access-Reject | Incorrect Mac | 2019-11-05 19:24:21 | 127.0.0.1 | 00:0C:29:35:F8:2F |
| 21 | zaib | zaib | Access-Reject | Incorrect Mac | 2019-11-05 19:25:23 | 127.0.0.1 | 00:0C:29:35:F8:2F |
| 22 | zaib | zaib | Access-Accept | | 2019-11-05 19:25:29 | 127.0.0.1 | 11:11:11:11:11:11 |
| 23 | zaib | zaib | Access-Reject | Your account has expired=2C zaib | 2019-11-05 19:25:44 | 127.0.0.1 | 11:11:11:11:11:11 |
| 24 | zaib | zaib | Access-Accept | Account Expired | 2019-11-05 19:26:16 | 127.0.0.1 | 11:11:11:11:11:11 |
| 25 | zaib | zaib | Access-Accept | Account Expired | 2019-11-05 19:26:25 | 101.11.11.254 | |
| 26 | zaib | zaib | Access-Accept | Account Expired | 2019-11-05 19:27:22 | 101.11.11.254 | |
| 27 | zaib | zaib1 | Access-Reject | Account Expired | 2019-11-05 19:27:41 | 101.11.11.254 | |
| 28 | zaib1 | zaib | Access-Reject | | 2019-11-05 19:27:55 | 101.11.11.254 | |
| 29 | zaib1 | zaib | Access-Reject | Username not found | 2019-11-05 19:29:04 | 101.11.11.254 | |
| 30 | zaib | zaib1 | Access-Reject | Account Expired | 2019-11-05 19:29:12 | 101.11.11.254 | |
| 31 | zaib | zaib1 | Access-Reject | Account Expired | 2019-11-05 19:30:28 | 101.11.11.254 | |
| 32 | zaib | zaib1 | Access-Reject | Wrong Password | 2019-11-05 19:30:46 | 101.11.11.254 | |
| 33 | zaib1 | zaib | Access-Reject | Username not found | 2019-11-05 19:30:54 | 101.11.11.254 | |
| 34 | zaib1 | zaib | Access-Reject | Username not found | 2019-11-05 19:32:45 | 101.11.11.254 | |
| 35 | zaib | zaib | Access-Accept | | 2019-11-05 19:32:51 | 101.11.11.254 | |
| 36 | zaib | zaib | Access-Accept | | 2019-11-05 19:33:01 | 101.11.11.254 | |
| 37 | zaib | zaib | Access-Reject | You are already logged in - access denied | 2019-11-05 19:33:57 | 101.11.11.254 | |
| 38 | zaib | zaib | Access-Accept | | 2019-11-05 19:35:21 | 101.11.11.254 | |
| 39 | zaib | zaib | Access-Accept | | 2019-11-05 19:35:28 | 127.0.0.1 | 11:11:11:11:11:11 |
| 40 | zaib | zaib | Access-Accept | | 2019-11-05 19:35:51 | 127.0.0.1 | |
| 41 | zaib | | Access-Reject | Incorrect Mac | 2019-11-05 19:37:23 | 101.11.11.255 | 00:0C:29:71:60:DA |
| 42 | zaib | | Access-Accept | | 2019-11-05 19:37:38 | 101.11.11.255 | 00:0C:29:71:60:DA |
| 43 | zaib | | Access-Reject | Incorrect Mac | 2019-11-05 19:39:13 | 101.11.11.255 | 00:0C:29:71:60:DA |
| 44 | zaib | zaib | Access-Reject | You are already logged in - access denied | 2019-11-06 08:37:04 | 101.11.11.254 | |
| 45 | zaib | zaib | Access-Accept | | 2019-11-06 08:37:24 | 101.11.11.254 | |
| 46 | zaib | | Access-Reject | Incorrect Mac | 2019-11-07 07:56:07 | 101.11.11.255 | 00:0C:29:71:60:DA |
| 47 | zaib | | Access-Reject | You are already logged in - access denied | 2019-11-07 07:57:03 | 101.11.11.255 | 00:0C:29:71:60:DA |
| 48 | zaib | | Access-Accept | | 2019-11-07 07:57:18 | 101.11.11.255 | 00:0C:29:71:60:DA |
| 49 | zaib@zaib | | Access-Reject | Wrong Password | 2019-11-07 07:57:23 | 101.11.11.255 | 00:0C:29:71:60:DA |
| 50 | zaib@zaib | | Access-Accept | | 2019-11-07 08:00:37 | 101.11.11.255 | 00:0C:29:71:60:DA |
| 51 | zaib | zaib | Access-Reject | Incorrect Mac | 2019-11-07 15:54:53 | 127.0.0.1 | 11:11:11:11:11:11 |
| 52 | zaib | zaib | Access-Accept | | 2019-11-07 15:56:12 | 127.0.0.1 | 00:0C:29:71:60:DA |
| 53 | zaib | zaib | Access-Accept | | 2019-11-07 15:57:05 | 101.11.11.254 | |
| 54 | zaib | zaib | Access-Accept | | 2019-11-07 15:58:05 | 127.0.0.1 | 00:0C:29:71:60:DA |
+----+-----------+-------+---------------+-------------------------------------------+---------------------+---------------+-------------------+

Unable to login with username like zaib@zaib

If you are unable to login with username like z@ib@zaib , then disable the filter_username module under authorize { section

edit file

nano /etc/freeradius/sites-enabled/default

Navigate to authorize { section, & comment the filter_username

authorize {
#
# Take a User-Name, and perform some checks on it, for spaces and other
# invalid characters. If the User-Name appears invalid, reject the
# request.
# See policy.d/filter for the definition of the filter_username policy.

# filter_username

Mikrotik-Rate-Limit not working with FR 3
(Updated on 9-NOV-2019)

Today I encountered issue at local network, where radius was not sending the Mikrotik-Rate-Limit syntax to the NAS. After doing some troubleshooting it came to my knowledge that you have to change op value from == to := , Example

Mikrotik-Rate-Limit := 1024k/1024k

Trimming RADACCT table to make it SLIM & blazing Responsive

greensboro-tree-solutions-tree-trimming-1-1024x683

Over the period of time, radacct table will grow enormously. This can slow down many queries , therefore its a good idea to move all closed session entries (NOT NULL) to another table like radacct_archive. I created following bash script which helped me to sort many issues.

Note: Make sure to change credentials section

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

& copy paste following

#!/usr/bin/env bash
####!/bin/sh
#set -x
#MYSQL DETAILS
DATE=`date`
logger radacct_trim script started $DATE
SQLUSER="root"
SQLPASS="YOUR_PASSWORD"
DB="radius"
TBL_ARCH="radacct_archive"
TBL_ARCH_EXISTS=$(printf 'SHOW TABLES LIKE "%s"' "$TBL_ARCH")
MONTHS="12"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLUSER --skip-column-names -s -e"
# This is one time step.
echo "
Script Started @ $DATE
"
echo "- Step 1 : Checking for DB: $DB / TABLE: $TBL_ARCH ..."
DBCHK=`mysqlshow --user=$SQLUSER $DB | grep -v Wildcard | grep -o $DB`
if [ "$DBCHK" == "$DB" ]; then
echo " > $DB DB found"
else
echo " > $DB not found. Creating now ..."
$CMD "create database if not exists $DB;"
fi
if [[ $(mysql -u$SQLUSER -e "$TBL_ARCH_EXISTS" $DB) ]]
then
echo " > $TBL_ARCH TABLE found IN DB: $DB"
else
echo " > $TBL_ARCH TABLE not found IN DB: $DB / Creating now ..."
$CMD "use $DB; create table if not exists $TBL_ARCH LIKE radacct;"
fi

# Start Action: copy data from radacct to new db/archive table
NOTULL_COUNT=`$CMD "use $DB; select count(*) from radacct WHERE acctstoptime is not null;"`
echo "- Step 2 : Found $NOTULL_COUNT records in radacct table , Now copying $NOTULL_COUNT records to $TBL_ARCH table ..."
$CMD "use $DB; INSERT IGNORE INTO $TBL_ARCH SELECT * FROM radacct WHERE acctstoptime is not null;"
echo "- Step 3 : Deleting $NOTULL_COUNT records old data from radacct table (which have acctstoptime NOT NULL) ..."
# --- Now Delete data from CURRENT RADACCT table so that it should remain fit and smart ins size
$CMD "use $DB; DELETE FROM radacct WHERE acctstoptime is not null;"
echo "- Step 4 : Copying old data from $TBL_ARCH older then $MONTHS months ..."
# --- Now Delete data from RADACCT_ARCHIVE table so that it should not grow either more than we required i.e 1 Year - one year archived data is enough IMO
$CMD "use $DB; DELETE FROM $TBL_ARCH WHERE date(acctstarttime) < (CURDATE() - INTERVAL $MONTHS MONTH);"
DATE=`date`
logger radacct_trim script ended with $NOTULL_COUNT records processed for trimming @ $DATE
echo "
radacct_trim script ended with $NOTULL_COUNT records processed for trimming @ $DATE"

Schedule this BASH script to run every minute (or as per required)

1 * * * * /temp/radacct_trim.sh

Check for STALE Sessions in RADACCT [for FR ver3 in particular]

This bash script will close sessions in RADACCT whose interim updates have not received in last XX minutes. You can schedule it to run every minute or as required

#!/usr/bin/env bash
###!/bin/sh
#set -x
#trap "set +x; set -x" DEBUG
# BASH base script to close STALE sessions from freeradius, whose accounting is not updated in last X minutes in RADACCT table
# By Syed Jahanzaib
# CREATED on : 25-July-2018
# Local Variables

# Mysql credentials
SQLID="root"
SQLPASS="YOUR_PASSWORD"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
DB="radius"
INTERVAL="11"
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
TBL_LOG="log"
STALE_USR_LIST="/tmp/stale_sessions.txt"
# Date Time Variables
DATE=$(date +%d-%m-%Y)
DT_HMS=$(date +'%H:%M:%S')
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')

#Check and close session for staleness
$CMD "use $DB; select username,radacctid,nasipaddress from radacct WHERE acctstoptime IS NULL AND acctupdatetime  $STALE_USR_LIST
# IF no user found , show error and exit - zaib
CHK=`wc -m $STALE_USR_LIST | awk {'print $1}'`
if [ "$CHK" -eq 0 ]
then
echo "No stall sesion found (which accounting session have not updated in last $INTERVAL minutes) , exiting ..."
exit 1
fi
$CMD "use $DB; UPDATE radacct SET acctstoptime = NOW(), acctterminatecause = 'Clear-Stale-Session' WHERE acctstoptime IS NULL AND acctupdatetime /dev/null 2>&1

WordPress is not letting pasting of code. look for this line

wp post problem.PNG


TIPS for MYSQL:

following are some mySQL tuning made according to the hardware. These are just my assumptions that this setting will work fine. However you may tune your setup according to your hardware. Install & run MYSQLTUNER tool which will better guide you as per the actual hardware/software scneario.

/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 127.0.0.1
skip_name_resolve
key_buffer_size = 4G
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
max_connections = 2000
table_open_cache = 15000
query_cache_limit = 200M
query_cache_size = 0
query_cache_type = 0
#log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size = 22G
innodb_log_file_size = 11G
innodb_buffer_pool_instances = 22

MYSQLTUNER to see mySQL performance

It is good idea to install mysqltuner

apt-get install mysqltuner

Let your mysql Run for 1-2 days, then run this tool

mysqltuner

MYSQL FIND ENGINE TYPE FOR DB

mysql -uroot -pzaib1234
use radius;
SHOW TABLE STATUS\G

OUTPUT:

mysql> SHOW TABLE STATUS\G
*************************** 2. row ***************************
Name: radacct
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 212992
Data_free: 0
Auto_increment: 2323234
Create_time: 2019-11-05 19:06:21
Update_time: 2019-11-07 10:27:08
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:

In above OUTPUT, You can see the type, with 5.5+ its generally innodb

 


Monitor Disk Read Write

To monitor all disks read/write speed install this tool

apt-get install -y iotop

Run it using below cmd

iotop -o

HTOP – Monitor ALL Processes / CPU Cores Usage Monitor

install this

apt-get install -y htop
htop

Additional:

Doing Stress Test on Radius using BASH scripts

stress test on fr 3

FREERADIUS Stress Test Using BASH script – Zaib


Regard’s
Syed Jahanzaib

April 5, 2019

Mikrotik with Freeradius/mySQL # Part-22 – Create Dynamic Address List using Mikrotik-Address-List Attribute

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

fre



Disclaimer! This is important!

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

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

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

Regard’s
Syed Jahanzaib~


Scenario:

  • We have FREERADIUS installed as a AAA system in Ubuntu 16.04 server
  • Mikrotik version 6.44 is acting as PPPoE NAS connected with radius for AAA

Requirement:

When any user connects with our NAS, he should be added to mikrotik dynamic address list under IP > firewall > address list, so that we can manipulate this address list for different tasks, example mark connections/packets/routing and use them in Queues / Routes section or perform different sort of filtering as required.

In this particular task we are dynamically adding user in particular address list using radius attributes, then using this address list packet marking is being made, and then in Queues we are using these marked packets for different sort of bandwidth policies, example for normal internet we will limit 1mb per user , and for CDN traffic we will add addition 2mb for YT & FB. [and vice versa for different packages accordingly]

 


Solution:

We will use Mikrotik-Address-List attribute in radgroupreply section. as shown here.

1# Adding User entry in RADCHECK table so user can authenticate …

mysql> select * from radcheck;
+----+----------+--------------------+----+-------------------+
| id | username | attribute | op | value |
+----+----------+--------------------+----+-------------------+
| 1 | zaib | Cleartext-Password | := | zaib |
+----+----------+--------------------+----+-------------------+
1 rows in set (0.01 sec)

2# Adding Radius Group Reply for 1mb Group, Example 1mb group user will get 1mb dynamic queue plus they will be added dynamically in address list name 1mb

mysql> select * from radgroupreply;
+----+-----------+-----------------------+----+--------------+
| id | groupname | attribute | op | value |
+----+-----------+-----------------------+----+--------------+
| 21 | 1mb | Mikrotik-Rate-Limit | == | 1024k/1024k |
| 22 | 1mb | Mikrotik-Address-List | := | 1mb |
+----+-----------+-----------------------+----+--------------+
2 rows in set (0.00 sec)

2# Adding username ZAIB in the Radius user group & assign him 1mb Group.

 

mysql> select * from radusergroup;
+----+----------+-----------+----------+
| id | username | groupname | priority |
+----+----------+-----------+----------+
| 5 | zaib | 1mb | 1 |
+----+----------+-----------+----------+
1 row in set (0.00 sec)

RADTEST:

Now we will test user via RADTEST cmd …


radtest zaib zaib localhost 1812 testing123

Result:

Sending Access-Request of id 130 to 127.0.0.1 port 1812
User-Name = "zaib"
User-Password = "zaib"
NAS-IP-Address = 101.11.11.254
NAS-Port = 1812
Message-Authenticator = 0x00000000000000000000000000000000
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=130, length=50

Mikrotik-Rate-Limit = "1024k/1024k"
Mikrotik-Address-List = "1mb"

Freeradius Debug Result:

Sending Access-Accept of id 156 to 127.0.0.1 port 34563
Mikrotik-Rate-Limit == "1024k/1024k"
Mikrotik-Address-List := "1mb"
Finished request 32.

Now try to connect user from your user device, & upon connection you will see new address list entry for this user IP ..

& its 1mb queues have been created as well


# Mikrotik Mangling & Queueing Section !

Now we will move towards Mikrotik related configuration for mangling & queue. in above steps we added DYNAMIC queue for test purposes, & as we will be using simple queues therefore we need to remove the dynamic queue, Do so , then we will move further …

  • Marking upload & download separately for 1mb user address list …

/ip firewall mangle
add action=mark-packet chain=forward comment="1mb users UPLOAD" new-packet-mark=1mb_users_up src-address-list=1mb passthrough=no
add action=mark-packet chain=forward comment="1mb users DOWNLOAD" dst-address-list=1mb new-packet-mark=1mb_users_down passthrough=no
  • Creating PCQ base 1mb download/upload limit variable …
/queue type
add kind=pcq name=download-1mb pcq-classifier=dst-address pcq-dst-address6-mask=64 pcq-rate=1024k pcq-src-address6-mask=64
add kind=pcq name=upload-1mb pcq-classifier=src-address pcq-dst-address6-mask=64 pcq-rate=1024k pcq-src-address6-mask=64
  • Creating PCQ base simple Queues to actual limit each user with 1mb download/upload …
/queue simple
add name="1mb user DOWN - PCQ" packet-marks=1mb_users_down queue=upload-1mb/download-1mb target=""
add name="1mb user UP - PCQ" packet-marks=1mb_users_up queue=128k-per-user/128k-per-user target=""

 

PC#1

1st- user - 1mb user test

PC#2

2nd pc 128K 1mb


TIPS:

How to remove all dynamic queues [can be used in script login section]

dynamic queue removal.PNG

/queue simple remove [find where dynamic]

Conclusion:

As we can see that address list have been created successfully, now we can manipulate it for our different tasks using marked packets for customized PCQ base queues for policy base queueing.

I will write more on it later if manage to get some spare time.


 

Regard’s
Syed Jahanzaib

 

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 billing system designed for mid-large scale ISP’s. It’s powered by Linux (Ubuntu) using Freeradius v 2.x.x & Dynamic & responsive GUI panel coded in PHP 7.x . 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.


Workflow ….

workflow.jpg

  • It’s a cash base system, (cards options not available)
  • on TOP, ISP have the Super Admin account , this account assign services with desired rates to the ADMIN account
  • ADMIN account cannot create users directly, Admin first creates reseller , assign him some services with desired rates, & transfer desired 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 screenshots …

pakrad1

dashboard view.png

free user

dealer panel view 2

dealer panel view my billing

dealer panel view

user usage history.PNG


 


Some major Features …

Exclusive.jpg

billing dept.png

  • Dynamic , responsive & appealing web design (advance responsive PHP designing)
  • Just one time license cost & you get Lifetime license (PHP code is encrypted)
  • 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 hankies pankies
  • Purely Designed as a Reseller base system, Example – Admin > Reseller > Dealer / Sub-dealer 
  • Profit calculation for view purposes
  • Free ID’s assignment for dealer for there Test purposes  *under process*
  • 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 *under process*
  • 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 / / User charges reports (User usage Graphs base option *under process*)
  • Record 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

  • http://pakrad.galaxy.net.pk:8181
    Credentials  ….
  • superadmin/superadmin
  • admin/admin
  • reseller/reseller
  • reseller2/reseller2
  • dealer/dealer
  • dealer2/dealer2
  • subdealer/subdealer

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,

1 * * * * 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 …

WordPress is not letting full code pasting here dueto [ ] limitation. therefore I have copied to my google drive. You can see it here.

https://drive.google.com/drive/folders/1BRIvT6lr9s66nzPP2tRsBV6G-0YA-Zkw


Results:

radius bw poller result.PNG


### ./bw.sh

- Script Start Time - 10:23:39

- INFO: mysql service is accessible. Proceeding further ... OK
- INFO: radius database exist. Proceeding further ... OK
- INFO: Total number of services with Dynamic bandwidth enabled = 11 / No.s ... OK
- INFO: Total number of users with Dynamic bandwidth enabled = 2300 / No.s ...
- INFO: Checking for Dynamic Bandwidth Policies and implement change on the fly for online users , if any ...
- DOWNGRADE ** - XXX1 / 1Mb_day_2mb_night is eligible for DOWNGRADE but NOT online, Ignoring ...
- DOWNGRADE ** - XXX2 / 1Mb_day_2mb_night / 172.16.12.52 / 80702383 is online on NAS 10.0.0.100, and eligible for DOWNGRADE to 1024k/1024k

- DOWNGRADE ** - XXX3 / 1Mb_day_2mb_night is eligible for DOWNGRADE but NOT online,Ignoring...
- DOWNGRADE ** - XXX4 / 1Mb_day_2mb_night / 172.16.6.209 / 80702352 is online on NAS 10.0.0.100, and eligible for DOWNGRADE to 1024k/1024k
- DOWNGRADE ** - XXX5 / 1Mb_day_2mb_night is eligible for DOWNGRADE but NOT online, Ignoring ...
- DOWNGRADE ** - XXX6 / 1Mb_day_2mb_night is eligible for DOWNGRADE but NOT online, Ignoring ...
- DOWNGRADE ** - XXX7 / 1Mb_day_2mb_night is eligible for DOWNGRADE but NOT online, Ignoring ...
^C
- Script Ends Here
- EXITING peacefully ...
- Script Start Time - 10:23:39
- Script End Time - 10:23:40
- Total Number of users packages changed on NAS for UP-GRADE = 0
- Total Number of users packages changed on NAS for DOWN-GRADE = 4

Bandwidth poller script ended @ 10:23:40 ... Powered by SYED.JAHANZAIB

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

 

Older Posts »

%d bloggers like this: