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
Hi Thanks for your useful information on dma authentication log, i am facing issue where if user is dialing with wrong password i am getting only “Access-Accept -” in radius reply table please suggest where we can customize the reply msg.
LikeLiked by 2 people
Comment by Rajkumar Lodh — August 23, 2017 @ 5:41 PM
Hi Thanks for your useful information on dma authentication log, i am facing issue where if user is dialing with wrong password i am getting only “Access-Reject -” in radius reply table please suggest where we can customize the reply msg.
LikeLike
Comment by Rajkumar Lodh — August 23, 2017 @ 5:54 PM
thank you very much sir its working for me
LikeLike
Comment by Ramanjineyulu — August 31, 2017 @ 10:45 AM
when user request comes first time the user is logged in successfully but in radpostauth table shown request reject.
LikeLike
Comment by Akash Patel — January 22, 2019 @ 11:50 PM
I am facing a weird issue with Radius Manager 4.1.6. When a user is disabled and trying to login, getting error Invalid Service Reference. Could you please give a hint, what could be possible cause?
LikeLike
Comment by I P Meel — August 23, 2019 @ 10:19 AM
If used is disabled , RM will give you something like “Invalid Service Reference”
LikeLike
Comment by Syed Jahanzaib / Pinochio~:) — August 23, 2019 @ 10:27 AM
Actually, when a user is disabled, it should give message Account is Disabled!
LikeLike
Comment by I P Meel — August 23, 2019 @ 12:36 PM
unfortunately this is the way it works in this version
LikeLike
Comment by Syed Jahanzaib / Pinochio~:) — August 23, 2019 @ 12:52 PM
I have 2 server with rm4.1, The server with centOS user is getting reply “Account is Disabled” but on Ubuntu getting “Invalid Service Reference”
LikeLike
Comment by I P Meel — August 23, 2019 @ 1:53 PM
hi. if i want to get ip-address of client on freeradius . How can i do ?
i try to use “Framed-IP-Adress”. It’s not work.
LikeLike
Comment by Zha' Max — December 11, 2020 @ 10:35 AM