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@daork.net> wrote:
On 29/12/2017, at 9:38 PM, Houman <houmie@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