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@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

OK, thanks, that confirms basically everything I thought. My question is, then, since those attributes are so standard with IPv6 RADIUS accounting, and they each store different things, why are those three fields not already in the database schema? Is it just because nobody has submitted a patch (pull request) that adds those three fields into the schema and adds the proper queries? Or is it because the developers feel that most people aren't going to need them? I might assume the latter, but in that event, I'm just concerned that it's going to end up being a big mess when it comes to software that integrates with FreeRADIUS/MySQL if everybody adds those fields themselves but they name them in different ways. For instance, our billing system, which is designed for our NAS and integrates with FreeRADIUS/MySQL -- it doesn't support IPv6 RADIUS accounting yet because our NAS doesn't support it, but our NAS vendor is currently in the process of adding support for IPv6 RADIUS accounting. Once they add support, our billing system vendor will need to read from those three fields in the radacct table, but if all of their customers name them differently then you end up with a bit of a mess in terms of integration. I can add these three fields myself into our instance, but would rather know that those are the same field names that others are going to use. Chances are that others will name them similarly, of course (it would seem obvious to name them like the attributes but all lower case without hyphens) but at least if it is in FreeRADIUS by default, I know that that naming is the standard that should be used. I just don't want to be in the position where I am adding those three fields on a dozen RADIUS servers, then a year from now someone decides to add them to FreeRADIUS as built-in fields but the fields have different names, and then for integration purposes I now need to go back to all of these RADIUS servers that I set up and rename the fields in the database in order for them to work with the third party software. Do you understand my concern? If you say that it is likely that if I add those three fields and adjust the queries and submit a pull request that it will be accepted, I will do so. Thanks. On 8/16/2018 6:55 PM, Alan DeKok wrote:

...

On Aug 16, 2018, at 4:10 PM, Michael Ducharme <mducharme@gmail.com> wrote:

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. Yes. That's why I suggested updating the query.

If you want to add a "framedipv6address" field to SQL, it's simple. It's just a text file... add another line, and then update the SQL queries.

...

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? They're different attributes, so no.

...

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. If they're different... they should be recorded.

Alan DeKok.

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

On 8/17/2018 6:54 AM, Alan DeKok wrote:

I did say 2 messages ago to send a pull request... I don't need to be convinced. Please just send a pull request. It's not hard. Alan DeKok.

Done. I did two, one for 3.0.x and one for 4.0.x.

how does this all work in practice where the clien thas multiple concurrent IPv6 addresses on the NAS? does the NAS send multiple records (one for each address), send multiple IPv6 addresses in a single update RADIUS datagram ? alan On 18 August 2018 at 02:14, Alan DeKok <aland@deployingradius.com> wrote:

On Aug 17, 2018, at 7:48 PM, Michael Ducharme <mducharme@gmail.com> wrote:

...

On 8/17/2018 6:54 AM, Alan DeKok wrote:

...

I did say 2 messages ago to send a pull request... I don't need to be

convinced. Please just send a pull request. It's not hard.

...

...

Alan DeKok.

Done. I did two, one for 3.0.x and one for 4.0.x.

Thanks. They look good, other than a few minor typos.

The integration tests in v4 help a lot. :)

Alan DeKok.

- List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html

I would say it is even more complicated: If assigning framed prefixes is enabled on the NAS, each customer is given a /64 prefix and router advertisements are sent out so that the CPE can get a global address via SLAAC (reported as Framed-IPv6-Prefix and Framed-Interface-Id). If DHCPv6-PD is enabled on the NAS, each customer who requests a prefix will be assigned one, typically a /56 (reported as Delegated-IPv6-Prefix). If DHCPv6 address assignment is enabled, then the CPE can get a global IP through DHCPv6 (reported as Framed-IPv6-Address). So if the customer has a router that supports everything and has everything enabled, it could get two addresses on its WAN port, one via SLAAC and one via DHCPv6, and then a prefix via DHCPv6-PD for use on the internal network (LAN ports, guest wifi, etc.). If only routers are connecting via PPP and not customer computers directly, you can look at the Delegated-IPv6-Prefix to see what prefix the customer's computers are using. If customer computers connected directly to the NAS (ex. through an L2TP VPN), then the computer will use either a global address via SLAAC (found in the Framed-IPv6-Prefix and Framed-Interface-Id accounting) or a global address via DHCPv6 (found in Framed-IPv6-Address), or both. Because, depending on the exact situation, the end user device may be on an address in the Delegated-IPv6-Prefix (this is the case if they go through a router) or an address in the Framed-IPv6-Prefix (if they are on SLAAC) or an address in Framed-IPv6-Address (if they receive an address through DHCPv6 address assignment), all three fields must be stored. As an ISP, we are required to forward copyright infringement notices to customers, and in order to look up the address on the notice, we need to search all three fields (unlike in IPv4 where we only search one field). Michael On 8/18/2018 4:11 AM, Nathan Ward wrote:

