Re: add AuthType to lastauth table

Rick Romero <[email protected]>
Newsgroups gmane.mail.vpopmail
Message-ID <[email protected]> 
Quoting Matt Brookings <[email protected]>:
>> -----BEGIN PGP SIGNED MESSAGE-----
>   > Hash: SHA1
>   >
>   > On 11/09/2010 01:45 PM, Rick Romero wrote:
>   >> I'm interested in knowing not only what IP the user last auth'd, but
>   >> also how they connected.  That gives me more info right from the tables
>   >> on how a particular is using the system, and how the system is utilized
>   >> overall.
>   >>
>   >> I attached a patch and honestly I haven't even tested yet - just thought
>   >> I'd throw it out there..
>   >>
>   >> - It utilizes a new field 'type' char(10), and puts the text AuthType in
>   >> there.  I'm not sure if a table change is handled via README or  
> automated..
>   >> - It also adjusts the vget_lastauth to grab the 'latest' record for that
>   >> user from the lastauth table, no matter how they auth'd.
>   >> - The new info is only accessible by direct query.
>   >
>   > 5.4 is feature-frozen.  It only accepts bugfixes.  If you would like
>   > to work on the patch for 5.5, it would be appreciated.
How's this? I did change the fieldname from 'type' to 'authtype'.
I also modified all the backends as described above, except for ldap,
openldap and cdb.   Those 3 will accept the 'authtype' parameter in the
function for completeness, but will not use it.

I've never done Oracle, but I tried to use the rank() function to get a
single 'latest' result for the vget_lastauth function. Not sure about that
one..

Rick


!DSPAM:4cd9bdf632711223351550!
vpopmail-5.5.0-authtype.patch (text/x-diff, 11.5 KB) 
diff -ru vpopmail-5.5.0-orig/backends/cdb/vcdb.c vpopmail-5.5.0/backends/cdb/vcdb.c
--- vpopmail-5.5.0-orig/backends/cdb/vcdb.c	2010-11-05 13:37:23.000000000 -0500
+++ vpopmail-5.5.0/backends/cdb/vcdb.c	2010-11-09 14:56:12.000000000 -0600
@@ -1074,7 +1074,7 @@
     return(unlink(dir_control_file));
 }
 
-int set_lastauth(char *user, char *domain, char *remoteip )
+int set_lastauth(char *user, char *domain, char *remoteip, char *authtype)
 {
  char *tmpbuf;
  FILE *fs;
diff -ru vpopmail-5.5.0-orig/backends/ldap/vldap.c vpopmail-5.5.0/backends/ldap/vldap.c
--- vpopmail-5.5.0-orig/backends/ldap/vldap.c	2010-11-05 13:37:24.000000000 -0500
+++ vpopmail-5.5.0/backends/ldap/vldap.c	2010-11-09 14:36:14.000000000 -0600
@@ -1235,7 +1235,7 @@
 }
 
 
