Freeradius3 + SQL -> radusergroup check is not matched

[Martin Bednar]

Hello,

I'm struggling with this issue for couple days so any help would be appreciated

I'm running 3.0.13 version with mariadb as backend

# rpm -aq | grep -i radius
freeradius-config-3.0.13-19.1.x86_64
freeradius-3.0.13-19.1.x86_64
freeradius-utils-3.0.13-19.1.x86_64
freeradius-mysql-3.0.13-19.1.x86_64

basic authentication is working fine, however I have problem with
groupcheck. What I'm trying to achieve is to limit user per SSID. I
have created 2 profiles with appropriate reply actions

MariaDB [radius]> select * from radgroupcheck;
+----+---------------------------+-------------------+----+----------------------+
| id | groupname                 | attribute         | op | value
          |
+----+---------------------------+-------------------+----+----------------------+
|  1 | Reject-Profile            | Auth-Type         | := | Reject
          |
|  6 | SSID_EMPL-Test     | Aruba-Essid-Name  | == | EMPL-Test            |

MariaDB [radius]> select * from radgroupreply;
+----+---------------------------+---------------+----+--------+
| id | groupname                 | attribute     | op | value  |
+----+---------------------------+---------------+----+--------+
|  1 | Reject-Profile            | Auth-Type     | := | Reject |
|  6 | SSID_EMPL-Test            | Reply-Message | =  | Vitaj  |


and assigned them to the user


MariaDB [radius]> select * from radusergroup;
+-----------------------+---------------------------+----------+
| username              | groupname                 | priority |
+-----------------------+---------------------------+----------+
| miro                  | SSID_EMPL-Test            |        1 |
| miro                  | Reject-Profile            |        2 |
+-----------------------+---------------------------+----------+

Expectation is that Radius will check whether user is connecting to
the SSID "EMPL-Test" if so it will be accepted otherwise it will go to
Reject profile and request will be rejected.

Problem is that user is always rejected even when is connecting to the
correct SSID.

(6) Received Access-Request Id 9 from 10.200.99.38:56327 to
10.1.20.5:1812 length 258
(6)   User-Name = "miro"
(6)   NAS-IP-Address = 10.200.99.38
(6)   NAS-Port = 0
(6)   NAS-Identifier = "10.200.99.38"
(6)   NAS-Port-Type = Wireless-802.11
(6)   Calling-Station-Id = "606720cbfb52"
(6)   Called-Station-Id = "84d47ec68cda"
(6)   Service-Type = Login-User
(6)   Framed-MTU = 1100
(6)   EAP-Message =
0x0207002b190017030100205b4dc9fa8209387ae85ddd94e674431e428c4baacb2f58cc7c14d4d310106e6d
(6)   State = 0x97bd745292ba6df3cc8378af9230d359
(6)   Aruba-Essid-Name = "EMPL-Test"
(6)   Aruba-Location-Id = "84:d4:7e:c6:8c:da"
(6)   Aruba-AP-Group = "instant-C6:8C:DA"
(6)   Aruba-Device-Type = "Win 7"

We can see in the output that Aruba-Essid-Name = "EMPL-Test"

but in the SQL part there is no match on that profile, instead of that
it is going to next one Reject-profile and rejecting the request:

(7) sql: EXPAND %{User-Name}
(7) sql:    --> miro
(7) sql: SQL-User-Name set to 'miro'
rlm_sql (sql): Reserved connection (1)
(7) sql: EXPAND SELECT id, username, attribute, value, op FROM
radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id
(7) sql:    --> SELECT id, username, attribute, value, op FROM
radcheck WHERE username = 'miro' ORDER BY id
(7) sql: Executing select query: SELECT id, username, attribute,
value, op FROM radcheck WHERE username = 'miro' ORDER BY id
(7) sql: User found in radcheck table
(7) sql: Conditional check items matched, merging assignment check items
(7) sql:   Cleartext-Password := "miro"
(7) sql:   Expiration := "May 31 2017 00:00:00 CEST"
(7) sql: EXPAND SELECT id, username, attribute, value, op FROM
radreply WHERE username = '%{SQL-User-Name}' ORDER BY id
(7) sql:    --> SELECT id, username, attribute, value, op FROM
radreply WHERE username = 'miro' ORDER BY id
(7) sql: Executing select query: SELECT id, username, attribute,
value, op FROM radreply WHERE username = 'miro' ORDER BY id
(7) sql: EXPAND SELECT groupname FROM radusergroup WHERE username =
'%{SQL-User-Name}' ORDER BY priority
(7) sql:    --> SELECT groupname FROM radusergroup WHERE username =
'miro' ORDER BY priority
(7) sql: Executing select query: SELECT groupname FROM radusergroup
WHERE username = 'miro' ORDER BY priority
(7) sql: User found in the group table
(7) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM
radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id
(7) sql:    --> SELECT id, groupname, attribute, Value, op FROM
radgroupcheck WHERE groupname = 'SSID_EMPL-Test' ORDER BY id
(7) sql: Executing select query: SELECT id, groupname, attribute,
Value, op FROM radgroupcheck WHERE groupname = 'SSID_EMPL-Test' ORDER
BY id
(7) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM
radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id
(7) sql:    --> SELECT id, groupname, attribute, Value, op FROM
radgroupcheck WHERE groupname = 'Reject-Profile' ORDER BY id
(7) sql: Executing select query: SELECT id, groupname, attribute,
Value, op FROM radgroupcheck WHERE groupname = 'Reject-Profile' ORDER
BY id
(7) sql: Group "Reject-Profile": Conditional check items matched
(7) sql: Group "Reject-Profile": Merging assignment check items
(7) sql:   Auth-Type := Reject
(7) sql: EXPAND SELECT id, groupname, attribute, value, op FROM
radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id
(7) sql:    --> SELECT id, groupname, attribute, value, op FROM
radgroupreply WHERE groupname = 'Reject-Profile' ORDER BY id
(7) sql: Executing select query: SELECT id, groupname, attribute,
value, op FROM radgroupreply WHERE groupname = 'Reject-Profile' ORDER
BY id
(7) sql: Group "Reject-Profile": Merging reply items
(7) sql:   Auth-Type := Reject
rlm_sql (sql): Released connection (1)


