Problem with MySQL and Accouting-On record
Ángel L. Mateo
amateo at um.es
Mon Nov 9 14:51:54 CET 2020
Sorry... I was describing the config, putting the relevant elements, I
thought it would be enough. The whole config is:
* this is the server config:
server vpn {
authorize {
update request {
X-Atica-Service = "vpn"
X-Atica-Service-Filter = "vpn"
}
preprocess
vpn_log
suffix
ldap
files_vpn
pap
mschap
}
authenticate {
Auth-Type PAP {
pap
}
Auth-Type MS-CHAP {
mschap
}
pam
}
preacct {
suffix
}
accounting {
update request {
X-Atica-Service = "vpn"
X-Atica-Service-Filter = "vpn"
}
vpn_log
syslog_accounting
sql_log_um
pool_vpn { notfound = 1 }
pool_vpn_alu { notfound = 1 }
pool_vpn_avanttic { notfound = 1 }
pool_vpn_gnoss { notfound = 1 }
pool_vpn_izertis { notfound = 1 }
pool_vpn_ext_pas { notfound = 1 }
pool_vpn_ingenia { notfound = 1 }
sqlippool
}
session {
}
post-auth {
update reply { User-Name = &request:User-Name }
pool_vpn
pool_vpn_alu
pool_vpn_avanttic
pool_vpn_gnoss
pool_vpn_izertis
pool_vpn_ext_pas
pool_vpn_ingenia
sqlippool
vpn_log
expiration { userlock = 1 }
if (userlock) {
update reply {
&reply:Reply-Message = "La clave de su cuenta %{User-Name} expiró
el %{control:Expiration}. Debe renovar su clave (cambiarla) en
https://webmail.um.es/cambiaclave/. Para más información diríjase al CAU
de la UMU (ext 4222, tlf 868884222, dumbo at um.es)."
}
syslog_expiration
reject
} else {
syslog
}
if (&control:Expiration) { update reply { &reply:Reply-Message =
"La clave de su cuenta %{User-Name} va a expirar el
%{control:Expiration}. Debe renovar (cambiar) su clave antes del
%{control:Expiration} o no podrá acceder a las aplicaciones y servicios
de la UMU. Para cambiar su clave acceda a
https://webmail.um.es/cambiaclave/. Para más información diríjase al CAU
de la UMU (ext 4222, tlf 868884222, dumbo at um.es)." } }
Post-Auth-Type REJECT {
vpn_log
syslog
}
}
pre-proxy {
}
post-proxy {
}
}
server buffered-sql-eduroam {
listen {
type = detail
filename = ${radacctdir}/eduroam/eduroam_sql
load_factor = 10
poll_interval = 1
retry_interval = 30
track = yes
}
authorize {
}
authenticate {
}
preacct {
preprocess
acct_unique
}
accounting {
sql
}
session {
}
post-auth {
}
pre-proxy {
}
post-proxy {
}
}
sql sql {
dialect = "mysql"
driver = "rlm_sql_${dialect}"
server = "MYSQLSERVER"
port = "3306"
login = "MYSQLUSER"
password = "PASSWORD"
radius_db = "dbradiuslog"
acct_table1 = "radacct"
acct_table2 = "radacct"
postauth_table = "radpostauth"
authcheck_table = "radcheck"
groupcheck_table = "radgroupcheck"
authreply_table = "radreply"
groupreply_table = "radgroupreply"
usergroup_table = "radusergroup"
read_groups = yes
delete_stale_sessions = yes
pool {
start = 1
min = 1
max = ${thread[pool].max_servers}
spare = 1
uses = 0
retry_delay = 60
lifetime = 0
idle_timeout = 60
connect_timeout = 3.0
}
read_clients = no
client_table = nas
group_attribute = "${.:instance}-${.:name}-Group"
$INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf
}
detail sql_log_um {
filename = ${radacctdir}/eduroam/eduroam_sql
escape_filenames = no
permissions = 0600
header = "%t"
locking = false
suppress {
User-Password
}
}
I put the queries.conf as an attach.
Do you any other config?
El 6/11/20 a las 14:19, Alan DeKok escribió:
> On Nov 6, 2020, at 8:13 AM, Ángel L. Mateo <amateo at um.es> wrote:
>> I have a radius server (freeradius 3.0.19) to authenticate a VPN server (among other services). I have virtual server to listen from this device. In this server I have the config:
>>
>> accouting {
>> ...
>> sql_log_um
>> ...
>> }
>
> OK...
>
>> where "sql_log_um" es a detail config to a file, like this:
>
> We don't need to see the config files. *All* of the documentation says this. Including the message you get when you join the list.
>
> Is there somewhere *else* we should put the documentation so that people will read it?
>
>> the problem I have is that is queried is run but the record is not deleted from the detail file neither is marked with the Donestamp mark, so buffered-sql runs it again and again, without passing to next records.
>
> If only there was some kind of debug output which let you know what the server was doing.
>
> Honestly... the documentation says over and over again what to do. Why ignore it?
>
> Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
--
Angel L. Mateo Martínez
Sección de Telemática
Área de Tecnologías de la Información
y las Comunicaciones Aplicadas (ATICA)
http://www.um.es/atica
Tfo: 868889150
Fax: 868888337
-------------- next part --------------
safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
sql_user_name = "%{User-Name}"
client_query = "\
SELECT id, nasname, shortname, type, secret, server \
FROM ${client_table}"
authorize_check_query = "\
SELECT id, username, attribute, value, op \
FROM ${authcheck_table} \
WHERE username = '%{SQL-User-Name}' \
ORDER BY id"
authorize_reply_query = "\
SELECT id, username, attribute, value, op \
FROM ${authreply_table} \
WHERE username = '%{SQL-User-Name}' \
ORDER BY id"
group_membership_query = "\
SELECT groupname \
FROM ${usergroup_table} \
WHERE username = '%{SQL-User-Name}' \
ORDER BY priority"
authorize_group_check_query = "\
SELECT id, groupname, attribute, \
Value, op \
FROM ${groupcheck_table} \
WHERE groupname = '%{${group_attribute}}' \
ORDER BY id"
authorize_group_reply_query = "\
SELECT id, groupname, attribute, \
value, op \
FROM ${groupreply_table} \
WHERE groupname = '%{${group_attribute}}' \
ORDER BY id"
simul_count_query = "\
SELECT COUNT(*) \
FROM ${acct_table1} \
WHERE username = '%{SQL-User-Name}' \
AND acctstoptime IS NULL"
simul_verify_query = "\
SELECT \
radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, \
callingstationid, framedprotocol \
FROM ${acct_table1} \
WHERE username = '%{SQL-User-Name}' \
AND acctstoptime IS NULL"
accounting {
reference = "%{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}"
# Write SQL queries to a logfile. This is potentially useful for bulk inserts
# when used with the rlm_sql_null driver.
column_list = "\
acctsessionid, acctuniqueid, username, \
realm, nasipaddress, nasportid, \
nasporttype, acctstarttime, acctupdatetime, \
acctstoptime, acctsessiontime, acctauthentic, \
connectinfo_start, connectinfo_stop, acctinputoctets, \
acctoutputoctets, calledstationid, callingstationid, \
acctterminatecause, servicetype, framedprotocol, \
framedipaddress"
type {
accounting-on {
#
# Bulk terminate all sessions associated with a given NAS
#
query = "\
UPDATE ${....acct_table1} \
SET \
acctstoptime = FROM_UNIXTIME(\
%{integer:Event-Timestamp}), \
acctsessiontime = '%{integer:Event-Timestamp}' \
- UNIX_TIMESTAMP(acctstarttime), \
acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' \
WHERE acctstoptime IS NULL \
AND nasipaddress = '%{NAS-IP-Address}' \
AND acctstarttime <= FROM_UNIXTIME(\
%{integer:Event-Timestamp})"
}
accounting-off {
query = "${..accounting-on.query}"
}
#
# Implement the "sql_session_start" policy.
# See raddb/policy.d/accounting for more details.
#
# You also need to fix the other queries as
# documented below. Look for "sql_session_start".
#
post-auth {
query = "\
INSERT INTO ${....acct_table1} \
(${...column_list}) \
VALUES(\
'%{Acct-Session-Id}', \
'%{Acct-Unique-Session-Id}', \
'%{SQL-User-Name}', \
'%{Realm}', \
'%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
NULLIF('%{%{NAS-Port-ID}:-%{NAS-Port}}', ''), \
'%{NAS-Port-Type}', \
FROM_UNIXTIME(%{integer:Event-Timestamp}), \
NULL, \
NULL, \
0, \
'', \
'%{Connect-Info}', \
NULL, \
0, \
0, \
'%{Called-Station-Id}', \
'%{Calling-Station-Id}', \
NULL, \
'%{Service-Type}', \
NULL, \
'')"
query = "\
UPDATE ${....acct_table1} SET \
AcctStartTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
AcctUpdateTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
ConnectInfo_start = '%{Connect-Info}', \
AcctSessionId = '%{Acct-Session-Id}' \
WHERE UserName = '%{SQL-User-Name}' \
AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
AND NASPortType = '%{NAS-Port-Type}' \
AND AcctStopTime IS NULL"
}
start {
#
# Insert a new record into the sessions table
#
query = "\
INSERT INTO ${....acct_table1} \
(${...column_list}) \
VALUES \
('%{Acct-Session-Id}', \
'%{Acct-Unique-Session-Id}', \
'%{SQL-User-Name}', \
'%{Realm}', \
'%{NAS-IP-Address}', \
'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
'%{NAS-Port-Type}', \
FROM_UNIXTIME(%{integer:Event-Timestamp}), \
FROM_UNIXTIME(%{integer:Event-Timestamp}), \
NULL, \
'0', \
'%{Acct-Authentic}', \
'%{Connect-Info}', \
'', \
'0', \
'0', \
'%{Called-Station-Id}', \
'%{Calling-Station-Id}', \
'', \
'%{Service-Type}', \
'%{Framed-Protocol}', \
'%{Framed-IP-Address}')"
#
# When using "sql_session_start", you should comment out
# the previous query, and enable this one.
#
# Just change the previous query to "-query",
# and this one to "query". The previous one
# will be ignored, and this one will be
# enabled.
#
-query = "\
UPDATE ${....acct_table1} \
SET \
AcctSessionId = '%{Acct-Session-Id}', \
AcctUniqueId = '%{Acct-Unique-Session-Id}', \
AcctAuthentic = '%{Acct-Authentic}', \
ConnectInfo_start = '%{Connect-Info}', \
ServiceType = '%{Service-Type}', \
FramedProtocol = '%{Framed-Protocol}', \
framedipaddress = '%{Framed-IP-Address}', \
AcctStartTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
AcctUpdateTime = FROM_UNIXTIME(%{integer:Event-Timestamp}) \
WHERE UserName = '%{SQL-User-Name}' \
AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
AND NASPortType = '%{NAS-Port-Type}' \
AND AcctStopTime IS NULL"
#
# Key constraints prevented us from inserting a new session,
# use the alternate query to update an existing session.
#
query = "\
UPDATE ${....acct_table1} SET \
acctstarttime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
acctupdatetime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
connectinfo_start = '%{Connect-Info}' \
WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
}
interim-update {
#
# Update an existing session and calculate the interval
# between the last data we received for the session and this
# update. This can be used to find stale sessions.
#
query = "\
UPDATE ${....acct_table1} \
SET \
acctupdatetime = (@acctupdatetime_old:=acctupdatetime), \
acctupdatetime = FROM_UNIXTIME(\
%{integer:Event-Timestamp}), \
acctinterval = %{integer:Event-Timestamp} - \
UNIX_TIMESTAMP(@acctupdatetime_old), \
framedipaddress = '%{Framed-IP-Address}', \
acctsessiontime = %{%{Acct-Session-Time}:-NULL}, \
acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' \
<< 32 | '%{%{Acct-Input-Octets}:-0}', \
acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \
<< 32 | '%{%{Acct-Output-Octets}:-0}' \
WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
#
# The update condition matched no existing sessions. Use
# the values provided in the update to create a new session.
#
query = "\
INSERT INTO ${....acct_table1} \
(${...column_list}) \
VALUES \
('%{Acct-Session-Id}', \
'%{Acct-Unique-Session-Id}', \
'%{SQL-User-Name}', \
'%{Realm}', \
'%{NAS-IP-Address}', \
'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
'%{NAS-Port-Type}', \
FROM_UNIXTIME(%{integer:Event-Timestamp} - %{%{Acct-Session-Time}:-0}), \
FROM_UNIXTIME(%{integer:Event-Timestamp}), \
NULL, \
%{%{Acct-Session-Time}:-NULL}, \
'%{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}', \
'', \
'%{Service-Type}', \
'%{Framed-Protocol}', \
'%{Framed-IP-Address}')"
#
# When using "sql_session_start", you should comment out
# the previous query, and enable this one.
#
# Just change the previous query to "-query",
# and this one to "query". The previous one
# will be ignored, and this one will be
# enabled.
#
-query = "\
UPDATE ${....acct_table1} \
SET \
AcctSessionId = '%{Acct-Session-Id}', \
AcctUniqueId = '%{Acct-Unique-Session-Id}', \
AcctAuthentic = '%{Acct-Authentic}', \
ConnectInfo_start = '%{Connect-Info}', \
ServiceType = '%{Service-Type}', \
FramedProtocol = '%{Framed-Protocol}', \
framedipaddress = '%{Framed-IP-Address}', \
AcctUpdateTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
AcctSessionTime = %{%{Acct-Session-Time}:-NULL}, \
AcctInputOctets = '%{%{Acct-Input-Gigawords}:-0}' \
<< 32 | '%{%{Acct-Input-Octets}:-0}', \
AcctOutputOctets = '%{%{Acct-Output-Gigawords}:-0}' \
<< 32 | '%{%{Acct-Output-Octets}:-0}' \
WHERE UserName = '%{SQL-User-Name}' \
AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
AND NASPortType = '%{NAS-Port-Type}' \
AND AcctStopTime IS NULL"
}
stop {
#
# Session has terminated, update the stop time and statistics.
#
query = "\
UPDATE ${....acct_table2} SET \
acctstoptime = FROM_UNIXTIME(\
%{integer:Event-Timestamp}), \
acctsessiontime = %{%{Acct-Session-Time}:-NULL}, \
acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' \
<< 32 | '%{%{Acct-Input-Octets}:-0}', \
acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \
<< 32 | '%{%{Acct-Output-Octets}:-0}', \
acctterminatecause = '%{Acct-Terminate-Cause}', \
connectinfo_stop = '%{Connect-Info}' \
WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
#
# The update condition matched no existing sessions. Use
# the values provided in the update to create a new session.
#
query = "\
INSERT INTO ${....acct_table2} \
(${...column_list}) \
VALUES \
('%{Acct-Session-Id}', \
'%{Acct-Unique-Session-Id}', \
'%{SQL-User-Name}', \
'%{Realm}', \
'%{NAS-IP-Address}', \
'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
'%{NAS-Port-Type}', \
FROM_UNIXTIME(%{integer:Event-Timestamp} - %{%{Acct-Session-Time}:-0}), \
FROM_UNIXTIME(%{integer:Event-Timestamp}), \
FROM_UNIXTIME(%{integer:Event-Timestamp}), \
%{%{Acct-Session-Time}:-NULL}, \
'%{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}')"
#
# When using "sql_session_start", you should comment out
# the previous query, and enable this one.
#
# Just change the previous query to "-query",
# and this one to "query". The previous one
# will be ignored, and this one will be
# enabled.
#
-query = "\
UPDATE ${....acct_table1} \
SET \
AcctSessionId = '%{Acct-Session-Id}', \
AcctUniqueId = '%{Acct-Unique-Session-Id}', \
AcctAuthentic = '%{Acct-Authentic}', \
ConnectInfo_start = '%{Connect-Info}', \
ServiceType = '%{Service-Type}', \
FramedProtocol = '%{Framed-Protocol}', \
framedipaddress = '%{Framed-IP-Address}', \
AcctStopTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
AcctUpdateTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
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}', \
ConnectInfo_stop = '%{Connect-Info}' \
WHERE UserName = '%{SQL-User-Name}' \
AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
AND NASPortType = '%{NAS-Port-Type}' \
AND AcctStopTime IS NULL"
}
#
# No Acct-Status-Type == ignore the packet
#
accounting {
query = "SELECT true"
}
}
}
post-auth {
# Write SQL queries to a logfile. This is potentially useful for bulk inserts
# when used with the rlm_sql_null driver.
query = "\
INSERT INTO ${..postauth_table} \
(username, pass, reply, authdate) \
VALUES ( \
'%{SQL-User-Name}', \
'%{%{User-Password}:-%{Chap-Password}}', \
'%{reply:Packet-Type}', \
'%S')"
}
More information about the Freeradius-Users
mailing list