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

[Houman]

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 at deployingradius.com> wrote:

> On Dec 30, 2017, at 8:41 AM, Houman <houmie at 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
>