When you have multiple freeradius servers, you want to store
authentication attempts in a database rather than a flat file.
The following patch allow for SQL logging after authentication. It
extends the rlm_sql module so now you can put one more query in your
sql.conf file.
The following patch depends on the the patch "Post-Auth-Type" I posted
earlier.
$ cvs diff -u raddb/sql.conf src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql
src/modules/rlm_sql/conf.h src/modules/rlm_sql/rlm_sql.c
Index: raddb/sql.conf
===================================================================
RCS file: /source/radiusd/raddb/sql.conf,v
retrieving revision 1.28
diff -u -r1.28 sql.conf
--- raddb/sql.conf 30 Jul 2003 22:15:30 -0000 1.28
+++ raddb/sql.conf 17 Sep 2003 13:09:43 -0000
@@ -32,7 +32,10 @@
# and stop table in acct_table2
acct_table1 = "radacct"
acct_table2 = "radacct"
-
+
+ # Allow for storing data after authentication
+ postauth_table = "radpostauth"
+
authcheck_table = "radcheck"
authreply_table = "radreply"
@@ -179,4 +182,13 @@
#######################################################################
group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE
UserName='%{SQL-User-Name}'"
+
+ #######################################################################
+ # Authentication Logging Queries
+ #######################################################################
+ # postauth_query - Insert some info after authentication
+ #######################################################################
+
+ postauth_query = "INSERT into ${postauth_table} (id, user, pass, reply, date)
values ('', '%{User-Name}', '%{User-Password}', '%{reply:Packet-Type}', NOW())"
+
}
Index: src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql
===================================================================
RCS file: /source/radiusd/src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql,v
retrieving revision 1.11
diff -u -r1.11 db_mysql.sql
--- src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql 16 Jul 2003 17:35:41
-0000 1.11
+++ src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql 17 Sep 2003 13:09:43
-0000
@@ -117,6 +117,19 @@
KEY UserName (UserName(32))
) ;
+#
+# Table structure for table 'radpostauth'
+#
+
+CREATE TABLE radpostauth (
+ id int(11) NOT NULL auto_increment,
+ user varchar(64) NOT NULL default '',
+ pass varchar(64) NOT NULL default '',
+ reply varchar(32) NOT NULL default '',
+ date timestamp(14) NOT NULL,
+ PRIMARY KEY (id)
+) ;
+
######################################################################
#
# The next two tables are commented out because they are not
Index: src/modules/rlm_sql/conf.h
===================================================================
RCS file: /source/radiusd/src/modules/rlm_sql/conf.h,v
retrieving revision 1.16
diff -u -r1.16 conf.h
--- src/modules/rlm_sql/conf.h 7 Sep 2002 13:23:01 -0000 1.16
+++ src/modules/rlm_sql/conf.h 17 Sep 2003 13:09:43 -0000
@@ -46,6 +46,8 @@
int num_sql_socks;
int connect_failure_retry_delay;
int query_on_not_found;
+ char *sql_postauth_table;
+ char *postauth_query;
/* individual driver config */
void *localcfg;
Index: src/modules/rlm_sql/rlm_sql.c
===================================================================
RCS file: /source/radiusd/src/modules/rlm_sql/rlm_sql.c,v
retrieving revision 1.119
diff -u -r1.119 rlm_sql.c
--- src/modules/rlm_sql/rlm_sql.c 6 Aug 2003 17:05:47 -0000 1.119
+++ src/modules/rlm_sql/rlm_sql.c 17 Sep 2003 13:09:44 -0000
@@ -121,6 +121,10 @@
offsetof(SQL_CONFIG,simul_count_query), NULL, ""},
{"simul_verify_query", PW_TYPE_STRING_PTR,
offsetof(SQL_CONFIG,simul_verify_query), NULL, ""},
+ {"postauth_table", PW_TYPE_STRING_PTR,
+ offsetof(SQL_CONFIG,sql_postauth_table), NULL, "radpostauth"},
+ {"postauth_query", PW_TYPE_STRING_PTR,
+ offsetof(SQL_CONFIG,postauth_query), NULL, ""},
{NULL, -1, 0, NULL, NULL}
};
@@ -1072,6 +1076,47 @@
}
+/*
+ * Execute postauth_query after authentication
+ */
+static int rlm_sql_postauth(void *instance, REQUEST *request) {
+ SQLSOCK *sqlsocket = NULL;
+ SQL_INST *inst = instance;
+ char querystr[MAX_QUERY_LEN];
+
+ DEBUG("rlm_sql (%s): Processing sql_postauth", inst->config->xlat_name);
+
+ /* If postauth_query is not defined, we stop here */
+ if (inst->config->postauth_query[0] == '\0')
+ return RLM_MODULE_NOOP;
+
+ /* Expand variables in the query */
+ memset(querystr, 0, MAX_QUERY_LEN);
+ radius_xlat(querystr, sizeof(querystr), inst->config->postauth_query,
+ request, sql_escape_func);
+ query_log(request, inst, querystr);
+ DEBUG2("rlm_sql (%s) in sql_postauth: query is %s",
+ inst->config->xlat_name, querystr);
+
+ /* Initialize the sql socket */
+ sqlsocket = sql_get_socket(inst);
+ if (sqlsocket == NULL)
+ return RLM_MODULE_FAIL;
+
+ /* Process the query */
+ if (rlm_sql_query(sqlsocket, inst, querystr)) {
+ radlog(L_ERR, "rlm_sql (%s) in sql_postauth: Database query error -
%s",
+ inst->config->xlat_name,
+ (char *)(inst->module->sql_error)(sqlsocket, inst->config));
+ sql_release_socket(inst, sqlsocket);
+ return RLM_MODULE_FAIL;
+ }
+ (inst->module->sql_finish_query)(sqlsocket, inst->config);
+
+ sql_release_socket(inst, sqlsocket);
+ return RLM_MODULE_OK;
+}
+
/* globally exported name */
module_t rlm_sql = {
"SQL",
@@ -1086,7 +1131,7 @@
rlm_sql_checksimul, /* checksimul */
NULL, /* pre-proxy */
NULL, /* post-proxy */
- NULL /* post-auth */
+ rlm_sql_postauth /* post-auth */
},
rlm_sql_detach, /* detach */
rlm_sql_destroy, /* destroy */
--
Nicolas Baradakis
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