What I don't really understand is that if I do the test with radclient
I got Accept:

# echo "User-Name=miro,User-Password=miro,Aruba-Essid-Name=EMPL-Test"
| radclient localhost:1812 auth test123
Sent Access-Request Id 148 from 0.0.0.0:35642 to 127.0.0.1:1812 length 61
Received Access-Accept Id 148 from 127.0.0.1:1812 to 0.0.0.0:0 length 33

#radiusd -X

(9) Received Access-Request Id 225 from 127.0.0.1:41535 to
127.0.0.1:1812 length 61
(9)   User-Name = "miro"
(9)   User-Password = "miro"
(9)   Aruba-Essid-Name = "EMPL-Test"
...
(9) sql: EXPAND SELECT id, username, attribute, value, op FROM
radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id
(9) sql:    --> SELECT id, username, attribute, value, op FROM
radcheck WHERE username = 'miro' ORDER BY id
(9) sql: Executing select query: SELECT id, username, attribute,
value, op FROM radcheck WHERE username = 'miro' ORDER BY id
(9) sql: User found in radcheck table
(9) sql: Conditional check items matched, merging assignment check items
(9) sql:   Cleartext-Password := "miro"
(9) sql:   Expiration := "May 31 2017 00:00:00 CEST"
(9) sql: EXPAND SELECT id, username, attribute, value, op FROM
radreply WHERE username = '%{SQL-User-Name}' ORDER BY id
(9) sql:    --> SELECT id, username, attribute, value, op FROM
radreply WHERE username = 'miro' ORDER BY id
(9) sql: Executing select query: SELECT id, username, attribute,
value, op FROM radreply WHERE username = 'miro' ORDER BY id
(9) sql: EXPAND SELECT groupname FROM radusergroup WHERE username =
'%{SQL-User-Name}' ORDER BY priority
(9) sql:    --> SELECT groupname FROM radusergroup WHERE username =
'miro' ORDER BY priority
(9) sql: Executing select query: SELECT groupname FROM radusergroup
WHERE username = 'miro' ORDER BY priority
(9) sql: User found in the group table
(9) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM
radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id
(9) sql:    --> SELECT id, groupname, attribute, Value, op FROM
radgroupcheck WHERE groupname = 'SSID_EMPL-Test' ORDER BY id
(9) sql: Executing select query: SELECT id, groupname, attribute,
Value, op FROM radgroupcheck WHERE groupname = 'SSID_EMPL-Test' ORDER
BY id
(9) sql: Group "SSID_EMPL-Test": Conditional check items matched
(9) sql: Group "SSID_EMPL-Test": Merging assignment check items
(9) sql: EXPAND SELECT id, groupname, attribute, value, op FROM
radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id
(9) sql:    --> SELECT id, groupname, attribute, value, op FROM
radgroupreply WHERE groupname = 'SSID_EMPL-Test' ORDER BY id
(9) sql: Executing select query: SELECT id, groupname, attribute,
value, op FROM radgroupreply WHERE groupname = 'SSID_EMPL-Test' ORDER
BY id
(9) sql: Group "SSID_EMPL-Test": Merging reply items
(9) sql:   Reply-Message = "Vitaj"


It seems that in both cases is Aruba-Essid-Name = "EMPL-Test" the same
so I don't understand the difference in behaviour. I tried also
different attribute but with the same result. I changed AP type also
the same result.

Any idea what could be the problem ?

Thank you,

-- 
Martin