Re: Problem with MySQL and Accouting-On record

9 Nov 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@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@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@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