#! /bin/bash
#
# /sbin/start-stop-daemon -b --start --exec /etc/freeradius/acct_radius_syslog-ng.sh
#
#Script a lancer sur le radius central (surveiller s'il est toujours present)
# ce script recuperera la requete sql creee grace a /etc/syslog-ng/syslog-ng.conf
#pour inclure dans la base de donnees accounting les @ip attribuees par les serveurs DHCP.
while [ 1 ]
do
if [ ! -e /var/log/mysql.pipe ]; then
mkfifo /var/log/mysql.pipe
sleep 5
continue
fi
if [ ! -e /var/run/syslog-ng.pid ] ; then
sleep 10
continue
fi
# si on ne trouve pas le pid de syslog on attend :
if [ -z `pgrep syslog-ng` ] ; then
sleep 10
continue
fi
mysql -A -h localhost -u AcctDHCP -pAcctDHCP accounting < /var/log/mysql.pipe
done
#
# Sample configuration file for ISC dhcpd for Debian
#
# $Id: dhcpd.conf,v 1.1.1.1 2002/05/21 00:07:44 peloy Exp $
#
# The ddns-updates-style parameter controls whether or not the server will
# attempt to do a DNS update when a lease is confirmed. We default to the
# behavior of the version 2 packages ('none', since DHCP v2 didn't
# have support for DDNS.)
ddns-update-style none;
# option definitions common to all supported networks...
option domain-name "grenoble.cnrs.fr";
option domain-name-servers 147.173.1.26, 147.173.1.27;
default-lease-time 600;
max-lease-time 7200;
# If this DHCP server is the official DHCP server for the local
# network, the authoritative directive should be uncommented.
authoritative;
# Use this to send dhcp log messages to a different log file (you also
# have to hack syslog.conf to complete the redirection).
log-facility local7;
# No service will be given on this subnet, but declaring it helps the
# DHCP server to understand the network topology.
#subnet 10.152.187.0 netmask 255.255.255.0 {
#}
# This is a very basic subnet declaration.
#subnet 10.254.239.0 netmask 255.255.255.224 {
# range 10.254.239.10 10.254.239.20;
# option routers rtr-239-0-1.example.org, rtr-239-0-2.example.org;
#}
# This declaration allows BOOTP clients to get dynamic addresses,
# which we don't really recommend.
#subnet 10.254.239.32 netmask 255.255.255.224 {
# range dynamic-bootp 10.254.239.40 10.254.239.60;
# option broadcast-address 10.254.239.31;
# option routers rtr-239-32-1.example.org;
#}
# A slightly different configuration for an internal subnet.
#subnet 10.5.5.0 netmask 255.255.255.224 {
# range 10.5.5.26 10.5.5.30;
# option domain-name-servers ns1.internal.example.org;
# option domain-name "internal.example.org";
# option routers 10.5.5.1;
# option broadcast-address 10.5.5.31;
# default-lease-time 600;
# max-lease-time 7200;
#}
# Hosts which require special configuration options can be listed in
# host statements. If no address is specified, the address will be
# allocated dynamically (if possible), but the host-specific information
# will still come from the host declaration.
#host passacaglia {
# hardware ethernet 0:0:c0:5d:bd:95;
# filename "vmunix.passacaglia";
# server-name "toccata.fugue.com";
#}
# Fixed IP addresses can also be specified for hosts. These addresses
# should not also be listed as being available for dynamic assignment.
# Hosts for which fixed IP addresses have been specified can boot using
# BOOTP or DHCP. Hosts for which no fixed address is specified can only
# be booted with DHCP, unless there is an address range on the subnet
# to which a BOOTP client is connected which has the dynamic-bootp flag
# set.
#host fantasia {
# hardware ethernet 08:00:07:26:c0:a5;
# fixed-address fantasia.fugue.com;
#}
# You can declare a class of clients and then do address allocation
# based on that. The example below shows a case where all clients
# in a certain class get addresses on the 10.17.224/24 subnet, and all
# other clients get addresses on the 10.0.29/24 subnet.
#class "foo" {
# match if substring (option vendor-class-identifier, 0, 4) = "SUNW";
#}
#shared-network 224-29 {
# subnet 10.17.224.0 netmask 255.255.255.0 {
# option routers rtr-224.example.org;
# }
# subnet 10.0.29.0 netmask 255.255.255.0 {
# option routers rtr-29.example.org;
# }
# pool {
# allow members of "foo";
# range 10.17.224.10 10.17.224.250;
# }
# pool {
# deny members of "foo";
# range 10.0.29.10 10.0.29.230;
# }
#}
# VLAN240 : VLAN Interne
subnet 147.173.240.0 netmask 255.255.252.0 {
range dynamic-bootp 147.173.240.2 147.173.240.253;
option broadcast-address 147.173.243.255;
option routers 147.173.243.254;
}
# VLAN244 : VLAN DMZ
subnet 147.173.244.0 netmask 255.255.255.0 {
range dynamic-bootp 147.173.244.2 147.173.244.253;
option broadcast-address 147.173.244.255;
option routers 147.173.244.254;
}
# VLAN245 : VLAN Invite
subnet 147.173.245.0 netmask 255.255.255.0 {
range dynamic-bootp 147.173.245.2 147.173.245.253;
option broadcast-address 147.173.245.255;
option routers 147.173.245.254;
}
# VLAN247 : VLAN Quarantaine
subnet 147.173.247.0 netmask 255.255.255.0 {
range dynamic-bootp 147.173.247.2 147.173.247.253;
option broadcast-address 147.173.247.255;
option routers 147.173.247.254;
}
# VLAN248 : VLAN Manipulations
subnet 147.173.248.0 netmask 255.255.255.0 {
range dynamic-bootp 147.173.248.2 147.173.248.253;
option broadcast-address 147.173.248.255;
option routers 147.173.248.254;
}
# VLAN249 : VLAN Installations
subnet 147.173.249.0 netmask 255.255.255.0 {
range dynamic-bootp 147.173.249.2 147.173.249.253;
option broadcast-address 147.173.249.255;
option routers 147.173.249.254;
}
#!/bin/sh
# Start/stop acct_radius :
#
### BEGIN INIT INFO
# Provides: acct_radius_syslog-ng.sh
# Default-Start: au demarrage de freeradius
### END INIT INFO
test -f /etc/freeradius/acct_radius_syslog-ng.sh || exit 0
. /lib/lsb/init-functions
PATH=/sbin:/usr/sbin:/bin:/usr/bin:/etc/freeradius
case "$1" in
start) log_daemon_msg "Lancement de acct_radius_syslog-ng.sh" "acct_radius"
start-stop-daemon -b --start --exec /etc/freeradius/acct_radius_syslog-ng.sh
log_end_msg $?
;;
stop) log_daemon_msg "Arret de acct_radius_syslog-ng.sh" "acct_radius"
start-stop-daemon --stop --name acct_radius_syslog-ng.sh
log_end_msg $?
;;
restart) log_daemon_msg "Relance de acct_radius_syslog-ng.sh" "acct_radius"
start-stop-daemon -b --start --exec /etc/freeradius/acct_radius_syslog-ng.sh
start-stop-daemon --stop --name acct_radius_syslog-ng.sh
log_end_msg $?
;;
esac
exit 0
# This file describes the network interfaces available on your system # and how to activate them. For more information, see interfaces(5). # The loopback network interface auto lo iface lo inet loopback # The primary network interface auto eth0 iface eth0 inet static address 192.168.192.193 netmask 255.255.255.255 # VLAN240 : VLAN Interne auto vlan240 iface vlan240 inet static address 147.173.240.1 netmask 255.255.252.0 gateway 147.173.243.254 network 147.173.240.0 broadcast 147.173.243.255 vlan_raw_device eth0 # VLAN244 : VLAN DMZ auto vlan244 iface vlan244 inet static address 147.173.244.1 netmask 255.255.255.0 vlan_raw_device eth0 # VLAN245 : VLAN Invite auto vlan245 iface vlan245 inet static address 147.173.245.1 netmask 255.255.255.0 vlan_raw_device eth0 # VLAN247 : VLAN Quarantaine auto vlan247 iface vlan247 inet static address 147.173.247.1 netmask 255.255.255.0 vlan_raw_device eth0 # VLAN248 : VLAN Manipulations auto vlan248 iface vlan248 inet static address 147.173.248.1 netmask 255.255.255.0 vlan_raw_device eth0 # VLAN249 : VLAN Installation auto vlan249 iface vlan249 inet static address 147.173.249.1 netmask 255.255.255.0 vlan_raw_device eth0
#Modif de /etc/syslog-ng/syslog-ng.conf :
#----------------------------------------
# Definition de la source de log => adresse IP du serveur DHCP
# Comme on aura plusieurs serveurs, on met 0.0.0.0 soit tous les serveurs de la planète
source dhcp {
tcp(ip(0.0.0.0) port(514));
};
#
log {
source(dhcp);
destination(acct_radius);
};
#
# destinations
###########################################################################
# Ici on modifie les enregistrements effectués par l'accounting
# afin de rajouter l'adresse IP au client delivré par le serveur DHCP.
# Le protocole EAP ne permettant pas de faire passer l'adresse IP du client dans la trame.
# De ce fait il faut modifier l'enregistrement effectué par l'accounting,
# récupérer la ligne de la personne connectée depuis moins d'une minute
# avec une adresse MAC identique dans le log du serveur DHCP et celle
# enregistrée dans la base de données accounting. Il faut aussi que le champ
# devant contenir l'adresse IP du client soit vide (FramedIPAdress). Le
# $MSG est de la forme :
# dhcpd: DHCPACK on 147.173.240.253 to 00:30:13:c5:96:6d (portable-cric) via vlan240
# Le champ CallingStationId est de la forme 00-30-13-C5-96-6D
# MySQL n'est pas sensible à la casse dans les comparaisons de chaine par =.
############################################################################
destination acct_radius { pipe("/var/log/mysql.pipe" \
template("UPDATE radacct SET FramedIPAddress = SUBSTRING_INDEX(SUBSTRING_INDEX('$MSG','on ',-1),' to',1) WHERE TIMESTAMPDIFF(SECOND,AcctStartTime,CONCAT('$YEAR-$MONTH-$DAY',' ','$HOUR:$MIN:$SEC')) <= 180 AND \
REPLACE(REPLACE(CallingStationId,'-',''),':','') = REPLACE(SUBSTRING(SUBSTRING_INDEX('$MSG','to ',-1),1,17),':','') \
AND FramedIPAddress = \"\" ;\n") template-escape(yes));
};
L'attribution de l'adresse par le serveur DHCP peut être faite selon l'adresse matérielle de l'ordinateur client pour les machines connues (pour résoudre les problèmes de licences logicielles demandant une adresse IP fixe), et une attribution dynamique pour les autres.
Traces d'install octobre 2008 :
*******************************
Accounting:
-----------
apt-get install mysql-client mysql-server
cd /usr/local/freeradius/freeradius-server-2.1.1
cp debian/tmp/etc/freeradius/sql/mysql/schema.sql /etc/freeradius/
Remplacer dans /etc/freeradius/schema.sql :
acctsessionid varchar(32) NOT NULL default '',
par :
acctsessionid varchar(298) NOT NULL default '',
Preciser en sites-available/default :
# Log traffic to an SQL database
sql
On met un mot de passe :
srv# mysql -u root
mysql> USE mysql;
mysql> UPDATE user SET Password=password('PwdRootMysql') WHERE user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> exit
srv# mysql -u root -p mysql -e "flush privileges" (pour valider)
On crée une base 'accounting' :
srv# mysql -u root -p -e "create database accounting"
srv# cd /etc/freeradius
On place le schema dans la nouvelle base :
srv# mysql -u root -p accounting < schema.sql
Definition du mot de passe et des droits pour 'AcctRadius' pour la base 'accounting' :
srv# mysql -u root -p mysql -e "insert into user set Host='localhost', User='AcctRadius',Password=password('PwdAcct')"
srv# mysql -u root -p mysql -e "flush privileges"
srv# mysql -u root -p mysql -e "insert into db set Host='localhost', Db='accounting', User='AcctRadius', Select_priv='Y', Insert_priv='Y', Update_priv='Y'"
srv# mysql -u root -p mysql -e "flush privileges"
Visualisation de la table accounting :
--------------------------------------
srv# mysql -u root -p
mysql> use accounting;
mysql> SHOW TABLES;
+----------------------+
| Tables_in_accounting |
+----------------------+
| radacct |
| radcheck |
| radgroupcheck |
| radgroupreply |
| radpostauth |
| radreply |
| radusergroup |
+----------------------+
7 rows in set (0.00 sec)
Reporter le password dans sql.conf: password = "PwdAcct"
Un programme est lancé pour écouter sur le pipe et envoyer la requête au serveur MySQL.
On ne peut pas utiliser les fonctions sql de syslog-ng car on veut faire des UPDATE
et non des INSERT. Ce programme acct_radius_syslog-ng.sh est disponible en annexe.
Il faut penser à le lancer pour que les logs soient mis à jour. Ce script a besoin d'un compte SQL
pour pouvoir mettre à jour la base d'accounting. On crée ce compte par les commandes :
srv# mysql -u root -p mysql -e "insert into user set Host='localhost', user='AcctDHCP', Password=password('PwdDhcp')"
srv# mysql -u root -p mysql -e "insert into db set Host='localhost', db='accounting', user='AcctDHCP', Select_priv='Y', Update_priv='Y'"
srv# mysql -u root -p mysql -e "flush privileges"
Purge des données de + d'un an: creer le fichier /etc/cron.daily/purge_bases_radius :
-------------------------------------------------------------------------------------
#!/bin/sh
mysql -u root -pPwdRootMysql accounting -e "SELECT AcctStartTime FROM radacct where AcctStartTime<DATE_ADD(NOW(),INTERVAL -1 YEAR)"
Lancer acct_radius_syslog-ng.sh :
---------------------------------
/etc/init.d/acct_radius start en /etc/rc2.d
Vérif de l'accounting :
-----------------------
srv# mysql -u root -p
mysql> use accounting
1) si acct_radius_syslog-ng.sh ne tourne pas :
==============================================
mysql> select UserName FramedIPAddress, AcctStartTime, AcctStopTime, CallingStationId, FramedIPAddress from radacct;
+-------------------+---------------------+---------------------+-------------------+-----------------+
| FramedIPAddress | AcctStartTime | AcctStopTime | CallingStationId | FramedIPAddress |
+-------------------+---------------------+---------------------+-------------------+-----------------+
| test1.daniel | 2008-10-23 16:48:46 | 2008-10-24 09:41:40 | 00-30-13-C5-96-6D | |
+-------------------+---------------------+---------------------+-------------------+-----------------+
=> on n'a pas l'adresse IP attribuée
(idem si le serveur DHCP d'un labo n'a pas transmis ces logs au serveur DHCP Central)
2) acct_radius_syslog-ng.sh tourne :
====================================
+-------------------+---------------------+---------------------+-------------------+-----------------+
| FramedIPAddress | AcctStartTime | AcctStopTime | CallingStationId | FramedIPAddress |
+-------------------+---------------------+---------------------+-------------------+-----------------+
| test1.daniel | 2008-10-23 16:48:46 | 2008-10-24 09:41:40 | 00-30-13-C5-96-6D | |
| test1.daniel | 2008-10-24 09:46:44 | 2008-10-24 09:52:14 | 00-30-13-C5-96-6D | 147.173.240.253 |
| test1.daniel | 2008-10-24 10:44:12 | 2008-10-24 10:44:33 | 00-30-13-C5-96-6D | 147.173.245.253 |
+-------------------+---------------------+---------------------+-------------------+-----------------+
Accounting Chilli Spot :
------------------------
+-------------------+---------------------+---------------------+-------------------+-----------------+
| FramedIPAddress | AcctStartTime | AcctStopTime | CallingStationId | FramedIPAddress |
+-------------------+---------------------+---------------------+-------------------+-----------------+
| test1.daniel | 2008-10-24 16:12:44 | 0000-00-00 00:00:00 | 00-30-13-C5-96-6D | 10.8.246.2 |
+-------------------+---------------------+---------------------+-------------------+-----------------+
10.8.246.2 est l'adresse affectée au PC par Chilli, côté INTERNE.
Vu de l'extérieur, le PC a comme adresse l'équivalent en 147.173.X.X, soit 147.173.246.2
(ceci est fait en iptables du serveur Chilli)
Ses traces de connexions le confirment :
24/10/2008 16:26:04 [147.173.246.2] 'test1.daniel' est authentifie