sql module behavior differs from 2.2.1 to 3.0.0 ?

Philippe MARASSE philippe.marasse at ch-poitiers.fr
Thu Oct 31 10:16:06 CET 2013


Le 24/10/2013 00:25, Arran Cudbard-Bell a écrit :
> On 23 Oct 2013, at 13:28, Philippe MARASSE <philippe.marasse at ch-poitiers.fr> wrote:
>
>> Le 23/10/2013 14:12, Arran Cudbard-Bell a écrit :
>>>> (2) sql :       expand: "%{User-Name}" -> '002324609e3f'
>>>> (2) sql : SQL-User-Name set to "002324609e3f"
>>>> rlm_sql (sql): Reserved connection (4)
>>>> (2) sql :       expand: "SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id" -> 'SELECT id, username, attribute, value, op FROM radcheck WHERE
>>>> username = '002324609E3F' ORDER BY id'
>>>> rlm_sql (sql): Executing query: 'SELECT id, username, attribute, value, op FROM radcheck WHERE username = '002324609E3F' ORDER BY id'
>>>> (2) sql :       expand: "SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority" -> 'SELECT groupname FROM radusergroup WHERE username = '002324609E3F' ORDER BY
>>>> priority'
>>>> rlm_sql (sql): Executing query: 'SELECT groupname FROM radusergroup WHERE username = '002324609E3F' ORDER BY priority'
>>>> rlm_sql (sql): Released connection (4)
>>>> (2)   [sql] = noop
>>> It’s consistent with the users file, which also returns noop if not entries match.
>>>
>>> Things like rlm_ldap are different because you’re looking for a specific object in the directory, so it’s ok to return notfound.
>>>
>>> I guess both rlm_files and rlm_sql could return notfound if no key matched, and noop if no entry matched. Do people think this would be a useful distinction?
>>>
>>> Arran Cudbard-Bell <a.cudbardb at freeradius.org>
>>> FreeRADIUS Development Team
>>>
>>> -
>>> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>> Thanks for your answer. Maybe I was mistaken to rely on sql return code in the authorize section ?
>>
>> If it's consistent with other modules, I'd rather modify my authenticate section to do a sql query in order to check the presence of the user, shouldn't I ?
> Um, nope. That’s not authentication…
>
> I’ve modified the behaviour to return notfound if the user *really* wasn’t found, as in there’s mention of the user at all, and there’s no default user, and there’s no profile user.
>
> If the user was found, but check items prevented the entry being used rlm_sql now returns NOOP, else if an entry matched it now returns OK.
>
> Also modified group processing a bit, so you can disable the check and reply queries if you want.
>
> Really rlm_sql should also return updated if it added any control or reply items, i’ll have a look at that tomorrow if I find some spare time.
>
> If the mean time i’d appreciate it if you could test v3.0.x or master to check behaviour is as you expect.

cloned master branch => not working, always noop returned if user is not found, is your 
patch commited on this branch ??

cloned v3.0.x branch => half success, as this user is not found :

(0) sql :       expand: "%{User-Name}" -> '002324609e3f'
(0) sql : SQL-User-Name set to "002324609e3f"
rlm_sql (sql): Reserved connection (4)
(0) sql :       expand: "SELECT id, username, attribute, value, op FROM radcheck WHERE 
username = '%{SQL-User-Name}' ORDER BY id" -> 'SELECT id, username, attribute, value, op 
FROM radcheck WHERE username = '002324609E3F' ORDER BY id'
rlm_sql (sql): Executing query: 'SELECT id, username, attribute, value, op FROM radcheck 
WHERE username = '002324609E3F' ORDER BY id'
(0) sql :       expand: "SELECT groupname FROM radusergroup WHERE username = 
'%{SQL-User-Name}' ORDER BY priority" -> 'SELECT groupname FROM radusergroup WHERE 
username = '002324609E3F' ORDER BY priority'
rlm_sql (sql): Executing query: 'SELECT groupname FROM radusergroup WHERE username = 
'002324609E3F' ORDER BY priority'
rlm_sql (sql): Released connection (4)
(0)   [sql] = notfound

but this one exists and get rejected in v3.0.1 :

(33) sql :      expand: "%{User-Name}" -> 'eca86bf20216'
(33) sql : SQL-User-Name set to "eca86bf20216"
rlm_sql (sql): Reserved connection (6)
(33) sql :      expand: "SELECT id, username, attribute, value, op FROM radcheck WHERE 
username = '%{SQL-User-Name}' ORDER BY id" -> 'SELECT id, username, attribute, value, op 
FROM radcheck WHERE username = 'eca86bf20216' ORDER BY id'
rlm_sql (sql): Executing query: 'SELECT id, username, attribute, value, op FROM radcheck 
WHERE username = 'eca86bf20216' ORDER BY id'
(33) sql : User found in radcheck table
(33) sql : Check items matched
(33) sql :      expand: "SELECT id, username, attribute, value, op FROM radreply WHERE 
username = '%{SQL-User-Name}' ORDER BY id" -> 'SELECT id, username, attribute, value, op 
FROM radreply WHERE username = 'eca86bf20216' ORDER BY id'
rlm_sql (sql): Executing query: 'SELECT id, username, attribute, value, op FROM radreply 
WHERE username = 'eca86bf20216' ORDER BY id'
(33) sql :      expand: "SELECT groupname FROM radusergroup WHERE username = 
'%{SQL-User-Name}' ORDER BY priority" -> 'SELECT groupname FROM radusergroup WHERE 
username = 'eca86bf20216' ORDER BY priority'
rlm_sql (sql): Executing query: 'SELECT groupname FROM radusergroup WHERE username = 
'eca86bf20216' ORDER BY priority'
rlm_sql (sql): Released connection (6)
(33)   [sql] = notfound

