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

[Nathan Ward]

> On 30/12/2017, at 6:42 AM, Houman <houmie at 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