How to use sqlcounter to disconnect a user after reaching the daily quota?

[Nathan Ward]

> On 28/12/2017, at 9:28 PM, Houman <houmie at gmail.com> wrote:
> 
> Hello & Merry Christmas.
> 
> 
> I have managed to enable accounting after all and it seems that the module
> sqlcounter is loaded too.
> 
> Looking at the documentation here
> <https://freeradius.org/radiusd/man/rlm_counter.txt>
> 
> The  rlm_counter  module  provides  a general framework to measure
> total data transferred in a given period. This is very useful in a
> 'Prepaid Service' situation, where a user has paid for a  finite
> amount  of  usage and should not be allowed to use more than that
> service.
> 
> This is perfect as I need exactly that.
> 
> It seems I have to change count_attribute to data usage in order to measure
> the usage instead of session time, but I'm not quite sure where to find the
> accepted values.
> 
> Nonetheless, I'm very confused how I'm supposed to use this module.


Hi,

rlm_counter is intended for Acct-Session-Time, which is only sent in an accounting Stop packet. It increments the counter by the absolute value - i.e. it is not intended for being called in accounting Update packets.
The intent is that on first login, Session-Timeout is set in the Access-Accept to the maximum for that period, then on subsequent logins Session-Timeout is set in the Access-Accept for remaining time in that period.

This requires that the NAS disconnect the session when Session-Timeout is reached.


You could potentially use it for data, however, you would need to either:
1) only call the module on accounting Stop packets, after calculating Acct-Output-Gigawords << 32 | Acct-Output-Octets in to a new attribute
2) calculate data used per accounting Update packet by querying some database, store that in a new attribute, and call the module with that data

Your NAS will probably need support for some sort of attribute that works similar to Session-Timeout, but for octets. Some NASes have things like this.


I think you are probably better off using the rlm_sqlcounter module for your use case, as you are already storing the accounting data in SQL. Here are some examples of queries for calculating the usage in a period (found from Google, so, YMMV - use this as inspiration only):
https://github.com/GraseHotspot/grase-conf-freeradius/blob/master/freeradius/perl_modules/sqlcounter.conf <https://github.com/GraseHotspot/grase-conf-freeradius/blob/master/freeradius/perl_modules/sqlcounter.conf>

You might want to set Reply-Name to something that you then check for after calling the sqlcounter module, and if it is set, use CoA to disconnect the subscriber (or limit their speed or something).

You might also consider implementing the same functionality as an SQL xlat, which would in my view be simpler. You could construct a fairly simple query which looks at a couple of tables and returns a 1/0 for whether the user is over their limit for the time period, and then execute the CoA. You could simplify it by executing two queries - one which loads the per-user max per period, the other which loads the current usage per period, and then do the comparison in unlang. You might want to test the performance of both options and see if there is much difference, and if there is, if the difference impacts you materially.

Of course, this requires your NAS support CoA.


Good luck! :-)

--
Nathan Ward