Postgresql groupreply problem

[georg at wojti.de]

Alan DeKok schrieb am 31.08.2015 14:30:

> On Aug 31, 2015, at 4:15 AM, georg at wojti.de wrote:
>> I want to change the user MAC-authentication with an HP-Switch from the
>> users-file to postgresql. The first thing is, that I have a some lines which
>> check whether it is a Call-Check or not. If not then the radius has to reject
>> this. But I think it's ok if I put this into the users-file. I have written
>> the Cleartext-Password in this file, too, because I get an chap error (chap:
>> ERROR: &control:Cleartext-Password is required for authentication), when I put
>> this into the database.
> 
>  The server doesn't care where it gets the Cleartext-Password from.  It doesn't
>  matter if it's in a database, or in the "users" file
> 
>> In the Moment my configurations looks like this:
>> 
>> -------------- users file --------------
>> DEFAULT Service-Type != Call-Check, Auth-Type := Reject
>>        Fall-Through = Yes
> 
>  You should delete that "Fall-Through".  It does nothing useful.
> 
>> DEFAULT Cleartext-Password := "xxxXXXxxx"
>>        Fall-Through = Yes
>> 
>> DEFAULT
>>        Egress-VLANID := 0x3200005C   #<-- check in server.conf; reject if it's
>>        the same
> 
>  Why is this entry here?  It ALWAYS matches ALL incoming requests.  So...
>  you'll always reject all requests.
> 
>  That doesn't make any sense.
 
I have removed the files section. So the server has to test sql.
looks like this:

-------------- server.conf --------------
...
authorize {
       suffix
       if (Realm != "NULL") {
               reject
       }
       chap
       sql.sw-hp-mac-v3
}

I have read the Rlm_sql wiki many times but there is no resolution to my problem. I tried many differen constellations for Example

Test #1:
If "The user IS NOT found in radcheck" freeradius should start the group processing. In the usergroup table ist the username (in my case the MAC of the client) and the group, in groupcheck tabe is an entry with the Cleartext-Password and in the groupreply table is an enty for Egress-VLANID.

Test #2:
If "The user IS found in radcheck, the check items DO match AND Fall-Through is set in the radreply table" the group processing starts, too. In this case i have the Cleartext-Password in the check table and in the reply table is an entry with Fall-Through=Yes. In the usergroup table ist the MAC and the group,too, and in the groupreply table is an enty for Egress-VLANID. There is nothing to check for the groups so this table is empty.

Result for both:
The Group-Problem still exists!
1. Freeradius checks the usergroups and -> "rlm_sql_postgresql: query affected rows = 1 , fields = 5". If i test the sql-command manually, I get the Group "abc" -> this is OK
2. Next, freeradius calls the command "SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '%{Sql-Group}' ORDER BY id" but freeradius does not insert any group name into the sql-command. Why?!
   Looks like this "SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '' ORDER BY id"
And so no attribute from the group tables is checked. 

>> -------------- Database --------------
>> (Hope that the format is ok. The tables radreply and radgroupcheck are empty)
>> 
>> +----------------------------------------------------------------------+
>> | radcheck                                                             |
>> +----+-------------------+--------------------+----+-------------------+
>> | id | username          | attribute          | op | value             |
>> +----+-------------------+--------------------+----+-------------------+
>> |  0 | 00:11:22:33:44:55 | Calling-Station-Id | == | 00-11-22-33-44-55 |
>> +----+-------------------+--------------------+----+-------------------+
> 
>  Which does nothing other than check the Calling-Station-Id.  It does NOT add a
>  Cleartext-Password.
> 
>> +-----------------------------------------------+
>> | radusergroup                                  |
>> +----+-------------------+-----------+----------+
>> | id | username          | groupname | priority | 
>> +----+-------------------+-----------+----------+
>> |  0 | 00:11:22:33:44:55 |       abc |        0 |
>> +----+-------------------+-----------+----------+
>> 
>> +--------------------------------------------------+
>> | radgroupreply                                    |
>> +----+-----------+---------------+----+------------+
>> | id | groupname | attribute     | op | value      |
>> +----+-----------+---------------+----+------------+
>> |  0 |       abc | Egress-VLANID | := | 0x320000E0 |
>> +----+-----------+---------------+----+------------+
>> 
>> 
>> 
>> The main problem is that the freeradius does not insert the GroupName in the
>> sql-commands and so there is no correct reply.
> 
>  Because the radgroupcheck is empty.
> 
>  If you want to know how the SQL module works, go to the wiki, and search for
>  "rlm_sql".  It has an entire page devoted to explaining exactly how the module
>  works, including processing of radcheck / radreply, and radgroupcheck /
>  radgroupreply.
> 
>  i.e. this is documented.  Read the documentation, and you will fi the problem.
> 
>  Alan DeKok.
> 
> 
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html