radsqlrelay - and default config

[A.L.M.Buxey at lboro.ac.uk]

hi,

I've been looking at the current state of the default (commented
out ready for use) radsqlrelay commands in radiusd.conf . for a
quick reminder they look like this:

#       sql_log {
#               path = "${radacctdir}/sql-relay"
#               acct_table = "radacct"
#               postauth_table = "radpostauth"
#               sql_user_name = "%{%{User-Name}:-DEFAULT}"
#
#               Start = "INSERT INTO ${acct_table} (AcctSessionId, UserName, \
#                NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, \
#                AcctSessionTime, AcctTerminateCause) VALUES                 \
#                ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', \
#                '%{Framed-IP-Address}', '%S', '0', '0', '');"
#               Stop = "INSERT INTO ${acct_table} (AcctSessionId, UserName,  \
#                NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, \
#                AcctSessionTime, AcctTerminateCause) VALUES                 \
#                ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', \
#                '%{Framed-IP-Address}', '0', '%S', '%{Acct-Session-Time}',  \
#                '%{Acct-Terminate-Cause}');"
#               Alive = "INSERT INTO ${acct_table} (AcctSessionId, UserName, \
#                NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, \
#                AcctSessionTime, AcctTerminateCause) VALUES                 \
#                ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', \
#                '%{Framed-IP-Address}', '0', '0', '%{Acct-Session-Time}','');"
#
#               Post-Auth = "INSERT INTO ${postauth_table}                   \
#                (username, pass, reply, authdate) VALUES                            \
#                ('%{User-Name}', '%{User-Password:-Chap-Password}',         \
#                '%{reply:Packet-Type}', '%S');"
#       }

now - whilst radsqlrelay is recommended for those whose accounting DB systems
cannot keep up with the real flow of packets - and we recommend it - its not
productive to have imho a broken default config. the issues which i can see
from this are the following

1) with a default postgres install, those '0' dates are not going to work with the
supplied schema for PGSQL - in fact it takes a bit of breaking postgres for
such values to work.  the simple change is to have a null entry.

2) likewise for MySQL - '0000-00-00 00:00:00' is iirc the correct way of doing it

3) BOTH mysql and postgres (i havent done any looking into oracle) will have issues
with the radacct entries - for each of them has another index in the radacct table
which cannot be null and must be unique - 

mysql
acctuniqueid varchar(32) NOT NULL default ''
KEY acctuniqueid (acctuniqueid)

postgres
AcctUniqueId            VARCHAR(32) NOT NULL

such a value is not being provided in the field. which means when the user
finally runs radsqlrelay they will be faced with issues.

as far as i can see, radpostauth will work fine.

so what I'd propose is we get a few folk who are successfully using this function,
get their radiusd.conf entries, and have 2 sections for uncommenting - one for
postgres and one for mysql. 

alan