Re: SQL help from someone who groks c, please?
Phil Mayers wrote: Dan Mahoney, System Admin wrote: 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. So if I was looking to select a different response based on NAS what I should be doing is creating a stored procedure that ends up authenticating for me? I don't quite see where this would fit in with the rlm_sql logic. Would that go in the sql.conf file? For using a new schema, would that mean instead adding an extra column in the radcheck table and the response table to associate with the NAS IP? Would it be easier to create a function that inserts a prefex to the user name then processes the SQL as normal? The only issue I see with this is doubling the amount of users and user responses in the database . Either way, I think i'm going to have to modify the rlm_sql.c file and then having to recompile FreeRadius after I'm done editing it? -- View this message in context: http://www.nabble.com/SQL-help-from-someone-who-groks-c%2C-please--tf3172009.html#a8870617 Sent from the FreeRadius - User mailing list archive at Nabble.com. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL help from someone who groks c, please?
On Thu, 8 Feb 2007, ChristosH wrote:
No, wrong.
You can include any %{check-item} in your query. I didn't have to modify
the code at all, but my queries are PERVERSE. Yours will probably be
simpler.
If you want to give me your proposed database setup and schema, and what
you need to auth against, I might be able to offer you a quick answer. If
it's a longer and more involved thing, contact me off-list and I might be
able to work something for you.
This is my auth query (which is actually two) (beware, it's for our site
database which polls a
LOT of info from different tables you won't need -- however I find this to
be a far more real-world example than dedicated radius tables) -- You can
see that in this case I manually insert the Password Attribute, and
Operator by using string literals.
authorize_check_query=\
SELECT `adm_permissions`.`admPermitID` AS `id`,
`adm_permissions`.`admp_username` AS `UserName`, 'Password' as Attribute,
\
`adm_permissions`.`admp_password` AS `Value`, '==' as Op FROM
`adm_permissions` , `switches` Inner Join `interface_ip` ON \
`switches`.`id` = `interface_ip`.`deviceid` WHERE admp_username =
'%{SQL-User-Name}' AND \
`interface_ip`.`interface_is_primary` = '1' AND \
interface_address = '%{NAS-IP-Address}'
This above gets permissions for any staff user, and checks our one-to-many
interface table to find out what device they're actually logging into.
UNION SELECT IPCustomerID as id,
`ip_customer`.`ipc_rmtusername`, 'Password' as \
Attribute,\
`ip_customer`.`ipc_rmtpassword` as Value, '==' as Op FROM `ip_customer`
Inner Join `interface_ip` ON \
`ip_customer`.`ipc_rmtip` = `interface_ip`.`interface_address` Inner Join
`switches` ON `switches`.`id` = \
`interface_ip`.`deviceid` WHERE interface_address = '%{NAS-IP-Address}'
AND ipc_rmtusername = '%{SQL-User-Name}' \
GROUP BY `ip_customer`.`ipc_rmtusername`,\
`interface_ip`.`interface_address`
This does the same for any customer user.
Then my reply-items
authorize_reply_query = SELECT `ip_customer`.`ipCustomerID` AS `id`,
`ip_customer`.`ipc_rmtusername` AS UserName,\
`rad_reply`.`Attribute`,`rad_reply`.`Value`, `rad_reply`.`Op` FROM
`ip_customer` Left Join `interface_ip` ON \
`ip_customer`.`ipc_rmtip` =`interface_ip`.`interface_address` Inner Join
`switches` ON `switches`.`id` = \
`interface_ip`.`deviceid` Inner Join`rad_reply` ON `switches`.`role` =
`rad_reply`.`devicetype` WHERE \
`rad_reply`.`Usertype` = '2' AND ipc_rmtusername ='%{SQL-User-Name}' and
interface_address = '%{NAS-IP-Address}' group by \
ipc_rmtusername, interface_address
This only lets a customer in if it has a devicetype of 2 (which is a
remote reboot unit AND if they are listed as having a device on that unit.
We have a table that specifies if you are a customer user then your reply
is Outlet If you're staff then it's Admin-User.
UNION
SELECT`adm_permissions`.`admPermitID` AS `id`,
`adm_permissions`.`admp_username` \
AS `UserName`, `rad_reply`.`Attribute`,`rad_reply`.`Value`,
`rad_reply`.`Op` FROM `adm_permissions` , `switches` Inner \
Join `interface_ip` ON `switches`.`id` =`interface_ip`.`deviceid` Inner
Join `rad_reply` ON `switches`.`role` = \
`rad_reply`.`devicetype` WHERE`rad_reply`.`Usertype` = '1' AND
admp_username = '%{SQL-User-Name}' and interface_address = \
'%{NAS-IP-Address}'
Do the same as above with staff.
UNION SELECT `remote`.`port`as id,
`ip_customer`.`ipc_rmtusername` as UserName, _latin1 \
'APC-Outlets' as Attribute,group_concat(remote.port order by remote.port
asc separator ',') as Value, _latin1 ':=' as \
Op FROM `remote` Inner Join`ip_customer` ON `remote`.`suite` =
`ip_customer`.`ipc_suite` AND `remote`.`row` = \
`ip_customer`.`ipc_row` AND`remote`.`rack` = `ip_customer`.`ipc_rack` AND
`remote`.`server` = `ip_customer`.`ipc_server` \
Inner Join `interface_ip` ON`remote`.`deviceid` =
`interface_ip`.`deviceid` Inner Join `switches` ON remote.deviceid = \
switches.id WHERE`ip_customer`.`ipc_rmtreboot` = 'y' AND
ip_customer.ipc_rmtusername = '%{SQL-User-Name}' AND ipc_rmtip = \
'%{NAS-IP-Address}' AND switches.role = '4' GROUP BY interface_address,
`ip_customer`.`ipc_rmtusername`
If they are a customer, return a comma-separated list of which outlets
they
are authorized for. (See the APC radius spec).
Phil Mayers wrote:
Dan Mahoney, System Admin wrote:
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.
So if I was looking to select a different response based on NAS what I
should be doing is creating a stored procedure that ends up authenticating
for me? I don't quite see where this would fit in with the rlm_sql logic.
Would that go in the sql.conf file? For using a new schema, would that mean
instead adding an
Re: SQL help from someone who groks c, please?
On Thu, 8 Feb 2007, ChristosH wrote: Phil Mayers wrote: A stored procedure is one solution to a particular set of problems. Whether it's appropriate depends on what you're trying to do. What do you want to achieve? You can certainly vary the reply info based on NAS without a stored procedure. Well, what I want to do is return a different vendor specific response based on the NAS IP. The user data doesn't change depending on the NAS IP, but depending on where the user tries to authenticate from they'll have a different source NAS IP in the authenticate request packet and my response has to return a different response depending on where they are. Right now I have only 2 different responses that they could be, so I don't think it should be too difficult. Is there a quick workaround? Okay, so create a table with your NASes, include the IP adderss, include a type flag. Create another table with the responses for each type, join to the query on the type flag. Use those responses. -Dan -- A mother can be an inspiration to her little son, change his thoughts, his mind, his life, just with her gentle hum. -No Doubt, Different People, from Tragic Kingdom 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
Re: SQL help from someone who groks c, please?
Dan Mahoney, System Admin wrote: I assume you mean the core SQL module, and nothing driver specific, yes? Yep. The relevant code is in rlm_sql.c so it's driver-independent. 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? Sorry my bad - should have been more precise. The code I am looking at is a checkout of HEAD. I'm not familiar with the dev/release plans for 2.0 - hopefully one of the devs will chip in here. 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). Very good point - removing the where username= bit from the group membership query would return each group in turn (ordered by precedence). ...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). I hazily recall a discussion w.r.t. MySQL and (I think) the SQL_MULTI_STATEMENT option not being on in the driver, but here we reach the limit of my knowledge. As far as I can tell from the source, rlm_sql just passes the xlat'ed string to the driver, so any handling of lack therefore of multiple statements would be in there. -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 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL help from someone who groks c, please?
On Mon 05 Feb 2007 10:56, Phil Mayers wrote: Dan Mahoney, System Admin wrote: I assume you mean the core SQL module, and nothing driver specific, yes? Yep. The relevant code is in rlm_sql.c so it's driver-independent. 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? Sorry my bad - should have been more precise. The code I am looking at is a checkout of HEAD. I'm not familiar with the dev/release plans for 2.0 - hopefully one of the devs will chip in here. Hopefully soon! I am pushing Alan to release in the near future, and I think he is coming around :-) I have been running CVS head in production for close to 6 months now and we have successfully closed all the bugs that have affected me (Zero memory leaks or crashes this month. Yay!). I run rlm_sql_postgresql, rlm_acctlog, rlm_exec, radrelay, rlm_sqlippool and rlm_detail in a fairly complex, high volume setup including a number of proxy servers. I am still doing minor cleanups and feature additions on rlm_sqlippool but I have moved it to the stable modules list as it has proven itself (at least in my setup on postgresql). Oh, I also us attribute filters pre and post proxy.. (They rock!) I will be turning some attention onto rlm_python this week also as I want to put it into production on a couple of servers. Cheers -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc pgpLwnLeG28zI.pgp Description: PGP signature - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL help from someone who groks c, please?
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
