From: Harris Vaegan-Lloyd <harris@hightide.net.au>
To: miquels@cistron.nl
Subject: SQL for radiusd patch.
Message-ID: <35CEC1CA.C54AEEDE@hightide.net.au>
Date: Mon, 10 Aug 1998 09:47:54 +0000
Organization: Hightide Internet
I hada a desperate need to further destory my little free time and make
my gf hate me so I've added the ability to authenticate and log to a
PostgreSQL databse from radiusd-cistron. I hope you like it ;). It's a
bit hairy at the moment but a fair few people seem to want it as much as
I did.
diff -ur -N radiusd-cistron-1.5.4.3/doc/README.sql radiusd-sql/doc/README.sql
--- radiusd-cistron-1.5.4.3/doc/README.sql Thu Jan 1 10:00:00 1970
+++ radiusd-sql/doc/README.sql Mon Aug 10 19:24:02 1998
@@ -0,0 +1,46 @@
+Greetings dear valued customer..
+
+All the addition to radiusd-cistron to add authentication and logging with an postgresql databse are copyright Harris Vaegan-Lloyd and placed under the current version of the GPL.
+
+This is messy at the moment and I wouldn't reccomend it for use in a production environment. However
+if your marketting department has though up some completly insane pricing scheme for dialup you should
+find it useful ;). All the radius log analysis stuff I've use so far has been very nice but, well. I
+like SQL and think everything is improved by being able to work with a database ;).
+
+Radiusd expects two tables, one with username and passwords, and one to hold the logs. usernames and passwords are self-explanatory. The logs table holds the username, time logged in, time logged out, data in and data out. Time logged in is calculated by subtracting the Acct-Time-Session value. bwin and bwout are calculated from the Acct-Input-Octets and Acct-Output-Octets values. User name is from, well , User-Name, strangely enough.
+
+Example SQL code -
+
+To create the table.
+connect radiusd -
+CREATE TABLE users (pass varchar(16), username varchar(8));
+CREATE TABLE logs (bwin int4, bwout int4, name varchar(32), start datetime, stop datetime);
+
+To find the total bandwidth use in megs for a user -
+select (sum(bwin) + sum(bwout)) /1024) from logs where name='user'
+
+To find the total time use in seconds for a user.
+select sum(date_part('epoch',age(stop,start))) from logs where name='user';
+
+To get it for a specific time period add something like -
+where (date_part('month', start) = 8) and (date_part('year', start) = 1998)
+
+So say if you wanted to find out the total cost for a user considering you charged 35 cents a megabyte and 1.1 cents a minutes with 5 hours a month and 20 megs free youc could use this line to find out the total cost for the user..
+
+select int4larger(sum(bwin + bwout) / 1024 - 20, 0) * 35 +
+ int4larger(((sum(date_part('epoch',age(stop,start))) / 60) - 300)::int4,0) * 1.1
+ where name = 'user' and date_part('month', start) = 8 and date_part('year', start) = 1998;
+
+Not exactly a simple SQL statment but it took me a quarter of an hour to get the same thing that took me four days off and on to do in perl.
+
+TODO -
+- Make the authentication part check a boolean variable to see if the user is locked out or not, maybe a way to set the reason for lockout as well.
+- Throw together a web interface to get user status info for admins and user alike, generate reports etc etc etc. This is the kind of boring cruft I do for a living so it shouldn't take too long but will be ages before I actually get around to it.
+- Do some checks to see if the database connection is OK before each insert, and if it's not reconnect.
+- Give it some real testing. The perl scripts I used to send the accounting packets don't really count.
+- Find a new job where I can get some real time to work on it ;).
+
+
+Compilation instructions.
+
+Uncomment the lines that begin with SQL and SQLLIB in the Makefile as per PAM support. Then make as usual.
diff -ur -N radiusd-cistron-1.5.4.3/src/Make.inc radiusd-sql/src/Make.inc
--- radiusd-cistron-1.5.4.3/src/Make.inc Sat Jul 4 23:49:25 1998
+++ radiusd-sql/src/Make.inc Sun Aug 9 18:41:46 1998
@@ -7,6 +7,8 @@
BINDIR = /usr/local/bin
SBINDIR = /usr/local/sbin
BINDIR = /usr/local/bin
SBINDIR = /usr/local/sbin
diff -ur -N radiusd-cistron-1.5.4.3/src/acct.c radiusd-sql/src/acct.c
--- radiusd-cistron-1.5.4.3/src/acct.c Wed Jun 24 00:13:24 1998
+++ radiusd-sql/src/acct.c Sun Aug 9 18:28:39 1998
@@ -27,7 +27,9 @@
#include "radiusd.h"
#include "radutmp.h"
-
+#if USE_SQL
+#include "/usr/local/pgsql/include/libpq-fe.h"
+#endif
/*
* FIXME: this should be configurable.
*/
@@ -40,7 +42,9 @@
static char porttypes[] = "ASITX";
#define LOCK_LEN sizeof(struct radutmp)
-
+#ifdef USE_SQL
+PGconn *PSQLconn;
+#endif
/*
* UUencode 4 bits base64. We use this to turn a 4 byte field
* (an IP adres) into 6 bytes of ASCII. This is used for the
@@ -455,8 +459,88 @@
return ret;
}
+#if USE_SQL
+int rad_accounting_sql(AUTH_REQ *auth_req)
+{
+ VALUE_PAIR *pair;
+ time_t curtime;
+ struct tm *tm;
+ char *start_time;
+ char *stop_time;
+ ulong sess_time;
+ char *name;
+ UINT4 bwin;
+ UINT4 bwout;
+ char *sql;
+ PGresult* res;
+
+
+ curtime = time(0);
+ start_time = malloc(32);
+ stop_time = malloc(32);
+ name = malloc(32);
+
+ /* next we use gmtime() to take this time and get it into a format
+ * suitable for SQL. */
+ tm = gmtime(&curtime);
+
+ snprintf(stop_time, 32, "%d-%d-%d %d:%d:%d",
+ (1900 + tm->tm_year),
+ (tm->tm_mon + 1),
+ tm->tm_mday,
+ tm->tm_hour, tm->tm_min, tm->tm_sec);
+
+ /* Init the SQL string. */
+ sql = malloc(256);
+
+ pair = auth_req->request;
+ while(pair != (VALUE_PAIR *)NULL) {
+ if (! strcmp(pair->name, "Acct-Status-Type")) {
+ if (pair->lvalue != PW_STATUS_STOP ) {
+ return 0; // We only need the stop packets for
+ } // the sql accounting. */
+ } else if (! strcmp(pair->name, "Acct-Input-Octets")) {
+ bwin = pair->lvalue;
+ } else if (! strcmp(pair->name, "Acct-Output-Octets")) {
+ bwout = pair->lvalue;
+ } else if (! strcmp(pair->name, "User-Name")) {
+ sprintf(name, "%s", pair->strvalue);
+ } else if (! strcmp(pair->name, "Acct-Session-Time")) {
+ sess_time = pair->lvalue;
+ }
+ pair = pair->next;
+ }
+
+ /* O.K. Now we have the time that the session ended, we use the
+ * Acct-Session-Time to work out when it started
+ * We got the length of the session from the accouning request and
+ * the time that it ended form when we received this request.
+ */
Vova
