SQL help from someone who groks c, please?

[Phil Mayers]

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.

> username with different check-items).  What I need to know is which way it 
> was "intended" to work (either one of my two thoughts here, or something 


> entirely -- or even "what I want to do isn't possible".)
> 
> Here's the thought (apologies if the tabs get messed up).
> 
> a) The rows returned are parsed in order, just as if they were a users 
> file, and something (perhaps a password entry or an op of ==) triggers the 
> system that it's on the "next record".

Not in the current server.

> 
> of
> 
> b) The "id" field (which some of the docs say are unused) is used to 
> "bind" multiple items having the same ID.

No, not at all.


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.

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.


However, in the CVS version of FreeRadius, the SQL code works much more 
like you'd expect:

  * 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. That is, you could have "groups" such as:

jeremy-vpnservice
jeremy-switches
jeremy-routers

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