IPv6 accounting RADIUS SQL schema?

[Nathan Ward]

> On 20/08/2018, at 7:28 AM, Alan DeKok <aland at deployingradius.com> wrote:
> 
> On Aug 19, 2018, at 12:54 PM, Michael Ducharme <mducharme at gmail.com> wrote:
>> It is unfortunately extremely common for billing systems to integrate with FreeRADIUS/MySQL via direct database access. Ours uses the accounting data to determine the IP allocation history for the customer or the previous history for an IP, and staff can view the history records through the billing software itself. The billing system also continually monitors the radacct table for new records -- instead of sending an access-reject to a customer who is behind on billing, they are sent an access-accept. A few seconds later, after the billing system has found a new record in the radacct table that shows the customer is online on that IP, the billing system logs into the NAS via its API and adds that IP to an ACL which blocks internet access and forces the customer into a walled garden to make a payment. If the customer disconnects, the billing system logs back into the NAS and removes the IP from the ACL. It is complex but generally works pretty decently,
> 
>  My $0.02 is that this sounds like a good use-case for a smart DHCP server.  :)
> 
>  i.e. have FreeRADIUS handle DHCP, too.  On initial request, it can check the MAC address in radacct for the username who last logged in.  Then, check their billing history.  If their account is in arrears, give them an IP from a walled garden.

There’s about 50 different ways to solve this problem, yeah.

Juniper BNGs support a captive portal which can rewrite HTTP requests and/or DNAT, on a per customer basis, with parameters from RADIUS.
You can put customers in “walled garden” VRFs, again, through RADIUS.
If you run DHCP pools local to your BNG, you can specify a walled garden pool in Access-Accept.

Some solutions are more attractive than others in they let you trigger this through CoA or some other mechanism that doesn’t involve taking the customer offline (which, as you can imagine with low quality CPE etc. etc. can be an issue). Which option is supported where differs between vendors and models - i.e. some vendors let you change firewall filters, VRFs, etc. in CoA, some don’t, etc.

My view - if my billing system is so tightly integrated that it requires direct database access to my RADIUS system, I use another billing system - it doesn’t scale like that.
That doesn’t help Michael of course, but, may help others interested in this topic.

--
Nathan Ward