sqlite3: Implement eDBregister_vpnclientaddr()

This commit implements the eDBregister_vpnclientaddr() needed by the
newer eurephia-auth plug-in.  This is needed to improve the tun support
in eurephia.

In addition, this also updates the SQL schema to include IPv4 and in
the future IPv6 addresses in the lastlog and VPN address history
(openvpn_vpnaddr_history).  The old openvpn_macaddr_history table
is deprecated.

Signed-off-by: David Sommerseth <dazo@users.sourceforge.net>

3 files changed, 119 insertions, 10 deletions

diff --git a/database/sqlite/edb-sqlite.c b/database/sqlite/edb-sqlite.c
index d9d4af6..4c15a5a 100644
--- a/database/sqlite/edb-sqlite.c
+++ b/database/sqlite/edb-sqlite.c
@@ -1,7 +1,7 @@
 /*  edb-sqlite.c  --  Main driver for eurephia authentication plugin for OpenVPN
  *                    This is the SQLite database driver
  *
- *  GPLv2 only - Copyright (C) 2008 - 2011
+ *  GPLv2 only - Copyright (C) 2008 - 2012
  *               David Sommerseth <dazo@users.sourceforge.net>
  *
  *  This program is free software; you can redistribute it and/or
@@ -37,9 +37,9 @@
 #include <unistd.h>
 #include <assert.h>
 
-#define DRIVERVERSION "1.2"     /**< Defines the software version of this driver */
+#define DRIVERVERSION "1.3"     /**< Defines the software version of this driver */
 #ifndef DRIVERAPIVERSION
-# define DRIVERAPIVERSION 2     /**< Sets the API version level of this driver */
+# define DRIVERAPIVERSION 3     /**< Sets the API version level of this driver */
 #endif
 
 #include <sqlite3.h>
