sqlippool duplicated IP assignments

[Iliya Peregoudov]

On 06.12.2013 21:21, Antonio Modesto Amaral Sousa wrote:
>
>
> On Fri 06 Dec 2013 02:59:54 PM BRST, Alan DeKok wrote:
>> Antonio Modesto Amaral Sousa wrote:
>>> I'm using freeradius-2.1.12_1 with MySQL 5.1 and sqlippool configured to
>>> assign IP's to my PPPoE clients, I redistribute these routes with iBGP,
>>> until yesterday it was working fine, the problem started after a
>>> blackout that happened last night.
>>
>>    When a blackout happens, the PPPoE server SHOULD tell the RADIUS
>> server that all of the sessions have been dropped.  If it doesn't, the
>> RADIUS server has no idea the sessions have been dropped.
>>
>>> I have a lot of clients that are
>>> being assigned duplicated IP addresses and I just don't know why,
>>
>>    That shouldn't happen, even in the above scenario.  It should be
>> assigned new IPs, not IPs which are already assigned.
>>
>>    Or, maybe the PPPoE server told FreeRADIUS that the sessions were all
>> dropped... then let the users keep the old IP addresses.
>>
>>> I've
>>> configured a long lease time for each assignment so that the NAS has a
>>> good time frame to transmit the interim updates, my radippool table is
>>> using the engine InnoDB and I think the select lock should be working
>>> fine. Any suggestions?
>>
>>    Blame the PPPoE server for everything.  FreeRADIUS assumes that
>> IP's are:
>>
>> - assigned when it says to assign them
>>
>> - NOT assigned to other users
>>
>> - timed out when Session-Timeout = 0
>>
>> - closed when the PPPoE server / NAS reboots
>>
>>
>>    FreeRADIUS also assumes that it gets told when something happens with
>> the user session.  If the PPPoE server isn't doing the right thing, and
>> isn't telling FreeRADIUS what it's doing... FreeRADIUS can do nothing.
>
>
> Hello Alan,
>
> I noticed something in the sqlippool config:
>
> ## The ORDER BY clause of this query tries to allocate the same IP-address
> ## which user had last session...
> #allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
> # WHERE pool_name = '%{control:Pool-Name}' AND (expiry_time < NOW() OR
> expiry_time IS NULL) \
> # ORDER BY (username <> '%{User-Name}'), \
> # (callingstationid <> '%{Calling-Station-Id}'), \
> # expiry_time \
> # LIMIT 1 \
> # FOR UPDATE"
>
> # ## If you prefer to allocate a random IP address every time, i
> # ## use this query instead
> allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
>   WHERE pool_name = '%{control:Pool-Name}' \
>   AND expiry_time IS NULL \
>   ORDER BY RAND() \
>   LIMIT 1 \
>   FOR UPDATE"
>
>
> The first allocate-find method assumes that the IP is free if the
> expiry_time is null, or if it's in the past, but the second one just
> check if the expiry time is null, isn't it wrong? Can I insert the other
> condition? because I noticed that I have some records here that weren't
> updated and are with the expiry_time in the past, probably because the
> NAS didn't told radius that...

Maybe pool in database is depleted (so that SELECT ... FOR UPDATE start 
returning no rows), radiusd start returning no Framed-IP-Address in 
reply, and PPPoE BRAS start assigning IP addresses from locally 
configured pool.

rlm_sqlippool return notfound return code when pool is depleted. This 
return code can be overriden inadvertently by noop or ok or updated 
return code later in postauth section.