Managing the RADIUS database

[Steve Bertrand]

Steve Bertrand wrote:
> Hi all,
> 
> I'm curious to know what you use to manage your RADIUS database,
> particularly the accounting tables.
> 
> For quite some time, I was using severely hacked versions of the
> dialup_admin scripts.
> 
> Since then, I've written (ie. been writing) an ISP mgmt/accounting
> system that relies very heavily on the RADIUS accounting information for
> billing and statistics, but only after the data has been aggregated.

I'm rehashing this post for a couple of reasons. Very lengthy, read at
your own risk.

I've dedicated a lot of time toward the RADIUS accounting aggregation
project, and the project is becoming slimmer and more streamlined by the
day.

This post is organized into sections, for those that are/can:

- WARNING
- INTERESTED IN BW/TIME BILLING
- PROVIDE DATA FOR TESTING
- Perl HACKERS WHO WILL FIX MY CODE
- OTHER NOTES

# WARNING

(un)Fortunately, "I'm not a programmer, I'm but a humble network
engineer (tm)". There may be references within my explanations that may
sway toward the understanding of a programmer, in which Google will
easily help.

Again, I'm not a programmer, so the real developers will likely laugh.
This is ok, so long as I can make myself understandable to the
non-developer FreeRADIUS user.

# INTERESTED IN BW/TIME BILLING

The radacct table in the RADIUS database grows at an enormously fast
rate. Regardless of server resources, trying to search or perform
actions on this table can be a daunting task.

My objective is to consolidate this data into daily database tables that
aggregate the information by user and nas address. This daily info can
then be aggregated further into a monthly table, which is ideal for ISP
billing purposes.

To further, I found that billing was easiest if NAS/RAS's could be
'classified' into a named group, based on their IP/IPv6 address and/or
their DNS names into classes, so that for example, your ADSL access gear
within 192.168.0.0/24 and 172.16.0.0/25 could be classified and then
aggregated into the 'highspeed' class.

This would allow the username 'steveb' to have all access data
aggregated into a named class, so his high speed usage billing would be
bandwidth based, but if he dialed in via traditional POTS with the same
username as well, he could also be identified and billed as a dial-up
user in within a different NAS pool as well.

Much work has gone into the classification system, and user-defined
'classes' can be defined dynamically via a standard configuration file.

The entire philosophy of this software is to make it easier for *SPs to
better manage their accounting data, and make billing and reporting as
simple as possible.

# PROVIDE DATA FOR TESTING

I've tested, updated POD, tested, updated more POD, made revision after
revision, and tested again using FreeRADIUS data that dates back to 2004.

The data I've been testing with is our own. Much of it has been through
multiple schema changes, server relocations etc, but it still works.

I'm looking for data (preferably dumped from mysqladmin, or entire
tables), that I can throw at this software so I'm not continuously
working with my own data. Raw data is preferable, but I can work with
sanitized data. I'm also willing to work with an NDA for testing, and
will even guarantee that data will be tested in a non-connected
environment if specified.

Data from the radacct table is all that is required. I can specify
SSH/PGP etc if required. Email me off-list if you can help.

# Perl HACKERS WHO WILL FIX MY CODE

...will receive SVN access to the repo, so long as they are willing to
accept the fact that I'm not a programmer, and that I have my own
bugzilla for bugs that I'll want to enter in manually. It will take me
time to learn the development ropes.

- the SQL is dbic'd, all but the 'archive' code, because I don't know
how to create tables with dynamic names using DBIX::Class
- MySQL replication can be configured via config file, so long as MySQL
replication is pre-configured in MySQL
- slave db servers can be added dynamically via config file
- master write locking is possible via config file, and pushes reads to
slave servers
- alternate config files can be supplied via param or environment var
- Perl 'make test' et-al occurs against a pre-defined dataset within an
SQLite3 database that is generated via `perl Makefile.PL`
- MySQL and SQLite have been thoroughly tested (for the archive tables.
The read production radacct table is in MySQL)
- currently, the software assumes that both the live radacct table, and
the aggregation tables are in the same db (this will be changed tomorrow..?)
- relatively full documentation, OO interface etc

# OTHER NOTES

Since I have more than this on the go, I've applied to PAUSE for a new
top-level namespace (ISP::), but haven't heard back yet. I am seriously
dedicated to my ISP project, and as such, creating a project that
fulfills the need of *SPs billing purposes is of utmost importance.

I hope that the simple Perl code I've written so far will inspire those
who see it to help create a decisive method for RADIUS billing for the
long term.

I've used FreeRADIUS for years, and my ultimate hope is that what I'm
trying to do will eventually help out a portion of the community.

Thanks to Alan for his dedication to this project, Ivan for his
seemingly endless supply of support, and everyone else here who supports
this tremendously critical piece of infrastructure.

Steve