-int set_lastauth(char *user, char *domain, char *remoteip ) {
+int set_lastauth(char *user, char *domain, char *remoteip, char *authtype ) {
     return(vset_lastauth_time(user, domain, remoteip, time(NULL) ));
 }
 
diff -ru vpopmail-5.5.0-orig/backends/mysql/vmysql.c vpopmail-5.5.0/backends/mysql/vmysql.c
--- vpopmail-5.5.0-orig/backends/mysql/vmysql.c	2010-11-05 13:37:23.000000000 -0500
+++ vpopmail-5.5.0/backends/mysql/vmysql.c	2010-11-09 14:51:10.000000000 -0600
@@ -1298,7 +1298,7 @@
 
 /************************************************************************/
 #ifdef ENABLE_AUTH_LOGGING
-int set_lastauth(char *user, char *domain, char *remoteip )
+int set_lastauth(char *user, char *domain, char *remoteip, char *authtype )
 {
  int err;
 
@@ -1307,11 +1307,11 @@
     qnprintf( SqlBufUpdate, SQL_BUF_SIZE,
         "INSERT INTO lastauth "
         "SET user = '%s', domain = '%s', "
-        "remote_ip = '%s', timestamp = %lu "
+        "remote_ip = '%s', timestamp = %lu, authtype = '%s' "
         "ON DUPLICATE KEY UPDATE "
         "user = '%s', domain = '%s', "
         "remote_ip = '%s', timestamp = %lu",
-        user, domain, remoteip, time(NULL),
+        user, domain, remoteip, time(NULL), authtype,
         user, domain, remoteip, time(NULL));
     if (mysql_query(&mysql_update,SqlBufUpdate)) {
         vcreate_lastauth_table();
@@ -1332,7 +1332,7 @@
     if ( (err=vauth_open_read()) != 0 ) return(err);
 
     qnprintf( SqlBufRead,  SQL_BUF_SIZE,
-    "SELECT timestamp FROM lastauth WHERE user='%s' AND domain='%s'", 
+    "SELECT timestamp FROM lastauth WHERE user='%s' AND domain='%s' order by timestamp LIMIT 1", 
         pw->pw_name, domain);
     if (mysql_query(&mysql_read,SqlBufRead)) {
         vcreate_lastauth_table();
@@ -1359,7 +1359,7 @@
     if ( vauth_open_read() != 0 ) return(NULL);
 
     qnprintf( SqlBufRead,  SQL_BUF_SIZE,
-    "SELECT remote_ip FROM lastauth WHERE user='%s' AND domain='%s'", 
+    "SELECT remote_ip FROM lastauth WHERE user='%s' AND domain='%s' order by timestamp LIMIT 1", 
         pw->pw_name, domain);
     if (mysql_query(&mysql_read,SqlBufRead)) {
         vcreate_lastauth_table();
diff -ru vpopmail-5.5.0-orig/backends/mysql/vmysql.h.in vpopmail-5.5.0/backends/mysql/vmysql.h.in
--- vpopmail-5.5.0-orig/backends/mysql/vmysql.h.in	2010-11-05 13:37:23.000000000 -0500
+++ vpopmail-5.5.0/backends/mysql/vmysql.h.in	2010-11-09 14:52:02.000000000 -0600
@@ -96,6 +96,7 @@
 domain char(96) NOT NULL,\
 remote_ip char(18) not null,  \
 timestamp bigint default 0 NOT NULL, \
+authtype char(10) NOT NULL ,\
 primary key (user, domain)"
 
 char *vauth_munch_domain(char *);
diff -ru vpopmail-5.5.0-orig/backends/openldap/vopenldap.c vpopmail-5.5.0/backends/openldap/vopenldap.c
--- vpopmail-5.5.0-orig/backends/openldap/vopenldap.c	2010-11-05 13:37:23.000000000 -0500
+++ vpopmail-5.5.0/backends/openldap/vopenldap.c	2010-11-09 14:52:48.000000000 -0600
@@ -856,7 +856,7 @@
     return(0);
 }
 
-int set_lastauth(char *user, char *domain, char *remoteip ) {
+int set_lastauth(char *user, char *domain, char *remoteip, char *authtype ) {
     return(set_lastauth_time(user, domain, remoteip, time(NULL) ));
 }
 
diff -ru vpopmail-5.5.0-orig/backends/oracle/voracle.h vpopmail-5.5.0/backends/oracle/voracle.h
--- vpopmail-5.5.0-orig/backends/oracle/voracle.h	2010-11-05 13:37:23.000000000 -0500
+++ vpopmail-5.5.0/backends/oracle/voracle.h	2010-11-09 15:00:55.000000000 -0600
@@ -82,6 +82,7 @@
 pw_domain char(96) NOT NULL,\
 remote_ip char(18) not null,  \
 timestamp int default 0 NOT NULL, \
+authtype char(10) NOT NULL, \
 primary key (pw_user, pw_domain)"
 
 char *vauth_munch_domain(char *);
diff -ru vpopmail-5.5.0-orig/backends/oracle/voracle.pc vpopmail-5.5.0/backends/oracle/voracle.pc
--- vpopmail-5.5.0-orig/backends/oracle/voracle.pc	2010-11-05 13:37:23.000000000 -0500
+++ vpopmail-5.5.0/backends/oracle/voracle.pc	2010-11-09 14:45:05.000000000 -0600
@@ -1115,7 +1115,7 @@
 }
 
 #ifdef ENABLE_AUTH_LOGGING
-int vset_lastauth(char *user, char *domain, char *remoteip )
+int vset_lastauth(char *user, char *domain, char *remoteip, char *authtype )
 {
  int err;
 
@@ -1123,7 +1123,7 @@
 
     qnprintf( SqlBufUpdate, SQL_BUF_SIZE,
 "update lastauth set pw_user='%s', pw_domain='%s', \
-remote_ip='%s', timestamp=%lu", user, domain, remoteip, time(NULL)); 
+remote_ip='%s', timestamp=%lu, authtype='%s'", user, domain, remoteip, time(NULL), authtype); 
 
 fprintf(stderr, "auth log: %s\n", SqlBufUpdate);
     EXEC SQL PREPARE S FROM :SqlBufUpdate;
@@ -1141,7 +1141,7 @@
 
 
     qnprintf( SqlBufUpdate,  SQL_BUF_SIZE,
-    "select timestamp from lastauth where pw_user='%s' and pw_domain='%s'", 
+    "select timestamp from (select timstamp rank() over (order by timestamp) r from lastauth where pw_user='%s' and pw_domain='%s') where r=1", 
         pw->pw_name, domain);
     EXEC SQL PREPARE SD FROM :SqlBufUpdate;
     EXEC SQL DECLARE CD CURSOR FOR SD;
diff -ru vpopmail-5.5.0-orig/backends/postgres/vpgsql.c vpopmail-5.5.0/backends/postgres/vpgsql.c
--- vpopmail-5.5.0-orig/backends/postgres/vpgsql.c	2010-11-05 13:37:23.000000000 -0500
+++ vpopmail-5.5.0/backends/postgres/vpgsql.c	2010-11-09 15:10:25.000000000 -0600
@@ -1177,7 +1177,7 @@
 }
 
 #ifdef ENABLE_AUTH_LOGGING
-int set_lastauth(char *user, char *domain, char *remoteip )
+int set_lastauth(char *user, char *domain, char *remoteip, char *authtype )
 {
   PGresult *pgres;
   int err=0;
@@ -1185,7 +1185,7 @@
   if ( (err=vauth_open(1)) != 0 ) return(err);
 
   qnprintf( SqlBufUpdate, SQL_BUF_SIZE,
-    "UPDATE lastauth SET remote_ip='%s', timestamp=%lu " \
+    "UPDATE lastauth SET remote_ip='%s', timestamp=%lu, authtype='%s' " \
     "WHERE userid='%s' AND domain='%s'", remoteip, time(NULL), user, domain); 
 
 #ifdef DEBUG
@@ -1203,8 +1203,8 @@
     if( pgres ) PQclear(pgres);
 
     qnprintf( SqlBufUpdate, SQL_BUF_SIZE,
-      "INSERT INTO lastauth (userid, domain, remote_ip, timestamp) " \
-      "VALUES ('%s', '%s', '%s', %lu)", user, domain, remoteip, time(NULL)); 
+      "INSERT INTO lastauth (userid, domain, remote_ip, timestamp, authtype) " \
+      "VALUES ('%s', '%s', '%s', %lu, '%s')", user, domain, remoteip, time(NULL), authtype); 
 
 #ifdef DEBUG
 fprintf(stderr,"INSERT command to run is \n\n%s\n\n", SqlBufUpdate);
@@ -1226,8 +1226,8 @@
 
 /* and try INSERTing now... */
     qnprintf( SqlBufUpdate, SQL_BUF_SIZE,
-      "INSERT INTO lastauth (userid, domain, remote_ip, timestamp) " \
-      "VALUES ('%s', '%s', '%s', %lu)", user, domain, remoteip, time(NULL)); 
+      "INSERT INTO lastauth (userid, domain, remote_ip, timestamp, authtype) " \
+      "VALUES ('%s', '%s', '%s', %lu, '%s')", user, domain, remoteip, time(NULL), authtype); 
 
     pgres=PQexec(pgc, SqlBufUpdate);
     }
@@ -1249,14 +1249,14 @@
 
   if ( (err=vauth_open(0)) != 0 ) return(err);
 
-  qnprintf( SqlBufRead,  SQL_BUF_SIZE, "SELECT timestamp FROM lastauth WHERE userid='%s' AND domain='%s'", pw->pw_name, domain);
+  qnprintf( SqlBufRead,  SQL_BUF_SIZE, "SELECT timestamp FROM lastauth WHERE userid='%s' AND domain='%s' ORDER BY timestamp LIMIT 1", pw->pw_name, domain);
 
   pgres=PQexec(pgc, SqlBufRead);
 
   if ( !pgres || PQresultStatus(pgres) != PGRES_TUPLES_OK ) {
     if( pgres ) PQclear(pgres);
     vcreate_lastauth_table();
-    qnprintf( SqlBufRead,  SQL_BUF_SIZE, "SELECT timestamp FROM lastauth WHERE userid='%s' AND domain='%s'", pw->pw_name, domain);
+    qnprintf( SqlBufRead,  SQL_BUF_SIZE, "SELECT timestamp FROM lastauth WHERE userid='%s' AND domain='%s' ORDER BY timestamp LIMIT 1", pw->pw_name, domain);
     pgres=PQexec(pgc, SqlBufRead);
     if ( !pgres || PQresultStatus(pgres) != PGRES_TUPLES_OK ) {
       fprintf(stderr,"vpgsql: sql error[g]: %s\n", PQerrorMessage(pgc));
@@ -1281,13 +1281,13 @@
 
   if ( vauth_open(0) != 0 ) return(NULL);
 
-  qnprintf( SqlBufRead,  SQL_BUF_SIZE, "select remote_ip from lastauth where userid='%s' and domain='%s'",  pw->pw_name, domain);
+  qnprintf( SqlBufRead,  SQL_BUF_SIZE, "select remote_ip from lastauth where userid='%s' and domain='%s' ORDER BY timestamp LIMIT 1",  pw->pw_name, domain);
 
   pgres=PQexec(pgc, SqlBufRead);
   if ( !pgres || PQresultStatus(pgres) != PGRES_TUPLES_OK ) {
     if( pgres ) PQclear(pgres);
     vcreate_lastauth_table();
-    qnprintf( SqlBufRead,  SQL_BUF_SIZE, "select remote_ip from lastauth where userid='%s' and domain='%s'", pw->pw_name, domain);
+    qnprintf( SqlBufRead,  SQL_BUF_SIZE, "select remote_ip from lastauth where userid='%s' and domain='%s' ORDER BY timestamp LIMIT 1", pw->pw_name, domain);
 
     pgres=PQexec(pgc, SqlBufRead);
     if ( !pgres || PQresultStatus(pgres) != PGRES_TUPLES_OK ) {
diff -ru vpopmail-5.5.0-orig/backends/postgres/vpgsql.h.in vpopmail-5.5.0/backends/postgres/vpgsql.h.in
--- vpopmail-5.5.0-orig/backends/postgres/vpgsql.h.in	2010-11-05 13:37:23.000000000 -0500
+++ vpopmail-5.5.0/backends/postgres/vpgsql.h.in	2010-11-09 14:59:59.000000000 -0600
@@ -87,6 +87,7 @@
 domain varchar(96) NOT NULL,\
 remote_ip varchar(18) NOT NULL,  \
 timestamp bigint default 0 NOT NULL, \
+authtype varchar(10) NOT NULL, \
 PRIMARY key (userid, domain)"
 
 char *vauth_munch_domain(char *);
diff -ru vpopmail-5.5.0-orig/vauthmodule.h vpopmail-5.5.0/vauthmodule.h
--- vpopmail-5.5.0-orig/vauthmodule.h	2010-11-05 13:37:24.000000000 -0500
+++ vpopmail-5.5.0/vauthmodule.h	2010-11-09 14:34:40.000000000 -0600
@@ -24,7 +24,7 @@
 int (*vread_dir_control)(vdir_type *vdir, char *domain, uid_t uid, gid_t gid );
 int (*vwrite_dir_control)(vdir_type *vdir, char *domain, uid_t uid, gid_t gid);
 int (*vdel_dir_control)(char *domain);
-int (*vset_lastauth)( char *user, char *domain, char *remoteip);
+int (*vset_lastauth)( char *user, char *domain, char *remoteip, char *authtype );
 time_t (*vget_lastauth)( struct vqpasswd *pw, char *domain);
 char *(*vget_lastauthip)( struct vqpasswd *pw, char *domain);
 char *(*valias_select)( char *alias, char *domain );
diff -ru vpopmail-5.5.0-orig/vchkpw.c vpopmail-5.5.0/vchkpw.c
--- vpopmail-5.5.0-orig/vchkpw.c	2010-11-05 13:37:24.000000000 -0500
+++ vpopmail-5.5.0/vchkpw.c	2010-11-09 14:34:13.000000000 -0600
@@ -600,7 +600,7 @@
 #ifdef MIN_LOGIN_INTERVAL
 	  last_time = vget_lastauth(vpw, TheDomain );
 #endif
-	  vset_lastauth(TheUser,TheDomain,IpAddr);
+	  vset_lastauth(TheUser,TheDomain,IpAddr,AuthType);
 #ifdef MIN_LOGIN_INTERVAL
 	  if(( vget_lastauth(vpw,TheDomain ) - last_time ) < MIN_LOGIN_INTERVAL ) { 
 		 vchkpw_exit(1);
diff -ru vpopmail-5.5.0-orig/vpopmail.c vpopmail-5.5.0/vpopmail.c
--- vpopmail-5.5.0-orig/vpopmail.c	2010-11-05 13:37:24.000000000 -0500
+++ vpopmail-5.5.0/vpopmail.c	2010-11-09 14:32:40.000000000 -0600
@@ -755,7 +755,7 @@
 #endif
 
   if (vauth_module_feature("AUTH_LOGGING")) {
-	  if (vset_lastauth(username,domain,NULL_REMOTE_IP) !=0) {
+	  if (vset_lastauth(username,domain,NULL_REMOTE_IP, "") !=0) {
 		 /* should we back out of all the work we have done so far? */
 		 chdir(calling_dir);
 		 fprintf (stderr, "Failed to create create lastauth entry\n");
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.