IPv6 accounting RADIUS SQL schema?

[Michael Ducharme]

Hi,

On 8/18/2018 8:37 PM, Nathan Ward wrote:
> Additionally, it is common that WAN side addresses are not assigned at all - and only DHCPv6 IA-PD is assigned - and there may even be multiple IA-PD assigned.
Yes, I am aware - we do not assign WAN side addresses. An unfortunate 
side effect of this is that *many* consumer routers will then not 
function - quite a few consumer routers ask for address and prefix, and 
if offered a prefix with no address, will refuse it instead of correctly 
accepting the prefix. Once our NAS vendor adds proper RADIUS accounting 
support for IPv6, we might assign WAN-side addresses for that reason only.

We are only starting to provide managed residential gateways to our 
customers - our network has been historically BYOR 
(bring-your-own-router), and we even have some customers who have no 
computer and no router, only an xbox plugged into their modem configured 
with the PPPoE credentials. At the moment, the way our network is 
configured, those xbox customers are out of luck for IPv6, since the 
xbox is unlikely to request a prefix via DHCPv6-PD. Given our 
environment, as long as we have decent RADIUS accounting, we are likely 
to enable both SLAAC framed-prefix and DHCPv6 address provisioning, if 
only to provide working IPv6 connectivity to as many customers as 
possible, given the plethora of different ways they can connect.

> I think the short story is that a RADIUS “session” can have 0+ IPv6 addresses/prefixes. This is I suppose similar to IPv4, if you use Framed-Route to give customers additional addresses - these can exist in Accounting-Request messages.
The big difference with Framed-Route in IPv4 is there is not generally a 
mechanism for customers to be automatically assigned their own prefix 
with IPv4 -- at least I have never heard of a means to do so. The only 
way that the customer is going to get an IPv4 prefix is if the attribute 
is provided via RADIUS in the Access-Accept packet, and in that event, 
you don't necessarily need the accounting to know that the customer has 
that prefix (since somewhere else in the same RADIUS database there is 
the attribute that provided it in the first place). This is different 
with IPv6, where the customer can get a Delegated-IPv6-Prefix and/or 
Framed-IPv6-Prefix by automatic means without that prefix having been 
originally assigned via RADIUS, and having the accounting record is 
therefore more crucial.
>
> I don’t know if this is something that FreeRADIUS should attempt to solve in a generic way, or if there should perhaps be some examples and have it left up to the operator. If it is solved in a generic way, how would that be done?
> - Additional tables for prefix assignments?
> - ARRAY support in SQL? (Not in MySQL, but is in other SQL DBs)
> - JSON or some other serialisation of an array in to text?
> - ARRAY for most DBs, JSON blob for others?

I considered the idea of another table, because it is tempting to have 
the idea of a structure that represents IPv6 addresses that the customer 
has in a single place, but that increases complexity in its own way. If 
you did have a second table, however, it would be even more tempting to 
have it store all other attributes that aren't recorded elsewhere, with 
fields for radacctid (to associate it with the correct accounting 
record), attribute name, and attribute value. It's always a weakness of 
the DB-field-per-attribute SQL approach that you aren't recording all 
accounting fields in the database, and the separate table would provide 
that - the database could record every value that the NAS provides. 
Alternatively as you suggest, you could have a JSON blob that records 
the remaining attributes. I don't personally like the idea of using a 
type like ARRAY that MySQL doesn't support, especially since it is 
probably the case that most FreeRADIUS SQL implementations out there are 
using MySQL.

However, I don't think it is necessarily a technical problem to have 
three different fields to store the IPv6 address and searching across 
three fields. The biggest risk is going to be lack of understanding of 
what the fields are for, where developers who create billing systems or 
web interfaces that offer some kind of address search function may only 
program it to search one of the three fields, not understanding the 
purpose of the different fields. I expect that a developer extending 
their billing system or web interface to IPv6, not having an 
understanding of some of the technicalities, would think that search of 
a "framedipv6address" field was sufficient and not bother to search the 
"framedipv6prefix" and "delegatedipv6prefix" fields, and that would end 
up creating issues for end users.

Michael