ippool 0.0.0.0 assignment problem

[Alan DeKok]

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.

> 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.