Freeradius 3.0.11 SQL Xlat problem
Devrim Seral
dseral at gmail.com
Sat Mar 5 16:37:50 CET 2016
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:
> >* 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 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