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

[Alan DeKok]

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.