IPv6 accounting RADIUS SQL schema?

[Michael Ducharme]

Hi Alan,

Thanks for the info. However, how does that solution handle dual stack? 
If client gets both IPv4 and IPv6, a user would want to record both, not 
just one or the other. Although I am not a FreeRADIUS expert by any 
means, I looked at the unlang manpage, it looks like that query will 
store the IPv6 address in the field only if the client is not getting an 
IPv4 address. If the client is getting both, if I understand the unlang 
manpage correctly, it will store only the IPv4 and the IPv6 address will 
go unrecorded.

Also, if I understand the RFC correctly (and it's possible that I do 
not), this attribute handles only cases where a single host requests an 
address via DHCPv6, rather than a router requesting a prefix, or a NAS 
assigning a framed prefix for SLAAC assignment. If I understand things 
correctly, these cases would be reported only through Framed-IPv6-Prefix 
and/or Delegated-IPv6-Prefix accounting. I don't believe those prefixes 
would be reported in Framed-IPv6-Address?

Based on that I had planned to add three new fields into our radacct 
table in MySQL - one for framed IPv6 address, one for 
Framed-IPv6-Prefix, and one for Delegated-IPv6-Prefix. Adding three new 
fields however seems not only a bit messy, it may not be in line with 
what the developers had in mind, which is why I asked the question.

My apologies if I misunderstand anything that seems obvious, I just want 
to clearly understand what was intended for these situations.

Thanks!

On 8/16/2018 11:43 AM, Alan DeKok wrote:
> On Aug 16, 2018, at 2:16 PM, Michael Ducharme <mducharme at gmail.com> wrote:
>> I am wondering whether there is any standard SQL schema for IPv6 radius accounting, and standard queries?
>    The schemas are pretty much already IPv6 compliant.
>
> - Postgresql has the "framedipaddress" field as INET, which is IPv4 or IPv6
>
> - MySQL has it "varchar", which is anything...
>
> - as does Oracle and sqlite
>
>    The only thing that needs doing is to update the queries to use:
>
> 	%{%{Framed-IP-Address}:-%{Framed-IPv6-Address}} instead of just %{Framed-IP-Address}
>
>> I understand from doing a bit of research that to store the IPv6 schema we have to manually add fields to the SQL database and the queries to the queries.conf. That is certainly doable and is easy enough, however instead of naming them what I want, I would rather have some official guidance as to what the fields should be named and what the queries should be.
>    It's all there already.
>
>> The reason is that we use some third party software that reads accounting data directly out out of the FreeRADIUS radacct table, and it expects the standard field names to be used - unfortunately there seem to not be standardized field names for SQL for IPv6 RADIUS accounting yet, and it would simply save us some future headache if we had this. They seem to not want to build support for IPv6 until there is some standard schema released by FreeRADIUS that gives the MySQL field names for IPv6 accounting info.
>>
>> Also small ISPs are beginning to roll out IPv6 more and more, ISPs that use FreeRADIUS, so it might be good to add those into the standard install at some point? Just an idea.
>    Send a github pull request for the v3.0.x branch.  It should be pretty simple.
>
>    Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html