Stripping Realms from SQL Accounting Queries

[Tim Gustafson]

Hi,

I'm using FreeRADIUS with LDAP for authentication and mySQL for logging.  The LDAP queries seem to be stripping the realm name properly, whereas the mySQL queries are not.  I'm running FreeRADIUS 2.1.6 on FreeBSD:

FreeRADIUS Version 2.1.6, for host i386-portbld-freebsd7.2, built on Nov 10 2009 at 08:34:04

Here's what I've got in my modules/ldap:

----- modules/ldap -----
ldap {
  server = "foo"
  basedn = "dc=foo"
  identity = "uid=foo"
  password = "foo"
  filter = "(uid=%{Stripped-User-Name:-%{User-Name}})"
  ldap_connections_number = 5
  timeout = 4
  timelimit = 3
  net_timeout = 1
  
  tls {
    start_tls = yes
  }
  
  access_attr = "uid"
  dictionary_mapping = ${confdir}/ldap.attrmap
  password_attribute = sambaNTPassword
  edir_account_policy_check = no

  groupname_attribute = "cn"
  groupmembership_attribute = "memberUid"
  groupmembership_filter = "(memberUid=%{Stripped-User-Name:-%{User-Name}})"
}
----- modules/ldap -----

Here's what I've got in my sql/mysql/wpa.conf file:

----- sql/mysql/wpa.conf -----
accounting_start_query = "insert into RadiusLog (SessionID, UserName, WapIpAddress, UserMacAddress, StartTime) value ('%{Acct-Session-Id}', '%{Stripped-User-Name:-%{User-Name}}', '%{NAS-IP-Address}', '%{Calling-Station-Id}', '%S')"

accounting_stop_query = "update RadiusLog set EndTime = '%S', BytesIn = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', BytesOut = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}' where SessionID = '%{Acct-Session-Id}' and UserName = '%{Stripped-User-Name:-%{User-Name}}' and StartTime >= date_sub(now(), interval 30 day)"

accounting_update_query = "update RadiusLog set BytesIn = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', BytesOut = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}' where SessionID = '%{Acct-Session-Id}' and UserName = '%{Stripped-User-Name:-%{User-Name}}' and StartTime >= date_sub(now(), interval 30 day)"
----- sql/mysql/wpa.conf -----

And here are some sample queries from the SQL trace file:

----- /var/log/radacct/radius.sql -----
insert into RadiusLog (SessionID, UserName, WapIpAddress, UserMacAddress, StartTime) value ('0004F815', 'tjg at foo', '192.168.32.8', '0024.2b58.2f46', '2009-11-23 10:52:58');
update RadiusLog set EndTime = '2009-11-23 10:53:46', BytesIn = '0' << 32 | '2487', BytesOut = '0' << 32 | '1356' where SessionID = '0004F815' and UserName = 'tjg at foo' and StartTime >= date_sub(now(), interval 30 day);
----- /var/log/radacct/radius.sql -----

I tried adding a realm definition in proxy.conf, like this:

----- proxy.conf -----
realm foo {
 type  = radius
 authhost = LOCAL
 accthost = LOCAL
 strip
}
----- proxy.conf -----

But that didn't help at all.

I know I must be missing something obvious.  Most of the Google hits on this talk about creating realms and stuff, but that hasn't helped me.  Can anyone share some wisdom here?

Thanks!

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg at soe.ucsc.edu
831-459-5354