sql module behavior differs from 2.2.1 to 3.0.0 ?
Hello, I'm upgrading my freeradius servers from 2.2.1 to 3.0.0 with complete review of the configuration files. We're using MySQL backend to store freeradius datas. It seems that sql module return code is different when the user is not found in database : - Freeradius 2.2.1 returns "notfound", which is fine - Freeradius 3.0.0 returns "noop", which I did not expect ! Is the new behavior normal ? Relevant parts of debug log : FR 2.2.1 : [sql] expand: %{User-Name} -> 002324609e3f [sql] sql_set_user escaped user --> '002324609e3f' rlm_sql (sql): Reserving sql socket id: 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_mysql: query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '002324609e3f' ORDER BY id [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_mysql: query: SELECT groupname FROM radusergroup WHERE username = '002324609e3f' ORDER BY priority rlm_sql (sql): Released sql socket id: 0 [sql] User 002324609e3f not found ++[sql] returns notfound FR 3.0.0 : (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 Rgds. -- 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
(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
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 ? Rgds. -- 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
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. -Arran Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team
On 24/10/2013 00:25, Arran Cudbard-Bell wrote:
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… Indeed. I've forgotten to mention that for the moment our network switches do MAC "Autentication", where username = password = mac-address so in order to authorize access to the right VLAN, a simple check of user existence is enough.
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. Great :-), I'll test that mid next week as I'm actually on holidays.
Rdgs.
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
cloned master branch => not working, always noop returned if user is not found, is your patch commited on this branch ??
It was, but then it got nuked by accident. I've committed that and couple of other fixes now.
cloned v3.0.x branch => half success, as this user is not found :
Ok, this should work now. Thanks for testing. Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team
Le 31/10/2013 13:01, Arran Cudbard-Bell a écrit :
cloned master branch => not working, always noop returned if user is not found, is your patch commited on this branch ?? It was, but then it got nuked by accident.
I've committed that and couple of other fixes now.
cloned v3.0.x branch => half success, as this user is not found : Ok, this should work now. Hmmm, half success, return code is now OK but groups are not processed although I've read_groups = yes in sql module configuration ??
(12) sql : expand: "%{User-Name}" -> '002324609e3f' (12) sql : SQL-User-Name set to "002324609e3f" rlm_sql (sql): Reserved connection (6) (12) 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 user name = '002324609E3F' ORDER BY id' rlm_sql (sql): Executing query: 'SELECT id, username, attribute, value, op FROM radcheck WHERE username = '002324609E3F' ORDER BY id' (12) sql : User found in radcheck table (12) sql : Check items matched (12) 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 = '002324609E3F' ORDER BY id' rlm_sql (sql): Executing query: 'SELECT id, username, attribute, value, op FROM radreply WHERE username = '002324609E3F' ORDER BY id' (12) 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 (6) (12) [sql] = ok BTW, the SQL Username seems to be uppercased.
Thanks for testing. You're welcome.
Rgds. -- 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
participants (2)
-
Arran Cudbard-Bell -
Philippe MARASSE