[RADIATOR] check-items in chained authby queries
Michael
ringo at vianet.ca
Thu Feb 3 22:29:07 CST 2011
oh, and, you may not want to stop there. you may want to find out why %0 and %1
don't work. I think it should as per source code and manual. Since you are
using the 2 '?' the order is very important. You can't change the order of:
WHERE username=? AND groupname=?
to this:
WHERE groupname=? AND username=?
..cause it will not work and will break your setup.
Maybe the radiator coders can check out the source. i'm sure they'll see these
emails. To me, the source looks fine.
Michael
On Thu, 3 Feb 2011, Linuxchuck wrote:
> Michael,
>
> I have version 4.7 based on the log output during startup.
>
> Your solution works! I really appreciate your assistance on all this. Now to finish working out a way to add the proper reply type based on group membership, and I can call my eval "done", and push the move to production. I'm so ready to get rid of our windows-based RADIUS server, and this one is looking like it is exactly what we need.
>
> You've been a great help.
>
> Respectfully,
> Chuck
>
>
> On 02/03/2011 08:17 PM, Michael wrote:
>>
>> the version, 4.7, that i have, it looks like the GroupMembershipQuery should honor %0 and %1 and replace them with the 'sql quoted user name' and 'sql quoted group name' as per the manual as well.
>>
>> But, as per your debug, it looks like it's using 'bind variables', so i think sql would replace the question marks (?) with these bind variables.
>>
>> What version do you have?
>>
>> let me know how using 2 '?' instead of %0 and %1 goes.
>>
>> Michael
>>
>>
>> formats the query in AuthSQL.pm, and passes the user/group as @extras:
>> ----------------------------------------------------------------------
>> my $q = &Radius::Util::format_special($self->{GroupMembershipQuery}, $p,
>> $self, $qusername, $qgroupname);
>>
>> format routine:
>> ---------------
>> sub format_special
>> {
>> my ($s, $p, $self, @extras) = @_;
>> ...
>>
>> and formats here, replacing all %(digits) with values from @extras:
>> -------------------------------------------------------------------
>> $s =~ s/%([%aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTUuvVwWxXyYzZ]|\d+)/$1 =~ m@(^\d+)@ ? $extras[$1] : &{$conversions{$1}}($p)/egs;
>>
>>
>>
>>
>>
>> On Thu, 3 Feb 2011, Michael wrote:
>>
>>>
>>> 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 at open.com.au
>>>>>>>> http://www.open.com.au/mailman/listinfo/radiator
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>
>>> _______________________________________________
>>> radiator mailing list
>>> radiator at open.com.au
>>> http://www.open.com.au/mailman/listinfo/radiator
>>>
>
More information about the radiator
mailing list