There are at least 2 problems with current PostgreSQL schema and queries:
1. There is no NASPort in schema - should be NASPort NUMERIC(5)
2. In schema we use
AcctStartTime datetime DEFAULT now() NOT NULL,
AcctStopTime datetime DEFAULT now() NOT NULL,
in queries:
accounting_update_query = ..WHERE .. AND AcctStopTime = 0 - will not work (
now() != 0 )
the same for "accounting_start_query_alt" and "accounting_start_query_alt"
I think, right way is not to use "DEFAULT" and "NOT NULL" in schema and use
"AcctStopTime IS NULL" in queries.
patch:
diff -ur radiusd.ORIG/raddb/postgresql.conf radiusd/raddb/postgresql.conf
--- radiusd.ORIG/raddb/postgresql.conf Fri Jun 7 00:06:19 2002
+++ radiusd/raddb/postgresql.conf Sat Sep 7 11:40:29 2002
@@ -123,15 +123,19 @@
# and added NAS-IP-Address to Stop query (strange, but radius can not determine
username when updating field with ip address and query is empty)
# also i changed NAS-Port-id to NAS-Port (cisco nas gives me NAS-Port)
# Hmmm... please let me know if i forgot smthing... and if i made mistake :)
+#
+# Sergey Holod ([EMAIL PROTECTED]): Who is "me"?
+# ..: To my mind, accounting_onof will never work because thare are nothing to
+update.. need to change driver or use INSERT insteed..#
+
accounting_onoff_query = "UPDATE ${acct_table1} SET AcctStopTime='%S',
AcctSessionTime=extract(epoch from (timestamp('%S') - timestamp(AcctStartTime))),
AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = %{Acct-Delay-Time} WHERE
AcctSessionTime=0 AND AcctStopTime=0 AND NASIPAddress= '%{NAS-IP-Address}' AND
AcctStartTime <= '%S'"
- accounting_update_query = "UPDATE ${acct_table1} SET FramedIPAddress =
'%{Framed-IP-Address}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName =
'%{SQL-User-Name}' AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime = 0"
+ accounting_update_query = "UPDATE ${acct_table1} SET FramedIPAddress =
+'%{Framed-IP-Address}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName =
+'%{SQL-User-Name}' AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
accounting_start_query = "INSERT into radacct (AcctSessionId, AcctUniqueId,
UserName, Realm, NASIPAddress, NASPort, NASPortType, AcctStartTime, AcctSessionTime,
AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets,
CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol,
FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Acct-Session-Id}',
'%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}',
'%{NAS-Port}', '%{NAS-Port-Type}', '%S', '0', '%{Acct-Authentic}', '%{Connect-Info}',
'', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}',
'%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')"
- accounting_start_query_alt = "UPDATE ${acct_table1} SET AcctStartTime = '%S',
AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start = '%{Connect-Info}' WHERE
AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND
NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime = 0"
+ accounting_start_query_alt = "UPDATE ${acct_table1} SET AcctStartTime = '%S',
+AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start = '%{Connect-Info}' WHERE
+AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND
+NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
- accounting_stop_query = "UPDATE ${acct_table1} SET AcctStopTime = '%S',
AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets}',
AcctOutputOctets = '%{Acct-Output-Octets}', AcctTerminateCause =
'%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}', FramedIPAddress =
'%{Framed-IP-Address}', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId =
'%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress =
'%{NAS-IP-Address}' AND AcctStopTime = 0"
+ accounting_stop_query = "UPDATE ${acct_table1} SET AcctStopTime = '%S',
+AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets}',
+AcctOutputOctets = '%{Acct-Output-Octets}', AcctTerminateCause =
+'%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}', FramedIPAddress =
+'%{Framed-IP-Address}', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId =
+'%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress =
+'%{NAS-IP-Address}' AND AcctStopTime IS NULL"
accounting_stop_query_alt = "INSERT into radacct ( AcctSessionId,
AcctUniqueId, UserName, Realm, NASIPAddress, NASPort, NASPortType, AcctStartTime,
AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop,
AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId,
AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay,
AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}',
'%{NAS-Port-Type}', '0', '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '',
'%{Connect-Info}', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}',
'%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}',
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0',
'%{Acct-Delay-Time}')"
diff -ur radiusd.ORIG/src/modules/rlm_sql/drivers/rlm_sql_postgresql/db_postgresql.sql
radiusd/src/modules/rlm_sql/drivers/rlm_sql_postgresql/db_postgresql.sql
--- radiusd.ORIG/src/modules/rlm_sql/drivers/rlm_sql_postgresql/db_postgresql.sql
Thu Feb 21 18:23:47 2002
+++ radiusd/src/modules/rlm_sql/drivers/rlm_sql_postgresql/db_postgresql.sql Wed
+Sep 4 09:03:19 2002
@@ -56,10 +56,11 @@
UserName VARCHAR(32) DEFAULT '' NOT NULL,
Realm VARCHAR(30) DEFAULT '',
NASIPAddress VARCHAR(15) DEFAULT '' NOT NULL,
+ NASPort NUMERIC(5),
NASPortId NUMERIC(12),
NASPortType VARCHAR(32),
- AcctStartTime datetime DEFAULT now() NOT NULL,
- AcctStopTime datetime DEFAULT now() NOT NULL,
+ AcctStartTime datetime,
+ AcctStopTime datetime,
AcctSessionTime NUMERIC(12),
AcctAuthentic VARCHAR(32),
ConnectInfo_start VARCHAR(32),
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
