prepaid volume quota with cisco nas

[riki]

Hello,

i am working with Cisco NAS, it supports enforcing volume based quota.
On first authorizations, i am expected to send QV value. When user runs
out of quota, NAS wants to reauthorize. No problem on NAS side [1].

I am on freeradius-3.0.4 from Centos 7 repositories. Accounting is on
PostgresSQL backend, acctinputoctets and acctoutputoctets are updated
every 5 minutes.

I can use following query for sqlcounter check on last session end
expire it hourly:

query = "\
        SELECT SUM(AcctInputOctets) + SUM(AcctOutputOctets) FROM radacct \
        WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 +
AcctSessionTime \
        > '%b'"

I want to limit session volume for 5MB per every hour (if achieved in 20
minutes, do not authorize for next 40 minutes). Counters are cumulative
for session so i have identified so far these options:

1. reset session when quota reached with CoA update/disconnect, it will
causes new session every hour if quota is depleted and my query works.

2. i can use a trigger for update on radacct table which will insert
delta of old and new value with timestamp to kind of "stats" table and
use this one for sqlcounter query, counting data for last 12 5-minute
intervals or so.

3. modify radacct table/update query to do the same as point 2.

If you have some other ideas, please let me know.

>From the future upgrade compatibility point of view, the 2 looks best.


[1]
http://www.cisco.com/c/en/us/td/docs/ios-xml/ios/isg/configuration/xe-3s/asr1000/isg-xe-3s-asr1000-book/isg-prepaid-bill.html#GUID-56577557-AC74-42A8-81F6-B61862510C20


Thanks for feedback

r.