Hi,

...

On 18/08/2018, at 10:44 PM, Alan Buxey <alan.buxey@gmail.com> wrote:

how does this all work in practice where the clien thas multiple concurrent IPv6 addresses on the NAS? does the NAS send multiple records (one for each address), send multiple IPv6 addresses in a single update RADIUS datagram ? IPv4+IPv6 varies between NAS models and configurations: Sometimes you get IPv6-only “sessions” (i.e. maybe auth, and accounting). Sometimes you get combined IPv4 and IPv6 sessions. Sometimes you get a session for PPP and IPv4, and another for IPv6.

Within that, IPv6 with multiple addresses/prefixes generally results in a single session. Is is very common (the norm) to have multiple addresses - i.e. a subscriber will get a /128 for their “WAN” address, and a /56 or similar for things behind their CPE. These are represented as Framed-IPv6-Prefix and Delegated-IPv6-Prefix respectively, though Cisco (on ASR9k at least) don’t send Framed-IPv6-Prefix and instead send it as Cisco-AVPair = “addrv6=blah”. The usual nonsense, as you can imagine :-)

Check out 3.6 of RFC6911.

-- Nathan Ward

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

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

On 8/18/2018 10:49 PM, Nathan Ward wrote:

If this would be the approach then I think it’s best left as examples of what could be done, rather than change the standard schema - a standard schema needs to support 0+ of each of the fields you suggest here, not simply 0-1. There *needs* to be a standard schema for IPv6 soon, the billing system we use does not support IPv6 RADIUS accounting yet mainly because there are no standard FreeRADIUS/MySQL field names to read the data from (hence the reason for my question and my pull request). How can I get our billing system vendor to support IPv6 RADIUS accounting in FreeRADIUS/MySQL when there are not even standard fields for storing this data?

And, regarding 0+ instead of 0-1, even though the attributes may technically be usable multiple times, how often does this happen in practice? I would expect extremely rarely, based on what I have seen, especially in an automated sense. If there are multiple Delegated-IPv6-Prefix attributes, chances are that it is not due to an automated system but instead due to RADIUS assignment of Delegated-IPv6-Prefix, and that case is like the IPv4 Framed-Route attribute. If Delegated-IPv6-Prefix is assigned via RADIUS from the beginning, then at least there is a record that that prefix was assigned to that customer besides the accounting record. I just don't want this to turn into a "lets do nothing" situation, due to a few potential corner case situations, because then nothing is going to happen. Michael

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.

...

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.

How will this work (either way) if the client supports MAC spoofing? Surely you should prevent access via username, not client MAC (they could also just use another client, or W-NIC,...) Seems to me like a game of whack-a-mole :) ******************************************************************************************************************** This message may contain confidential information. If you are not the intended recipient please inform the sender that you have received the message in error before deleting it. Please do not disclose, copy or distribute information in this e-mail or take any action in relation to its contents. To do so is strictly prohibited and may be unlawful. Thank you for your co-operation. NHSmail is the secure email and directory service available for all NHS staff in England and Scotland. NHSmail is approved for exchanging patient data and other sensitive information with NHSmail and other accredited email services. For more information and to find out how you can switch, https://portal.nhs.net/help/joiningnhsmail

Hi, On 8/19/2018 12:28 PM, Alan DeKok wrote:

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.

Yes, the way their system works is not the way that I would have set it up. They did in that seemingly strange way because they don't want to require the use of RADIUS - if RADIUS is not used with their software, a script runs on the DHCP server that passes the IP and MAC to the billing system as parameters in an HTTP GET request whenever a lease is created or deleted.

Hi, On 8/19/2018 4:29 PM, Nathan Ward wrote:

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. To be fair to our vendor, it is designed specifically as a WISP billing system, and WISPs are generally on the small side. Many ISPs of that size also have a fear of RADIUS servers due to not understanding them and the complexity, so the successful vendors are those that promise to not require a RADIUS server in order to work. When we first talked to the vendor, they went out of their way to make it clear to us that their product did not require a RADIUS server and worked fine without it, apparently due to past experience where a WISP went with a different product on the perception that our vendor required a RADIUS server to work properly. It did not bother us because we have used RADIUS for a long time.

Larger ISPs just would not use that product to begin with.