How to use sqlcounter to disconnect a user after reaching the daily quota?
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. I can see the module is loaded when I run it as freeradius -X. But how do I set it up to allow each user only 3 GB of data usage within a month? Or even for testing purposes 500KB on daily basis? When the month or the day has passed, then the user should be allowed access again. Which config files do I have to edit? Many Thanks for your advice, Houman
On 28/12/2017, at 9:28 PM, Houman <houmie@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... <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
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@daork.net> wrote:
On 28/12/2017, at 9:28 PM, Houman <houmie@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
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
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
On 30/12/2017, at 6:42 AM, Houman <houmie@gmail.com> wrote:
Hi Nathan,
I have done quite a bit of research and found this <https://forum.mikrotik.com/viewtopic.php?t=105880&sid=8eec19d12eec19f401d538... <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?
What have you defined “My-Total-Limit” to be? Mikrotik-Total-Limit is a Mikrotik specific attribute, and has a predefined impact as to how the Mikrotik NAS will behave (i.e. the NAS will disconnect the user once the limit is reached). I have never heard of My-Total-Limit before, and I’m wondering if you’ve just replaced “Mikrotik” with “My”? Attributes must be defined in a dictionary if they are to be used - and you can’t just make up your own and expect them to do things. This solution won’t work for what you want, anyway - it will only give you a total limit, not a limit in a specific time period (i.e. day), but I don’t think it’s a good idea to tell you much more than that. 3 reasons: 1) It looks like you’re lying to find a solution you can copy and paste - I don’t think you’ll be able to do that. RADIUS is almost always going to differ between NAS vendors, versions, etc. etc. as to what it can do, and how you use those features to achieve different outcomes. When working with RADIUS, you’re very often going to need to build up new solutions rather than copy and paste. 2) I talked about different limit timeframes and logic problems in my previous email, and you don’t seem to have taken it in. 3) This solution requires that the NAS disconnect the user when the limit is reached, and that the NAS maintain the limit within a session. That is not supported by your NAS, and does not work well for non-time based limits (i.e. data limits) when you only care about usage within a particular time period. Give a man a fish and all that - you’re dangerously close to asking me to implement this whole solution for you without you needing any knowledge, which I’m not prepared to do. I’d suggest spending some time playing with this stuff and figuring out how it works, and then building your solution based on that knowledge. One suggestion of how to do that would be to think about what information the NAS is sending in the accounting updates (look at some RADIUS packet captures in Wireshark, perhaps) and think about what FreeRADIUS stores right now, and then the logic you’d ultimately like FreeRADIUS to go through in order to get the outcome you want. What information does it have in each request? What information does it have from previous requests that it can call on? Is that information enough to achieve what you want? Figure out what you need first, then figure out how to get there, and if there’s an existing solution that fits your needs - it will be much easier for you to map your detailed requirements to a solution, than trying to make 30 different solutions function then see if they meet your needs by trial and error. If you have more specific questions though, I’m happy to help further. -- Nathan Ward
Hello Nathan, I'm sorry if I have given you the wrong impression. I have indeed misunderstood Mikrotik specific attribute and I shouldn't have freely changed that. I have backed off from that approach. In the meanwhile, I got the book FreeRadius from Dirk Van Der Walt, but it's quite old and uses the old syntax. But most of all it only explains the time session counters. Not data usage. I keep researching to learn more about it. Your tip with Wireshark is a good one, to sniff the connection and see what information the NAS is sending. The thing is I'm already getting the Octets and am able to count the data usage. You were right about the lack of date range in my previous attempt. I have improved the query and extended the where-clause by month and year. Here it is: https://stackoverflow.com/questions/48028701/how-to-use-sqlcounter-to-discon... When I run the query manually in the database it is giving me a higher number than the limit specified in the radcheck. And yet it still allows me to connect, even though I was expecting a Session-Timeout to be raised. I remember you mentioned using CoA instead. But I'm doing baby steps here. :-) I could, of course, code a solution in Python by executing the query above and see if it has reached the set limit. If that's the case I could just delete the user from Radcheck and then find a way to timeout the session in my NAS. I was hoping for a cleaner out-of-the-box solution. I didn't quite expect not to find anyone with a similar problem. I don't want to take much of your time, Happy New Year, Houman On 30 December 2017 at 12:34, Nathan Ward <lists+freeradius@daork.net> wrote:
On 30/12/2017, at 6:42 AM, Houman <houmie@gmail.com> wrote:
Hi Nathan,
I have done quite a bit of research and found this <https://forum.mikrotik.com/viewtopic.php?t=105880&sid= 8eec19d12eec19f401d5386ca5a59468#p527331 <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?
What have you defined “My-Total-Limit” to be? Mikrotik-Total-Limit is a Mikrotik specific attribute, and has a predefined impact as to how the Mikrotik NAS will behave (i.e. the NAS will disconnect the user once the limit is reached). I have never heard of My-Total-Limit before, and I’m wondering if you’ve just replaced “Mikrotik” with “My”? Attributes must be defined in a dictionary if they are to be used - and you can’t just make up your own and expect them to do things.
This solution won’t work for what you want, anyway - it will only give you a total limit, not a limit in a specific time period (i.e. day), but I don’t think it’s a good idea to tell you much more than that. 3 reasons: 1) It looks like you’re lying to find a solution you can copy and paste - I don’t think you’ll be able to do that. RADIUS is almost always going to differ between NAS vendors, versions, etc. etc. as to what it can do, and how you use those features to achieve different outcomes. When working with RADIUS, you’re very often going to need to build up new solutions rather than copy and paste. 2) I talked about different limit timeframes and logic problems in my previous email, and you don’t seem to have taken it in. 3) This solution requires that the NAS disconnect the user when the limit is reached, and that the NAS maintain the limit within a session. That is not supported by your NAS, and does not work well for non-time based limits (i.e. data limits) when you only care about usage within a particular time period.
Give a man a fish and all that - you’re dangerously close to asking me to implement this whole solution for you without you needing any knowledge, which I’m not prepared to do. I’d suggest spending some time playing with this stuff and figuring out how it works, and then building your solution based on that knowledge.
One suggestion of how to do that would be to think about what information the NAS is sending in the accounting updates (look at some RADIUS packet captures in Wireshark, perhaps) and think about what FreeRADIUS stores right now, and then the logic you’d ultimately like FreeRADIUS to go through in order to get the outcome you want. What information does it have in each request? What information does it have from previous requests that it can call on? Is that information enough to achieve what you want?
Figure out what you need first, then figure out how to get there, and if there’s an existing solution that fits your needs - it will be much easier for you to map your detailed requirements to a solution, than trying to make 30 different solutions function then see if they meet your needs by trial and error.
If you have more specific questions though, I’m happy to help further.
-- Nathan Ward
- List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html
On Dec 30, 2017, at 8:41 AM, Houman <houmie@gmail.com> wrote:
In the meanwhile, I got the book FreeRadius from Dirk Van Der Walt, but it's quite old and uses the old syntax. But most of all it only explains the time session counters. Not data usage. I keep researching to learn more about it.
The counters aren't magic. They are documented, and they work as documented. If you want time-based counters, use rlm_sqlcounter. If you want bandwidth-based counters, use something else.
Your tip with Wireshark is a good one, to sniff the connection and see what information the NAS is sending. The thing is I'm already getting the Octets and am able to count the data usage. You were right about the lack of date range in my previous attempt. I have improved the query and extended the where-clause by month and year. Here it is: https://stackoverflow.com/questions/48028701/how-to-use-sqlcounter-to-discon...
Why stack overflow? Why not this list? When you make it harder for us to help you, we're less inclined to help you.
When I run the query manually in the database it is giving me a higher number than the limit specified in the radcheck. And yet it still allows me to connect, even though I was expecting a Session-Timeout to be raised.
The debug output should say why. And it's likely because sqlcounter does time-based counting. It doesn't do quota-based accounting. The two are entirely separate. For quota-based accounting, you can just run an SQL query manually. Get the current bandwidth used from SQL, via an SQL query. Then, use radcheck to check it... In raddb/dictionary, add an attribute: ATTRIBUTE My-Daily-Usage integer64 3001 authorize { ... # get the users current usage from SQL update request { My-Daily-Usage = "%{sql:SELECT....}" } ... sql ... } And in radcheck, put "My-Daily-Usage < 1000000" If the usage is under that, they will be allowed in. If it's over that, they will be rejected. It won't automatically limit the usage, because that's unfortunately not part of RADIUS. But the above changes *should* work, and should get you a step forward. Alan DeKok.
Alan, It works! Thank you so much for the code snippets. I wasn't that far off after all it seems. Btw I have inserted the integer64 at the end of the dictionary file. I think this is the right way judging by the existing samples in the file. ATTRIBUTE My-Daily-Usage 3001 integer64 I'm happy with the solution to disallow the user entirely once the daily-usage has been reached. My only concern is if there is an easy way to convey a message back to the client, to inform the user why the VPN connection is failing? Maybe something like this? update reply { Reply-Message := "You have reached your bandwidth limit" } On 30 December 2017 at 14:01, Alan DeKok <aland@deployingradius.com> wrote:
On Dec 30, 2017, at 8:41 AM, Houman <houmie@gmail.com> wrote:
In the meanwhile, I got the book FreeRadius from Dirk Van Der Walt, but it's quite old and uses the old syntax. But most of all it only explains the time session counters. Not data usage. I keep researching to learn more about it.
The counters aren't magic. They are documented, and they work as documented.
If you want time-based counters, use rlm_sqlcounter. If you want bandwidth-based counters, use something else.
Your tip with Wireshark is a good one, to sniff the connection and see what information the NAS is sending. The thing is I'm already getting the Octets and am able to count the data usage. You were right about the lack of date range in my previous attempt. I have improved the query and extended the where-clause by month and year. Here it is: https://stackoverflow.com/questions/48028701/how-to-use- sqlcounter-to-disconnect-a-user-after-reaching-the-monthly-quota
Why stack overflow? Why not this list?
When you make it harder for us to help you, we're less inclined to help you.
When I run the query manually in the database it is giving me a higher number than the limit specified in the radcheck. And yet it still allows me to connect, even though I was expecting a Session-Timeout to be raised.
The debug output should say why. And it's likely because sqlcounter does time-based counting. It doesn't do quota-based accounting.
The two are entirely separate.
For quota-based accounting, you can just run an SQL query manually. Get the current bandwidth used from SQL, via an SQL query. Then, use radcheck to check it...
In raddb/dictionary, add an attribute:
ATTRIBUTE My-Daily-Usage integer64 3001
authorize { ...
# get the users current usage from SQL update request { My-Daily-Usage = "%{sql:SELECT....}" } ... sql ... }
And in radcheck, put "My-Daily-Usage < 1000000"
If the usage is under that, they will be allowed in. If it's over that, they will be rejected.
It won't automatically limit the usage, because that's unfortunately not part of RADIUS. But the above changes *should* work, and should get you a step forward.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html
On 31/12/2017, at 10:31 AM, Houman <houmie@gmail.com> wrote:
Alan,
It works! Thank you so much for the code snippets. I wasn't that far off after all it seems. Btw I have inserted the integer64 at the end of the dictionary file. I think this is the right way judging by the existing samples in the file.
ATTRIBUTE My-Daily-Usage 3001 integer64
I'm happy with the solution to disallow the user entirely once the daily-usage has been reached. My only concern is if there is an easy way to convey a message back to the client, to inform the user why the VPN connection is failing?
This does not achieve what you want, by itself. 1) Sessions that span multiple days will break your logic. In your stack overflow post you’re actually looking at usage for the whole month, not a day. 2) A user will not be disconnected when they reach their limit, as your NAS doesn’t support that. If what you’ve got here is fine for you then that’s fine I guess - but it's not something I’d put in to production.
Maybe something like this?
update reply { Reply-Message := "You have reached your bandwidth limit" }
That will set the Reply-Message attribute in the reply. No idea if your NAS will do what you want with that, though. -- Nathan Ward
Hi Alan, I have a follow-up question to this solution you had proposed. I was hoping to use radgroupcheck instead of radcheck table. So I inserted the limitation in there: INSERT INTO `radgroupcheck` (`id`, `groupname`, `attribute`, `op`, `value`) VALUES (1, 'group-1', 'My-Daily-Usage', '<', '1000000'); And inserted added the user to the group: INSERT INTO `radusergroup` (`username`, `groupname`, `priority`) VALUES ('my-user', 'group-1', 10); I also updated /etc/freeradius/mods-available/sql to read groups. sql { read_groups = yes ... } Two observations: 1) It doesn't work. 2) When I login with NAS, the new entry in radacct doesn't include the groupname. What do I have to do? Many Thanks, On 30 December 2017 at 14:01, Alan DeKok <aland@deployingradius.com> wrote:
On Dec 30, 2017, at 8:41 AM, Houman <houmie@gmail.com> wrote:
In the meanwhile, I got the book FreeRadius from Dirk Van Der Walt, but it's quite old and uses the old syntax. But most of all it only explains the time session counters. Not data usage. I keep researching to learn more about it.
The counters aren't magic. They are documented, and they work as documented.
If you want time-based counters, use rlm_sqlcounter. If you want bandwidth-based counters, use something else.
Your tip with Wireshark is a good one, to sniff the connection and see what information the NAS is sending. The thing is I'm already getting the Octets and am able to count the data usage. You were right about the lack of date range in my previous attempt. I have improved the query and extended the where-clause by month and year. Here it is: https://stackoverflow.com/questions/48028701/how-to-use- sqlcounter-to-disconnect-a-user-after-reaching-the-monthly-quota
Why stack overflow? Why not this list?
When you make it harder for us to help you, we're less inclined to help you.
When I run the query manually in the database it is giving me a higher number than the limit specified in the radcheck. And yet it still allows me to connect, even though I was expecting a Session-Timeout to be raised.
The debug output should say why. And it's likely because sqlcounter does time-based counting. It doesn't do quota-based accounting.
The two are entirely separate.
For quota-based accounting, you can just run an SQL query manually. Get the current bandwidth used from SQL, via an SQL query. Then, use radcheck to check it...
In raddb/dictionary, add an attribute:
ATTRIBUTE My-Daily-Usage integer64 3001
authorize { ...
# get the users current usage from SQL update request { My-Daily-Usage = "%{sql:SELECT....}" } ... sql ... }
And in radcheck, put "My-Daily-Usage < 1000000"
If the usage is under that, they will be allowed in. If it's over that, they will be rejected.
It won't automatically limit the usage, because that's unfortunately not part of RADIUS. But the above changes *should* work, and should get you a step forward.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html
On Jan 7, 2018, at 1:55 PM, Houman <houmie@gmail.com> wrote:
I have a follow-up question to this solution you had proposed. I was hoping to use radgroupcheck instead of radcheck table.
So I inserted the limitation in there: INSERT INTO `radgroupcheck` (`id`, `groupname`, `attribute`, `op`, `value`) VALUES (1, 'group-1', 'My-Daily-Usage', '<', '1000000');
And inserted added the user to the group: INSERT INTO `radusergroup` (`username`, `groupname`, `priority`) VALUES ('my-user', 'group-1', 10);
I also updated /etc/freeradius/mods-available/sql to read groups. sql { read_groups = yes ... }
Two observations: 1) It doesn't work.
See the FAQ for "it doesn't work".
2) When I login with NAS, the new entry in radacct doesn't include the groupname. What do I have to do?
Why would you expect the group name to be in the radacct table? All of the documentation describes how the SQL module works. None of it says that the group name is stored in radacct. Alan DeKok.
Alan, 1) When I said it doesn't work, I meant I tried to replicate your previous solution by utilising radgroupcheck instead of radcheck. And I have explained the steps I have taken to make this possible. Would you say I have missed any step? 2) What is the purpose of groupname field in radacct table then? I was expecting freeradius to check that the user belongs to that group in radusergroup and then would automatically fill that field as well in radacct next to everything it already does. On 7 January 2018 at 19:17, Alan DeKok <aland@deployingradius.com> wrote:
On Jan 7, 2018, at 1:55 PM, Houman <houmie@gmail.com> wrote:
I have a follow-up question to this solution you had proposed. I was hoping to use radgroupcheck instead of radcheck table.
So I inserted the limitation in there: INSERT INTO `radgroupcheck` (`id`, `groupname`, `attribute`, `op`, `value`) VALUES (1, 'group-1', 'My-Daily-Usage', '<', '1000000');
And inserted added the user to the group: INSERT INTO `radusergroup` (`username`, `groupname`, `priority`) VALUES ('my-user', 'group-1', 10);
I also updated /etc/freeradius/mods-available/sql to read groups. sql { read_groups = yes ... }
Two observations: 1) It doesn't work.
See the FAQ for "it doesn't work".
2) When I login with NAS, the new entry in radacct doesn't include the groupname. What do I have to do?
Why would you expect the group name to be in the radacct table?
All of the documentation describes how the SQL module works. None of it says that the group name is stored in radacct.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list /users.html
On Jan 7, 2018, at 2:29 PM, Houman <houmie@gmail.com> wrote:
1) When I said it doesn't work, I meant I tried to replicate your previous solution by utilising radgroupcheck instead of radcheck. And I have explained the steps I have taken to make this possible. Would you say I have missed any step?
What does the FAQ say about "it does't work"? If only there was some "debug output" that told you what the server was doing...
2) What is the purpose of groupname field in radacct table then? I was expecting freeradius to check that the user belongs to that group in radusergroup and then would automatically fill that field as well in radacct next to everything it already does.
Does the SQL module documentation say that happens? Does the debug log show that happens? Do the default queries have "group name" in them? Honestly, this isn't magic. The server doesn't magically do things. It does exactly what it's configured to do. It does what it's documented to do. It shows what it does in the debug output. All you need to do is to read the documentation, and to read the debug output. If you're not doing that, then you won't get anything done. Alan DeKok.
Hi Alan, I have attached the debug file from freeradius -x. I have also been looking extensively at the documentation. I can see the group is showing up in the queries. My-Daily-Usage is also correctly calculated to 538215123, which is above the set limit of 1000000, so the connection should fail. What I can't see is the limit of 1000000 in the radgroupcheck table showing up anywhere in the log. I'm quite stuck on this, Looking at the log I'm not sure what I have missed. Would you be able to throw me a hint, please? Many Thanks, On 8 January 2018 at 01:43, Alan DeKok <aland@deployingradius.com> wrote:
On Jan 7, 2018, at 2:29 PM, Houman <houmie@gmail.com> wrote:
1) When I said it doesn't work, I meant I tried to replicate your previous solution by utilising radgroupcheck instead of radcheck. And I have explained the steps I have taken to make this possible. Would you say I have missed any step?
What does the FAQ say about "it does't work"?
If only there was some "debug output" that told you what the server was doing...
2) What is the purpose of groupname field in radacct table then? I was expecting freeradius to check that the user belongs to that group in radusergroup and then would automatically fill that field as well in radacct next to everything it already does.
Does the SQL module documentation say that happens?
Does the debug log show that happens?
Do the default queries have "group name" in them?
Honestly, this isn't magic. The server doesn't magically do things. It does exactly what it's configured to do. It does what it's documented to do. It shows what it does in the debug output.
All you need to do is to read the documentation, and to read the debug output. If you're not doing that, then you won't get anything done.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html
participants (3)
-
Alan DeKok -
Houman -
Nathan Ward