Mirko Alberio - Systems & Software Integration Manager e-mail:mirko.alberio@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@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!