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

[Nathan Ward]

> 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