Stripping Realms from SQL Accounting Queries

[tnt at kalik.net]

> 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?

Perhaps do what is suggested wherever you care to look (this list,
documentation, website, ...) - run server in debug mode (radiusd -X). Then
you will see exactly what is happening.

Ivan Kalik