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