Re: SQL help from someone who groks c, please?

"Dan Mahoney, System Admin" <danm@prime.gushi.org> · Sun, 4 Feb 2007 20:32:35 -0500 (EST)

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