Hi, I'm having trouble getting FreeRADIUS to recognize the group of a user using sql. I'm running version 3.0.8, and as far as I can tell, everything runs smoothly up to the authorize_group_check_query. It executes, but after this, on debugging with radiusd -XX, authentication breaks off with Debug: (0) sql: ... falling-through to profile processing Debug: rlm_sql (sql): Released connection (4) The output of radiusd -X is as follows. Group attribute values are not verified or added to reply. I tried increasing minimum no of sql connections, but that just makes radius tell me i have too many idle connections and need to reduce min. (0) Received Access-Request Id 153 from 127.0.0.1:49747 to 127.0.1.1:1812 length 85 (0) User-Name = 'mynewuser' (0) User-Password = 'password' (0) NAS-IP-Address = 127.0.1.1 (0) NAS-Port = 0 (0) Message-Authenticator = 0x32010b83ba8a72dd523a231e353d1a1b (0) Framed-Protocol = PPP (0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/default (0) authorize { (0) policy filter_username { (0) if (!&User-Name) { (0) if (!&User-Name) -> FALSE (0) if (&User-Name =~ / /) { (0) if (&User-Name =~ / /) -> FALSE (0) if (&User-Name =~ /@.*@/ ) { (0) if (&User-Name =~ /@.*@/ ) -> FALSE (0) if (&User-Name =~ /\.\./ ) { (0) if (&User-Name =~ /\.\./ ) -> FALSE (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE (0) if (&User-Name =~ /\.$/) { (0) if (&User-Name =~ /\.$/) -> FALSE (0) if (&User-Name =~ /@\./) { (0) if (&User-Name =~ /@\./) -> FALSE (0) } # policy filter_username = notfound (0) [preprocess] = ok (0) [chap] = noop (0) [mschap] = noop (0) [digest] = noop (0) suffix: Checking for suffix after "@" (0) suffix: No '@' in User-Name = "mynewuser", looking up realm NULL (0) suffix: No such realm "NULL" (0) [suffix] = noop (0) eap: No EAP-Message, not doing EAP (0) [eap] = noop (0) files: users: Matched entry DEFAULT at line 182 (0) [files] = ok (0) sql: EXPAND %{User-Name} (0) sql: --> mynewuser (0) sql: SQL-User-Name set to 'mynewuser' 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 (0) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'mynewuser' ORDER BY id (0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'mynewuser' ORDER BY id (0) sql: User found in radcheck table (0) sql: Conditional check items matched, merging assignment check items (0) sql: Cleartext-Password := 'password' (0) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id (0) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'mynewuser' ORDER BY id (0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'mynewuser' ORDER BY id (0) sql: User found in radreply table, merging reply items (0) sql: Reply-Message = 'OK' (0) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (0) sql: --> SELECT groupname FROM radusergroup WHERE username = 'mynewuser' ORDER BY priority (0) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'mynewuser' ORDER BY priority (0) sql: User found in the group table (0) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id (0) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id (0) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id rlm_sql (sql): Released connection (4) (0) [sql] = ok (0) [expiration] = noop (0) [logintime] = noop (0) [pap] = updated (0) } # authorize = updated (0) Found Auth-Type = PAP (0) # Executing group from file /usr/local/etc/raddb/sites-enabled/default (0) Auth-Type PAP { (0) pap: Login attempt with password (0) pap: User authenticated successfully (0) [pap] = ok (0) } # Auth-Type PAP = ok (0) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default (0) post-auth { (0) update { (0) No attributes updated (0) } # update = noop (0) sql: EXPAND .query (0) sql: --> .query (0) sql: Using query template 'query' rlm_sql (sql): Reserved connection (4) (0) sql: EXPAND %{User-Name} (0) sql: --> mynewuser (0) sql: SQL-User-Name set to 'mynewuser' (0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') (0) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'mynewuser', 'password', 'Access-Accept', '2015-07-12 20:57:34') (0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'mynewuser', 'password', 'Access-Accept', '2015-07-12 20:57:34') (0) sql: SQL query returned: success (0) sql: 1 record(s) updated rlm_sql (sql): Released connection (4) (0) [sql] = ok (0) [exec] = noop (0) policy remove_reply_message_if_eap { (0) if (&reply:EAP-Message && &reply:Reply-Message) { (0) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE (0) else { (0) [noop] = noop (0) } # else = noop (0) } # policy remove_reply_message_if_eap = noop (0) } # post-auth = ok (0) Sent Access-Accept Id 153 from 127.0.1.1:1812 to 127.0.0.1:49747 length 0 (0) Framed-Protocol = PPP (0) Framed-Compression = Van-Jacobson-TCP-IP (0) Reply-Message = 'OK' (0) Finished request Thank you
Hi,
From the log it is clear that radius found the group of the user as student!
(0) sql: User found in the group table (0) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id (0) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id (0) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id rlm_sql (sql): Released connection (4) Regards, Randeep On Thu, Jul 16, 2015 at 7:47 AM, ankita therese <ankitatherese@gmail.com> wrote:
Hi,
I'm having trouble getting FreeRADIUS to recognize the group of a user using sql. I'm running version 3.0.8, and as far as I can tell, everything runs smoothly up to the authorize_group_check_query. It executes, but after this, on debugging with radiusd -XX, authentication breaks off with
Debug: (0) sql: ... falling-through to profile processing Debug: rlm_sql (sql): Released connection (4)
The output of radiusd -X is as follows. Group attribute values are not verified or added to reply. I tried increasing minimum no of sql connections, but that just makes radius tell me i have too many idle connections and need to reduce min.
(0) Received Access-Request Id 153 from 127.0.0.1:49747 to 127.0.1.1:1812 length 85 (0) User-Name = 'mynewuser' (0) User-Password = 'password' (0) NAS-IP-Address = 127.0.1.1 (0) NAS-Port = 0 (0) Message-Authenticator = 0x32010b83ba8a72dd523a231e353d1a1b (0) Framed-Protocol = PPP (0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/default (0) authorize { (0) policy filter_username { (0) if (!&User-Name) { (0) if (!&User-Name) -> FALSE (0) if (&User-Name =~ / /) { (0) if (&User-Name =~ / /) -> FALSE (0) if (&User-Name =~ /@.*@/ ) { (0) if (&User-Name =~ /@.*@/ ) -> FALSE (0) if (&User-Name =~ /\.\./ ) { (0) if (&User-Name =~ /\.\./ ) -> FALSE (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE (0) if (&User-Name =~ /\.$/) { (0) if (&User-Name =~ /\.$/) -> FALSE (0) if (&User-Name =~ /@\./) { (0) if (&User-Name =~ /@\./) -> FALSE (0) } # policy filter_username = notfound (0) [preprocess] = ok (0) [chap] = noop (0) [mschap] = noop (0) [digest] = noop (0) suffix: Checking for suffix after "@" (0) suffix: No '@' in User-Name = "mynewuser", looking up realm NULL (0) suffix: No such realm "NULL" (0) [suffix] = noop (0) eap: No EAP-Message, not doing EAP (0) [eap] = noop (0) files: users: Matched entry DEFAULT at line 182 (0) [files] = ok (0) sql: EXPAND %{User-Name} (0) sql: --> mynewuser (0) sql: SQL-User-Name set to 'mynewuser' 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 (0) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'mynewuser' ORDER BY id (0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'mynewuser' ORDER BY id (0) sql: User found in radcheck table (0) sql: Conditional check items matched, merging assignment check items (0) sql: Cleartext-Password := 'password' (0) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id (0) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'mynewuser' ORDER BY id (0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'mynewuser' ORDER BY id (0) sql: User found in radreply table, merging reply items (0) sql: Reply-Message = 'OK' (0) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (0) sql: --> SELECT groupname FROM radusergroup WHERE username = 'mynewuser' ORDER BY priority (0) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'mynewuser' ORDER BY priority (0) sql: User found in the group table (0) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id (0) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id (0) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id rlm_sql (sql): Released connection (4) (0) [sql] = ok (0) [expiration] = noop (0) [logintime] = noop (0) [pap] = updated (0) } # authorize = updated (0) Found Auth-Type = PAP (0) # Executing group from file /usr/local/etc/raddb/sites-enabled/default (0) Auth-Type PAP { (0) pap: Login attempt with password (0) pap: User authenticated successfully (0) [pap] = ok (0) } # Auth-Type PAP = ok (0) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default (0) post-auth { (0) update { (0) No attributes updated (0) } # update = noop (0) sql: EXPAND .query (0) sql: --> .query (0) sql: Using query template 'query' rlm_sql (sql): Reserved connection (4) (0) sql: EXPAND %{User-Name} (0) sql: --> mynewuser (0) sql: SQL-User-Name set to 'mynewuser' (0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') (0) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'mynewuser', 'password', 'Access-Accept', '2015-07-12 20:57:34') (0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'mynewuser', 'password', 'Access-Accept', '2015-07-12 20:57:34') (0) sql: SQL query returned: success (0) sql: 1 record(s) updated rlm_sql (sql): Released connection (4) (0) [sql] = ok (0) [exec] = noop (0) policy remove_reply_message_if_eap { (0) if (&reply:EAP-Message && &reply:Reply-Message) { (0) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE (0) else { (0) [noop] = noop (0) } # else = noop (0) } # policy remove_reply_message_if_eap = noop (0) } # post-auth = ok (0) Sent Access-Accept Id 153 from 127.0.1.1:1812 to 127.0.0.1:49747 length 0 (0) Framed-Protocol = PPP (0) Framed-Compression = Van-Jacobson-TCP-IP (0) Reply-Message = 'OK' (0) Finished request
Thank you - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Randeep Mob: +919447831699[kerala] Mob: +919880050349[B'lore] http://twitter.com/Randeeppr http://in.linkedin.com/in/randeeppr [image: --] Randeep Raman [image: http://]about.me/Randeeppr <http://about.me/Randeeppr>
Hello, The problem is that though it is identifying the group correctly, RADIUS is not checking the conditions. the request sent was radtest mynewuser password 127.0.1.1 0 testing123 0 localhost and these are the tables involved: radcheck: ____________________________________________ | id | username | attribute | op | value | |----------------------------------------------------------------------------| | 1 | mynewuser | Cleartext-Password | := | password | |----------------------------------------------------------------------------| radusergroup: ____________________________ | username | groupname | priority | |-------------------------------------------------| | mynewuser | student | 1 | |-------------------------------------------------| radgroupcheck: ______________________________________________ | id | groupname | attribute | op | value | |------------------------------------------------------------------------------| | 1 | student | NAS-IP-Address | != | 127.0.1.1 | |------------------------------------------------------------------------------| radreply: _________________________________________ | id | username | attribute | op | value | |-----------------------------------------------------------------------| | 1 | mynewuser | Reply-Message | = | "OK" | |-----------------------------------------------------------------------| radgroupreply: ___________________________________________ | id | groupname | attribute | op | value | |-------------------------------------------------------------------------| | 1 | student | Reply-Message | = | "OK" | |-------------------------------------------------------------------------| The request should have been rejected based on the entry in radgroupcheck, but its not. When the NAS-IP-Address check is given in radcheck, it rejects requests properly, not so much when in radgroupcheck. output of radiusd -XX for that request is as follows Fri Jul 17 21:52:39 2015 : Debug: (0) Received Access-Request Id 179 from 127.0.0.1:50203 to 127.0.1.1:1812 length 79 Fri Jul 17 21:52:39 2015 : Debug: (0) User-Name = 'mynewuser' Fri Jul 17 21:52:39 2015 : Debug: (0) User-Password = 'password' Fri Jul 17 21:52:39 2015 : Debug: (0) NAS-IP-Address = 127.0.0.1 Fri Jul 17 21:52:39 2015 : Debug: (0) NAS-Port = 0 Fri Jul 17 21:52:39 2015 : Debug: (0) Message-Authenticator = 0x3ae644a3e918da4140340c4e256c8ddf Fri Jul 17 21:52:39 2015 : Debug: (0) session-state: No State attribute Fri Jul 17 21:52:39 2015 : Debug: (0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/default Fri Jul 17 21:52:39 2015 : Debug: (0) authorize { Fri Jul 17 21:52:39 2015 : Debug: (0) policy filter_username { Fri Jul 17 21:52:39 2015 : Debug: (0) if (!&User-Name) { Fri Jul 17 21:52:39 2015 : Debug: (0) if (!&User-Name) -> FALSE Fri Jul 17 21:52:39 2015 : Debug: (0) if (&User-Name =~ / /) { Fri Jul 17 21:52:39 2015 : Debug: No matches Fri Jul 17 21:52:39 2015 : Debug: (0) if (&User-Name =~ / /) -> FALSE Fri Jul 17 21:52:39 2015 : Debug: (0) if (&User-Name =~ /@.*@/ ) { Fri Jul 17 21:52:39 2015 : Debug: No matches Fri Jul 17 21:52:39 2015 : Debug: (0) if (&User-Name =~ /@.*@/ ) -> FALSE Fri Jul 17 21:52:39 2015 : Debug: (0) if (&User-Name =~ /\.\./ ) { Fri Jul 17 21:52:39 2015 : Debug: No matches Fri Jul 17 21:52:39 2015 : Debug: (0) if (&User-Name =~ /\.\./ ) -> FALSE Fri Jul 17 21:52:39 2015 : Debug: (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { Fri Jul 17 21:52:39 2015 : Debug: No matches Fri Jul 17 21:52:39 2015 : Debug: (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE Fri Jul 17 21:52:39 2015 : Debug: (0) if (&User-Name =~ /\.$/) { Fri Jul 17 21:52:39 2015 : Debug: No matches Fri Jul 17 21:52:39 2015 : Debug: (0) if (&User-Name =~ /\.$/) -> FALSE Fri Jul 17 21:52:39 2015 : Debug: (0) if (&User-Name =~ /@\./) { Fri Jul 17 21:52:39 2015 : Debug: No matches Fri Jul 17 21:52:39 2015 : Debug: (0) if (&User-Name =~ /@\./) -> FALSE Fri Jul 17 21:52:39 2015 : Debug: (0) } # policy filter_username = notfound Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: calling preprocess (rlm_preprocess) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: returned from preprocess (rlm_preprocess) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) [preprocess] = ok Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: calling chap (rlm_chap) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: returned from chap (rlm_chap) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) [chap] = noop Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: calling mschap (rlm_mschap) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: returned from mschap (rlm_mschap) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) [mschap] = noop Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: calling digest (rlm_digest) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: returned from digest (rlm_digest) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) [digest] = noop Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: calling suffix (rlm_realm) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) suffix: Checking for suffix after "@" Fri Jul 17 21:52:39 2015 : Debug: (0) suffix: No '@' in User-Name = "mynewuser", looking up realm NULL Fri Jul 17 21:52:39 2015 : Debug: (0) suffix: No such realm "NULL" Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: returned from suffix (rlm_realm) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) [suffix] = noop Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: calling eap (rlm_eap) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) eap: No EAP-Message, not doing EAP Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: returned from eap (rlm_eap) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) [eap] = noop Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: calling files (rlm_files) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: returned from files (rlm_files) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) [files] = noop Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: calling sql (rlm_sql) for request 0 Fri Jul 17 21:52:39 2015 : Debug: %{User-Name} Fri Jul 17 21:52:39 2015 : Debug: Parsed xlat tree: Fri Jul 17 21:52:39 2015 : Debug: attribute --> User-Name Fri Jul 17 21:52:39 2015 : Debug: (0) sql: EXPAND %{User-Name} Fri Jul 17 21:52:39 2015 : Debug: (0) sql: --> mynewuser Fri Jul 17 21:52:39 2015 : Debug: (0) sql: SQL-User-Name set to 'mynewuser' Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: FROM 1 TO 6 MAX 7 Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: Examining SQL-User-Name Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: APPENDING SQL-User-Name FROM 0 TO 6 Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: TO in 6 out 7 Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[0] = User-Name Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[1] = User-Password Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[2] = NAS-IP-Address Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[3] = NAS-Port Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[4] = Message-Authenticator Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[5] = Event-Timestamp Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[6] = SQL-User-Name Fri Jul 17 21:52:39 2015 : Debug: rlm_sql (sql): Reserved connection (4) Fri Jul 17 21:52:39 2015 : Debug: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: Parsed xlat tree: Fri Jul 17 21:52:39 2015 : Debug: literal --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = ' Fri Jul 17 21:52:39 2015 : Debug: attribute --> SQL-User-Name Fri Jul 17 21:52:39 2015 : Debug: literal --> ' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'mynewuser' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'mynewuser' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: User found in radcheck table Fri Jul 17 21:52:39 2015 : Debug: (0) sql: Conditional check items matched, merging assignment check items Fri Jul 17 21:52:39 2015 : Debug: (0) sql: Cleartext-Password := 'password' Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: FROM 1 TO 0 MAX 1 Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: Examining Cleartext-Password Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: APPENDING Cleartext-Password FROM 0 TO 0 Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: TO in 0 out 1 Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[0] = Cleartext-Password Fri Jul 17 21:52:39 2015 : Debug: SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: Parsed xlat tree: Fri Jul 17 21:52:39 2015 : Debug: literal --> SELECT id, username, attribute, value, op FROM radreply WHERE username = ' Fri Jul 17 21:52:39 2015 : Debug: attribute --> SQL-User-Name Fri Jul 17 21:52:39 2015 : Debug: literal --> ' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'mynewuser' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'mynewuser' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ... falling-through to group processing Fri Jul 17 21:52:39 2015 : Debug: SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority Fri Jul 17 21:52:39 2015 : Debug: Parsed xlat tree: Fri Jul 17 21:52:39 2015 : Debug: literal --> SELECT groupname FROM radusergroup WHERE username = ' Fri Jul 17 21:52:39 2015 : Debug: attribute --> SQL-User-Name Fri Jul 17 21:52:39 2015 : Debug: literal --> ' ORDER BY priority Fri Jul 17 21:52:39 2015 : Debug: (0) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority Fri Jul 17 21:52:39 2015 : Debug: (0) sql: --> SELECT groupname FROM radusergroup WHERE username = 'mynewuser' ORDER BY priority Fri Jul 17 21:52:39 2015 : Debug: (0) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'mynewuser' ORDER BY priority Fri Jul 17 21:52:39 2015 : Debug: (0) sql: User found in the group table Fri Jul 17 21:52:39 2015 : Debug: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: Parsed xlat tree: Fri Jul 17 21:52:39 2015 : Debug: literal --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = ' Fri Jul 17 21:52:39 2015 : Debug: attribute --> Sql-Group Fri Jul 17 21:52:39 2015 : Debug: literal --> ' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ... falling-through to profile processing Fri Jul 17 21:52:39 2015 : Debug: rlm_sql (sql): Released connection (4) Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: returned from sql (rlm_sql) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) [sql] = ok Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: calling expiration (rlm_expiration) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: returned from expiration (rlm_expiration) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) [expiration] = noop Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: calling logintime (rlm_logintime) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: returned from logintime (rlm_logintime) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) [logintime] = noop Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: calling pap (rlm_pap) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authorize]: returned from pap (rlm_pap) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) [pap] = updated Fri Jul 17 21:52:39 2015 : Debug: (0) } # authorize = updated Fri Jul 17 21:52:39 2015 : Debug: (0) Found Auth-Type = PAP Fri Jul 17 21:52:39 2015 : Debug: (0) # Executing group from file /usr/local/etc/raddb/sites-enabled/default Fri Jul 17 21:52:39 2015 : Debug: (0) Auth-Type PAP { Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authenticate]: calling pap (rlm_pap) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) pap: Login attempt with password "password" Fri Jul 17 21:52:39 2015 : Debug: (0) pap: Comparing with "known good" Cleartext-Password "password" Fri Jul 17 21:52:39 2015 : Debug: (0) pap: User authenticated successfully Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[authenticate]: returned from pap (rlm_pap) for request 0 Fri Jul 17 21:52:39 2015 : Debug: (0) [pap] = ok Fri Jul 17 21:52:39 2015 : Debug: (0) } # Auth-Type PAP = ok Fri Jul 17 21:52:39 2015 : Debug: (0) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default Fri Jul 17 21:52:39 2015 : Debug: (0) post-auth { Fri Jul 17 21:52:39 2015 : Debug: (0) update { Fri Jul 17 21:52:39 2015 : Debug: (0) No attributes updated Fri Jul 17 21:52:39 2015 : Debug: (0) } # update = noop Fri Jul 17 21:52:39 2015 : Debug: (0) modsingle[post-auth]: calling sql (rlm_sql) for request 0 Fri Jul 17 21:52:39 2015 : Debug: .query Fri Jul 17 21:52:39 2015 : Debug: Parsed xlat tree: Fri Jul 17 21:52:39 2015 : Debug: literal --> .query Fri Jul 17 21:52:39 2015 : Debug: (0) sql: EXPAND .query Fri Jul 17 21:52:39 2015 : Debug: (0) sql: --> .query Fri Jul 17 21:52:39 2015 : Debug: (0) sql: Using query template 'query' Fri Jul 17 21:52:39 2015 : Debug: rlm_sql (sql): Reserved connection (4) Fri Jul 17 21:52:39 2015 : Debug: %{User-Name} Fri Jul 17 21:52:39 2015 : Debug: Parsed xlat tree: Fri Jul 17 21:52:39 2015 : Debug: attribute --> User-Name Fri Jul 17 21:52:39 2015 : Debug: (0) sql: EXPAND %{User-Name} Fri Jul 17 21:52:39 2015 : Debug: (0) sql: --> mynewuser Fri Jul 17 21:52:39 2015 : Debug: (0) sql: SQL-User-Name set to 'mynewuser' Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: FROM 1 TO 6 MAX 7 Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: Examining SQL-User-Name Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: APPENDING SQL-User-Name FROM 0 TO 6 Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: TO in 6 out 7 Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[0] = User-Name Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[1] = User-Password Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[2] = NAS-IP-Address Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[3] = NAS-Port Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[4] = Message-Authenticator Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[5] = Event-Timestamp Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ::: to[6] = SQL-User-Name Fri Jul 17 21:52:39 2015 : Debug: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') Fri Jul 17 21:52:39 2015 : Debug: Parsed xlat tree: Fri Jul 17 21:52:39 2015 : Debug: literal --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( ' Fri Jul 17 21:52:39 2015 : Debug: attribute --> SQL-User-Name Fri Jul 17 21:52:39 2015 : Debug: literal --> ', ' Fri Jul 17 21:52:39 2015 : Debug: if { Fri Jul 17 21:52:39 2015 : Debug: attribute --> User-Password Fri Jul 17 21:52:39 2015 : Debug: } Fri Jul 17 21:52:39 2015 : Debug: else { Fri Jul 17 21:52:39 2015 : Debug: attribute --> CHAP-Password Fri Jul 17 21:52:39 2015 : Debug: } Fri Jul 17 21:52:39 2015 : Debug: literal --> ', ' Fri Jul 17 21:52:39 2015 : Debug: attribute --> Packet-Type Fri Jul 17 21:52:39 2015 : Debug: literal --> ', ' Fri Jul 17 21:52:39 2015 : Debug: percent --> S Fri Jul 17 21:52:39 2015 : Debug: literal --> ') Fri Jul 17 21:52:39 2015 : Debug: (0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') Fri Jul 17 21:52:39 2015 : Debug: (0) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'mynewuser', 'password', 'Access-Accept', '2015-07-17 21:52:39') Fri Jul 17 21:52:39 2015 : Debug: (0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'mynewuser', 'password', 'Access-Accept', '2015-07-17 21:52:39') Fri Jul 17 21:52:40 2015 : Debug: (0) sql: SQL query returned: success Fri Jul 17 21:52:40 2015 : Debug: (0) sql: 1 record(s) updated Fri Jul 17 21:52:40 2015 : Debug: rlm_sql (sql): Released connection (4) Fri Jul 17 21:52:40 2015 : Debug: (0) modsingle[post-auth]: returned from sql (rlm_sql) for request 0 Fri Jul 17 21:52:40 2015 : Debug: (0) [sql] = ok Fri Jul 17 21:52:40 2015 : Debug: (0) modsingle[post-auth]: calling exec (rlm_exec) for request 0 Fri Jul 17 21:52:40 2015 : Debug: (0) modsingle[post-auth]: returned from exec (rlm_exec) for request 0 Fri Jul 17 21:52:40 2015 : Debug: (0) [exec] = noop Fri Jul 17 21:52:40 2015 : Debug: (0) policy remove_reply_message_if_eap { Fri Jul 17 21:52:40 2015 : Debug: (0) if (&reply:EAP-Message && &reply:Reply-Message) { Fri Jul 17 21:52:40 2015 : Debug: (0) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE Fri Jul 17 21:52:40 2015 : Debug: (0) else { Fri Jul 17 21:52:40 2015 : Debug: (0) modsingle[post-auth]: calling noop (rlm_always) for request 0 Fri Jul 17 21:52:40 2015 : Debug: (0) modsingle[post-auth]: returned from noop (rlm_always) for request 0 Fri Jul 17 21:52:40 2015 : Debug: (0) [noop] = noop Fri Jul 17 21:52:40 2015 : Debug: (0) } # else = noop Fri Jul 17 21:52:40 2015 : Debug: (0) } # policy remove_reply_message_if_eap = noop Fri Jul 17 21:52:40 2015 : Debug: (0) } # post-auth = ok Fri Jul 17 21:52:40 2015 : Debug: (0) Sent Access-Accept Id 179 from 127.0.1.1:1812 to 127.0.0.1:50203 length 0 Fri Jul 17 21:52:40 2015 : Debug: (0) Finished request Fri Jul 17 21:52:40 2015 : Debug: Waking up in 4.9 seconds. Fri Jul 17 21:52:45 2015 : Debug: (0) <done>: Cleaning up request packet ID 179 with timestamp +6 when group is found using select, it just stops processing and falls through Fri Jul 17 21:52:39 2015 : Debug: (0) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id Fri Jul 17 21:52:39 2015 : Debug: (0) sql: ... falling-through to profile processing Fri Jul 17 21:52:39 2015 : Debug: rlm_sql (sql): Released connection (4) the number of groups is quite small and static, and It'd be OK if i could read them from a flat file instead... Is that possible? On 7/16/15, Randeep <randeep123@gmail.com> wrote:
Hi,
From the log it is clear that radius found the group of the user as student!
(0) sql: User found in the group table (0) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id (0) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id (0) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id rlm_sql (sql): Released connection (4)
Regards, Randeep
On Thu, Jul 16, 2015 at 7:47 AM, ankita therese <ankitatherese@gmail.com> wrote:
Hi,
I'm having trouble getting FreeRADIUS to recognize the group of a user using sql. I'm running version 3.0.8, and as far as I can tell, everything runs smoothly up to the authorize_group_check_query. It executes, but after this, on debugging with radiusd -XX, authentication breaks off with
Debug: (0) sql: ... falling-through to profile processing Debug: rlm_sql (sql): Released connection (4)
The output of radiusd -X is as follows. Group attribute values are not verified or added to reply. I tried increasing minimum no of sql connections, but that just makes radius tell me i have too many idle connections and need to reduce min.
(0) Received Access-Request Id 153 from 127.0.0.1:49747 to 127.0.1.1:1812 length 85 (0) User-Name = 'mynewuser' (0) User-Password = 'password' (0) NAS-IP-Address = 127.0.1.1 (0) NAS-Port = 0 (0) Message-Authenticator = 0x32010b83ba8a72dd523a231e353d1a1b (0) Framed-Protocol = PPP (0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/default (0) authorize { (0) policy filter_username { (0) if (!&User-Name) { (0) if (!&User-Name) -> FALSE (0) if (&User-Name =~ / /) { (0) if (&User-Name =~ / /) -> FALSE (0) if (&User-Name =~ /@.*@/ ) { (0) if (&User-Name =~ /@.*@/ ) -> FALSE (0) if (&User-Name =~ /\.\./ ) { (0) if (&User-Name =~ /\.\./ ) -> FALSE (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE (0) if (&User-Name =~ /\.$/) { (0) if (&User-Name =~ /\.$/) -> FALSE (0) if (&User-Name =~ /@\./) { (0) if (&User-Name =~ /@\./) -> FALSE (0) } # policy filter_username = notfound (0) [preprocess] = ok (0) [chap] = noop (0) [mschap] = noop (0) [digest] = noop (0) suffix: Checking for suffix after "@" (0) suffix: No '@' in User-Name = "mynewuser", looking up realm NULL (0) suffix: No such realm "NULL" (0) [suffix] = noop (0) eap: No EAP-Message, not doing EAP (0) [eap] = noop (0) files: users: Matched entry DEFAULT at line 182 (0) [files] = ok (0) sql: EXPAND %{User-Name} (0) sql: --> mynewuser (0) sql: SQL-User-Name set to 'mynewuser' 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 (0) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'mynewuser' ORDER BY id (0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'mynewuser' ORDER BY id (0) sql: User found in radcheck table (0) sql: Conditional check items matched, merging assignment check items (0) sql: Cleartext-Password := 'password' (0) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id (0) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'mynewuser' ORDER BY id (0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'mynewuser' ORDER BY id (0) sql: User found in radreply table, merging reply items (0) sql: Reply-Message = 'OK' (0) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (0) sql: --> SELECT groupname FROM radusergroup WHERE username = 'mynewuser' ORDER BY priority (0) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'mynewuser' ORDER BY priority (0) sql: User found in the group table (0) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id (0) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id (0) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'student' ORDER BY id rlm_sql (sql): Released connection (4) (0) [sql] = ok (0) [expiration] = noop (0) [logintime] = noop (0) [pap] = updated (0) } # authorize = updated (0) Found Auth-Type = PAP (0) # Executing group from file /usr/local/etc/raddb/sites-enabled/default (0) Auth-Type PAP { (0) pap: Login attempt with password (0) pap: User authenticated successfully (0) [pap] = ok (0) } # Auth-Type PAP = ok (0) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default (0) post-auth { (0) update { (0) No attributes updated (0) } # update = noop (0) sql: EXPAND .query (0) sql: --> .query (0) sql: Using query template 'query' rlm_sql (sql): Reserved connection (4) (0) sql: EXPAND %{User-Name} (0) sql: --> mynewuser (0) sql: SQL-User-Name set to 'mynewuser' (0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') (0) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'mynewuser', 'password', 'Access-Accept', '2015-07-12 20:57:34') (0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'mynewuser', 'password', 'Access-Accept', '2015-07-12 20:57:34') (0) sql: SQL query returned: success (0) sql: 1 record(s) updated rlm_sql (sql): Released connection (4) (0) [sql] = ok (0) [exec] = noop (0) policy remove_reply_message_if_eap { (0) if (&reply:EAP-Message && &reply:Reply-Message) { (0) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE (0) else { (0) [noop] = noop (0) } # else = noop (0) } # policy remove_reply_message_if_eap = noop (0) } # post-auth = ok (0) Sent Access-Accept Id 153 from 127.0.1.1:1812 to 127.0.0.1:49747 length 0 (0) Framed-Protocol = PPP (0) Framed-Compression = Van-Jacobson-TCP-IP (0) Reply-Message = 'OK' (0) Finished request
Thank you - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Randeep Mob: +919447831699[kerala] Mob: +919880050349[B'lore] http://twitter.com/Randeeppr http://in.linkedin.com/in/randeeppr
[image: --] Randeep Raman [image: http://]about.me/Randeeppr <http://about.me/Randeeppr> - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Jul 17, 2015, at 12:30 PM, ankita therese <ankitatherese@gmail.com> wrote:
The problem is that though it is identifying the group correctly, RADIUS is not checking the conditions.
I don't think so.
radgroupcheck: ______________________________________________ | id | groupname | attribute | op | value | |------------------------------------------------------------------------------| | 1 | student | NAS-IP-Address | != | 127.0.1.1 | |------------------------------------------------------------------------------| ... radgroupreply: ___________________________________________ | id | groupname | attribute | op | value | |-------------------------------------------------------------------------| | 1 | student | Reply-Message | = | "OK" | |-------------------------------------------------------------------------|
which really says: if (NAS-IP-Address != 127.0.0.1) { update reply { Reply-Message = "OK" } }
The request should have been rejected based on the entry in radgroupcheck,
No. Read this: http://wiki.freeradius.org/modules/Rlm_sql
but its not. When the NAS-IP-Address check is given in radcheck, it rejects requests properly, not so much when in radgroupcheck.
No. That's not how it works. If you add the NAS-IP-Address to radcheck, then the *entry* doesn't match. The other thing in the entry is setting the Cleartext-Password... but that is ONLY done if the entry matches. Which it doesn't. And since there's no Cleartext-Password for the user, he's rejected. Don't invent your own ideas about how rlm_sql works. It's behaviour is documented. It works as documented. Alan DeKok.
participants (3)
-
Alan DeKok -
ankita therese -
Randeep