SQL help from someone who groks c, please?
Hey all... Disclaimer: If I should ask this on the devel list, let me know and I will. As I'm not qualified in the languages to be a developer, this seemed the more logical place. I'm decent with Radius but have just come to freeradius over Cistron...I'm still in the process of assimilating the "op" syntax, but that's not the problem, I think. That said, I figured out two possible ways to handle my previous question regarding advanced SQL auth (including multiple occurances of the same 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". of b) The "id" field (which some of the docs say are unused) is used to "bind" multiple items having the same ID. i.e (citing my earlier example)... Jeremy nas-ip-address="the vpn server" password="x" Service-Type = "Framed-User" # Our switches, which we trust jeremy not to mess up Jeremy password = "y" Service-Type = "Admin-User" Jeremy nas-ip-address="a big powerful router" Service-Type = Reject If it's (a): Based on the standard values in the database, would this work: id username attribute value op 1 jeremy password X == 2 jeremy nas-ip-address "vpnsvr" := 3 jeremy password y == 4 jeremy password "z" == 5 jeremy nas-ip-address "router" := The problem I see with this one is that a) there's no easy way to keep your (presumably auto-increment) id field entries properly ordered. Also, line 4 above doesn't make sense -- there really ARE no check items that would work here. If line 4 above were deleted, it would instead match password=y, nas-ip-address=router (again, I may be missing something critical about operators and their signaling here). Also, based on this, you would have to then find which attributes in the reply table to ask, which could *possibly* be based on the id of the "password" attribute, but that's hardly normalized. If it's (b): id username attribute value op 1 jeremy password X == 1 jeremy nas-ip-address "vpnsvr" := 2 jeremy password y == 3 jeremy password "z" == 3 jeremy nas-ip-address "router" := This one implies you either have a keyless table (bad), or that there's another key value here, which isn't in the included schemas. It also implies that the server acutally LOOK AT the "id" value to know when to stop parsing. In this case your reply items would be pulling based on that "id" field, not the username at all (since in this case the username is not unique). /endif The assumption I worked with is: My knowledge of SQL is far better than my knowledge of the inner workings of freeradius-SQL. What I've done is I've had to hard code the NAS-IP address into my queries, coupled with some special tables to return the right values expected by certain network devices (for example), if it's a PDU, return the admin (or userlevel) service-type for those devices, if it's a switch, return the appropriate there -- even allowing our customers access to those PDU ports that they control, but this still isn't as flexible as an old-fashioned users file. In reality, I should be able to return ALL records related, and radius should pick up the differences in the check-items, instead of the query only returning a select few. This will make more sense if we decide we need to start checking more values than just the one. I've also had to UNION my queries together because employees and clients are in different tables (and I am only checking on employee access on some devices -- but this is also done through the SQL). I noticed, interestingly enough, that UNION works, but the semicolon does not (perhaps this is to prevent SQL injection?). As I am unable to grok the c, I can't be sure. I.e. at the moment there's about forty users, some of which have differing access than others, and there's no real radius "groups", since each person's needs are slightly different and vary based on what each person has been certified and tested on. Apologies for seeming long. If anyone has any ideas, let me know, please. I may be able to compensate someone for an hour or two of consultation time, if it gets the questions answered (please contact me off-list regarding this). -Dan -- --------Dan Mahoney-------- Techie, Sysadmin, WebGeek Gushi on efnet/undernet IRC ICQ: 13735144 AIM: LarpGM Site: http://www.gushi.org ---------------------------
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.
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 ---------------------------
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
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
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... Sent from the FreeRadius - User mailing list archive at Nabble.com.
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 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?
-- "A single death is a tragedy. A million deaths is a statistic." -Josef Stalin, As quoted on the cover to Savatage's "Dead Winter Dead" --------Dan Mahoney-------- Techie, Sysadmin, WebGeek Gushi on efnet/undernet IRC ICQ: 13735144 AIM: LarpGM Site: http://www.gushi.org ---------------------------
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?
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.
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? -- View this message in context: http://www.nabble.com/SQL-help-from-someone-who-groks-c%2C-please--tf3172009... Sent from the FreeRadius - User mailing list archive at Nabble.com.
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 ---------------------------
participants (4)
-
ChristosH -
Dan Mahoney, System Admin -
Peter Nixon -
Phil Mayers