On Sep 17, 2022, at 7:06 AM, Matteo Sgalaberni <sgala@sgala.com> wrote:

Hi, I found on this patch a huge change on the sqlippool:

https://github.com/FreeRADIUS/freeradius-server/commit/38e475f424d4abb1b3ec5...

I see that it's well documented on wiki. I have a doubt about the "allocate_existing".

I'm using the oldest query "allocate_find" that do in the same query "allocate_existing+allocate_find" and works well. I can probably understand why the developer who wrote this patch divided this, so it's simpler to disable the sticky ip.

I'm running in a problem with "sticky ip" (old query allocate_existing): allocate_find = " \ SELECT framedipaddress \ FROM ${ ippool_table } \ WHERE pool_name = '%{control:${pool_name}}' \ AND ( \ expiry_time < 'now' :: timestamp ( 0 ) \ OR ( nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' ) \ ) \ ORDER BY \ ( username <> '%{SQL-User-Name}' ), \ ( callingstationid <> '%{Calling-Station-Id}' ), \ expiry_time \ LIMIT 1 \ FOR UPDATE "

Sometimes, in rare case when the customer have a very quick disconnection/reconnection happen that the NAS send the Access-Request before the stop Accounting-Request. So in that case happen: 1) Access_Request -> allocate_find (find the same ip to the same user) + allocate_update (assign same ip, renew lease) 2) Accounting-Request Stop -> allocate_free (clear ip)

That's wrong. I'd argue that the NAS is broken. But... it's RADIUS. :( One question is what's in the packets? i.e. Is there any way to tell that the "stop" is for an old session, and not the new one? If the accounting packet contains an Acct-Start-Time that's *before* the time the Access-Request was received, then there's a simple solution. Update the IP pool schema to include a "last allocated" time. Set it when the IP is allocated (or renewed). And then check it during the "stop query. If the "last allocated" time is after the "Acct-Start-Time" of the "stop", then you can ignore the stop, and leave the IP address alone.

Now, the IP is assigned, but on sqlippool is marked as free...and sometime in the future another user will obtain the same IP and I have a duplicate assignment.

Is this behaviour normal and "known"? I can't find this "warn" on wiki/documentation.

It's RADIUS, anything can happen. NAS vendors will do the most "inventive" things. That's one reason why FreeRADIUS is so complicated: to deal with all of the corner cases like this.

If this is normal and known I think that I need to kill the "stitky ip" capability to solve the issue in these way: Way A) remove " nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}'" and ORDER BY \ (username <> '%{SQL-User-Name}') from allocate_find or Way B) using the new queries.conf of the last branch and commenting the "allocate_existing"

The IP in that case is not assigned again to the same user and I'm sure that the NAS packets order are not creating issues.

Whatever solution works for you is fine. That's why the queries are editable, and documented. You can change them to work around issues in your network. Alan DeKok.

On Sat, 17 Sept 2022 at 12:06, Matteo Sgalaberni <sgala@sgala.com> wrote:

I'm using the oldest query "allocate_find" that do in the same query "allocate_existing+allocate_find" and works well. I can probably understand why the developer who wrote this patch divided this, so it's simpler to disable the sticky ip.

I'm running in a problem with "sticky ip" (old query allocate_existing): allocate_find = " \ SELECT framedipaddress \ FROM ${ ippool_table } \ WHERE pool_name = '%{control:${pool_name}}' \ AND ( \ expiry_time < 'now' :: timestamp ( 0 ) \ OR ( nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' ) \ ) \ ORDER BY \ ( username <> '%{SQL-User-Name}' ), \ ( callingstationid <> '%{Calling-Station-Id}' ), \ expiry_time \ LIMIT 1 \ FOR UPDATE " Sometimes, in rare case when the customer have a very quick disconnection/reconnection happen that the NAS send the Access-Request before the stop Accounting-Request. So in that case happen:

The query optimiser will not be able to handle "WHERE ... OR ..." efficiently, so this form of query will not scale with high levels of concurrency. For most SQL databases it is the index entries that drive locking (the same index used in the lookup), rather than actual table rows, so it is best to ensure that queries which hold locks across multiple database round trips will identify the candidate IP via a single index entry, i.e. that the index contains sufficient data for the IP to be determined without reference to columns in the table. If not, you unintentionally lock multiple IPs, either blocking other queries, or in the case that SKIP LOCKED is used, reducing the set of candidates thereby giving the false impression of pool exhaustion. Thus motivating the split in the queries and development of the stored procedures which achieved massive performance gains for DHCP: https://networkradius.com/articles/2021/05/20/DHCP-enhancements-in-FreeRADIU... But that's an aside...

1) Access_Request -> allocate_find (find the same ip to the same user) + allocate_update (assign same ip, renew lease) 2) Accounting-Request Stop -> allocate_free (clear ip)

Now, the IP is assigned, but on sqlippool is marked as free...and sometime in the future another user will obtain the same IP and I have a duplicate assignment.

Does the NAS allocate a session identifier at auth time? Is Acct-Unique-Id stable between auth and acct, or can it be made so? In which case consider using this as the pool_key. This way activity related to updating/closing of the old session will not affect the new allocation since the pool_key will not match. You may need to step through the queries to ensure that your get the desired behaviour because in your configuration you are essentially allowing the new session to "take over" and existing session's IP allocation. That may be fine for your case, but it is also perfectly fine to have multiple sessions (even with the same username) over the same circuit in other cases, e.g. PPPoE proxies, some "double dip" configurations. NASs often generate auth requests instantaneously in response to incoming events (PPPoE PADI, IPoE FSoL), and generate acct requests via a low-priority, timer-driven state machine, often with less than ideal opportunities for coordination between these mechanisms. Therefore it's not uncommon for the CPE to retry possibly multiple authentications before the accounting session is terminated, e.g. due to a long PPPoE keepalive-timeout. It's somewhat normal to receive Stops (and even I-Us) for the ongoing sessions that are awaiting timeout.