Quoting Matt Brookings <m...@inter7.com>:
-----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!
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");
