On Mon, 5 Feb 2007, Phil Mayers wrote: > Dan Mahoney, System Admin wrote: >> >> That said, I figured out two possible ways to handle my previous question >> regarding advanced SQL auth (including multiple occurances of the same > > Minor point of terminology - SQL doesn't authenticate. It acts as a > store for config and reply items.
Of course. I myself am in the habit of stating things I already know to be true, just for the advantage of some poor soul trying to get something from mailing list archives. > Not in the current server. > > Neither of your examples will work, because cCurrently* in the release > version of FreeRadius, rlm_sql works as follows: > > * select per-user check items from radcheck > * select all group check items for that user from radgroupcheck > * merge them > * compare them - if match: > * select per-user reply items from radreply > * select all group reply items for that user from radgroupreply > * merge them > * add them to the reply > > Because of the merging of the check/reply items, with the currently > release version of FreeRadius it will be difficult to achieve what you > want. There are probably ways to use clever tricks with the schema, but > the algorithm that iterates over the SQL results is coded into the C > portion of the module, and is not really flexible enough. I assume you mean the core SQL module, and nothing driver specific, yes? > My suggestion is that you use a custom schema and queries for your > database - probably a stored procedure. Pass the NAS-IP-Address into > these queries, and return different values based on the nas. Effectively > you move the code that walks over the request and chooses the right > values into the SQL server. This I'm already doing. I just wanted to be sure I wasn't going about it wrong. > However, in the CVS version of FreeRadius, the SQL code works much more > like you'd expect: Also, for the edification of anyone who might stumble on this, the CVS code relevant to which version? I.e. would this be a 2.x branch thing? Note that I'm not asking the luserish "when will it be out I need it now!!!1" question, as I've already got workarounds in place to do what I need, but I've come across threads dating back five years in some software and was never sure which version was relevant. > * select per-user check items from radcheck > * compare them > * if match, add per-user reply items from radreply > * if Fall-Through: > * for each group > * select per-group check items > * compare them > * if match, add the per-group reply items > * stop unless Fall-Through > > With that schema, it would be relatively trivial to (ab)use groups as > users. Or I'd join against my "devices" table and return one entry for jeremy based on each network device in the grid with a simple left join (the distinction being that it's multiple answers based on a less-specific question, as opposed to a single check-item based on a more-specific query.) Again, my SQL know-how is good, so I can contrib some more advanced examples if need be. The concept of group ownership is still good, but with SQL working the way you describe, and fall-through working right, it's not strictly as necessary as it was: in theory it's possible with the new code to select ALL rows, and read the whole table just like an old-style users-file, no (waste of processor and resources aside). For example, right now I have a few tables that match user-type and device type, so that for any user type, and any device (say, cisco core routers, or extreme distribution switches), that lists the appropriate reply-items. The annoyance and minor difference here is the requirement to specialize the query -- with the current code, I need to include anything I want to specialize on (which means if there's five different check-items, I have to include them (and not all devices will include such check-items). No groups necessary (I am not using them now, in fact, my queries encompass that logic). > ...with appropriate check/reply items (e.g. check item might be > huntgroup or nas-ip-address). The groups would contain one user - in the > previous example, jeremy. Okay -- do you know anything about if the semicolon is intentionally ignored? I tried doing multiple queries in a single statement but got an error (thank god for UNION). -Dan -- --------Dan Mahoney-------- Techie, Sysadmin, WebGeek Gushi on efnet/undernet IRC ICQ: 13735144 AIM: LarpGM Site: http://www.gushi.org --------------------------- - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html