Hello all, After making this work for myself, I thought I would send the documentation on so that others could do it more easily. There are some duplicates that could be cut, but I leave that to you to decide. I have tried to be clear without being completely repetitive. Hopefully others will find it useful. There are 3 long lines in doc/rlm_sqlcounter that I see wrap badly - sorry about that. I was just trying to stay with the style of the file. Thanks, Index: doc/rlm_sqlcounter =================================================================== --- doc/rlm_sqlcounter (revision 939) +++ doc/rlm_sqlcounter (working copy) @@ -19,7 +19,7 @@ [1] Create a text file called sqlcounter.conf in the same directory where radiusd.conf resides (usually /usr/local/etc/raddb) -with the following content: +with the following content (for mysql): #-----# sqlcounter noresetcounter { @@ -59,6 +59,39 @@ #-----# +The respective lines for postgresql are: + +query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'" +query = "SELECT SUM(AcctSessionTime - GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'" +query = "SELECT SUM(AcctSessionTime - GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'" + +If you are running postgres 7.x, you may not have a GREATER function. + +An example of one is: + +CREATE OR REPLACE FUNCTION "greater"(integer, integer) RETURNS integer AS ' +DECLARE + res INTEGER; + one INTEGER := 0; + two INTEGER := 0; +BEGIN + one = $1; + two = $2; + IF one IS NULL THEN + one = 0; + END IF; + IF two IS NULL THEN + two = 0; + END IF; + IF one > two THEN + res := one; + ELSE + res := two; + END IF; + RETURN res; +END; +' LANGUAGE 'plpgsql'; + [2] Include the above file to radiusd.conf by adding a line in modules{ } section Index: raddb/radiusd.conf.in =================================================================== --- raddb/radiusd.conf.in (revision 939) +++ raddb/radiusd.conf.in (working copy) @@ -1454,24 +1454,42 @@ # This query properly handles calls that span from the # previous reset period into the current period but # involves more work for the SQL server than those - # below + # below. For mysql: query = "SELECT SUM(AcctSessionTime - \ GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) \ FROM radacct WHERE UserName='%{%k}' AND \ UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'" + # For postgresql: + # Note - if you are running 7.x, you may not have a GREATER + # function. Please see doc/sql_counter for an example. + # query = "SELECT SUM(AcctSessionTime - \ + # GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) \ + # FROM radacct WHERE UserName='%{%k}' AND \ + # AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'" + # This query ignores calls that started in a previous # reset period and continue into into this one. But it - # is a little easier on the SQL server + # is a little easier on the SQL server. For mysql: # query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE \ # UserName='%{%k}' AND AcctStartTime > FROM_UNIXTIME('%b')" + # For postgresql: + # query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE \ + # UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 > '%b'" + # This query is the same as above, but demonstrates an # additional counter parameter '%e' which is the # timestamp for the end of the period + # For mysql: # query = "SELECT SUM(AcctSessionTime) FROM radacct \ # WHERE UserName='%{%k}' AND AcctStartTime BETWEEN \ # FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')" +# + # For postgresql: + # query = "SELECT SUM(AcctSessionTime) FROM radacct \ + # WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 \ + # BETWEEN '%b' AND '%e'" } sqlcounter monthlycounter { @@ -1484,7 +1502,9 @@ # This query properly handles calls that span from the # previous reset period into the current period but # involves more work for the SQL server than those - # below + # below. + # The same notes above about the differences between mysql + # versus postgres queries apply here. query = "SELECT SUM(AcctSessionTime - \ GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) \ FROM radacct WHERE UserName='%{%k}' AND \ -- -------------------------------------------------------------------------- | Stephen Gran | What nonsense people talk about happy | | steve@lobefin.net | marriages! A man can be happy with any | | http://www.lobefin.net/~steve | woman so long as he doesn't love her. | | | -- Oscar Wilde | --------------------------------------------------------------------------