instead of: roupMembershipQuery SELECT groupname FROM v_usergroups WHERE username=%0 AND groupname=%1
try: roupMembershipQuery SELECT groupname FROM v_usergroups WHERE username=? AND groupname=? On Thu, 3 Feb 2011, Linuxchuck wrote: > Michael, > > Ok, I gave it a shot, and got some completely different results. Thanks for > the suggestion. The order of check items is certainly taken into account, > which I should have thought of. However, the error I am receiving is a > little strange. All I have done is changed the order of the two check items. > Now I am getting an error that looks to be more of a Perl error than a > Radiator error. > > Here is the debug log: > > Thu Feb 3 17:45:45 2011: DEBUG: Packet dump: > *** Received from 192.168.xxx.xxx port 1645 .... > Code: Access-Request > Identifier: 47 > Authentic: **************************************** > Attributes: > User-Name = "testuser" > User-Password = ****************************************** > NAS-Port = 1 > NAS-Port-Id = "tty1" > NAS-Port-Type = Virtual > Calling-Station-Id = "192.168.yyy.yyy" > NAS-IP-Address = 192.168.xxx.xxx > > Thu Feb 3 17:45:45 2011: DEBUG: Handling request with Handler > 'Realm=DEFAULT', Identifier '' > Thu Feb 3 17:45:45 2011: DEBUG: Deleting session for testuser, > 192.168.xxx.xxx, 1 > Thu Feb 3 17:45:45 2011: DEBUG: Handling with Radius::AuthGROUP: AuthSQLUSR > Thu Feb 3 17:45:45 2011: DEBUG: Handling with Radius::AuthSQL: > Thu Feb 3 17:45:45 2011: DEBUG: Handling with Radius::AuthSQL: > Thu Feb 3 17:45:45 2011: DEBUG: Query is: 'select PASSWORD, > 'GroupList="group1 group2 group3 group4 group5"', 'AuthType=AuthHOTP' from > SUBSCRIBERS where USERNAME='testuser'': > Thu Feb 3 17:45:45 2011: DEBUG: Radius::AuthSQL looks for match with > testuser [testuser] > Thu Feb 3 17:45:45 2011: DEBUG: Query is: 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group1'': testuser > group1 > Thu Feb 3 17:45:45 2011: ERR: Execute failed for 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group1'': called with 2 > bind variables when 0 are needed > Thu Feb 3 17:45:45 2011: ERR: Execute failed for 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group1'': called with 2 > bind variables when 0 are needed > Thu Feb 3 17:45:45 2011: DEBUG: Query is: 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group2'': testuser > group2 > Thu Feb 3 17:45:45 2011: ERR: Execute failed for 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group2'': called with 2 > bind variables when 0 are needed > Thu Feb 3 17:45:45 2011: ERR: Execute failed for 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group2'': called with 2 > bind variables when 0 are needed > Thu Feb 3 17:45:45 2011: DEBUG: Query is: 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group3'': testuser > group3 > Thu Feb 3 17:45:45 2011: ERR: Execute failed for 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group3'': called with 2 > bind variables when 0 are needed > Thu Feb 3 17:45:45 2011: ERR: Execute failed for 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group3'': called with 2 > bind variables when 0 are needed > Thu Feb 3 17:45:45 2011: DEBUG: Query is: 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group4'': testuser > group4 > Thu Feb 3 17:45:45 2011: ERR: Execute failed for 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group4'': called with 2 > bind variables when 0 are needed > Thu Feb 3 17:45:45 2011: ERR: Execute failed for 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group4'': called with 2 > bind variables when 0 are needed > Thu Feb 3 17:45:45 2011: DEBUG: Query is: 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group5'': testuser > group5 > Thu Feb 3 17:45:45 2011: ERR: Execute failed for 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group5'': called with 2 > bind variables when 0 are needed > Thu Feb 3 17:45:45 2011: ERR: Execute failed for 'SELECT groupname FROM > v_usergroups WHERE username='testuser' AND groupname='group5'': called with 2 > bind variables when 0 are needed > Thu Feb 3 17:45:45 2011: DEBUG: Radius::AuthSQL REJECT: User testuser is not > in any group in GroupList: testuser [testuser] > Thu Feb 3 17:45:45 2011: DEBUG: Query is: 'select PASSWORD, > 'GroupList="group1 group2 group3 group4 group5"', 'AuthType=AuthHOTP' from > SUBSCRIBERS where USERNAME='DEFAULT'': > Thu Feb 3 17:45:45 2011: DEBUG: Radius::AuthGROUP:AuthSQLUSR result: > REJECT, User testuser is not in any group in GroupList > Thu Feb 3 17:45:45 2011: DEBUG: AuthBy GROUP result: REJECT, User testuser > is not in any group in GroupList > Thu Feb 3 17:45:45 2011: INFO: Access rejected for testuser: User testuser > is not in any group in GroupList > > > If I cut-and-paste the query from the debug logs into a database query, it > returns "group1" as the sole result, indicating that testuser is indeed a > member. However, it appears that Radiator does not agree. > > Any further thoughts? I appear to be getting closer to my goals, and > appreciate your input. > > Chuck > > > On 02/03/2011 04:58 PM, Michael wrote: >> ah ok, i see. the AuthSQL specifies "Auth-Type=AuthHOTP". Never done this >> type of setup before, but maybe the 'Auth-Type=AuthHOTP' in the sql query >> should be after the 'GroupList="Group1 Group2 Group3"?? Again, not sure, >> but I would think the 'check' is done in order. it sounds like you want to >> do the group list check first before checking the AuthHOTP. I don't see any >> config in the AuthHOTP section though. >> >> Sorry, I'm reaching/guessing a little. >> >> >> Michael >> >> >> On 11-02-03 03:11 PM, Linuxchuck wrote: >>> Hi Michael, Thanks for the response. >>> >>> Actually, it does hit the AuthHOTP section. I should have put a little >>> more emphasis on the fact that there is an "AuthType=AuthHOTP" for the user >>> when it is looked up in the database. I did mention that, but it was kind >>> of jammed into the beginning, and was probably easy to miss. >>> >>> Here is the "slightly sanitized" debug output indicating AuthHOTP was >>> indeed used: >>> >>> Thu Feb 3 13:54:57 2011: DEBUG: Handling request with Handler >>> 'Realm=DEFAULT', Identifier '' >>> Thu Feb 3 13:54:57 2011: DEBUG: Deleting session for testuser, >>> 192.168.xxx.xxx, 1 >>> Thu Feb 3 13:54:57 2011: DEBUG: Handling with Radius::AuthGROUP: AuthSQL >>> Thu Feb 3 13:54:57 2011: DEBUG: Handling with Radius::AuthSQL: >>> Thu Feb 3 13:54:57 2011: DEBUG: Handling with Radius::AuthSQL: >>> Thu Feb 3 13:54:57 2011: DEBUG: Query is: 'select PASSWORD, >>> 'AuthType=AuthHOTP', 'GroupList="group1 group2 group3 group4 group5"' from >>> SUBSCRIBERS where USERNAME='testuser'': >>> Thu Feb 3 13:54:57 2011: DEBUG: Radius::AuthSQL looks for match with >>> testuser [testuser] >>> Thu Feb 3 13:54:57 2011: DEBUG: Handling with Radius::AuthGROUP: AuthHOTP >>> Thu Feb 3 13:54:57 2011: DEBUG: Handling with Radius::AuthSQLHOTP: >>> Thu Feb 3 13:54:57 2011: DEBUG: Radius::AuthSQLHOTP looks for match with >>> testuser [testuser] >>> Thu Feb 3 13:54:57 2011: WARNING: This AuthBy does not know how to get >>> user Groups >>> Thu Feb 3 13:54:57 2011: WARNING: This AuthBy does not know how to get >>> user Groups >>> Thu Feb 3 13:54:57 2011: WARNING: This AuthBy does not know how to get >>> user Groups >>> Thu Feb 3 13:54:57 2011: WARNING: This AuthBy does not know how to get >>> user Groups >>> Thu Feb 3 13:54:57 2011: WARNING: This AuthBy does not know how to get >>> user Groups >>> Thu Feb 3 13:54:57 2011: DEBUG: Radius::AuthSQLHOTP REJECT: User testuser >>> is not in any group in GroupList: testuser [testuser] >>> Thu Feb 3 13:54:57 2011: DEBUG: Radius::AuthGROUP:AuthHOTP result: >>> REJECT, User testuser is not in any group in GroupList >>> Thu Feb 3 13:54:57 2011: DEBUG: Radius::AuthSQL REJECT: User testuser is >>> not in any group in GroupList: testuser [testuser] >>> Thu Feb 3 13:54:57 2011: DEBUG: Query is: 'select PASSWORD, >>> 'AuthType=AuthHOTP', 'GroupList="group1 group2 group3 group4 group5"' from >>> SUBSCRIBERS where USERNAME='DEFAULT'': >>> Thu Feb 3 13:54:57 2011: DEBUG: Radius::AuthGROUP:AuthSQLUSR result: >>> REJECT, User testuser is not in any group in GroupList >>> Thu Feb 3 13:54:57 2011: DEBUG: AuthBy GROUP result: REJECT, User testuser >>> is not in any group in GroupList >>> Thu Feb 3 13:54:57 2011: INFO: Access rejected for testuser: User testuser >>> is not in any group in GroupList >>> >>> Thanks! >>> >>> On 02/03/2011 01:43 PM, Michael wrote: >>>> >>>> your "AuthBy GROUP AuthSQL" will not flow down into the "AuthBy GROUP >>>> AuthHOTP". I don't think the AuthHOTP will be used at all in this config. >>>> >>>> Look like you need an "AuthBy AuthHOTP" in the AuthSQL config, like this: >>>>> <AuthBy GROUP> >>>>> Identifier AuthSQL >>>>> AuthByPolicy ContinueWhileAccept >>>>> <AuthBy SQL> >>>>> GroupMembershipQuery SELECT groupname FROM >>>>> v_usergroups WHERE username=%0 AND groupname=%1 >>>>> AuthSelect select PASSWORD, 'Auth-Type=AuthHOTP', >>>>> 'GroupList="Group1 Group2 Group3"' from SUBSCRIBERS where USERNAME=%0 >>>>> AuthColumnDef 0, Class, request >>>>> AuthColumnDef 1, GENERIC, check >>>>> AuthColumnDef 2, GENERIC, check >>>>> </AuthBy> >>>> >>>> # now call the AuthHOTP >>>> AuthBy AuthHOTP >>>> >>>>> </AuthBy GROUP> >>>> >>>> >>>> Michael >>>> >>>> >>>> On 11-02-03 02:34 PM, Linuxchuck wrote: >>>>> Hello again, >>>>> >>>>> I am attempting to validate both the username and appropriate group >>>>> membership via MySQL on an incoming access-request before bothering to >>>>> process the HOTP password provided. If the username doesn't exist, or >>>>> the user is not a member of the group in the list provided, send a reject >>>>> and stop processing. >>>>> >>>>> The problem I run into is that the grouplist check appears to be >>>>> performed by the 2nd AuthBy clause, which fails because HOTP is not >>>>> capable of checking groups. I would like for the group check to occur >>>>> prior to the HOTP check. >>>>> >>>>> Here is my config layout so far: >>>>> >>>>> FYI: The user entry in MySQL provides a check-item of >>>>> "Auth-Type=AuthHOTP" >>>>> >>>>> <AuthBy GROUP> >>>>> Identifier AuthSQL >>>>> AuthByPolicy ContinueWhileAccept >>>>> <AuthBy SQL> >>>>> GroupMembershipQuery SELECT groupname FROM >>>>> v_usergroups WHERE username=%0 AND groupname=%1 >>>>> AuthSelect select PASSWORD, 'Auth-Type=AuthHOTP', >>>>> 'GroupList="Group1 Group2 Group3"' from SUBSCRIBERS where USERNAME=%0 >>>>> AuthColumnDef 0, Class, request >>>>> AuthColumnDef 1, GENERIC, check >>>>> AuthColumnDef 2, GENERIC, check >>>>> </AuthBy> >>>>> </AuthBy GROUP> >>>>> >>>>> <AuthBy GROUP> >>>>> Identifier AuthHOTP >>>>> <AuthBy SQLHOTP> >>>>> ... >>>>> </AuthBy> >>>>> </AuthBy GROUP> >>>>> >>>>> <Realm DEFAULT> >>>>> AuthBy AuthSQL >>>>> </Realm> >>>>> >>>>> I don't see any evidence that the Authby SQL is performing the group >>>>> check, and the log tells me "WARNING: This AuthBy does not know how to >>>>> get user Groups" under the HOTP section. >>>>> >>>>> Is there a way to accomplish what I'm after? >>>>> >>>>> Thanks! >>>>> >>>>> Chuck >>>>> _______________________________________________ >>>>> radiator mailing list >>>>> radiator@open.com.au >>>>> http://www.open.com.au/mailman/listinfo/radiator >>>>> >>>>> >>> >>> > _______________________________________________ radiator mailing list radiator@open.com.au http://www.open.com.au/mailman/listinfo/radiator
