* On 4 Mar 2016, at 08:53, Alan DeKok <aland at deployingradius.com <http://lists.freeradius.org/mailman/listinfo/freeradius-users>> wrote: *> >* On Mar 3, 2016, at 2:10 PM, Devrim Seral <dseral at gmail.com <http://lists.freeradius.org/mailman/listinfo/freeradius-users>> wrote: *>> >>* Hello freeradius Users, *>>* I have some strange problem with SQL Xlat that i haven't find out the way *>>* to fix it. *> >* It's odd. We're looking into it.
Dear Arran, Thanks for your fix. Now Xlat working but server didn't give correct response. I was used following attribute values and operators in my database; 15GB_Quota_2048KBurst Mikrotik-Xmit-Limit := `%{sql:SELECT 100000}` 15GB_Quota_2048KBurst Mikrotik-Recv-Limit := `%{sql:SELECT trafsumdw('%{User-Name}',15360)}` If you can check debug it was seem that rlm_sql EXPAND worked perfect. But unfortunately the end of process Attributes haven't assigned with correct values. I have apply your patch to freeradius 3.0.11 as my own Applied patches given below. (I checked several times); diff -ruN freeradius-server-3.0.11/src/lib/pair.c freeradius-server-3.0.11_new/src/lib/pair.c --- freeradius-server-3.0.11/src/lib/pair.c 2016-01-25 20:27:03.000000000 +0200 +++ freeradius-server-3.0.11_new/src/lib/pair.c 2016-03-05 10:39:28.626496775 +0200 @@ -1614,11 +1614,13 @@ * valuepair should not already have a value. */ if (vp->type != VT_NONE) { + fr_strerror_printf("Pair already has a value"); return -1; } raw = talloc_typed_strdup(vp, value); if (!raw) { + fr_strerror_printf("Out of memory"); return -1; } diff -ruN freeradius-server-3.0.11/src/modules/rlm_sql/sql.c freeradius-server-3.0.11_new/src/modules/rlm_sql/sql.c --- freeradius-server-3.0.11/src/modules/rlm_sql/sql.c 2016-01-25 20:27:03.000000000 +0200 +++ freeradius-server-3.0.11_new/src/modules/rlm_sql/sql.c 2016-03-05 10:43:04.029952559 +0200 @@ -115,7 +115,7 @@ * Verify the 'Attribute' field */ if (!row[2] || row[2][0] == '\0') { - REDEBUG("The 'Attribute' field is empty or NULL, skipping the entire row"); + REDEBUG("Attribute field is empty or NULL, skipping the entire row"); return -1; } @@ -136,14 +136,20 @@ * Complain about empty or invalid 'op' field */ operator = T_OP_CMP_EQ; - REDEBUG("The 'op' field for attribute '%s = %s' is NULL, or non-existent.", row[2], row[3]); + REDEBUG("The op field for attribute '%s = %s' is NULL, or non-existent.", row[2], row[3]); REDEBUG("You MUST FIX THIS if you want the configuration to behave as you expect"); } /* * The 'Value' field may be empty or NULL */ + if (!row[3]) { + REDEBUG("Value field is empty or NULL, skipping the entire row"); + return -1; + } + value = row[3]; + /* * If we have a new-style quoted string, where the * *entire* string is quoted, do xlat's. @@ -166,9 +172,7 @@ * Mark the pair to be allocated later. */ case T_BACK_QUOTED_STRING: - value = NULL; do_xlat = 1; - break; /* * Keep the original string. @@ -190,7 +194,7 @@ if (do_xlat) { if (fr_pair_mark_xlat(vp, value) < 0) { - REDEBUG("Error marking pair for xlat"); + REDEBUG("Error marking pair for xlat: %s",fr_strerror()); talloc_free(vp); return -1; Again i make something wrong! Thanks devrim Here is my debug; (5) Received Access-Request Id 79 from 127.0.0.1:35800 to 127.0.0.1:1812 length 76 (5) User-Name = "dseral" (5) User-Password = "123" (5) NAS-IP-Address = 127.0.1.1 (5) NAS-Port = 11 (5) Message-Authenticator = 0xf4f1c36c18e73fe161cdf6890e3b68f9 (5) # Executing section authorize from file /etc/freeradius/sites-enabled/default (5) authorize { (5) policy filter_username { (5) if (&User-Name) { (5) if (&User-Name) -> TRUE (5) if (&User-Name) { (5) if (&User-Name =~ / /) { (5) if (&User-Name =~ / /) -> FALSE (5) if (&User-Name =~ /@[^@]*@/ ) { (5) if (&User-Name =~ /@[^@]*@/ ) -> FALSE (5) if (&User-Name =~ /\.\./ ) { (5) if (&User-Name =~ /\.\./ ) -> FALSE (5) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (5) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE (5) if (&User-Name =~ /\.$/) { (5) if (&User-Name =~ /\.$/) -> FALSE (5) if (&User-Name =~ /@\./) { (5) if (&User-Name =~ /@\./) -> FALSE (5) } # if (&User-Name) = notfound (5) } # policy filter_username = notfound (5) [preprocess] = ok (5) [chap] = noop (5) [mschap] = noop (5) [digest] = noop (5) suffix: Checking for suffix after "@" (5) suffix: No '@' in User-Name = "dseral", looking up realm NULL (5) suffix: No such realm "NULL" (5) [suffix] = noop (5) eap: No EAP-Message, not doing EAP (5) [eap] = noop (5) [files] = noop (5) sql: EXPAND %{User-Name} (5) sql: --> dseral (5) sql: SQL-User-Name set to 'dseral' rlm_sql (sql): Closing connection (9): Hit idle_timeout, was idle for 109 seconds rlm_sql (sql): You probably need to lower "min" rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): Closing connection (10): Hit idle_timeout, was idle for 109 seconds rlm_sql (sql): You probably need to lower "min" rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): Closing connection (8): Hit idle_timeout, was idle for 109 seconds rlm_sql (sql): You probably need to lower "min" rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): 0 of 0 connections in use. You may need to increase "spare" rlm_sql (sql): Opening additional connection (11), 1 of 32 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 5.5.47-0+deb8u1, protocol version 10 rlm_sql (sql): Reserved connection (11) (5) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id (5) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'dseral' ORDER BY id (5) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'dseral' ORDER BY id (5) sql: User found in radcheck table (5) sql: Conditional check items matched, merging assignment check items (5) sql: Cleartext-Password := "123" (5) sql: Expiration := "Feb 24 2017 00:00:00 EET" (5) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'dseral' ORDER BY id (5) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'dseral' ORDER BY id (5) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (5) sql: --> SELECT groupname FROM radusergroup WHERE username = 'dseral' ORDER BY priority (5) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'dseral' ORDER BY priority (5) sql: User found in the group table (5) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id (5) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (5) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (5) sql: Group "15GB_Quota_2048KBurst": Conditional check items matched (5) sql: Group "15GB_Quota_2048KBurst": Merging assignment check items (5) sql: Simultaneous-Use := 1 (5) sql: EXPAND SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id (5) sql: --> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (5) sql: Executing select query: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (5) sql: Group "15GB_Quota_2048KBurst": Merging reply items (5) sql: Mikrotik-Recv-Limit := "`%{sql:SELECT trafsumdw('%{User-Name}',15360)}`" (5) sql: Mikrotik-Xmit-Limit := "`%{sql:SELECT 100000}`" (5) sql: Mikrotik-Rate-Limit := "192k/768k 512k/2048k 160k/1024k 30/30" (5) sql: Acct-Interim-Interval := 100 (5) sql: EXPAND %{User-Name} (5) sql: --> dseral (5) sql: SQL-User-Name set to 'dseral' rlm_sql (sql): 1 of 1 connections in use. You may need to increase "spare" rlm_sql (sql): Opening additional connection (12), 1 of 31 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 5.5.47-0+deb8u1, protocol version 10 rlm_sql (sql): Reserved connection (12) (5) sql: Executing select query: SELECT trafsumdw('dseral',15360) rlm_sql (sql): Released connection (12) rlm_sql (sql): Need 1 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (13), 1 of 30 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 5.5.47-0+deb8u1, protocol version 10 (5) sql: EXPAND `%{sql:SELECT trafsumdw('%{User-Name}',15360)}` (5) sql: --> `4294967290` (5) sql: EXPAND %{User-Name} (5) sql: --> dseral (5) sql: SQL-User-Name set to 'dseral' rlm_sql (sql): Reserved connection (12) (5) sql: Executing select query: SELECT 100000 rlm_sql (sql): Released connection (12) (5) sql: EXPAND `%{sql:SELECT 100000}` (5) sql: --> `100000` rlm_sql (sql): Released connection (11) (5) [sql] = ok (5) expiration: Account will expire at 'Feb 24 2017 00:00:00 EET' (5) [expiration] = ok (5) [logintime] = noop (5) [pap] = updated (5) } # authorize = updated (5) Found Auth-Type = PAP (5) # Executing group from file /etc/freeradius/sites-enabled/default (5) Auth-Type PAP { (5) pap: Login attempt with password (5) pap: Comparing with "known good" Cleartext-Password (5) pap: User authenticated successfully (5) [pap] = ok (5) } # Auth-Type PAP = ok (5) # Executing section post-auth from file /etc/freeradius/sites-enabled/default (5) post-auth { (5) update { (5) No attributes updated (5) } # update = noop (5) sql: EXPAND .query (5) sql: --> .query (5) sql: Using query template 'query' rlm_sql (sql): Reserved connection (11) (5) sql: EXPAND %{User-Name} (5) sql: --> dseral (5) sql: SQL-User-Name set to 'dseral' (5) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, date) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') (5) sql: --> INSERT INTO radpostauth (username, pass, reply, date) VALUES ( 'dseral', '123', 'Access-Accept', '2016-03-05 11:05:54') (5) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, date) VALUES ( 'dseral', '123', 'Access-Accept', '2016-03-05 11:05:54') (5) sql: rlm_sql_mysql: ERROR 1062 (Duplicate entry 'dseral' for key 'user'): 23000 (5) sql: SQL query returned: need alt query (5) sql: No additional queries configured rlm_sql (sql): Released connection (11) (5) [sql] = noop (5) [exec] = noop (5) policy remove_reply_message_if_eap { (5) if (&reply:EAP-Message && &reply:Reply-Message) { (5) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE (5) else { (5) [noop] = noop (5) } # else = noop (5) } # policy remove_reply_message_if_eap = noop (5) } # post-auth = noop (5) Login OK: [dseral] (from client localhost port 11) (5) Sent Access-Accept Id 79 from 127.0.0.1:1812 to 127.0.0.1:35800 length 0 (5) Mikrotik-Recv-Limit = 0 (5) Mikrotik-Xmit-Limit = 0 (5) Mikrotik-Rate-Limit = "192k/768k 512k/2048k 160k/1024k 30/30" (5) Acct-Interim-Interval = 100 (5) Session-Timeout = 30718446 (5) Finished request * Pushed a fix (hopefully). -Arran Arran Cudbard-Bell <a.cudbardb at freeradius.org <http://lists.freeradius.org/mailman/listinfo/freeradius-users>> FreeRADIUS development team On Thu, Mar 3, 2016 at 9:10 PM, Devrim Seral <dseral@gmail.com> wrote:
Hello freeradius Users, I have some strange problem with SQL Xlat that i haven't find out the way to fix it.
I was used freeradius 2.x before and my SQL Xlat working. But when i was switched to 3.0.11 my SQL Xlat not working anymore.
As you can see following debug log has; sql: ERROR: Error marking pair for xlat sql: ERROR: Error parsing user data from database result sql: ERROR: Error retrieving reply pairs for group 15GB_Quota_2048KBurst
My database contains following lines in radgroupreply table;
15GB_Quota_2048KBurst Mikrotik-Recv-Limit := `%{sql:SELECT trafsumdw('%{User-Name}',15360)}` 15GB_Quota_2048KBurst Mikrotik-Xmit-Limit := `%{sql:SELECT trafsumup('%{User-Name}',3840)}`
As i understand %{sql: SQL } routines has a problem. How can i fix it.. Thanks devrim
Following part have my debug output;
radiusd: FreeRADIUS Version 3.0.11, for host x86_64-pc-linux-gnu, built on Feb 13 2016 at 16:58:54
(4) Received Access-Request Id 161 from 127.0.0.1:38043 to 127.0.0.1:1812 length 76 (4) User-Name = "dseral" (4) User-Password = "123" (4) NAS-IP-Address = 127.0.1.1 (4) NAS-Port = 111 (4) Message-Authenticator = 0x01a430a9fdf69589934e577266f130f2 (4) # Executing section authorize from file /etc/freeradius/sites-enabled/default (4) authorize { (4) policy filter_username { (4) if (&User-Name) { (4) if (&User-Name) -> TRUE (4) if (&User-Name) { (4) if (&User-Name =~ / /) { (4) if (&User-Name =~ / /) -> FALSE (4) if (&User-Name =~ /@[^@]*@/ ) { (4) if (&User-Name =~ /@[^@]*@/ ) -> FALSE (4) if (&User-Name =~ /\.\./ ) { (4) if (&User-Name =~ /\.\./ ) -> FALSE (4) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (4) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE
(4) if (&User-Name =~ /\.$/) { (4) if (&User-Name =~ /\.$/) -> FALSE (4) if (&User-Name =~ /@\./) { (4) if (&User-Name =~ /@\./) -> FALSE (4) } # if (&User-Name) = notfound (4) } # policy filter_username = notfound (4) [preprocess] = ok (4) [chap] = noop (4) [mschap] = noop (4) [digest] = noop (4) suffix: Checking for suffix after "@" (4) suffix: No '@' in User-Name = "dseral", looking up realm NULL (4) suffix: No such realm "NULL" (4) [suffix] = noop (4) eap: No EAP-Message, not doing EAP (4) [eap] = noop (4) [files] = noop (4) sql: EXPAND %{User-Name} (4) sql: --> dseral (4) sql: SQL-User-Name set to 'dseral'
rlm_sql (sql): You probably need to lower "min" rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): Closing connection (10): Hit idle_timeout, was idle for 4561 seconds rlm_sql (sql): You probably need to lower "min" rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): Closing connection (8): Hit idle_timeout, was idle for 4561 seconds rlm_sql (sql): You probably need to lower "min" rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): 0 of 0 connections in use. You may need to increase "spare" rlm_sql (sql): Opening additional connection (11), 1 of 32 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 5.5.47-0+deb8u1, protocol version 10 rlm_sql (sql): Reserved connection (11) (4) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id (4) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'dseral' ORDER BY id (4) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'dseral' ORDER BY id (4) sql: User found in radcheck table (4) sql: Conditional check items matched, merging assignment check items (4) sql: Cleartext-Password := "123" (4) sql: Expiration := "Feb 24 2017 00:00:00 EET" (4) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id (4) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'dseral' ORDER BY id (4) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'dseral' ORDER BY id (4) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (4) sql: --> SELECT groupname FROM radusergroup WHERE username = 'dseral' ORDER BY priority (4) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'dseral' ORDER BY priority (4) sql: User found in the group table (4) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id (4) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (4) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (4) sql: Group "15GB_Quota_2048KBurst": Conditional check items matched (4) sql: Group "15GB_Quota_2048KBurst": Merging assignment check items (4) sql: Simultaneous-Use := 1 (4) sql: EXPAND SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id (4) sql: --> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (4) sql: Executing select query: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (4) sql: ERROR: Error marking pair for xlat (4) sql: ERROR: Error parsing user data from database result (4) sql: ERROR: Error retrieving reply pairs for group 15GB_Quota_2048KBurst rlm_sql (sql): Released connection (11) rlm_sql (sql): Need 2 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (12), 1 of 31 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 5.5.47-0+deb8u1, protocol version 10 (4) [sql] = fail (4) } # authorize = fail (4) Using Post-Auth-Type Reject (4) # Executing group from file /etc/freeradius/sites-enabled/default (4) Post-Auth-Type REJECT { (4) sql: EXPAND .query (4) sql: --> .query (4) sql: Using query template 'query' rlm_sql (sql): Reserved connection (11) (4) sql: EXPAND %{User-Name} (4) sql: --> dseral (4) sql: SQL-User-Name set to 'dseral' (4) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, date) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') (4) sql: --> INSERT INTO radpostauth (username, pass, reply, date) VALUES ( 'dseral', '123', 'Access-Reject', '2016-03-03 20:48:36') (4) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, date) VALUES ( 'dseral', '123', 'Access-Reject', '2016-03-03 20:48:36') ...