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

[Houman]

Hi Nathan,


I have done quite a bit of research and found this
<https://forum.mikrotik.com/viewtopic.php?t=105880&sid=8eec19d12eec19f401d5386ca5a59468#p527331>.
I have adjusted his solution to the latest version of Radius and was hoping
to run it through with you.

*vim /etc/freeradius/sites-enabled/default*

authorize {
        totalbytecounter{
                reject = 1
        }
        if(reject){
        update reply {
                Reply-Message := "ZAIB-RADIUS-REPLY - You have reached your
bandwidth limit"
        }
        reject
        }
    ...
}


*vim /etc/freeradius/mods-enabled/sqlcounter*

sqlcounter totalbytecounter {
        sql_module_instance = sql
        dialect = ${modules.sql.dialect}

        counter_name = My-Total-Limit
        check_name = My-Total-Limit
        reply_name = My-Total-Limit

        key = User-Name
        reset = never
        query = "SELECT ((SUM(AcctInputOctets)+SUM(AcctOutputOctets))) FROM
radacct WHERE UserName='%{%k}'"

}


Now in database:

INSERT INTO radcheck (username,attribute,op,VALUE) VALUES
('0799a559-1426-478a-b46a-a33f1198cd24','My-Total-Limit',':=','100000');

After restarting everything, I get these errors when running freeradius -X

/etc/freeradius/mods-enabled/sqlcounter[41]: Counter attribute
My-Total-Limit MUST be integer64
/etc/freeradius/mods-enabled/sqlcounter[41]: Instantiation failed for
module "totalbytecounter"

How do know why it doesn't accept the counter?

Many Thanks,
Houman


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

>
> > On 29/12/2017, at 9:38 PM, Houman <houmie at gmail.com> wrote:
> >
> > 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 <
> https://wiki.strongswan.org/projects/strongswan/wiki/EAPRAdius>
>
> No problem, and great!
>
> > 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?
>
> It is an rlm_sqlcounter instance, which you can call from your RADIUS
> config in an authorize section. You’d call it something more meaningful to
> your environment - use that only as inspiration.
>
> > 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?
>
> I may have led you astray here, it’s a few years since I’ve used
> sqlcounter. It can only be called in the authorize section (the
> documentation says it can be called in accounting, but that does not match
> the code). My intent was that you would call it in the accounting section
> for each Interim Update, and if it was zero (or close to zero) you could
> CoA to disconnect the user. That won’t work, as this module can’t be called
> in accounting.
>
> > 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?
>
> Yes - but you’d need to calculate it yourself with the sqlcounter
> implementation as well. I had a look closer, I think those chillispot
> examples are quite flawed when calculating data usage - they are fine for
> time usage, but for data usage they use seconds as part of the math, which
> is obviously bogus.
>
> Logic:
> 1) Look up the user somewhere, store the max permitted usage in
> Tmp-Integer-1 - or set it statically if the limit is the same for all users
> 2) Look up the radacct table and get the usage in the current time period.
> Store that in Tmp-Integer-2
> 3) If Tmp-Integer-2 > Tmp-Integer-1, CoA the user
>
> As with all these examples, you will need to look in to corner cases where
> the logic will fail. For example, by default, FreeRADIUS will UPDATE the
> radacct table, rather than INSERT. This means if you have long running
> sessions (i.e. longer than 1 day, or over a day boundary), you won’t be
> able to tell if the data usage from SQL queries is from today, or from
> yesterday, or from some other day in the past. This would make users
> disconnect ever time their daily limit was reached, even if their usage was
> spread over several days.
>
> Couple of ways to solve that, off the top of my head:
> 1) INSERT always rather than UPDATE - you’ll need to make sure you clean
> up old data, as this is a row for every start/update/stop. You also will
> have trouble with logic in other areas potentially.
> 2) Have the SQL UPDATE include an additional column for Acct-Output-Octets
> which is updated ONLY in the first update of each day, and use this to
> calculate usage today.
> 3) Use a trigger to update another table on UPDATE/INSERT, with
> Acct-Output-Octets for the start of the current day. This is similar to (2)
> but using a trigger and a second table.
>
> There are a lot of ways to solve this problem - how exactly really depends
> on your skills/environment I’d say.
>
> --
> Nathan Ward
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/
> list/users.html
>