@@ -86,7 +86,7 @@ static const eDBattempt_types_t eDBattempt_types[] = {
  * @copydoc eDB_DriverVersion()
  */
 const char *eDB_DriverVersion(void) {
-        return "edb-sqlite (v"DRIVERVERSION")  David Sommerseth 2008-2011 (C) GPLv2";
+        return "edb-sqlite (v"DRIVERVERSION")  David Sommerseth 2008-2012 (C) GPLv2";
 }
 
 
@@ -674,6 +674,65 @@ int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const cha
 
 
 /**
+ * @copydoc eDBregister_vpnclientaddr()
+ */
+int eDBregister_vpnclientaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const char *macaddr,
+                              const char *vpnip4addr, const char *vpnip6addr)
+{
+        dbresult *res = NULL;
+        int ret = 0;
+
+        DEBUG(ctx, 20, "Function call: eDBregister_vpnclientaddr(ctx, '%s', '%s', '%s', '%s')",
+              session->sessionkey, macaddr, vpnip4addr, vpnip6addr);
+
+        if( (macaddr == NULL) && (strlen_nullsafe(macaddr) > 18) ) {
+                eurephia_log(ctx, LOG_FATAL, 0, "Invalid MAC address");
+                return 0;
+        }
+
+        // Register client addresses into history table
+        res = sqlite_query(ctx,
+                           "INSERT INTO openvpn_vpnaddr_history (sessionkey, macaddr, ip4addr, ip6addr) "
+                           "VALUES ('%q','%q','%q','%q')",
+                           session->sessionkey,
+                           (ctx->tuntype == tuntype_TAP ? macaddr : ""),
+                           (vpnip4addr ? vpnip4addr : ""), (vpnip6addr ? vpnip6addr : ""));
+        if( sqlite_query_status(res) != dbSUCCESS ) {
+                eurephia_log(ctx, LOG_FATAL, 0, "Failed to log new VPN client addresses for session");
+                sqlite_log_error(ctx, res);
+                ret = 0;
+                goto exit;
+        }
+        sqlite_free_results(res);
+
+        // Update lastlog to reflect last used MAC address for the session
+        res = sqlite_query(ctx,
+                           "UPDATE openvpn_lastlog SET sessionstatus = 2, macaddr = '%q', vpnipaddr = '%q', vpnipv6addr = '%q' "
+                           " WHERE sessionkey = '%q' AND sessionstatus = 1",
+                           (macaddr ? macaddr : ""), (vpnip4addr ? vpnip4addr : ""), (vpnip6addr ? vpnip6addr : ""),
+                           session->sessionkey);
+        if( sqlite_query_status(res) == dbSUCCESS ) {
+                // TAP mode: Save the MAC address in the session values register - needed for the destroy session
+                if( (ctx->tuntype == tuntype_TAP) && eDBset_session_value(ctx, session, "macaddr", macaddr) == 0 ) {
+                        eurephia_log(ctx, LOG_FATAL, 0, "Could not save MAC address into session variables");
+                        ret = 0;
+                } else {
+                        ret = 1;
+                }
+        } else {
+                eurephia_log(ctx, LOG_FATAL, 0, "Could not update lastlog with new VPN client addresses for session");
+                sqlite_log_error(ctx, res);
+                ret = 0;
+        }
+
+ exit:
+        sqlite_free_results(res);
+        return ret;
+}
+
+
+
+/**
  * @copydoc eDBregister_logout()
  */
 int eDBregister_logout(eurephiaCTX *ctx, eurephiaSESSION *skey, 
diff --git a/database/sqlite/sql-schema-delta-1.sql b/database/sqlite/sql-schema-delta-1.sql
new file mode 100644
index 0000000..05028ff
--- /dev/null
+++ b/database/sqlite/sql-schema-delta-1.sql
@@ -0,0 +1,47 @@
+--
+-- eurephia database schema for SQLite3
+--
+-- This SQL scripts updates the previous SQL schema to the 
+-- new schema needed by edb-sqlite v1.3
+--
+-- GPLv2 only - Copyright (C) 2012
+--              David Sommerseth <dazo@users.sourceforge.net>
+--
+--  This program is free software; you can redistribute it and/or
+--  modify it under the terms of the GNU General Public License
+--  as published by the Free Software Foundation; version 2
+--  of the License.
+--
+--  This program is distributed in the hope that it will be useful,
+--  but WITHOUT ANY WARRANTY; without even the implied warranty of
+--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+--  GNU General Public License for more details.
+--
+--  You should have received a copy of the GNU General Public License
+--  along with this program; if not, write to the Free Software
+--  Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+--
+
+
+ALTER TABLE openvpn_lastlog ADD COLUMN vpnipv6addr varchar(48);
+
+-- openvpn_vpnaddr_history - This keeps an overview over which VPN client addresses
+-- a session have used, in case the client changes the address.
+-- This table replaces openvpn_macaddr_history.
+CREATE TABLE openvpn_vpnaddr_history (
+       sessionkey     varchar(64)  NOT NULL,
+       macaddr        varchar(20)  NOT NULL,
+       ip4addr        varchar(32)  ,
+       ip6addr        varchar(48)  ,
+       registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
+       semaid         integer      PRIMARY KEY AUTOINCREMENT
+);
+CREATE INDEX openvpn_vpnaddr_hist_sessionkey ON openvpn_vpnaddr_history(sessionkey);
+CREATE INDEX openvpn_vpnaddr_hist_macaddr ON openvpn_vpnaddr_history(macaddr);
+
+-- Copy over the old data from openvpn_macaddr_history to openvpn_vpnaddr_history
+INSERT INTO openvpn_vpnaddr_history (sessionkey, macaddr, registered, semaid)
+     SELECT sessionkey, macaddr, registered, semaid
+       FROM openvpn_macaddr_history
+      ORDER BY semaid;
+
diff --git a/database/sqlite/sql-schema.sql b/database/sqlite/sql-schema.sql
index 581aaac..6d2befb 100644
--- a/database/sqlite/sql-schema.sql
+++ b/database/sqlite/sql-schema.sql
@@ -1,7 +1,7 @@
 --
 -- eurephia database schema for SQLite3
 --
--- GPLv2 only - Copyright (C) 2008 - 2010
+-- GPLv2 only - Copyright (C) 2008 - 2012
 --              David Sommerseth <dazo@users.sourceforge.net>
 --
 --  This program is free software; you can redistribute it and/or
@@ -72,6 +72,7 @@ CREATE TABLE openvpn_lastlog (
        macaddr          varchar(20)  ,
        vpnipaddr        varchar(32)  NOT NULL,
        vpnipmask        varchar(32)  NOT NULL,
+       vpnipv6addr      varchar(48)  ,
        sessionstatus    integer      NOT NULL DEFAULT 0,
        sessionkey       varchar(128) ,
        login            timestamp    ,
@@ -84,16 +85,18 @@ CREATE TABLE openvpn_lastlog (
 );
 CREATE UNIQUE INDEX openvpn_lastlog_sessionkey ON openvpn_lastlog(sessionkey);
 
--- openvpn_macaddr_history - This keeps an overview over which MAC addresses a session
--- have used, in case the client changes the MAC address.
-CREATE TABLE openvpn_macaddr_history (
+-- openvpn_vpnaddr_history - This keeps an overview over which VPN client addresses
+-- a session have used, in case the client changes the address.
+CREATE TABLE openvpn_vpnaddr_history (
        sessionkey     varchar(64)  NOT NULL,
        macaddr        varchar(20)  NOT NULL,
+       ip4addr        varchar(32)  ,
+       ip6addr        varchar(48)  ,
        registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
        semaid         integer      PRIMARY KEY AUTOINCREMENT
 );
-CREATE INDEX openvpn_macaddr_hist_sessionkey ON openvpn_macaddr_history(sessionkey);
-CREATE INDEX openvpn_macaddr_hist_macaddr ON openvpn_macaddr_history(macaddr);
+CREATE INDEX openvpn_vpnaddr_hist_sessionkey ON openvpn_vpnaddr_history(sessionkey);
+CREATE INDEX openvpn_vpnaddr_hist_macaddr ON openvpn_vpnaddr_history(macaddr);
 
 -- openvpn_sessions - A little storage of variables needed by eurephia to keep track
 -- of all the sessions