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

[Houman]

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-disconnect-a-user-after-reaching-the-monthly-quota

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 at daork.net>
wrote:

>
>
> > 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
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/
> list/users.html
>