Enable `radpostauth` table for Radius Manager

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

1. Increase the REPLY column length to accommodate longer reply messages display properly
2. 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