rlm_postgresql , accounting and Framed-IP-Address

Francisco Gimeno kikov79 at gmail.com
Mon Feb 12 18:30:05 CET 2007


Hello

I'm having some problems when accounting with postgreSQL.
I'm using FreeRadius 1.1.3 with sqlippool and postgresql. Authentication is
done via PAM to a LDAP server. Some authorization is done with LDAP (pool
assignment, profiles...).
I would like to use accounting and simultaneous use check with postgreSQL.
It seems that everything is working well.. But for some reason I don't get
the assigned IP in my accounting tables whenever the user is logged. I just
get it whenever the user is disconnected. I would like to have that info in
real-time for debugging purposes.

This is my postgresql.sql related config:
----
        sql_user_name = "%{Stripped-User-Name:-%{User-Name}}"

        accounting_start_query = "INSERT into ${acct_table1} \
                (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress,
NASPortId, NASPortType, AcctStartTime, AcctAuthentic, \
                ConnectInfo_start, CalledStationId, CallingStationId,
ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay ) \
                values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
                '%{NAS-Port}', '%{NAS-Port-Type}', ('%S'::timestamp -
'%{Acct-Delay-Time:-0}'::interval), '%{Acct-Authentic}', '%{Connect-Info}',
\
                '%{Called-Station-Id}', '%{Calling-Station-Id}',
'%{Service-Type}', '%{Framed-Protocol}', \
                NULLIF('%{reply:Framed-IP-Address}', '')::inet,
'%{Acct-Delay-Time:-0}')"
----

The %{reply:Framed-IP-Address} or %{Framed-IP-Address} isn't xlated at
all... SQL module is executed after sqlippool, so, that variable should be
available.

The logs:
---
rlm_sql (sql): Reserving sql socket id: 2
radius_xlat:  'BEGIN'
rlm_sql_postgresql: query: BEGIN
rlm_sql_postgresql: Status: PGRES_COMMAND_OK
rlm_sql_postgresql: affected rows =
radius_xlat:  'UPDATE radippool   SET expiry_time = 'now'::timestamp(0) +
'3600 second'::interval   WHERE nasipaddress = '10.15.208.103' AND pool_name
= 'Pool-VODA-IPdina''
rlm_sql_postgresql: query: UPDATE radippool   SET expiry_time =
'now'::timestamp(0) + '3600 second'::interval   WHERE nasipaddress = '
10.15.208.103' AND pool_name = 'Pool-VODA-IPdina'
rlm_sql_postgresql: Status: PGRES_COMMAND_OK
rlm_sql_postgresql: affected rows = 2
radius_xlat:  'COMMIT'
rlm_sql_postgresql: query: COMMIT
rlm_sql_postgresql: Status: PGRES_COMMAND_OK
rlm_sql_postgresql: affected rows =
rlm_sql (sql): Released sql socket id: 2
  modcall[accounting]: module "sqlippool" returns ok for request 6
radius_xlat:  'vodafone.dinamica at junta'
rlm_sql (sql): sql_set_user escaped user --> 'vodafone.dinamica at junta'
radius_xlat:  'INSERT into radacct ??(AcctSessionId, AcctUniqueId, UserName,
Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthentic,
??ConnectInfo_start, CalledStationId, CallingStationId, ServiceType,
FramedProtocol, FramedIPAddress, AcctStartDelay ) ??values('45D09AA81E8F00',
'55addd923467ad3f', 'vodafone.dinamica at junta', '', '10.15.208.103', ??'0',
'Async', ('2007-02-12 17:51:15'::timestamp - '0'::interval), 'RADIUS', '',
??'', '10.15.208.106', 'Framed-User', 'PPP', ??NULLIF('', '')::inet, '0')'
radius_xlat:  '/var/log/freeradius/sqltrace.sql'
rlm_sql (sql): Reserving sql socket id: 1
rlm_sql_postgresql: query: INSERT into radacct ??(AcctSessionId,
AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType,
AcctStartTime, AcctAuthentic, ??ConnectInfo_start, CalledStationId,
CallingStationId, ServiceType, FramedProtocol, FramedIPAddress,
AcctStartDelay ) ??values('45D09AA81E8F00', '55addd923467ad3f', '
vodafone.dinamica at junta', '', '10.15.208.103', ??'0', 'Async', ('2007-02-12
17:51:15'::timestamp - '0'::interval), 'RADIUS', '', ??'', '10.15.208.106',
'Framed-User', 'PPP', ??NULLIF('', '')::inet, '0')
rlm_sql_postgresql: Status: PGRES_COMMAND_OK
rlm_sql_postgresql: affected rows = 1
rlm_sql (sql): Released sql socket id: 1
  modcall[accounting]: module "sql" returns ok for request 6
---
The assigned IP is 10.247.254.3 (or 10.247.254.5)...

The relevant part in radiusd.conf
----
accounting {
 sqlippool
 sql
}

-------------
By the way.. The username is not stripped, and @junta appears in the logs
after the xlat call :/

what am I doing wrong?

Thanks
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20070212/ef728819/attachment.html>


More information about the Freeradius-Users mailing list