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@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
>>
_______________________________________________
radiator mailing list
radiator@open.com.au
http://www.open.com.au/mailman/listinfo/radiator
