sqlippool, allocate_find and duplicate address if NAS packets are received in the reverse order

[Terry Burton]

On Sat, 17 Sept 2022 at 12:06, Matteo Sgalaberni <sgala at 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-FreeRADIUS-3.html

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.