Hey, Rafael!

The Key to Traffic-based Tickets in RADIUS

Introduction

Rafael Fonseca

Rafael Fonseca


snippet sql

The Key to Traffic-based Tickets in RADIUS

Posted by Rafael Fonseca on .
Featured

snippet sql

The Key to Traffic-based Tickets in RADIUS

Posted by Rafael Fonseca on .

I spent ages looking for information on how to limit user access based on traffic used through a captive portal using pfSense, and couldn't find somewhere with an easy to use SQL command that was generic enough to use with daloRADIUS.

Well, in case you were wandering (like me) around the ‘net, here it is:

SELECT IFNULL(SUM(AcctInputOctets + AcctOutputOctets),0) AS AcctTotalOctets FROM radacct WHERE username = '%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b';

This, in essence, is the SQL you need to use for the traffic-based counter that you need to setup in FreeRADIUS for this to work. Then add a check for an attribute like Max-Total-Octets and add that attribute with the amount you want to limit (in bytes) to the users who should be limited, and voilà!

You can use a reset value of never and just keep re-filling user's session traffic when they run out. Set a reply-message that tells them they've run out of data, though, or it will just look like they got their password wrong.

Rafael Fonseca

Rafael Fonseca

View Comments...