In v2.2.1 (database is shared between the two instances 3.0.1 & 2.2) :

[sql]   expand: SELECT id, username, attribute, value, op FROM radcheck           WHERE 
username = '%{SQL-User-Name}'           ORDER BY id -> SELECT id, username, attribute, 
value, op
      FROM radcheck           WHERE username = 'eca86bf20216'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, username, attribute, value, op           FROM 
radcheck           WHERE username = 'eca86bf20216'           ORDER BY id
[sql] User found in radcheck table
[sql]   expand: SELECT id, username, attribute, value, op FROM radreply           WHERE 
username = '%{SQL-User-Name}'           ORDER BY id -> SELECT id, username, attribute, 
value, op
      FROM radreply           WHERE username = 'eca86bf20216'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, username, attribute, value, op           FROM 
radreply           WHERE username = 'eca86bf20216'           ORDER BY id
[sql]   expand: SELECT groupname           FROM radusergroup           WHERE username = 
'%{SQL-User-Name}' ORDER BY priority -> SELECT groupname           FROM 
radusergroup           WHERE u
sername = 'eca86bf20216'           ORDER BY priority
rlm_sql_mysql: query:  SELECT groupname           FROM radusergroup           WHERE 
username = 'eca86bf20216' ORDER BY priority
[sql]   expand: SELECT id, groupname, attribute,           Value, op           FROM 
radgroupcheck           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT 
id, groupname, attribute,
          Value, op           FROM radgroupcheck           WHERE groupname = 
'pcs-miletrie'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, groupname, attribute, Value, op           FROM 
radgroupcheck           WHERE groupname = 'pcs-miletrie'           ORDER BY id
[sql] User found in group pcs-miletrie
[sql]   expand: SELECT id, groupname, attribute,           value, op           FROM 
radgroupreply           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT 
id, groupname, attribute,
          value, op           FROM radgroupreply           WHERE groupname = 
'pcs-miletrie'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, groupname, attribute, value, op           FROM 
radgroupreply           WHERE groupname = 'pcs-miletrie'           ORDER BY id
rlm_sql (sql): Released sql socket id: 1
++[sql] returns ok

It seems that group membership query got no result in v3.0.1 although it found some in 
v2.2.1 ?? I'm also using Huntgroups, MAC "users" are members of two groups, with different 
Huntgroup, here's results for queries from 2.2.1 :


mysql> SELECT id, username, attribute, value, op FROM radcheck           WHERE username = 
'eca86bf20216' ORDER BY id;
+------+--------------+--------------------+--------------+----+
| id   | username     | attribute          | value        | op |
+------+--------------+--------------------+--------------+----+
| 1327 | eca86bf20216 | Cleartext-Password | eca86bf20216 | := |
+------+--------------+--------------------+--------------+----+
1 row in set (0.00 sec)

mysql> SELECT id, username, attribute, value, op           FROM radreply           WHERE 
username = 'eca86bf20216'           ORDER BY id;
Empty set (0.00 sec)

mysql> SELECT groupname           FROM radusergroup           WHERE username = 
'eca86bf20216' ORDER BY priority;
+--------------+
| groupname    |
+--------------+
| pcs-miletrie |
| pcs-sites    |
+--------------+
2 rows in set (0.00 sec)

mysql>  SELECT id, groupname, attribute, Value, op           FROM radgroupcheck           
WHERE groupname = 'pcs-miletrie'           ORDER BY id;
+----+--------------+----------------+---------------+----+
| id | groupname    | attribute      | Value         | op |
+----+--------------+----------------+---------------+----+
| 18 | pcs-miletrie | Auth-Type      | EAP           | := |
| 19 | pcs-miletrie | Huntgroup-Name | LAN-Miletrie  | == |
| 23 | pcs-miletrie | EAP-Type       | MD5-Challenge | := |
+----+--------------+----------------+---------------+----+
3 rows in set (0.01 sec)

mysql> SELECT id, groupname, attribute,           value, op           FROM 
radgroupreply           WHERE groupname = 'pcs-miletrie'           ORDER BY id;
+----+--------------+-------------------------+----------+----+
| id | groupname    | attribute               | value    | op |
+----+--------------+-------------------------+----------+----+
|  9 | pcs-miletrie | Tunnel-Type             | VLAN     | := |
| 10 | pcs-miletrie | Tunnel-Medium-Type      | IEEE-802 | := |
| 11 | pcs-miletrie | Tunnel-Private-Group-Id | 10       | := |
+----+--------------+-------------------------+----------+----+
3 rows in set (0.01 sec)

Regards.

-- 
Philippe MARASSE

Pôle Infrastructures - Direction du Système d'Informations et de l'Organisation
Centre Hospitalier Henri Laborit
CS 10587 - 370 avenue Jacques Coeur
86021 Poitiers Cedex
Tel : 05.49.44.57.19


-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4539 bytes
Desc: Signature cryptographique S/MIME
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20131031/f6e81a16/attachment-0001.bin>


More information about the Freeradius-Users mailing list