dailycounter is not working with redundant sql
Oleg Sorocean
acdmail at gmail.com
Mon Mar 19 13:38:32 CET 2012
Dear all,
I have the radius configuration with 2 radius servers and mysql, I
configured radius for sql redundancy like below:
radiusd.conf
instantiate {
.....
redundant redundant_sql {
sql1
sql2
}
....
}
in default in each section authorization, accounting.... I am using
redundant_sql instead of sql
everything is working ok beside the daily counter which is not working
as expected if primary sql server is down
I configured daily counter like this
sqlcounter dailycounter {
counter-name = Daily-Session-Time
check-name = Max-Daily-Session
reply-name = Session-Timeout
sqlmod-inst=redundant_sql
key = User-Name
reset = daily
....
}
Which seems to be wrong, could you suggest please which would be the
correct way to configure this.
Also please see below the log for the user for which daily counter has
expired, but with on main sql server down, it is accepted in anyway.
Thank you in advance
Oleg
Radius log
---------------------------------------------------------------------------------------------------------------------------------
[root at radiusdb2 ~]# radiusd -X
FreeRADIUS Version 2.1.10, for host x86_64-unknown-linux-gnu, built on
Jul 19 2011 at 10:21:08
Copyright (C) 1999-2009 The FreeRADIUS server project and contributors.
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE.
You may redistribute copies of FreeRADIUS under the terms of the
GNU General Public License v2.
.......
........
......
Module: Loading virtual module redundant_sql
Module: Linked to module rlm_sql
Module: Instantiating module "sql1" from file /etc/raddb/sql.conf
sql sql1 {
driver = "rlm_sql_mysql"
server = "localhost"
port = ""
login = "XXXXXXXXXX"
password = "XXXXXXXXX"
radius_db = "XXXX"
read_groups = yes
sqltrace = yes
sqltracefile = "/var/log/radius/sqltrace.sql"
readclients = no
deletestalesessions = yes
num_sql_socks = 5
lifetime = 0
max_queries = 0
sql_user_name = "%{User-Name}"
default_user_profile = ""
nas_query = "SELECT id, nasname, shortname, type, secret,
server FROM nas"
authorize_check_query = "SELECT id, username, attribute,
value, op FROM radcheck WHERE username =
'%{SQL-User-Name}' ORDER BY id"
authorize_reply_query = "SELECT id, username, attribute,
value, op FROM radreply WHERE username =
'%{SQL-User-Name}' ORDER BY id"
authorize_group_check_query = "SELECT id, groupname,
attribute, Value, op FROM radgroupcheck
WHERE groupname = '%{Sql-Group}' ORDER BY id"
authorize_group_reply_query = "SELECT id, groupname,
attribute, value, op FROM radgroupreply
WHERE groupname = '%{Sql-Group}' ORDER BY id"
accounting_onoff_query = " UPDATE radacct
SET acctstoptime = '%S',
acctsessiontime = unix_timestamp('%S') -
unix_timestamp(acctstarttime),
acctterminatecause = '%{Acct-Terminate-Cause}',
acctstopdelay = %{%{Acct-Delay-Time}:-0} WHERE
acctstoptime IS NULL AND nasipaddress =
'%{NAS-IP-Address}' AND acctstarttime <= '%S'"
accounting_update_query = " UPDATE radacct
SET framedipaddress = '%{Framed-IP-Address}',
acctsessiontime = '%{Acct-Session-Time}',
acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 |
'%{%{Acct-Input-Octets}:-0}',
acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 |
'%{%{Acct-Output-Octets}:-0}'
WHERE acctsessionid = '%{Acct-Session-Id}' AND username
= '%{SQL-User-Name}' AND nasipaddress =
'%{NAS-IP-Address}'"
accounting_update_query_alt = " INSERT INTO radacct
(acctsessionid, acctuniqueid, username,
realm, nasipaddress, nasportid,
nasporttype, acctstarttime, acctsessiontime,
acctauthentic, connectinfo_start, acctinputoctets,
acctoutputoctets, calledstationid, callingstationid,
servicetype, framedprotocol, framedipaddress,
acctstartdelay, xascendsessionsvrkey) VALUES
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}',
'%{NAS-Port}', '%{NAS-Port-Type}',
DATE_SUB('%S', INTERVAL
(%{%{Acct-Session-Time}:-0} +
%{%{Acct-Delay-Time}:-0}) SECOND),
'%{Acct-Session-Time}', '%{Acct-Authentic}', '',
'%{%{Acct-Input-Gigawords}:-0}' << 32 |
'%{%{Acct-Input-Octets}:-0}',
'%{%{Acct-Output-Gigawords}:-0}' << 32 |
'%{%{Acct-Output-Octets}:-0}', '%{Called-Station-Id}',
'%{Calling-Station-Id}', '%{Service-Type}',
'%{Framed-Protocol}', '%{Framed-IP-Address}',
'0', '%{X-Ascend-Session-Svr-Key}')"
accounting_start_query = " INSERT INTO radacct
(acctsessionid, acctuniqueid, username,
realm, nasipaddress, nasportid,
nasporttype, acctstarttime, acctstoptime,
acctsessiontime, acctauthentic, connectinfo_start,
connectinfo_stop, acctinputoctets, acctoutputoctets,
calledstationid, callingstationid, acctterminatecause,
servicetype, framedprotocol, framedipaddress,
acctstartdelay, acctstopdelay, xascendsessionsvrkey)
VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}',
'%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}',
'%S', NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}',
'', '0', '0', '%{Called-Station-Id}',
'%{Calling-Station-Id}', '', '%{Service-Type}',
'%{Framed-Protocol}', '%{Framed-IP-Address}',
'%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"
accounting_start_query_alt = " UPDATE radacct SET
acctstarttime = '%S', acctstartdelay =
'%{%{Acct-Delay-Time}:-0}', connectinfo_start =
'%{Connect-Info}' WHERE acctsessionid =
'%{Acct-Session-Id}' AND username =
'%{SQL-User-Name}' AND nasipaddress =
'%{NAS-IP-Address}'"
accounting_stop_query = " UPDATE radacct SET
acctstoptime = '%S', acctsessiontime =
'%{Acct-Session-Time}', acctinputoctets =
'%{%{Acct-Input-Gigawords}:-0}' << 32 |
'%{%{Acct-Input-Octets}:-0}', acctoutputoctets =
'%{%{Acct-Output-Gigawords}:-0}' << 32 |
'%{%{Acct-Output-Octets}:-0}', acctterminatecause =
'%{Acct-Terminate-Cause}', acctstopdelay =
'%{%{Acct-Delay-Time}:-0}', connectinfo_stop =
'%{Connect-Info}' WHERE acctsessionid =
'%{Acct-Session-Id}' AND username =
'%{SQL-User-Name}' AND nasipaddress =
'%{NAS-IP-Address}'"
accounting_stop_query_alt = " INSERT INTO radacct
(acctsessionid, acctuniqueid, username, realm,
nasipaddress, nasportid, nasporttype, acctstarttime,
acctstoptime, acctsessiontime, acctauthentic,
connectinfo_start, connectinfo_stop, acctinputoctets,
acctoutputoctets, calledstationid, callingstationid,
acctterminatecause, servicetype, framedprotocol,
framedipaddress, acctstartdelay, acctstopdelay)
VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}',
'%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}',
DATE_SUB('%S', INTERVAL
(%{%{Acct-Session-Time}:-0} +
%{%{Acct-Delay-Time}:-0}) SECOND), '%S',
'%{Acct-Session-Time}', '%{Acct-Authentic}', '',
'%{Connect-Info}', '%{%{Acct-Input-Gigawords}:-0}' << 32
| '%{%{Acct-Input-Octets}:-0}',
'%{%{Acct-Output-Gigawords}:-0}' << 32 |
'%{%{Acct-Output-Octets}:-0}', '%{Called-Station-Id}',
'%{Calling-Station-Id}', '%{Acct-Terminate-Cause}',
'%{Service-Type}', '%{Framed-Protocol}',
'%{Framed-IP-Address}', '0', '%{%{Acct-Delay-Time}:-0}')"
group_membership_query = "SELECT groupname FROM
radusergroup WHERE username = '%{SQL-User-Name}'
ORDER BY priority"
connect_failure_retry_delay = 60
simul_count_query = "SELECT COUNT(*)
FROM radacct WHERE username =
'%{SQL-User-Name}' AND acctstoptime IS
NULL AND acctsessionid !=
'%{Acct-Session-Id}'"
simul_verify_query = "SELECT radacctid, acctsessionid,
username, nasipaddress, nasportid,
framedipaddress, callingstationid,
framedprotocol FROM radacct
WHERE username = '%{SQL-User-Name}'
AND acctstoptime IS NULL"
postauth_query = "INSERT INTO radpostauth
(username, pass, reply, authdate,reply_message,nas_ip)
VALUES ( '%{User-Name}',
'%{%{User-Password}:-%{Chap-Password}}',
'%{reply:Packet-Type}',
'%S','%{reply:Reply-Message}','%{NAS-IP-Address}')"
safe-characters =
"@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_:
/"
}
rlm_sql Creating new attribute sql1-SQL-Group
rlm_sql: Registering sql_groupcmp for sql1-SQL-Group
rlm_sql (sql1): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
rlm_sql (sql1): Attempting to connect to radius at localhost:/radius
rlm_sql (sql1): starting 0
rlm_sql (sql1): Attempting to connect rlm_sql_mysql #0
rlm_sql_mysql: Starting connect to MySQL server for #0
rlm_sql_mysql: Couldn't connect socket to MySQL server radius at localhost:radius
rlm_sql_mysql: Mysql error 'Can't connect to local MySQL server
through socket '/var/lib/mysql/mysql.sock' (2)'
rlm_sql (sql1): Failed to connect DB handle #0
rlm_sql (sql1): starting 1
rlm_sql (sql1): starting 2
rlm_sql (sql1): starting 3
rlm_sql (sql1): starting 4
rlm_sql (sql1): Failed to connect to any SQL server.
Module: Instantiating module "sql2" from file /etc/raddb/sql.conf
sql sql2 {
driver = "rlm_sql_mysql"
server = "radius-db3"
port = "3306"
login = "XXXXX"
password = "XXXXXXXXXXXXXXXXXXXXXXXX"
radius_db = "XXXXX"
read_groups = yes
sqltrace = yes
sqltracefile = "/var/log/radius/sqltrace.sql"
readclients = no
deletestalesessions = yes
num_sql_socks = 5
lifetime = 0
max_queries = 0
sql_user_name = "%{User-Name}"
default_user_profile = ""
nas_query = "SELECT id, nasname, shortname, type, secret,
server FROM nas"
authorize_check_query = "SELECT id, username, attribute,
value, op FROM radcheck WHERE username =
'%{SQL-User-Name}' ORDER BY id"
authorize_reply_query = "SELECT id, username, attribute,
value, op FROM radreply WHERE username =
'%{SQL-User-Name}' ORDER BY id"
authorize_group_check_query = "SELECT id, groupname,
attribute, Value, op FROM radgroupcheck
WHERE groupname = '%{Sql-Group}' ORDER BY id"
authorize_group_reply_query = "SELECT id, groupname,
attribute, value, op FROM radgroupreply
WHERE groupname = '%{Sql-Group}' ORDER BY id"
accounting_onoff_query = " UPDATE radacct
SET acctstoptime = '%S',
acctsessiontime = unix_timestamp('%S') -
unix_timestamp(acctstarttime),
acctterminatecause = '%{Acct-Terminate-Cause}',
acctstopdelay = %{%{Acct-Delay-Time}:-0} WHERE
acctstoptime IS NULL AND nasipaddress =
'%{NAS-IP-Address}' AND acctstarttime <= '%S'"
accounting_update_query = " UPDATE radacct
SET framedipaddress = '%{Framed-IP-Address}',
acctsessiontime = '%{Acct-Session-Time}',
acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 |
'%{%{Acct-Input-Octets}:-0}',
acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 |
'%{%{Acct-Output-Octets}:-0}'
WHERE acctsessionid = '%{Acct-Session-Id}' AND username
= '%{SQL-User-Name}' AND nasipaddress =
'%{NAS-IP-Address}'"
accounting_update_query_alt = " INSERT INTO radacct
(acctsessionid, acctuniqueid, username,
realm, nasipaddress, nasportid,
nasporttype, acctstarttime, acctsessiontime,
acctauthentic, connectinfo_start, acctinputoctets,
acctoutputoctets, calledstationid, callingstationid,
servicetype, framedprotocol, framedipaddress,
acctstartdelay, xascendsessionsvrkey) VALUES
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}',
'%{NAS-Port}', '%{NAS-Port-Type}',
DATE_SUB('%S', INTERVAL
(%{%{Acct-Session-Time}:-0} +
%{%{Acct-Delay-Time}:-0}) SECOND),
'%{Acct-Session-Time}', '%{Acct-Authentic}', '',
'%{%{Acct-Input-Gigawords}:-0}' << 32 |
'%{%{Acct-Input-Octets}:-0}',
'%{%{Acct-Output-Gigawords}:-0}' << 32 |
'%{%{Acct-Output-Octets}:-0}', '%{Called-Station-Id}',
'%{Calling-Station-Id}', '%{Service-Type}',
'%{Framed-Protocol}', '%{Framed-IP-Address}',
'0', '%{X-Ascend-Session-Svr-Key}')"
accounting_start_query = " INSERT INTO radacct
(acctsessionid, acctuniqueid, username,
realm, nasipaddress, nasportid,
nasporttype, acctstarttime, acctstoptime,
acctsessiontime, acctauthentic, connectinfo_start,
connectinfo_stop, acctinputoctets, acctoutputoctets,
calledstationid, callingstationid, acctterminatecause,
servicetype, framedprotocol, framedipaddress,
acctstartdelay, acctstopdelay, xascendsessionsvrkey)
VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}',
'%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}',
'%S', NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}',
'', '0', '0', '%{Called-Station-Id}',
'%{Calling-Station-Id}', '', '%{Service-Type}',
'%{Framed-Protocol}', '%{Framed-IP-Address}',
'%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"
accounting_start_query_alt = " UPDATE radacct SET
acctstarttime = '%S', acctstartdelay =
'%{%{Acct-Delay-Time}:-0}', connectinfo_start =
'%{Connect-Info}' WHERE acctsessionid =
'%{Acct-Session-Id}' AND username =
'%{SQL-User-Name}' AND nasipaddress =
'%{NAS-IP-Address}'"
accounting_stop_query = " UPDATE radacct SET
acctstoptime = '%S', acctsessiontime =
'%{Acct-Session-Time}', acctinputoctets =
'%{%{Acct-Input-Gigawords}:-0}' << 32 |
'%{%{Acct-Input-Octets}:-0}', acctoutputoctets =
'%{%{Acct-Output-Gigawords}:-0}' << 32 |
'%{%{Acct-Output-Octets}:-0}', acctterminatecause =
'%{Acct-Terminate-Cause}', acctstopdelay =
'%{%{Acct-Delay-Time}:-0}', connectinfo_stop =
'%{Connect-Info}' WHERE acctsessionid =
'%{Acct-Session-Id}' AND username =
'%{SQL-User-Name}' AND nasipaddress =
'%{NAS-IP-Address}'"
accounting_stop_query_alt = " INSERT INTO radacct
(acctsessionid, acctuniqueid, username, realm,
nasipaddress, nasportid, nasporttype, acctstarttime,
acctstoptime, acctsessiontime, acctauthentic,
connectinfo_start, connectinfo_stop, acctinputoctets,
acctoutputoctets, calledstationid, callingstationid,
acctterminatecause, servicetype, framedprotocol,
framedipaddress, acctstartdelay, acctstopdelay)
VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}',
'%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}',
DATE_SUB('%S', INTERVAL
(%{%{Acct-Session-Time}:-0} +
%{%{Acct-Delay-Time}:-0}) SECOND), '%S',
'%{Acct-Session-Time}', '%{Acct-Authentic}', '',
'%{Connect-Info}', '%{%{Acct-Input-Gigawords}:-0}' << 32
| '%{%{Acct-Input-Octets}:-0}',
'%{%{Acct-Output-Gigawords}:-0}' << 32 |
'%{%{Acct-Output-Octets}:-0}', '%{Called-Station-Id}',
'%{Calling-Station-Id}', '%{Acct-Terminate-Cause}',
'%{Service-Type}', '%{Framed-Protocol}',
'%{Framed-IP-Address}', '0', '%{%{Acct-Delay-Time}:-0}')"
group_membership_query = "SELECT groupname FROM
radusergroup WHERE username = '%{SQL-User-Name}'
ORDER BY priority"
connect_failure_retry_delay = 60
simul_count_query = "SELECT COUNT(*)
FROM radacct WHERE username =
'%{SQL-User-Name}' AND acctstoptime IS
NULL AND acctsessionid !=
'%{Acct-Session-Id}'"
simul_verify_query = "SELECT radacctid, acctsessionid,
username, nasipaddress, nasportid,
framedipaddress, callingstationid,
framedprotocol FROM radacct
WHERE username = '%{SQL-User-Name}'
AND acctstoptime IS NULL"
postauth_query = "INSERT INTO radpostauth
(username, pass, reply, authdate,reply_message,nas_ip)
VALUES ( '%{User-Name}',
'%{%{User-Password}:-%{Chap-Password}}',
'%{reply:Packet-Type}',
'%S','%{reply:Reply-Message}','%{NAS-IP-Address}')"
safe-characters =
"@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_:
/"
}
rlm_sql Creating new attribute sql2-SQL-Group
rlm_sql: Registering sql_groupcmp for sql2-SQL-Group
rlm_sql (sql2): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
rlm_sql (sql2): Attempting to connect to radius at radius-db3:3306/radius
rlm_sql (sql2): starting 0
rlm_sql (sql2): Attempting to connect rlm_sql_mysql #0
rlm_sql_mysql: Starting connect to MySQL server for #0
rlm_sql (sql2): Connected new DB handle, #0
rlm_sql (sql2): starting 1
rlm_sql (sql2): Attempting to connect rlm_sql_mysql #1
rlm_sql_mysql: Starting connect to MySQL server for #1
rlm_sql (sql2): Connected new DB handle, #1
rlm_sql (sql2): starting 2
rlm_sql (sql2): Attempting to connect rlm_sql_mysql #2
rlm_sql_mysql: Starting connect to MySQL server for #2
rlm_sql (sql2): Connected new DB handle, #2
rlm_sql (sql2): starting 3
rlm_sql (sql2): Attempting to connect rlm_sql_mysql #3
rlm_sql_mysql: Starting connect to MySQL server for #3
rlm_sql (sql2): Connected new DB handle, #3
rlm_sql (sql2): starting 4
rlm_sql (sql2): Attempting to connect rlm_sql_mysql #4
rlm_sql_mysql: Starting connect to MySQL server for #4
rlm_sql (sql2): Connected new DB handle, #4
Module: Checking post-auth {...} for more modules to load
Module: Linked to module rlm_attr_filter
Module: Instantiating module "attr_filter.access_reject" from file
/etc/raddb/modules/attr_filter
attr_filter attr_filter.access_reject {
attrsfile = "/etc/raddb/attrs.access_reject"
key = "%{User-Name}"
}
} # modules
} # server
server { # from file /etc/raddb/radiusd.conf
modules {
Module: Checking authenticate {...} for more modules to load
Module: Checking authorize {...} for more modules to load
Module: Linked to module rlm_preprocess
Module: Instantiating module "preprocess" from file
/etc/raddb/modules/preprocess
preprocess {
huntgroups = "/etc/raddb/huntgroups"
hints = "/etc/raddb/hints"
with_ascend_hack = no
ascend_channels_per_line = 23
with_ntdomain_hack = no
with_specialix_jetstream_hack = no
with_cisco_vsa_hack = no
with_alvarion_vsa_hack = no
}
Module: Loading virtual module redundant_sql
Module: Linked to module rlm_sqlcounter
Module: Instantiating module "dailycounter" from file
/etc/raddb/sql/mysql/counter.conf
sqlcounter dailycounter {
counter-name = "Daily-Session-Time"
check-name = "Max-Daily-Session"
reply-name = "Session-Timeout"
key = "User-Name"
sqlmod-inst = "redundant_sql"
query = "SELECT SUM(acctsessiontime) FROM radacct
WHERE username = '%{%k}' AND acctstarttime BETWEEN
FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
reset = "daily"
safe-characters =
"@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_:
/"
}
rlm_sqlcounter: Reply attribute Session-Timeout is number 27
rlm_sqlcounter: Counter attribute Daily-Session-Time is number 11275
rlm_sqlcounter: Check attribute Max-Daily-Session is number 11276
rlm_sqlcounter: Current Time: 1331850151 [2012-03-15 23:22:31], Next
reset 1331852400 [2012-03-16 00:00:00]
rlm_sqlcounter: Current Time: 1331850151 [2012-03-15 23:22:31], Prev
reset 1331766000 [2012-03-15 00:00:00]
Module: Checking preacct {...} for more modules to load
Module: Linked to module rlm_acct_unique
Module: Instantiating module "acct_unique" from file
/etc/raddb/modules/acct_unique
acct_unique {
key = "User-Name, Acct-Session-Id, NAS-IP-Address,
Client-IP-Address, NAS-Port"
}
Module: Checking accounting {...} for more modules to load
Module: Linked to module rlm_unix
Module: Instantiating module "unix" from file /etc/raddb/modules/unix
unix {
radwtmp = "/var/log/radius/radwtmp"
}
Module: Loading virtual module redundant_sql
Module: Instantiating module "attr_filter.accounting_response" from
file /etc/raddb/modules/attr_filter
attr_filter attr_filter.accounting_response {
attrsfile = "/etc/raddb/attrs.accounting_response"
key = "%{User-Name}"
}
Module: Checking session {...} for more modules to load
Module: Loading virtual module redundant_sql
Module: Checking post-auth {...} for more modules to load
Module: Loading virtual module redundant_sql
} # modules
} # server
radiusd: #### Opening IP addresses and Ports ####
listen {
type = "auth"
ipaddr = *
port = 0
}
listen {
type = "acct"
ipaddr = *
port = 0
}
listen {
type = "control"
listen {
socket = "/var/run/radiusd/radiusd.sock"
}
}
Listening on authentication address * port 1812
Listening on accounting address * port 1813
Listening on command file /var/run/radiusd/radiusd.sock
Ready to process requests.
rad_recv: Access-Request packet from host x.y.z.k port 45064, id=111, length=142
User-Name = "test11 at test.com"
User-Password = "xxxxxx"
NAS-IP-Address = x.y.z.k
NAS-Port = 2
Service-Type = Outbound-User
Calling-Station-Id = "x1.x2.x3.x4"
NAS-Identifier = "OpenVpn-tcp"
Acct-Session-Id = "7815B44B60809E4755317B1613AE0024"
NAS-Port-Type = Virtual
# Executing section authorize from file /etc/raddb/sites-enabled/default
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
++[mschap] returns noop
[suffix] Looking up realm "test.com" for User-Name = "test11 at test.com"
[suffix] No such realm "test.com"
++[suffix] returns noop
++[files] returns noop
++- entering group redundant_sql {...}
[sql1] expand: %{User-Name} -> test11 at test.com
[sql1] sql_set_user escaped user --> 'test11 at test.com'
rlm_sql (sql1): Ignoring unconnected handle 4..
rlm_sql (sql1): Ignoring unconnected handle 3..
rlm_sql (sql1): Ignoring unconnected handle 2..
rlm_sql (sql1): Ignoring unconnected handle 1..
rlm_sql (sql1): Ignoring unconnected handle 0..
rlm_sql (sql1): There are no DB handles to use! skipped 5, tried to connect 0
+++[sql1] returns fail
[sql2] expand: %{User-Name} -> test11 at test.com
[sql2] sql_set_user escaped user --> 'test11 at test.com'
rlm_sql (sql2): Reserving sql socket id: 4
[sql2] expand: SELECT id, username, attribute, value, op
FROM radcheck WHERE username = '%{SQL-User-Name}'
ORDER BY id -> SELECT id, username, attribute, value, op
FROM radcheck WHERE username = 'test11 at test.com'
ORDER BY id
rlm_sql_mysql: query: SELECT id, username, attribute, value, op
FROM radcheck WHERE username = 'test11 at test.com'
ORDER BY id
[sql2] User found in radcheck table
[sql2] expand: SELECT id, username, attribute, value, op
FROM radreply WHERE username = '%{SQL-User-Name}'
ORDER BY id -> SELECT id, username, attribute, value, op
FROM radreply WHERE username = 'test11 at test.com'
ORDER BY id
rlm_sql_mysql: query: SELECT id, username, attribute, value, op
FROM radreply WHERE username = 'test11 at test.com'
ORDER BY id
[sql2] expand: SELECT groupname FROM radusergroup
WHERE username = '%{SQL-User-Name}' ORDER BY priority ->
SELECT groupname FROM radusergroup WHERE username
= 'test11 at test.com' ORDER BY priority
rlm_sql_mysql: query: SELECT groupname FROM radusergroup
WHERE username = 'test11 at test.com' ORDER BY priority
[sql2] expand: SELECT id, groupname, attribute, Value, op
FROM radgroupcheck WHERE groupname = '%{Sql-Group}'
ORDER BY id -> SELECT id, groupname, attribute,
Value, op FROM radgroupcheck WHERE groupname =
'freeuser' ORDER BY id
rlm_sql_mysql: query: SELECT id, groupname, attribute,
Value, op FROM radgroupcheck WHERE groupname =
'freeuser' ORDER BY id
[sql2] User found in group freeuser
[sql2] expand: SELECT id, groupname, attribute, value, op
FROM radgroupreply WHERE groupname = '%{Sql-Group}'
ORDER BY id -> SELECT id, groupname, attribute,
value, op FROM radgroupreply WHERE groupname =
'freeuser' ORDER BY id
rlm_sql_mysql: query: SELECT id, groupname, attribute,
value, op FROM radgroupreply WHERE groupname =
'freeuser' ORDER BY id
rlm_sql (sql2): Released sql socket id: 4
+++[sql2] returns ok
++- group redundant_sql returns ok
[expiration] Checking Expiration time: 'Dec 29 2012 20:36:50'
++[expiration] returns ok
++[logintime] returns noop
++[pap] returns updated
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand: 'SELECT SUM(acctsessiontime) FROM radacct
WHERE username = '%{User-Name}' AND acctstarttime BETWEEN
FROM_UNIXTIME('1331766000') AND FROM_UNIXTIME('1331852400')'
[dailycounter] expand: SELECT SUM(acctsessiontime) FROM radacct
WHERE username = '%{User-Name}' AND acctstarttime BETWEEN
FROM_UNIXTIME('1331766000') AND
FROM_UNIXTIME('1331852400') -> SELECT SUM(acctsessiontime) FROM
radacct WHERE username = 'test11 at test.com' AND
acctstarttime BETWEEN FROM_UNIXTIME('1331766000') AND
FROM_UNIXTIME('1331852400')
sqlcounter_expand: '%{redundant_sql:SELECT SUM(acctsessiontime) FROM
radacct WHERE username = 'test11 at test.com' AND
acctstarttime BETWEEN FROM_UNIXTIME('1331766000') AND
FROM_UNIXTIME('1331852400')}'
[dailycounter] WARNING: Unknown module "redundant_sql" in string expansion "%"
rlm_sqlcounter: No integer found in string ""
++[dailycounter] returns noop
Found Auth-Type = PAP
# Executing group from file /etc/raddb/sites-enabled/default
+- entering group PAP {...}
[pap] login attempt with password "xxxxx"
[pap] Using clear text password "xxxxx"
[pap] User authenticated successfully
++[pap] returns ok
# Executing section session from file /etc/raddb/sites-enabled/default
+- entering group session {...}
++- entering group redundant_sql {...}
[sql1] expand: %{User-Name} -> test11 at test.com
[sql1] sql_set_user escaped user --> 'test11 at test.com'
[sql1] expand: SELECT COUNT(*) FROM
radacct WHERE username =
'%{SQL-User-Name}' AND acctstoptime IS
NULL AND acctsessionid !=
'%{Acct-Session-Id}' -> SELECT COUNT(*)
FROM radacct WHERE username =
'test11 at test.com' AND acctstoptime IS
NULL AND acctsessionid !=
'7815B44B60809E4755317B1613AE0024'
rlm_sql (sql1): Ignoring unconnected handle 4..
rlm_sql (sql1): Ignoring unconnected handle 3..
rlm_sql (sql1): Ignoring unconnected handle 2..
rlm_sql (sql1): Ignoring unconnected handle 1..
rlm_sql (sql1): Ignoring unconnected handle 0..
+++[sql1] returns fail
[sql2] expand: %{User-Name} -> test11 at test.com
[sql2] sql_set_user escaped user --> 'test11 at test.com'
[sql2] expand: SELECT COUNT(*) FROM
radacct WHERE username =
'%{SQL-User-Name}' AND acctstoptime IS
NULL AND acctsessionid !=
'%{Acct-Session-Id}' -> SELECT COUNT(*)
FROM radacct WHERE username =
'test11 at test.com' AND acctstoptime IS
NULL AND acctsessionid !=
'7815B44B60809E4755317B1613AE0024'
rlm_sql (sql2): Reserving sql socket id: 3
rlm_sql_mysql: query: SELECT COUNT(*)
FROM radacct WHERE username =
'test11 at test.com' AND acctstoptime IS
NULL AND acctsessionid !=
'7815B44B60809E4755317B1613AE0024'
rlm_sql (sql2): Released sql socket id: 3
+++[sql2] returns ok
++- group redundant_sql returns ok
Login OK: [test11 at test.com/xxxxx] (from client nas1.uk port 2 cli x1.x2.x3.x4)
# Executing section post-auth from file /etc/raddb/sites-enabled/default
+- entering group post-auth {...}
++- entering group redundant_sql {...}
[sql1] expand: %{User-Name} -> test11 at test.com
[sql1] sql_set_user escaped user --> 'test11 at test.com'
[sql1] expand: %{User-Password} -> xxxxxxx
[sql1] expand: INSERT INTO radpostauth
(username, pass, reply, authdate,reply_message,nas_ip)
VALUES ( '%{User-Name}',
'%{%{User-Password}:-%{Chap-Password}}',
'%{reply:Packet-Type}',
'%S','%{reply:Reply-Message}','%{NAS-IP-Address}') -> INSERT INTO
radpostauth (username, pass, reply,
authdate,reply_message,nas_ip) VALUES (
'test11 at test.com',
'Test123=241', 'Access-Accept', '2012-03-15
23:23:19','','x.y.z.k')
[sql1] expand: /var/log/radius/sqltrace.sql -> /var/log/radius/sqltrace.sql
rlm_sql (sql1) in sql_postauth: query is INSERT INTO radpostauth
(username, pass, reply,
authdate,reply_message,nas_ip) VALUES (
'test11 at test.com',
'Test123=241', 'Access-Accept', '2012-03-15
23:23:19','','x.y.z.k')
rlm_sql (sql1): Ignoring unconnected handle 4..
rlm_sql (sql1): Ignoring unconnected handle 3..
rlm_sql (sql1): Ignoring unconnected handle 2..
rlm_sql (sql1): Ignoring unconnected handle 1..
rlm_sql (sql1): Ignoring unconnected handle 0..
+++[sql1] returns fail
[sql2] expand: %{User-Name} -> test11 at test.com
[sql2] sql_set_user escaped user --> 'test11 at test.com'
[sql2] expand: %{User-Password} -> Test123=241
[sql2] expand: INSERT INTO radpostauth
(username, pass, reply, authdate,reply_message,nas_ip)
VALUES ( '%{User-Name}',
'%{%{User-Password}:-%{Chap-Password}}',
'%{reply:Packet-Type}',
'%S','%{reply:Reply-Message}','%{NAS-IP-Address}') -> INSERT INTO
radpostauth (username, pass, reply,
authdate,reply_message,nas_ip) VALUES (
'test11 at test.com',
'Test123=241', 'Access-Accept', '2012-03-15
23:23:19','','x.y.z.k')
[sql2] expand: /var/log/radius/sqltrace.sql -> /var/log/radius/sqltrace.sql
rlm_sql (sql2) in sql_postauth: query is INSERT INTO radpostauth
(username, pass, reply,
authdate,reply_message,nas_ip) VALUES (
'test11 at test.com',
'Test123=241', 'Access-Accept', '2012-03-15
23:23:19','','x.y.z.k')
rlm_sql (sql2): Reserving sql socket id: 2
rlm_sql_mysql: query: INSERT INTO radpostauth
(username, pass, reply, authdate,reply_message,nas_ip)
VALUES ( 'test11 at test.com',
'Test123=241',
'Access-Accept', '2012-03-15 23:23:19','','x.y.z.k')
rlm_sql (sql2): Released sql socket id: 2
+++[sql2] returns ok
++- group redundant_sql returns ok
++[exec] returns noop
Sending Access-Accept of id 111 to x.y.z.k port 45064
Session-Timeout := 1800
Acct-Interim-Interval := 600
Finished request 0.
Going to the next request
Waking up in 4.9 seconds.
rad_recv: Accounting-Request packet from host x.y.z.k port 53711,
id=240, length=142
User-Name = "test11 at test.com"
NAS-IP-Address = x.y.z.k
NAS-Port = 2
Service-Type = Outbound-User
Framed-Protocol = PPP
Framed-IP-Address = 10.100.0.6
Calling-Station-Id = "x1.x2.x3.x4"
NAS-Identifier = "OpenVpn-tcp"
Acct-Status-Type = Start
Acct-Session-Id = "7815B44B60809E4755317B1613AE0024"
NAS-Port-Type = Virtual
# Executing section preacct from file /etc/raddb/sites-enabled/default
+- entering group preacct {...}
++[preprocess] returns ok
[acct_unique] Hashing 'NAS-Port = 2,Client-IP-Address =
x.y.z.k,NAS-IP-Address = x.y.z.k,Acct-Session-Id =
"7815B44B60809E4755317B1613AE0024",User-Name = "test11 at test.com"'
[acct_unique] Acct-Unique-Session-ID = "cf0adf9ddd6a5d82".
++[acct_unique] returns ok
[suffix] Looking up realm "test.com" for User-Name = "test11 at test.com"
[suffix] No such realm "test.com"
++[suffix] returns noop
++[files] returns noop
# Executing section accounting from file /etc/raddb/sites-enabled/default
+- entering group accounting {...}
++[unix] returns ok
++- entering group redundant_sql {...}
[sql1] expand: %{User-Name} -> test11 at test.com
[sql1] sql_set_user escaped user --> 'test11 at test.com'
[sql1] expand: %{Acct-Delay-Time} ->
[sql1] ... expanding second conditional
[sql1] expand: INSERT INTO radacct
(acctsessionid, acctuniqueid, username, realm,
nasipaddress, nasportid, nasporttype,
acctstarttime, acctstoptime, acctsessiontime,
acctauthentic, connectinfo_start, connectinfo_stop,
acctinputoctets, acctoutputoctets, calledstationid,
callingstationid, acctterminatecause, servicetype,
framedprotocol, framedipaddress, acctstartdelay,
acctstopdelay, xascendsessionsvrkey) VALUES
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}',
'%{NAS-Port}', '%{NAS-Port-Type}', '%S', NULL,
'0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0',
'0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '',
'%{Service-Type}', '%{Framed-Protocol}',
'%{Framed-IP-Address}',
[sql1] expand: /var/log/radius/sqltrace.sql -> /var/log/radius/sqltrace.sql
rlm_sql (sql1): Ignoring unconnected handle 4..
rlm_sql (sql1): Ignoring unconnected handle 3..
rlm_sql (sql1): Ignoring unconnected handle 2..
rlm_sql (sql1): Ignoring unconnected handle 1..
rlm_sql (sql1): Ignoring unconnected handle 0..
+++[sql1] returns fail
[sql2] expand: %{User-Name} -> test11 at test.com
[sql2] sql_set_user escaped user --> 'test11 at test.com'
[sql2] expand: %{Acct-Delay-Time} ->
[sql2] ... expanding second conditional
[sql2] expand: INSERT INTO radacct
(acctsessionid, acctuniqueid, username, realm,
nasipaddress, nasportid, nasporttype,
acctstarttime, acctstoptime, acctsessiontime,
acctauthentic, connectinfo_start, connectinfo_stop,
acctinputoctets, acctoutputoctets, calledstationid,
callingstationid, acctterminatecause, servicetype,
framedprotocol, framedipaddress, acctstartdelay,
acctstopdelay, xascendsessionsvrkey) VALUES
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}',
'%{NAS-Port}', '%{NAS-Port-Type}', '%S', NULL,
'0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0',
'0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '',
'%{Service-Type}', '%{Framed-Protocol}',
'%{Framed-IP-Address}',
[sql2] expand: /var/log/radius/sqltrace.sql -> /var/log/radius/sqltrace.sql
rlm_sql (sql2): Reserving sql socket id: 1
rlm_sql_mysql: query: INSERT INTO radacct
(acctsessionid, acctuniqueid, username, realm,
nasipaddress, nasportid, nasporttype,
acctstarttime, acctstoptime, acctsessiontime,
acctauthentic, connectinfo_start, connectinfo_stop,
acctinputoctets, acctoutputoctets, calledstationid,
callingstationid, acctterminatecause, servicetype,
framedprotocol, framedipaddress, acctstartdelay,
acctstopdelay, xascendsessionsvrkey) VALUES
('7815B44B60809E4755317B1613AE0024', 'cf0adf9ddd6a5d82',
'test11 at test.com', '', 'x.y.z.k', '2',
'Virtual', '2012-03-15 23:23:19', NULL, '0', '', '',
'', '0', '0', '', 'x1.x2.x3.x4', '',
'Outbound-User', 'PPP', '10.100.0.6', '0', '0', '')
rlm_sql (sql2): Released sql socket id: 1
+++[sql2] returns ok
++- group redundant_sql returns ok
[attr_filter.accounting_response] expand: %{User-Name} -> test11 at test.com
attr_filter: Matched entry DEFAULT at line 12
++[attr_filter.accounting_response] returns updated
Sending Accounting-Response of id 240 to x.y.z.k port 53711
Finished request 1.
Cleaning up request 1 ID 240 with timestamp +48
Going to the next request
Waking up in 4.7 seconds.
Cleaning up request 0 ID 111 with timestamp +48
Ready to process requests.
More information about the Freeradius-Users
mailing list