Freeradius 3.0.11 SQL Xlat problem
Devrim Seral
dseral at gmail.com
Sat Mar 5 10:54:20 CET 2016
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
>* 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.
*
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 at 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')
> ...
>
>
>
>
More information about the Freeradius-Users
mailing list