Following post was tested with DMA,
For better approach you may want to see following …
Freeradius External Auth BASH Script & RADPOSTUATH logging with customized reply message !
Freeradius is a well known billing system which is commonly used by ISP’s worldwide due to its reliability, highly customizable and versatility. Many 3rd party vendors have made some good GUI fronted to manage the FR back-end engine.
It also sues mysql to facilitate logging of various users details. One of the table called radpsotauth which can hold information about users successfull/failed login attempts. Using this table, we can compliment our own GUI or 3rd party fronted (for easy management of freeradius engine) like DMASoftlab radius manager Authentication Log section so that admin / support can see users authentication logs. With some modification we can add useful information for quick troubleshooting example why user denied the authentication request, either invalid mac, wrong password, or account expired.
Note to *.*
- You can add UNLAG query as well to apply IF statement, and add REPLY result according to your requirements.
- This post was written for some reference purposes & will be updated as per request.
- This guide is incomplete post. But it can be used as a reference as well for future retrieval
Example:
As we can see in above image that in Radius Reply column, it clearly showing why user is denied like invalid mac address , account expires, invalid service reference (when user account id disabled in dma).
To enable these features we have to perform few steps as following …
RADDB DEFAULT CONFIG
First we need to edit the default sites config file for raddb.
Edit following file
nano /usr/local/etc/raddb/sites-available/default
Now search for “post-auth {” section
To make it simple and copy-paste format, Use following
post-auth {
sqlippool
reply_log
exec
sql
Post-Auth-Type REJECT {
sql
attr_filter.access_reject
}
}
As showed in the image below …
Save & Exit.
#DIALUP.CONF Section
Edit the post-auth section in /usr/local/etc/raddb/sql/mysql/dialup.conf file
At the end of this file you will see “postauth_query” section. You need to change it
Old post-auth query
After changing
or copy paste text as below…
#######################################################################
# Authentication Logging Queries
#######################################################################
# postauth_query - Insert some info after authentication
#######################################################################
postauth_query = "INSERT INTO ${postauth_table} \
(username, pass, reply, authdate, nasipaddress, mac) \
VALUES ( \
'%{User-Name}', \
'%{%{User-Password}:-%{Chap-Password}}', \
'%{reply:Packet-Type} - %{reply:Reply-Message}', \
'%S', \
'%{NAS-IP-Address}', \
'%{Calling-Station-Id}')"
Save & Exit.
#Alter the RADPOSTAUTH table using mysql cmd …
Using mysql cmd, we will perform 2 functions
- Increase the REPLY column length to accommodate longer reply messages display properly
- Add the MAC Address column so we can detect the calling user device mac address
mysql -uroot -pPASSWORD
use radius;
ALTER TABLE radpostauth MODIFY `reply` VARCHAR( 100 );
ALTER TABLE radpostauth ADD COLUMN mac TEXT;
exit
Restart the RADIUSD service
service radiusd restart
using CMD, you can now see the authentication log table
mysql -uroot -pPASSWORD -e “use radius; select * from radpostauth;”
& you will see the information
1 JOHN 123456 Access-Reject - The account has expired=21 2017-08-15 [14:14:05 192.168.1.1 10:FE:ED:33:BD:AX
Notes:
- You can modify the messages appearing in the different columns, you can add your own customized columns as well like called station, or others
- You can add UNLAG query as well to apply IF statement, and add REPLY result according to your requirements.
- It can chew up your disk space, so try to make text shortest possible, like error codes only, not the whole text.
- scheduled a cron job so that it can empty the table on weekly/monthly basis so that it may not swallow disk space plus performance should remain optimal.
- If used in heavy environment it can put considerable strain on your system resources by putting large quantity of mysql INSERT queries into the table.
Regard’s
Syed Jahanzaib
Syed Jahanzaib - Personal Blog to Share Knowledge ! 