SQL help from someone who groks c, please?

[Dan Mahoney, System Admin]

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