The module has been tested to run on the following:
The following needs to be set:
server |
Hostname to your server running the PostgreSQL DB |
login |
The name of the user you granted INSERT and UPDATE privilege to on the radius accounting table. |
password |
Password. |
radius_db |
This will be nav for a typical NAV install |
acct_table1 |
Must be set to radiusacct |
acct_table2 |
Same as above. FreeRADIUS lets you log Start and Stop packets to different tables, but we don’t make use of this. |
The accounting queries in this file also needs to be replaced, since we have messed around a bit with the default setup of the table. Replace them with these:
accounting_onoff_query = "UPDATE ${acct_table1} \
SET AcctStopTime = (now() - '%{Acct-Delay-Time:-0}'::interval), \
AcctSessionTime = (EXTRACT(EPOCH FROM(now()::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \
AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}' \
WHERE AcctSessionTime IS NULL AND AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= now()"
accounting_update_query = "UPDATE ${acct_table1} \
SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
AcctSessionTime = (EXTRACT(EPOCH FROM(now()::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \
AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint) \
WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
accounting_update_query_alt = "INSERT into ${acct_table1} \
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortType, CiscoNASPort, AcctStartTime, \
AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \
FramedProtocol, FramedIPAddress) \
values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
'%{NAS-Port-Type}', NULLIF('%{Cisco-NAS-Port}', ''), (now() - '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval), \
NULLIF('%{Acct-Session-Time}','')::bigint, \
(('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
(('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \
'%{Calling-Station-Id}', '%{Framed-Protocol}', \
NULLIF('%{Framed-IP-Address}', '')::inet)"
accounting_start_query = "INSERT into ${acct_table1} \
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortType, CiscoNASPort, AcctStartTime, \
CalledStationId, CallingStationId, FramedProtocol, FramedIPAddress, AcctStartDelay) \
values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
'%{NAS-Port-Type}', NULLIF('%{Cisco-NAS-Port}', ''), (now() - '%{Acct-Delay-Time:-0}'::interval), \
'%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Framed-Protocol}', \
NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}') "
accounting_start_query_alt = "UPDATE ${acct_table1} \
SET AcctStartTime = (now() - '%{Acct-Delay-Time:-0}'::interval) \
WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
accounting_stop_query = "UPDATE ${acct_table2} \
SET AcctStopTime = (now() - '%{Acct-Delay-Time:-0}'::interval), \
AcctSessionTime = NULLIF('%{Acct-Session-Time}', '')::bigint, \
AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), \
AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}', \
FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet \
WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
accounting_stop_query_alt = "INSERT into ${acct_table2} \
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortType, CiscoNASPort, AcctStartTime, AcctStopTime, \
AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \
AcctTerminateCause, FramedProtocol, FramedIPAddress, AcctStopDelay) \
values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
'%{NAS-Port-Type}', NULLIF('%{Cisco-NAS-Port}', ''), (now() - '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval), \
(now() - '%{Acct-Delay-Time:-0}'::interval), NULLIF('%{Acct-Session-Time}', '')::bigint, \
(('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
(('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \
'%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Framed-Protocol}', \
NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}')"
Note
Remember to set the $INCLUDE ${confdir}/postgresql.conf
statement
in radiusd.conf
, it defaults to sql.conf
which is the MySQL module.
We noticed how some sessions’ Acct-Unique-Session-Id
field changed
mid-session without any apparent reason. We found that some NAS-es had an
Acct-Session-Id
field containing, among other data, the session’s starting
time. This starting time, for some strange strange reason that we’re unaware
of, sometimes change, mid-session, by a second or two, causing
Acct-Unique-Session-Id
to also change, since it is a hash of
Acct-Session-Id
, among others. This small routine (or whatever the correct
naming is) strips the date and time out of Acct-Session-Id
.
It needs to be defined in the main section, ie. among the other
attr_rewrite
examples, and called in the preacct{}
section, before
acct_unique
(which generates the unique session id). The regexp isn’t very
precise, but it does the job:
attr_rewrite modify_acctsessionid {
attribute = Acct-Session-Id
searchin = packet
searchfor = "[0-3][0-9]\/[0-3][0-9]\/[0-9]{2}\ [0-2][0-9]\:[0-5][0-9]\:[0-5][0-9]"
replacewith = ""
append = no
}
Also add a line saying sql
to the accounting{}
section. We have put it
last, but we have also seen people recommending it being put between unix
and radutmp
, although the reason was unclear. We’ve experienced no known
problems by putting it last.
To get accounting data from your 802.1X Cisco switches, they must be configured using the following:
aaa accounting dot1x default start-stop group radius
radius-server vsa send accounting
Edit radius_config.py
, change the values according to your setup. You
should probably only have to change REAUTH_TIMEOUT
to reflect the
reauthentication interval on your FreeRADIUS server. The time is given as a
number of seconds. This value is used in the search results to indicate
whether a session is likely to still be active, or if it has timed out and not
sent an explicit Stop.
Restart FreeRADIUS, and you should be good to go.
Edit pg_hba.conf
on the database server to allow connections from the
FreeRADIUS server.
Edit the variables at the top of
</path/to/radius_module/src>/radiusparser.py
to reflect the hostname of
the NAV database server, the username and password you want for connecting to
the database, and the path to the FreeRADIUS log file, radius.log
.
Copy the script to a location of your choice on the server where the
radius.log
is accessible as part of the file system. Create a cron job
that executes this script as often as you would like to make sure that the
script is actually running. For example:
0 * * * * /path/to/radiusparser.py
This will run the script every hour, but if it detects that it is already running, it quits and leaves the running script alone.
Of course, you may also simply execute the script yourself by running it at the command line.
Edit radius_config.py
, change the values according to your setup. Not much
should need to be changed here, except perhaps the ACCT_TABLE
variable, if
you have chosen a different name than the default.
You probably don’t want the entries in the error log table or the
accounting log table accumulating forever. To arrange for periodic
deletion of old records, create a cron snippet in NAV’s etc/cron.d/
directory called radiusclean
and add the following lines to it:
50 5 * * 6 /path/to/navclean.py --radiusacct -E "3 months" -f
45 5 * * 6 /path/to/navclean.py --radiuslog -E "1 month" -f
To insert the cron snippet into navcron’s crontab, run:
sudo nav start radiusclean
This will run the navclean.py
program once a week, deleting all radius
accounting entries older than three months and all radius error log entries
older than one month. Feel free to change the intervals to something you
think is suitable for your organization.
Note that this step is entirely optional.
Everything should already be configured during the installation, but it might be desirable to have separate users for SELECT queries and queries that updates the table (INSERT, DELETE, UPDATE). This is up to you to decide. This will require that you create two new users in your db and do some modifications of these files:
db.conf
postgresql.conf
radius.py
radius_config.py
Make two new entries in db.conf
:
script_radius_front = <db user with SELECT privilege>
script_radius_back = <db user with INSERT, UPDATE, DELETE privileges>
postgresql.conf
is the same file that is mentioned in Step 1: Configuring
FreeRADIUS. Change login
here to whatever you called your user with
write-privileges. See radius.py
and radius_config.py
for what to
change there.