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

[Houman]

Dear Nathan,

Thank you very much for the comprehensive answer. I must admit, I'm fairly
new to Freeradius.
FYI my NAS is StrongSwan and it seems to be supporting CoA:
https://wiki.strongswan.org/projects/strongswan/wiki/EAPRAdius

I'm happy to use rlm_sqlcounter, if it makes it easier. You are right in
saying I'm using sql accounting anyway. I just double checked and I can see
Octlets inside Radacc table. So that's good news.

But looking at the example you posted, it is a bit confusing to me.

e.g. if I'm looking at the function sqlcounter chillispot_max_bytes_monthly
{ ... }

Is the function chillispot_max_bytes_monthly called from some other place?
How do I set this up?

You mentioned to look at the Reply-Name and set it to something
recognisable for later processing. In this case above, it is set to
ChilliSpot-Max-Total-Octets.
But I don't quite understand what you mean by checking if it's set? I
suppose the function chillispot_max_bytes_monthly above would somehow be
called for each user and as soon as the condition of max_bytes_month is
met, then it should trigger the COA, correct? Do you happen to have an
example for this case, which I could take on and study?

Regarding your third and simplest solution, which is using SQL xlat to return
a boolean flag if the user is over the limit. Yes, that is certainly
possible. I suppose I have to use the Octlets fields to calculate it myself
in xlat? Do you have any example or documentation how to do that?

Many Thanks,
Houman



On 28 December 2017 at 09:22, Nathan Ward <lists+freeradius at daork.net>
wrote:

>
>
> > 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
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/
> list/users.html