Freeradius 3.0.11 SQL Xlat problem
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') ...
On 4 Mar 2016, at 08:53, Alan DeKok <aland@deployingradius.com> wrote:
On Mar 3, 2016, at 2: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.
It's odd. We're looking into it.
Pushed a fix (hopefully). -Arran Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS development team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
* 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') ...
On Mar 5, 2016, at 4:54 AM, Devrim Seral <dseral@gmail.com> wrote:
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)}`
Use double quotes "". Not back quotes ``. See "man unlang" for why. Alan DeKok.
On 5 Mar 2016, at 08:45, Alan DeKok <aland@deployingradius.com> wrote:
On Mar 5, 2016, at 4:54 AM, Devrim Seral <dseral@gmail.com> wrote:
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)}`
Use double quotes "". Not back quotes ``.
Yeah the code might be wrong there too, currently only backticks are being marked for expansion. Might need to check the behaviour in v2.2.x and see what it was originally intended to be. -Arran Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS development team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
* 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)}`
Hello Alan, Thanks for reply. But as you can see prior debug xlat in sql work with back quotes (` `) While i was tried to add xlat between "" in this case freeradius gives following error; (0) sql - Executing select query: SELECT id, groupname, attribute, value, o p FROM radgroupreply WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (0) sql - ERROR: Error parsing value: Unknown or invalid value "%{sql:SELEC T 100000}" for attribute Mikrotik-Xmit-Limit (0) sql - ERROR: Error parsing user data from database result (0) sql - ERROR: Error retrieving reply pairs for group 15GB_Quota_2048KBur st But while i change back to back quote its seem execute query but not replied as value. I think we miss some part.. Also i download github version 3.1.x and this behavior is same. Could you check please. Debug output for Radius 3.1.x; (1) sql - EXPAND SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = ' %{SQL-Group}' ORDER BY id (1) sql - --> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '15G B_Quota_2048KBurst' ORDER BY id (1) sql - Executing select query: SELECT id, groupname, attribute, value, op FROM radgroupreply WH ERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (1) sql - Group "15GB_Quota_2048KBurst": Merging reply items (1) sql - &Mikrotik-Recv-Limit := "`%{sql:SELECT trafsumdw('%{User-Name}',15360)}`" (1) sql - &Mikrotik-Xmit-Limit := "`%{sql:SELECT 100000}`" (1) sql - &Mikrotik-Rate-Limit := "192k/768k 512k/2048k 160k/1024k 30/30" (1) sql - &Acct-Interim-Interval := 100 (1) sql - EXPAND `%{sql:SELECT trafsumdw('%{User-Name}',15360)}` (1) sql - 1 of 1 connections in use. You may need to increase "spare" (1) sql - Opening additional connection (8), 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+de b8u1, protocol version 10 (1) sql - Reserved connection (8) (1) sql - Released connection (8) (1) sql - Need 1 more connections to reach 10 spares (1) sql - Opening additional connection (9), 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+de b8u1, protocol version 10 (1) sql - EXPAND %{User-Name} (1) sql - --> dseral (1) sql - SQL-User-Name set to 'dseral' (1) sql - Reserved connection (8) (1) sql - Executing select query: SELECT trafsumdw('dseral',15360) (1) sql - Released connection (8) (1) sql - --> `4294967290` (1) sql - EXPAND `%{sql:SELECT 100000}` (1) sql - EXPAND %{User-Name} (1) sql - --> dseral (1) sql - SQL-User-Name set to 'dseral' (1) sql - Reserved connection (9) (1) sql - Executing select query: SELECT 100000 (1) sql - Released connection (9) (1) sql - --> `100000` (1) sql - Released connection (7) (1) sql (ok) (1) Sent Access-Accept Id 192 from 127.0.0.1:1812 to 127.0.0.1:45590 via lo length 0 (1) Mikrotik-Recv-Limit = 0 (1) Mikrotik-Xmit-Limit = 0 (1) Mikrotik-Rate-Limit = "192k/768k 512k/2048k 160k/1024k 30/30" (1) Acct-Interim-Interval = 100 (1) Session-Timeout = 30695059 (1) Finished request Thanks. devrim On Mar 5, 2016, at 4:54 AM, Devrim Seral <dseral at gmail.com <http://lists.freeradius.org/mailman/listinfo/freeradius-users>> wrote: * Use double quotes "". Not back quotes ``. See "man unlang" for why. Alan DeKok. 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') ...
participants (3)
-
Alan DeKok -
Arran Cudbard-Bell -
Devrim Seral