ippool 0.0.0.0 assignment problem

[Mirko Alberio]

Mirko Alberio - Systems & Software Integration Manager
e-mail:mirko.alberio at telemar.it

Telemar SpA Internet Quality Provider
Via Enrico Fermi, 235 - 36100 Vicenza - Italia
Tel 0444 291302 - Fax 0444 566310 -www.telemar.it
Assistenza tecnica 0444 1420000
Reg. Imp. Di Vicenza /C.F./P.I. 02508710247
Cap. Soc. € 120.000,00 I.V.
R.E.A. VI-236292

Il 14/12/2021 15:25, Alan DeKok ha scritto:
> On Dec 14, 2021, at 3:31 AM, Mirko Alberio via Freeradius-Users<freeradius-users at lists.freeradius.org>  wrote:
>> I found the base problem of my radius ippool assignment problem!
>>
>> Maybe for future reference for other users, the problem was: WHITESPACE in an ip pool address row!
>    It's best to be careful about what goes into a database.  :(
>
>    Unfortunately, MySQL doesn't have a native "ip address" data type.  So the "IP address" field in the IP Pool schema is just free-form text.  Which can create this kind of issue.
>
>    In contrast, PostgreSQL does have a native "ip address" data type.  So it's impossible for PostgreSQL to have this issue.

    Yes, I thought about using PostgreSQL, we are using it for an open
    source IP management system and it is very well structured for
    network type data storage, I must say.
    Also I saw that performance-wise would be better than MySql, even
    though now I am using MySql 8 and is performing well.

>
>> So in some way the NAS where assuming 0.0.0.0 as framed ip address for this.
>>
>> This is the radiusd -X output that helped me:
>    Exactly!
>
>> (1675) sqlippool: Executing query: COMMIT
>> (1675) sqlippool: EXPAND START TRANSACTION
>> (1675) sqlippool:    --> START TRANSACTION
>> (1675) sqlippool: Executing query: START TRANSACTION
>> (1675) sqlippool: EXPAND SELECT framedipaddress FROM radippool 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 SKIP LOCKED
>> (1675) sqlippool:    --> SELECT framedipaddress FROM radippool WHERE pool_name = 'pool1' AND (expiry_time < NOW() OR expiry_time IS NULL) ORDER BY (username <> 'xxxxxxxxx'), (callingstationid <> 'XX:XX:XX:75:36:B6'), expiry_time LIMIT 1 FOR UPDATE SKIP LOCKED
>> (1675) sqlippool: Executing select query: SELECT framedipaddress FROM radippool WHERE pool_name = 'pool1' AND (expiry_time < NOW() OR expiry_time IS NULL) ORDER BY (username <> 'xxxxxxxxx'), (callingstationid <> 'XX:XX:XX:75:36:B6'), expiry_time LIMIT 1 FOR UPDATE SKIP LOCKED
>> (1675) sqlippool: EXPAND COMMIT
>> (1675) sqlippool:    --> COMMIT
>> (1675) sqlippool: Executing query: COMMIT
>> *(1675) sqlippool: Invalid IP number **[12.34.56.7__] **returned from instbase query.*
>    I've fixed the typo ("instbase" ???).  I've also double-checked the sqlippool module for other messages.  It now produces ERROR messages when it can't assign an IP address.  These messages should show up as red, with a big ERROR prefix in the debug output.
>
>    Even using "tcpdump" or "wireshark" would have helped here.  If a user is online with "0.0.0.0", then look at the Access-Accept packet sent back for that user.  My guess is that there would be no Framed-IP-Address in the accept.  Which suggests that the sqlippool module wasn't allocating IP addresses.
>
>    I don't think it's a good idea to change the sqlippool module to be more "forgiving" about parsing IP addresses.  The purpose of a database is to store data, and to store the data in a consistent format.  If the data in the database is wrong, then no amount of poking FreeRADIUS will make things "just work".  Instead, it's best to just fix the database.
>
>    I'm happy to see this tracked down and fixed. And yes, ALWAYS run in it in debug mode!  There are very few situations where doing that doesn't help.
>
>    Alan DeKok.

    I agree: you could add a "trim" function but then would be just a
    temporary fix and other similar issue could go undetected without
    checking the goodness of data input before.

    Thanks for adding more ERROR messages anyway. Have a nice day!