Ключевые слова:SQL, radius, (найти похожие документы)
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.
--
Harris Vaegan-Lloyd. PSTN: +61-02-9557-2638
SMTP: harris@hightide.net.au
- Linux - Perl - Deveolpment - Admin - Web Programming - Databases -
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
+SERVERSQL_OBJS = radiusd.o dict.o files.o util.o md5.o attrprint.o acct.o \
+ misc.o pam.o log.o version.o pgsql.o
SERVER_OBJS = radiusd.o dict.o files.o util.o md5.o attrprint.o acct.o \
misc.o pam.o log.o version.o
SERVERDBM_OBJS = radiusd.o dict.o filesdbm.o util.o md5.o attrprint.o acct.o \
@@ -20,17 +22,21 @@
dbm: radiusd.dbm builddbm
radiusd: $(SERVER_OBJS)
- $(CC) $(LDFLAGS) -o radiusd $(SERVER_OBJS) $(LIBS) $(LCRYPT) $(PAMLIB)
+ $(CC) $(LDFLAGS) -o radiusd $(SERVER_OBJS) $(LIBS) $(LCRYPT) $(PAMLIB) $(SQLLIB)
radiusd.dbm: $(SERVERDBM_OBJS)
$(CC) $(LDFLAGS) -o radiusd.dbm $(SERVERDBM_OBJS) $(LIBS) $(LCRYPT) \
- $(DBMLIB) $(PAMLIB)
+ $(DBMLIB) $(PAMLIB) $(SQLLIB)
+
+radiusd.sql: $(SERVERSQL_OBJS)
+ $(CC) $(LDFLAGS) -o radiusd $(SERVERSQL_OBJS) $(LIBS) $(LCRYPT) \
+ $(DBMLIB) $(PAMLIB) $(SQLLIB)
radiusd.o: radiusd.c $(INCLUDES)
- $(CC) $(CFLAGS) $(PAM) -c radiusd.c
+ $(CC) $(CFLAGS) $(PAM) $(SQL) -c radiusd.c
acct.o: acct.c $(INCLUDES)
- $(CC) $(CFLAGS) -c acct.c
+ $(CC) $(CFLAGS) $(SQL) -c acct.c
attrprint.o: attrprint.c $(INCLUDES)
$(CC) $(CFLAGS) -c attrprint.c
@@ -52,6 +58,9 @@
pam.o: pam.c $(INCLUDES)
$(CC) $(CFLAGS) $(PAM) -c pam.c
+
+pgsql.o: pgsql.c $(INCLUDES)
+ $(CC) $(CFLAGS) $(SQL) -c pgsql.c
version.o: version.c $(INCLUDES)
$(CC) $(CFLAGS) -o version.o -c version.c
diff -ur -N radiusd-cistron-1.5.4.3/src/Makefile radiusd-sql/src/Makefile
--- radiusd-cistron-1.5.4.3/src/Makefile Sun Nov 23 01:51:42 1997
+++ radiusd-sql/src/Makefile Sun Aug 9 18:40:08 1998
@@ -14,15 +14,20 @@
endif
CC = gcc
-CFLAGS = -Wall -g # -DNOSHADOW
+CFLAGS = -Wall -g -I/usr/local/pgsql/include# -DNOSHADOW
LDFLAGS = # -s # tatic
-LIBS = $(LSHADOW)
+LIBS = $(LSHADOW) -lcrypt
+INCLUDES =
+
DBM = -DNDBM
DBMLIB = -ldb
#PAM = -DPAM
#PAMLIB = -lpam -ldl
+
+SQL = -DUSE_SQL
+SQLLIB = -lpq -L/usr/local/pgsql/lib
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.
+ */
+ curtime = curtime - sess_time;
+ tm = gmtime(&curtime);
+ snprintf(start_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);
+ /* Hey rocky! Watch me pull an insert statement out of my hat! */
+
+ snprintf(sql, 256, "insert into logs values ( %ld, %ld, '%s', '%s', '%s')", bwin, bwout, name, start_time, stop_time);
+
+ res = PQexec(PSQLconn, sql);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK ){
+ log(L_ERR, "Insert failed: %s\n", PQerrorMessage(PSQLconn));
+ }
+ free(sql);
+ free(name);
+ free(start_time);
+ free(stop_time);
+
+ return 0;
+}
+#endif /* USE_SQL */
int rad_accounting_orig(AUTH_REQ *authreq, int authtype, char *f)
{
@@ -638,6 +722,10 @@
* we run it first, the stripped info will also
* get into the "details" file.
*/
+#if USE_SQL
+ if (rad_accounting_sql(authreq) == 0)
+ reply = 1;
+#endif
if (rad_accounting_new(authreq, 1) == 0)
reply = 1;
if (rad_accounting_orig(authreq, auth, NULL) == 0)
@@ -647,6 +735,10 @@
* First log into the details file, before the
* username gets stripped by rad_accounting_new.
*/
+#if USE_SQL
+ if (rad_accounting_sql(authreq) == 0)
+ reply = 1;
+#endif
if (rad_accounting_orig(authreq, auth, NULL) == 0)
reply = 1;
if (rad_accounting_new(authreq, 1) == 0)
diff -ur -N radiusd-cistron-1.5.4.3/src/pgsql.c radiusd-sql/src/pgsql.c
--- radiusd-cistron-1.5.4.3/src/pgsql.c Thu Jan 1 10:00:00 1970
+++ radiusd-sql/src/pgsql.c Mon Aug 3 23:51:41 1998
@@ -0,0 +1,32 @@
+#include <stdio.h>
+#include "/usr/local/pgsql/include/libpq-fe.h"
+#include <unistd.h>
+#include "radiusd.h"
+
+PGconn *PSQLconn;
+
+void exit_nicely(PGconn* conn)
+{
+ PQfinish(conn);
+ exit(1);
+}
+
+int psql_pass(char *user, char *pass) {
+ PGresult* res;
+ char *passbuff;
+ char *sql;
+
+ passbuff = malloc(8);
+
+ sql = malloc(sizeof("select pass from users where username = ''") + sizeof(user));
+ sprintf(sql, "select pass from users where username = '%s'", user);
+ res = PQexec(PSQLconn, sql);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK) {
+ log(L_ERR, "No such user in SQL db.");
+ PQclear(res);
+ }
+ free(sql);
+ snprintf(passbuff,8,"%s",PQgetvalue(res,0,0));
+ PQclear(res);
+ return(strncmp(pass, passbuff, 8));
+}
diff -ur -N radiusd-cistron-1.5.4.3/src/radius.h radiusd-sql/src/radius.h
--- radiusd-cistron-1.5.4.3/src/radius.h Sat Jul 4 23:54:46 1998
+++ radiusd-sql/src/radius.h Wed Jul 15 20:16:15 1998
@@ -164,6 +164,7 @@
#define PW_AUTHTYPE_SECURID 2
#define PW_AUTHTYPE_CRYPT 3
#define PW_AUTHTYPE_REJECT 4
+#define PW_AUTHTYPE_SQL 252
#define PW_AUTHTYPE_PAM 253
#define PW_AUTHTYPE_ACCEPT 254
diff -ur -N radiusd-cistron-1.5.4.3/src/radiusd.c radiusd-sql/src/radiusd.c
--- radiusd-cistron-1.5.4.3/src/radiusd.c Sat Jul 4 23:54:26 1998
+++ radiusd-sql/src/radiusd.c Sun Aug 9 18:27:28 1998
@@ -93,7 +93,9 @@
UINT4 expiration_seconds;
UINT4 warning_seconds;
static AUTH_REQ *first_request;
-
+#if USE_SQL
+PGconn *PSQLconn;
+#endif
#if !defined(__linux__) && !defined(__GNU_LIBRARY__)
extern int errno;
#endif
@@ -160,6 +162,13 @@
int main(int argc, char **argv)
{
+#if USE_SQL
+ char *pghost = "localhost";
+ char *pgport = NULL;
+ char *pgoptions = NULL;
+ char *pgtty = NULL;
+ char *dBname = "radiusd";
+#endif
int salen;
int result;
struct sockaddr salocal;
@@ -334,6 +343,16 @@
perror ("acct bind");
exit(1);
}
+#if USE_SQL
+ /*
+ * Start that funky SQL thang happening.
+ */
+ PSQLconn = PQsetdb(pghost,pgport,pgoptions,pgtty,dBname);
+ if (PQstatus(PSQLconn) == CONNECTION_BAD) {
+ log(L_INFO, "Connection to the database failed.");
+ exit_nicely(PSQLconn);
+ }
+#endif
/*
* Disconnect from session
@@ -961,7 +980,7 @@
strcpy(userpass, string);
}
-#if 0 /* DEBUG */
+#if 1 /* DEBUG */
printf("auth_type=%d, string=%s, namepair=%s, password_pair=%s\n",
auth_type, string, name,
password_pair ? password_pair->strvalue : "");
@@ -1049,6 +1068,14 @@
CHAP_VALUE_LENGTH) != 0)
result = -1;
break;
+#if USE_SQL
+ case PW_AUTHTYPE_SQL:
+ printf("Entering Auth For SQL");
+ if(psql_pass(name, string) != 0) {
+ result = -1;
+ }
+ break;
+#endif /* USEUSE_SQL */
default:
result = -1;
break;
diff -ur -N radiusd-cistron-1.5.4.3/src/radiusd.h radiusd-sql/src/radiusd.h
--- radiusd-cistron-1.5.4.3/src/radiusd.h Wed Jun 24 00:41:10 1998
+++ radiusd-sql/src/radiusd.h Sun Aug 9 18:28:10 1998
@@ -33,7 +33,9 @@
#include "sysdep.h"
#include "radius.h"
#include "conf.h"
-
+#if USE_SQL
+#include "/usr/local/pgsql/include/libpq-fe.h"
+#endif
/* Server data structures */
typedef struct dict_attr {
@@ -121,7 +123,9 @@
extern int radius_pid;
extern int use_dbm;
extern int log_stripped_names;
-
+#if USE_SQL
+extern PGconn *PSQLconn;
+#endif
/*
* Function prototypes.
*/
@@ -197,3 +201,8 @@
int pam_pass(char *name, char *passwd);
#endif
+#ifdef USE_SQL
+/* pgsql.c */
+void exit_nicely(PGconn *conn);
+int psql_pass(char *user, char *pass);
+#endif
