Le 24/10/2013 00:25, Arran Cudbard-Bell a écrit :
On 23 Oct 2013, at 13:28, Philippe MARASSE <philippe.marasse@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@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