The other and slightly easier alternative is to set "ignore_null = yes" on your realm module so that users without a realm won't match the module, and then use:
select foo from bar where Username='%{Stripped-User-Name:-%{User-Name}}' and realm='${Realm:-pop.co.za}'
Being lazy - I'm starting here. (Also - less impact on existing users)
It's probably the most sensible thing to do
Changed 'ignore_null' to yes.. Smiled - and tried to dial-in
rad_recv: Access-Request packet from host 160.124.0.97:1645, id=81, length=106 NAS-IP-Address = 160.124.0.97 NAS-Port = 70 Cisco-NAS-Port = "Async70" NAS-Port-Type = Async User-Name = "mje" Called-Station-Id = "0800" User-Password = "verysecret" Service-Type = Framed-User Framed-Protocol = PPP Processing the authorize section of radiusd.conf modcall: entering group authorize for request 7 modcall[authorize]: module "preprocess" returns ok for request 7 modcall[authorize]: module "chap" returns noop for request 7 modcall[authorize]: module "mschap" returns noop for request 7 rlm_realm: No '@' in User-Name = "mje", skipping NULL due to config. *** Line above suggests the 'ignore_null' worked *** modcall[authorize]: module "suffix" returns noop for request 7 rlm_eap: No EAP-Message, not doing EAP modcall[authorize]: module "eap" returns noop for request 7 radius_xlat: 'mje' rlm_sql (sql): sql_set_user escaped user --> 'mje' radius_xlat: 'SELECT userid as id,UserName,'Password' as Attribute,password as Value,'==' as op FROM useracct WHERE Username='' AND realm='pop.co.za' AND .....
Something stole my username ???
Well, something in your SQL query is wrong. The default queries use %{SQL-User-Name} which is set to %{Stripped-User-Name:-%{User-Name}} which will work with and without a realm match. If you've changed that e.g. to make the queries use %{Stripped-User-Name} directly then it'll fail because of course now the realm module isn't matching, so no Stripped-User-Name is being set.
I'm not too sure of the logic here. I've left the NULL realm in "proxy.conf" uncommented - so its 'live'.
Yes - but you've told the realm module to ignore_null, so it'll never match and a user will never be "in" that realm.
It seems that the 'NULL' realm matched... then skipped (?) - so I got a null username and a null realm (all the other info got through!)
You got a null *STRIPPED* user name. Use the conditional expansion as above and per the default configs.
Looks like the SQL logic works though... I just hate complicating the SQL queries even more than I already have!
I guess FreeRadius 2.xx will make things easier?
Well no - if you break the SQL queries then 2.x will fail too ;o)