On 8/19/2018 2:47 AM, Nathan Ward wrote:
I would query why that data is in your billing system, but, I’m sure you have your reasons. Generally I prefer to expose data to “external” systems through APIs, rather than give them direct database access and tightly couple two applications together though a DB schema which may need to change for one side or the other.
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, except the odd time when an accounting-stop packet is dropped between NAS and RADIUS server. The biggest issue that we had was very recent, the result of a missing UNIQUE on the index for acctuniqueid, resulting in duplicate records in the radacct table that confused the billing system so much that it stopped consistently updating the ACL, resulting in a small number of customers who were up to date getting the walled garden, and a few customers who were behind on billing were getting free service.