sql auth problems with 2.0.0-pre
Gurus, may be i'm pulling some common mistake with my configuration being tested against cvs snapshot, but no idea which one. I've an sql profile telling: some.dotted.user Cleartext-Password = cisco NAS-IP-Address =~ "xxx.xxx.97.(85|86)" authentication request: User-Name = "some.dotted.user" User-Password = "cisco" Calling-Station-Id = "000000000000000" Framed-Protocol = PPP Service-Type = Framed-User NAS-IP-Address = xxx.xxx.97.85 gives the access-reject for unknown (for me) reason: rlm_sql (sqlauth): sql_set_user escaped user --> 'some.dotted.user' rlm_sql (sqlauth): Reserving sql socket id: 3 radius_xlat: 'SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'some.dotted.user' ORDER BY id' SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'some.dotted.user' ORDER BY id ... rlm_sql (sqlauth): Released sql socket id: 3 modcall[authorize]: module "sqlauth" returns ok for request 0 modcall: group authorize returns ok for request 0 rad_check_password: Found Auth-Type Local auth: type Local auth: No password configured for the user Login incorrect (No password configured for the user): [some.dotted.user/cisco] (from client localhost port 0 cli 00000000000000) auth: Failed to validate the user. I've checked the authorization sql query shown in debug - it properly returns the profile configured -- Sincerely Yours, Alexander
Alexander Serkin wrote:
Gurus, may be i'm pulling some common mistake with my configuration being tested against cvs snapshot, but no idea which one. I've an sql profile telling:
some.dotted.user Cleartext-Password = cisco NAS-IP-Address =~ "xxx.xxx.97.(85|86)"
Hmm I don't know how Cleartext-Password is mapped, always thought it was a legacy attribute. Try User-Password ? Also it's == not = for check items .
authentication request:
User-Name = "some.dotted.user" User-Password = "cisco" Calling-Station-Id = "000000000000000" Framed-Protocol = PPP Service-Type = Framed-User NAS-IP-Address = xxx.xxx.97.85
gives the access-reject for unknown (for me) reason:
rlm_sql (sqlauth): sql_set_user escaped user --> 'some.dotted.user' rlm_sql (sqlauth): Reserving sql socket id: 3 radius_xlat: 'SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'some.dotted.user' ORDER BY id' SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'some.dotted.user' ORDER BY id ... rlm_sql (sqlauth): Released sql socket id: 3 modcall[authorize]: module "sqlauth" returns ok for request 0 modcall: group authorize returns ok for request 0 rad_check_password: Found Auth-Type Local auth: type Local auth: No password configured for the user Login incorrect (No password configured for the user): [some.dotted.user/cisco] (from client localhost port 0 cli 00000000000000) auth: Failed to validate the user.
I've checked the authorization sql query shown in debug - it properly returns the profile configured
-- Arran Cudbard-Bell (ac221@sussex.ac.uk) Authentication Authorisation & Accounting Officer Infrastructure Services | ENG1 FF08 EXT:3900
Arran Cudbard-Bell wrote:
Alexander Serkin wrote:
Gurus, may be i'm pulling some common mistake with my configuration being tested against cvs snapshot, but no idea which one. I've an sql profile telling:
some.dotted.user Cleartext-Password = cisco NAS-IP-Address =~ "xxx.xxx.97.(85|86)"
Hmm I don't know how Cleartext-Password is mapped, always thought it was a legacy attribute.
Try User-Password ? Also it's == not = for check items .
Doesn't matter, Arran. Tried User-Password and '==' with the same result: module "sqlauth" returns ok but then: rad_check_password: Found Auth-Type Local auth: type Local auth: No password configured for the user -- Sincerely Yours, Alexander
Hi Alexander, On Thu, Apr 12, 2007 at 02:52:49PM +0400, Alexander Serkin wrote:
Doesn't matter, Arran. Tried User-Password and '==' with the same result: module "sqlauth" returns ok but then:
rad_check_password: Found Auth-Type Local auth: type Local auth: No password configured for the user
==> post your radiusd.conf; you probably explicitly overrides the result of sqlauth by setting the Auth-Type to Local somewhere in your config...
-- Sincerely Yours, Alexander - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Milan Holub holub (at) thenet (dot) ch -------------------------------------- TheNet-Internet Services AG, im Bernertechnopark, Morgenstr. 129 CH-3018, Bern, Switzerland 031 998 4333, Fax 031 998 4330 http://www.thenet.ch http://wlan.thenet.ch --------------------------------------
Milan Holub wrote:
Hi Alexander,
On Thu, Apr 12, 2007 at 02:52:49PM +0400, Alexander Serkin wrote:
Doesn't matter, Arran. Tried User-Password and '==' with the same result: module "sqlauth" returns ok but then:
rad_check_password: Found Auth-Type Local auth: type Local auth: No password configured for the user
==> post your radiusd.conf; you probably explicitly overrides the result of sqlauth by setting the Auth-Type to Local somewhere in your config...
yes i did. In users file: users: Matched entry DEFAULT at line 106: DEFAULT Huntgroup-Name == MSK, Realm == NULL, Auth-Type := Local Changed the line to DEFAULT Huntgroup-Name == MSK, Realm == NULL and added pap to the end of authorize section. Now with different negative result: modcall[authorize]: module "sqlauth" returns ok for request 0 rlm_pap: WARNING! No "known good" password found for the user. Authentication may fail because of this. modcall[authorize]: module "pap" returns noop for request 0 modcall: group authorize returns ok for request 0 auth: No authenticate method (Auth-Type) configuration found for the request: Rejecting the user auth: Failed to validate the user. -- Sincerely Yours, Alexander
Alexander Serkin wrote:
yes i did. In users file: users: Matched entry DEFAULT at line 106:
DEFAULT Huntgroup-Name == MSK, Realm == NULL, Auth-Type := Local
Don't set Auth-Type. It's wrong, and it's breaking the server. DO tell the server what the users "known good" password is.
Changed the line to DEFAULT Huntgroup-Name == MSK, Realm == NULL
and added pap to the end of authorize section.
Now with different negative result:
modcall[authorize]: module "sqlauth" returns ok for request 0 rlm_pap: WARNING! No "known good" password found for the user. Authentication may fail because of this. modcall[authorize]: module "pap" returns noop for request 0 modcall: group authorize returns ok for request 0 auth: No authenticate method (Auth-Type) configuration found for the request: Rejecting the user auth: Failed to validate the user.
This is because the server didn't find a Cleartext-Password for the request. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
Arran Cudbard-Bell wrote:
Hmm I don't know how Cleartext-Password is mapped, always thought it was a legacy attribute.
No. It's new in 1.1.4 && following. See "man rlm_pap".
Try User-Password ? Also it's == not = for check items .
No. Use Cleartext-Password, and :=. Also check that the "pap" module is listed last in the "authorize" section. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
Alan DeKok wrote:
Arran Cudbard-Bell wrote:
Hmm I don't know how Cleartext-Password is mapped, always thought it was a legacy attribute.
No. It's new in 1.1.4 && following. See "man rlm_pap".
Try User-Password ? Also it's == not = for check items .
No. Use Cleartext-Password, and :=.
Oh oops. What was Cleartext-Password introduced for ? To support the output of the Auto header function in pap / ldap ? -- Arran Cudbard-Bell (ac221@sussex.ac.uk) Authentication Authorisation & Accounting Officer Infrastructure Services | ENG1 FF08 EXT:3900
Arran Cudbard-Bell wrote:
What was Cleartext-Password introduced for ?
Because putting "User-Password" in the "users" file was wrong. User-Password is an attribute that goes in an Access-Request. Cleartext-Password does not go in any packet. Instead, is an internal server configuration, that tells the server what the users "known good" password is. The server then uses Cleartext-Password to compare to User-Password for PAP. Or, it hashes Cleartext-Password for CHAP. Or, it hashes it a different way for MS-CHAP.
To support the output of the Auto header function in pap / ldap ?
Partially, yes. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
Alexander Serkin wrote:
Gurus, may be i'm pulling some common mistake with my configuration being tested against cvs snapshot, but no idea which one. I've an sql profile telling:
some.dotted.user Cleartext-Password = cisco NAS-IP-Address =~ "xxx.xxx.97.(85|86)"
The problem is that regular expression check of NAS-IP-Address =~ "xxx.xxx.97.(85|86)" does not work. When i delete this check from sql it works, when i change the check to NAS-IP-Address == "xxx.xxx.97.85" it works too. What has changed since 1.1.5? The construction NAS-IP-Address =~ "xxx.xxx.97.(85|86)" did work for me there. In radiusd.conf we have: regular_expressions = yes extended_expressions = yes -- Sincerely Yours, Alexander
Alexander Serkin wrote:
The problem is that regular expression check of NAS-IP-Address =~ "xxx.xxx.97.(85|86)" does not work.
In the CVS head?
What has changed since 1.1.5?
The CVS head is massively re-written. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
Alan DeKok wrote:
Alexander Serkin wrote:
The problem is that regular expression check of NAS-IP-Address =~ "xxx.xxx.97.(85|86)" does not work.
In the CVS head?
Yes i played with CVS head today. Checked huge amount of regexp variants - none worked. -- Sincerely Yours, Alexander
Alexander Serkin wrote:
Alan DeKok wrote:
Alexander Serkin wrote:
The problem is that regular expression check of NAS-IP-Address =~ "xxx.xxx.97.(85|86)" does not work. In the CVS head?
Yes i played with CVS head today. Checked huge amount of regexp variants - none worked.
Yep can confirm this, .* and .+ matches though, .{4} also matches but .{5} doesn't... strange. Hmmmm. Seems only to be broken for ipaddr attributes. Still works with string attributes.... -- Arran Cudbard-Bell (ac221@sussex.ac.uk) Authentication Authorisation & Accounting Officer Infrastructure Services | ENG1 FF08 EXT:3900
Arran Cudbard-Bell wrote:
Seems only to be broken for ipaddr attributes.
Still works with string attributes....
OK, that helps. I did some profiling a while ago, and noticed that the server was printing IP addresses to strings all the time... even when they weren't used. The result was a significant waste of CPU time. The fix was to push the printing to the places that need it, like the regex matches. Maybe I missed one spot, I'll go check. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
Hi, got cvs tree today. The read_groups configuration check is not included in rlm_sql.c for some reason. Adding: {"read_groups", PW_TYPE_BOOLEAN, offsetof(SQL_CONFIG,read_groups), NULL, "yes"}, into static const CONF_PARSER module_config[] = { .. } helped a lot. Now my config from 1.1.6 is almost working. Thanks a lot. -- Sincerely Yours, Alexander
Hi, Is the "read_groups" configuration paramter reading strings intentionally removed from rlm_sql.c? Why? Let me suggest a patch: *** rlm_sql.c.orig 2007-05-15 14:10:35.000000000 +0400 --- rlm_sql.c 2007-06-18 19:46:59.000000000 +0400 *************** *** 57,62 **** --- 57,64 ---- offsetof(SQL_CONFIG,tracefile), NULL, SQLTRACEFILE}, {"readclients", PW_TYPE_BOOLEAN, offsetof(SQL_CONFIG,do_clients), NULL, "no"}, + {"read_groups", PW_TYPE_BOOLEAN, + offsetof(SQL_CONFIG,read_groups), NULL, "yes"}, {"deletestalesessions", PW_TYPE_BOOLEAN, offsetof(SQL_CONFIG,deletestalesessions), NULL, "yes"}, {"num_sql_socks", PW_TYPE_INTEGER, read_groups is checked on line 959 of rlm_sql.c, but it's not set anywhere before. thanks, -- Alexander
Alexander Serkin wrote:
Hi, Is the "read_groups" configuration paramter reading strings intentionally removed from rlm_sql.c? Why?
I don't think it was ever added. I'm not sure the functionality is even tested. i.e. Does it work? Alan DeKok.
Alan DeKok wrote:
Alexander Serkin wrote:
Hi, Is the "read_groups" configuration paramter reading strings intentionally removed from rlm_sql.c? Why?
I don't think it was ever added. I'm not sure the functionality is even tested.
i.e. Does it work?
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Read Groups in SQL ? Yes, very very well tested. It's horribly broken in 1.*.* though, or at least it was for me. Unfortunately whoever modified rlm_sql in cvs head chose a very inefficient querying system. First you query to pull out group membership, second you query to get each groups check items, then to get each groups reply items ... It just doesn't scale when a users a member of lots of groups. Previously you pulled out all the records for all the groups a user was a member of in two queries, one for check items and one for reply items.. --- Still think it would be a nice idea to have the option to disable single user lookups, SQL queries really are very expensive . -- Arran Cudbard-Bell (A.Cudbard-Bell@sussex.ac.uk) Authentication, Authorisation and Accounting Officer Infrastructure Services | ENG1 E1-1-08 University Of Sussex, Brighton EXT:01273 873900 | INT: 3900
Arran Cudbard-Bell wrote:
Alan DeKok wrote:
I don't think it was ever added. I'm not sure the functionality is even tested.
i.e. Does it work?
Alan DeKok.
Read Groups in SQL ? Yes, very very well tested. It's horribly broken in 1.*.* though, or at least it was for me.
Unfortunately whoever modified rlm_sql in cvs head chose a very inefficient querying system.
First you query to pull out group membership, second you query to get each groups check items, then to get each groups reply items ... It just doesn't scale when a users a member of lots of groups.
Previously you pulled out all the records for all the groups a user was a member of in two queries, one for check items and one for reply items..
Yes. It worked for me this way until at least 1.1.6. You are right, Alan, - read_grops configuration checks were not in 1.1.x also, but they worked somehow. Starting from 2.0.0-pre only user checks are performed by default. The only way to make groups to be checked was the supposed patch. Or adding "Fall-Through=yes" for all user profiles in radcheck table which is not good. -- Sincerely Yours, Alexander
Unfortunately whoever modified rlm_sql in cvs head chose a very inefficient querying system.
So change it - stored procedures maybe?
First you query to pull out group membership, second you query to get each groups check items, then to get each groups reply items ... It just doesn't scale when a users a member of lots of groups.
Previously you pulled out all the records for all the groups a user was a member of in two queries, one for check items and one for reply items..
Eh? I've got to strongly disagree with that - the old code was a DISASTROUS scheme. If you had 2 groups with check items: RESIDENCES: check: Calling-Station-Id ~ 192.168. reply: Filter-Id = resnet CONFERENCES: check: Calling-Station-Id ~ 10. reply: Filter-Id = conferencenet ...and "johndoe" was in BOTH, NEITHER of them would *ever* match. Merging the groups' check items was just idiotic. The new version is far, far better.
---
Still think it would be a nice idea to have the option to disable single
Not sure what you mean by that?
user lookups, SQL queries really are very expensive .
Expensive: we're doing ~260k authentications a day, each involving at least 1 SQL SELECT and 1 SQL INSERT and we've no problems. Hardware is nothing massively silly - dual-proc DL380 running both the SQL and Radius server.
Phil Mayers wrote:
Unfortunately whoever modified rlm_sql in cvs head chose a very inefficient querying system.
So change it - stored procedures maybe?
Stored procedures ... but then you lose the only advantage of using the SQL modules in authorisation, which is that it can evaluate the check items when assigning users to groups.
First you query to pull out group membership, second you query to get each groups check items, then to get each groups reply items ... It just doesn't scale when a users a member of lots of groups.
Previously you pulled out all the records for all the groups a user was a member of in two queries, one for check items and one for reply items..
Eh? I've got to strongly disagree with that - the old code was a DISASTROUS scheme. If you had 2 groups with check items:
RESIDENCES: check: Calling-Station-Id ~ 192.168. reply: Filter-Id = resnet
CONFERENCES: check: Calling-Station-Id ~ 10. reply: Filter-Id = conferencenet
...and "johndoe" was in BOTH, NEITHER of them would *ever* match. Merging the groups' check items was just idiotic. The new version is far, far better.
I agree the old way of doing things was just plain broken, but splitting the querying scheme into two parts, while making processing the SQL result far easier, is inefficient. You can pull out all rows relating to the groups a user is a member of, and process them on the RADIUS server using the group name as a key. This could be done in one query if you were feeling adventurous, or probably more easily with two. SELECT * FROM `radgroupcheck`,`radusergroup` WHERE radusergroup.UserName = '%{User-Name}' AND radgroupcheck.GroupName = radusergroup.GroupName SELECT * FROM `radgroupreply`,`radusergroup` WHERE radusergroup.UserName = '%{User-Name}' AND radgroupcheck.GroupName = radusergroup.GroupName This is far more scalable... Think of a user who belongs to 5 groups, we don't know which groups will actually be valid so all have to be pulled down from the SQL server ... Thats one radcheck one radreply one membership query. 5 check items queries. 5 reply items queries. If you've configured your server very efficiently you'll need to grab these at least twice for PEAP... So thats 26 Select queries every time that user authenticates... as opposed to 8, (or 4 without radcheck,radreply). Another possible alternative improvement using the current scheme,would be to bring all the rows relating to a group down, then evaluate them, and only continue querying if fall-through was true, or the check items didn't match.
Not sure what you mean by that?
Only do group lookups as opposed to checking for the users existence radcheck and radreply... Not everyone uses SQL for storing attributes in Authentication..... We use it because it provides a flexible means of overriding defaults in the users file, and allows special authorisation groups to be added, and memberships to be changed, without restarting the server.
user lookups, SQL queries really are very expensive .
Expensive: we're doing ~260k authentications a day, each involving at least 1 SQL SELECT and 1 SQL INSERT and we've no problems. Hardware is nothing massively silly - dual-proc DL380 running both the SQL and Radius server.
Our SQL server is running on the campus webserver, it's horribly overloaded and horribly slow. Your running your SQL server on the same box as the RADIUS server which gives you a significant advantage in that your queries aren't affected by network latency. Don't get me wrong, I appreciate you fixing the horribly broken SQL code, I was just suggesting some things that might improve efficiency. Thanks, Arran
You can pull out all rows relating to the groups a user is a member of, and process them on the RADIUS server using the group name as a key. This could be done in one query if you were feeling adventurous, or probably more easily with two.
That's a very reasonable suggestion, and would not I suspect be hard to code. However, I think it would prevent certain types of dynamic processing in the SQL side, so it would ideally be an optional configuration e.g. "bulk_query_groups = yes/no" or something.
Another possible alternative improvement using the current scheme,would be to bring all the rows relating to a group down, then evaluate them, and only continue querying if fall-through was true, or the check items didn't match.
Good suggestion - but it already does that. See lines 610 (the "for" loop dependent on the dofallthrough flag being 1) and 665/699 (setting of the dofallthrough flag based on the per-group Fall-Through reply items) of rlm_sql.c in todays CVS checkout. I'm fairly sure the CVS module has done that for months
Our SQL server is running on the campus webserver, it's horribly overloaded and horribly slow.
Then frankly I'm surprised you aren't experiencing more problems
Don't get me wrong, I appreciate you fixing the horribly broken SQL code, I was just suggesting some things that might improve efficiency.
I didn't fix it - the only code I've ever contributed to FreeRadius has been tiny 1-liners in the bug tracker. I was speaking up in support of whoever did.
participants (5)
-
Alan DeKok -
Alexander Serkin -
Arran Cudbard-Bell -
Milan Holub -
Phil Mayers