Re: NAS-IP-Address in mysql
On Thu, 15 Feb 2007, VeNoMouS wrote: > Hi guys After doing some tests, I just discovered that I cant have more then > one NAS-IP-Address in radgroupcheck (it seems to ignore the others) does > anyone know of a work around as i dont want to use the huntgroup file (makes > it kinda anonying since im doing a web frontend for administration). this is getting to be a really common question :) Yes, you need to embed the nas-ip-address as part of your query (in the WHERE clause (same way as you match the username) so only the matching items are returned. -Dan > > > Cheers > > > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html > -- "I can feel it, comin' back again...Like a rolling thunder chasin' the wind..." -Dan Mahoney, JS, JB & SL, May 10th, 1997, Approx 1AM 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?
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?
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
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
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 usernameattribute value op 1 jeremy passwordX == 2 jeremy nas-ip-address "vpnsvr":= 3 jeremy passwordy == 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 usernameattribute value op 1 jeremy passwordX == 1 jeremy nas-ip-address "vpnsvr":= 2 jeremy passwordy == 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: 13735
Advanced SQL Auth/Generate clients.conf from SQL?
Hey all, Two questions, related to SQL... 1: I recently became enamored by the power of SQL, and while I find no easy way through SQL to do multiple check-items easily in a logical fall-throughable order. I.e. through SQL how would one do (for an entry level tech): 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 I see an easy way to return items based on group membership (which seems to be rather 1:n right now, i.e. it doesn't appear that a person can be a member of more than one group). With the standard tables. I.e. I'm not sure how the various items are "linked" and ordered where you are able to have multiple instances of the same usernames, but varying other auth attributes. I do think I've found a happy medium (albeit with a more complex join/union system on the backend) -- but it involves embedding a lot of values into the query, which shouldn't be the case (I couldn't find a really-advanced example anywhere -- if anyone has one, I would LOVE to see it (as it translates to a "normal" users file. I'm not using this for dialup, instead we're using it for network management (because RADIUS is a good common denominator, far better than tac+). For that reason, we're going to have a lot of specific instances where we do (or don't) want people to have very specific types of access. 2: My second question is, now that I've got a list of all my NASes in SQL, has anyone written (or added to beta or something similar) code to do *those* via SQL? Specifically we already have all our network devices (and shared secrets) in a SQL database anyway, and it would be cool to use them in realtime. I could just as easily bang together a perl script for this, and throw it in a crontab with a HUP tool (every hour or whatnot) -- and I would be happy to contibute such a script, but I'd like to know I'm not reinventing the wheel (especially because SQL-realtime is so much cooler than "once an hour"). Any ideas GREATLY appreciated. -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
