/* edb-sqlite.c -- Main driver for eurephia authentication plugin for OpenVPN * This is the SQLite database driver * * GPLv2 only - Copyright (C) 2008 - 2012 * David Sommerseth * * 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. * */ /** * @file edb-sqlite.c * @author David Sommerseth * @date 2008-08-06 * * @brief eurephia database driver for the SQLite3 database. * This file is the main API for the driver. * */ #include #include #include #include #include #define DRIVERVERSION "1.3" /**< Defines the software version of this driver */ #ifndef DRIVERAPIVERSION # define DRIVERAPIVERSION 3 /**< Sets the API version level of this driver */ #endif #include #include #include #include #include #include #include #include #include "sqlite.h" /** * Mapping struct - maps attempt types (attempt_IPADDR, attempt_CERTIFICATE, attempt_USERNAME) * to database field names, configuration options (with default values) and description */ typedef struct { char *colname; /**< Field when doing look up in blacklist and attempts tables */ char *colname_where; /**< Field when using column name in WHERE section of SQL queries */ char *allow_cfg; /**< Configure parameter for the attempt limits */ char *descr; /**< Description, used to give more readable output for users */ char *default_value; /**< Default value, if config option is not found */ char *value_func; /**< If not NULL, the value will be passed through the given SQL function */ } eDBattempt_types_t; /** * Static mapping table with the needed values. Uses the eDBattempt_types_t struct. */ static const eDBattempt_types_t eDBattempt_types[] = { {NULL, NULL, NULL, NULL}, {"remoteip\0", "remoteip\0", "allow_ipaddr_attempts\0", "IP Address\0", "10\0", NULL}, {"digest\0", "lower(digest)\0", "allow_cert_attempts\0", "Certificate\0", "5\0", "lower\0"}, {"username\0", "username\0", "allow_username_attempts\0", "Username\0", "5\0", NULL}, {NULL, NULL, NULL, NULL} }; /** * @copydoc eDB_DriverVersion() */ const char *eDB_DriverVersion(void) { return "edb-sqlite (v"DRIVERVERSION") David Sommerseth 2008-2012 (C) GPLv2"; } /** * @copydoc eDB_DriverAPIVersion() */ int eDB_DriverAPIVersion() { return DRIVERAPIVERSION; } /* * local functions */ /** * Internal driver function for simplifying update of openvpn_blacklist. It will simply just * update the 'last_accessed' field in the blacklist table. * * @param ctx eurephiaCTX * @param blid Blacklist ID, integer value corresponding to the record in the database */ void update_attempts(eurephiaCTX *ctx, const char *blid) { dbresult *res = NULL; if( blid != NULL ) { res = sqlite_query(ctx, "UPDATE openvpn_blacklist " " SET last_accessed = CURRENT_TIMESTAMP WHERE blid = %q", blid); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not update openvpn_blacklist.last_accessed for blid=%s", blid); sqlite_log_error(ctx, res); } sqlite_free_results(res); } } /* * Public driver functions */ /** * @copydoc eDBconnect() * Connect to the database ... connection is stored in the eurephiaCTX context */ int eDBconnect(eurephiaCTX *ctx, const int argc, const char **argv) { eDBconn *dbc = NULL; dbresult *res = NULL; int rc; DEBUG(ctx, 20, "Function call: eDBconnect(ctx, %i, '%s')", argc, argv[0]); if( (argc != 1) || (argv[0] == NULL) || (strlen(argv[0]) < 1) ) { eurephia_log(ctx, LOG_PANIC, 0, "Wrong parameters to edb-sqlite. Cannot open database."); return 0; } // Connect to the database dbc = (eDBconn *) malloc_nullsafe(ctx, sizeof(eDBconn)+2); dbc->dbname = strdup(argv[0]); eurephia_log(ctx, LOG_INFO, 1, "Opening database '%s'", dbc->dbname); rc = sqlite3_open(argv[0], (void *) &dbc->dbhandle); if( rc ) { eurephia_log(ctx, LOG_PANIC, 0, "Could not open database '%s'", dbc->dbname); free_nullsafe(ctx, dbc->dbname); free_nullsafe(ctx, dbc); return 0; } dbc->config = NULL; ctx->dbc = dbc; // Load configuration parameters into memory eurephia_log(ctx, LOG_INFO, 1, "Reading config from database (openvpn_config)"); res = sqlite_query(ctx, "SELECT datakey, dataval FROM openvpn_config"); if( sqlite_query_status(res) == dbSUCCESS ) { int i = 0; eurephiaVALUES *cfg = NULL; cfg = eCreate_value_space(ctx, 11); if( cfg == NULL ) { eurephia_log(ctx, LOG_FATAL, 0, "Could not allocate memory for config variables"); sqlite_free_results(res); return 0; } for( i = 0; i < sqlite_get_numtuples(res); i++ ) { eAdd_value(ctx, cfg, sqlite_get_value(res, i, 0), sqlite_get_value(res, i, 1)); } ctx->dbc->config = cfg; } else { sqlite_log_error(ctx, res); } sqlite_free_results(res); return 1; } /** * @copydoc eDBdisconnect() * Disconnect from the database */ void eDBdisconnect(eurephiaCTX *ctx) { eDBconn *dbc = NULL; DEBUG(ctx, 20, "Function call: eDBdisconnect(ctx)"); if( ctx->dbc == NULL ) { eurephia_log(ctx, LOG_WARNING, 0, "Database not open, cannot close database."); return; } dbc = ctx->dbc; eurephia_log(ctx, LOG_INFO, 1, "Closing database '%s'", dbc->dbname); // Close database connection sqlite3_close((sqlite3 *) dbc->dbhandle); free_nullsafe(ctx, dbc->dbname); dbc->dbhandle = NULL; // Free up config memory eFree_values(ctx, dbc->config); free_nullsafe(ctx, dbc); ctx->dbc = NULL; } /** * @copydoc eDBauth_TLS() */ int eDBauth_TLS(eurephiaCTX *ctx, const char *org, const char *cname, const char *email, const char *digest, const unsigned int depth) { dbresult *res = NULL; int certid = 0; char *blid = NULL; DEBUG(ctx, 20, "Function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %i)", org, cname, email, digest, depth); // Check if certificate is valid, and not too many attempts has been tried with the given certificate res = sqlite_query(ctx, "SELECT cert.certid, blid " " FROM openvpn_certificates cert" " LEFT JOIN openvpn_blacklist bl USING(digest)" " WHERE organisation='%q' AND common_name='%q' " " AND email='%q' AND depth='%i' AND lower(cert.digest)=lower('%q')%c", org, cname, email, depth, digest, 0); if( sqlite_query_status(res) == dbSUCCESS ) { certid = atoi_nullsafe(sqlite_get_value(res, 0, 0)); blid = strdup_nullsafe(sqlite_get_value(res, 0, 1)); // Check if the certificate is blacklisted or not. blid != NULL when blacklisted if( blid != NULL ) { // If the certificate or IP is blacklisted, update status and deny access. eurephia_log(ctx, LOG_WARNING, 0, "Attempt with BLACKLISTED certificate (certid %i)", certid); update_attempts(ctx, blid); certid = -1; } free_nullsafe(ctx, blid); } else { eurephia_log(ctx, LOG_FATAL, 0, "Could not look up certificate information"); sqlite_log_error(ctx, res); } sqlite_free_results(res); DEBUG(ctx, 20, "Result function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %i) - %i", org, cname, email, digest, depth, certid); return certid; } /** * @copydoc eDBauth_user() */ int eDBauth_user(eurephiaCTX *ctx, const int certid, const char *username, const char *passwd) { dbresult *res = NULL; char *crpwd = NULL, *activated = NULL, *deactivated = NULL, *blid_uname = NULL, *blid_cert = NULL; char *dbpwd = NULL; int uicid = 0, uid = 0, pwdok = 0; DEBUG(ctx, 20, "Function call: eDBauth_user(ctx, %i, '%s','xxxxxxxx')", certid, username); // Generate SHA512 hash of password, used for password auth res = sqlite_query(ctx, "SELECT uicid, ou.uid, activated, deactivated, bl1.blid, bl2.blid, password " " FROM openvpn_users ou" " JOIN openvpn_usercerts uc USING(uid) " " LEFT JOIN openvpn_blacklist bl1 ON( ou.username = bl1.username) " " LEFT JOIN (SELECT blid, certid " " FROM openvpn_certificates " " JOIN openvpn_blacklist USING(digest)) bl2 ON(uc.certid = bl2.certid)" " WHERE uc.certid = '%i' AND ou.username = '%q'", certid, username); memset(crpwd, 0, strlen_nullsafe(crpwd)); free_nullsafe(ctx, crpwd); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_FATAL, 0, "Could not lookup user in database (certid %i, username '%s'", certid, username); sqlite_log_error(ctx, res); sqlite_free_results(res); return 0; } if( sqlite_get_numtuples(res) == 1 ) { uid = atoi_nullsafe(sqlite_get_value(res, 0, 1)); activated = sqlite_get_value(res, 0, 2); deactivated = sqlite_get_value(res, 0, 3); blid_uname = sqlite_get_value(res, 0, 4); blid_cert = sqlite_get_value(res, 0, 5); dbpwd = sqlite_get_value(res, 0, 6); if( dbpwd == NULL ) { eurephia_log(ctx, LOG_WARNING, 0,"Authentication failed for user '%s'. DB error.", username); pwdok = 0; } else { crpwd = eurephia_pwd_crypt(ctx, passwd, dbpwd); pwdok = ((crpwd != NULL) && (strcmp(crpwd, dbpwd) == 0) ? 1 : 0); memset(crpwd, 0, strlen_nullsafe(crpwd)); memset(dbpwd, 0, strlen_nullsafe(dbpwd)); free_nullsafe(ctx, crpwd); } if( blid_uname != NULL ) { eurephia_log(ctx, LOG_WARNING, 0, "User account is BLACKLISTED (uid: %i, %s)", uid, username); uicid = -1; } else if( blid_cert != NULL ) { eurephia_log(ctx, LOG_WARNING, 0, "User account linked with a BLACKLISTED certificate " "(uid: %i, %s) - certid: %s", uid, username, certid); uicid = -1; } else if( activated == NULL ) { eurephia_log(ctx, LOG_WARNING, 0, "User account is not activated (uid: %i, %s)", uid, username); uicid = -1; } else if( deactivated != NULL ) { eurephia_log(ctx, LOG_WARNING, 0, "User account is deactivated (uid: %i, %s)", uid, username); uicid = -1; } else if( pwdok != 1 ) { eurephia_log(ctx, LOG_WARNING, 0,"Authentication failed for user '%s'. Wrong password.", username); sleep(2); uicid = -1; } else { dbresult *upd = NULL; uicid = atoi_nullsafe(sqlite_get_value(res, 0, 0)); // Update last accessed status upd = sqlite_query(ctx, "UPDATE openvpn_users SET last_accessed = CURRENT_TIMESTAMP" " WHERE uid = %i", uid); if( sqlite_query_status(upd) != dbSUCCESS) { eurephia_log(ctx, LOG_ERROR, 0, "Could not update last access status for uid %i", uid); sqlite_log_error(ctx, upd); } sqlite_free_results(upd); } } else { eurephia_log(ctx, LOG_WARNING, 0, "Authentication failed for user '%s'. " "Could not find user or user-certificate link.", username); sleep(2); uicid = 0; } sqlite_free_results(res); DEBUG(ctx, 20, "Result function call: eDBauth_user(ctx, %i, '%s','xxxxxxxx') - %i", certid, username, uicid); return uicid; } /** * @copydoc eDBget_uid() */ int eDBget_uid(eurephiaCTX *ctx, const int certid, const char *username) { dbresult *res = NULL; int ret = 0; DEBUG(ctx, 20, "Function call: eDBget_uid(ctx, %i, '%s')", certid, username); res = sqlite_query(ctx, "SELECT uid " " FROM openvpn_usercerts " " JOIN openvpn_users USING (uid) " " WHERE certid = '%i' AND username = '%q'", certid, username); if( (sqlite_query_status(res) != dbSUCCESS) || (sqlite_get_numtuples(res) != 1) ) { eurephia_log(ctx, LOG_FATAL, 0, "Could not lookup userid for user '%s'", username); if( sqlite_query_status(res) == dbERROR ) { sqlite_log_error(ctx, res); } ret = -1; } else { ret = atoi_nullsafe(sqlite_get_value(res, 0, 0)); } sqlite_free_results(res); return ret; } /** * @copydoc eDBblacklist_check() */ int eDBblacklist_check(eurephiaCTX *ctx, const int type, const char *val) { dbresult *blr = NULL, *atpr = NULL; int atpexceed = -1, blacklisted = 0; char *atpid = NULL, *blid = NULL; DEBUG(ctx, 20, "Function call: eDBblacklist_check(ctx, '%s', '%s')", eDBattempt_types[type].descr, val); blr = sqlite_query(ctx, "SELECT blid FROM openvpn_blacklist WHERE %s = %s%s'%q'%s", eDBattempt_types[type].colname_where, defaultValue(eDBattempt_types[type].value_func, ""), (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? "(" : ""), val, (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? ")" : "")); if( sqlite_query_status(blr) == dbSUCCESS ) { blid = strdup_nullsafe(sqlite_get_value(blr, 0, 0)); if( blid != NULL ) { eurephia_log(ctx, LOG_WARNING, 0, "Attempt from blacklisted %s: %s", eDBattempt_types[type].descr, val); blacklisted = 1; // [type] is blacklisted } // Update attempt information update_attempts(ctx, blid); } else { eurephia_log(ctx, LOG_FATAL, 0, "Querying openvpn_blacklist for blacklisted %s failed", eDBattempt_types[type].descr); sqlite_log_error(ctx, blr); } sqlite_free_results(blr); if( blacklisted == 0 ) { // Check if this [type] has been attempted earlier - if it has reaced the maximum // attempt limit, blacklist it atpr = sqlite_query(ctx, "SELECT atpid, attempts >= %q FROM openvpn_attempts WHERE %s = '%q'", defaultValue(eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg), eDBattempt_types[type].default_value), eDBattempt_types[type].colname_where, val); if( sqlite_query_status(atpr) == dbSUCCESS ) { atpid = strdup_nullsafe(sqlite_get_value(atpr, 0, 0)); atpexceed = atoi_nullsafe(sqlite_get_value(atpr, 0, 1)); // If [type] has reached attempt limit and it is not black listed, black list it if( (atpexceed > 0) && (blid == NULL) ) { eurephia_log(ctx, LOG_WARNING, 0, "%s got BLACKLISTED due to too many failed attempts: %s", eDBattempt_types[type].descr, val); blr = sqlite_query(ctx, "INSERT INTO openvpn_blacklist (%s) VALUES ('%q')", eDBattempt_types[type].colname, val); if( sqlite_query_status(blr) != dbSUCCESS ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not blacklist %s (%s)", eDBattempt_types[type].descr, val); sqlite_log_error(ctx, blr); } blacklisted = 1; // [type] is blacklisted sqlite_free_results(blr); } free_nullsafe(ctx, atpid); } else { eurephia_log(ctx, LOG_CRITICAL, 0, "Querying openvpn_attempts for blacklisted %s failed", eDBattempt_types[type].descr); sqlite_log_error(ctx, blr); } sqlite_free_results(atpr); } free_nullsafe(ctx, blid); DEBUG(ctx, 20, "Result - function call: eDBblacklist_check(ctx, '%s', '%s') - %i", eDBattempt_types[type].descr, val, blacklisted); return blacklisted; } /** * @copydoc eDBregister_attempt() */ void eDBregister_attempt(eurephiaCTX *ctx, int type, int mode, const char *value) { dbresult *res; char *id = NULL, *atmpt_block = NULL, *blid = NULL; int attempts = 0; DEBUG(ctx, 20, "Function call: eDBregister_attempt(ctx, %s, %s, '%s')", eDBattempt_types[type].colname, (mode == ATTEMPT_RESET ? "ATTEMPT_RESET" : "ATTEMPT_REGISTER"), value); // // openvpn_attempts // res = sqlite_query(ctx, "SELECT atpid, attempts > %s, blid, attempts " " FROM openvpn_attempts " " LEFT JOIN openvpn_blacklist USING(%s)" " WHERE %s = %s%s'%q'%s", defaultValue(eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg), eDBattempt_types[type].default_value), eDBattempt_types[type].colname, eDBattempt_types[type].colname_where, defaultValue(eDBattempt_types[type].value_func, ""), (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? "(" : ""), value, (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? ")" : "") ); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_FATAL, 0, "Could not look up atpid in openvpn_attempts"); sqlite_log_error(ctx, res); sqlite_free_results(res); return; } attempts = atoi_nullsafe(sqlite_get_value(res, 0, 3)); // If we are asked to reset the attempt counter and we do not find any attempts, exit here if( (mode == ATTEMPT_RESET) && ((sqlite_get_numtuples(res) == 0) || (attempts == 0))) { sqlite_free_results(res); return; } id = strdup_nullsafe(sqlite_get_value(res, 0, 0)); atmpt_block = strdup_nullsafe(sqlite_get_value(res, 0, 1)); blid = strdup_nullsafe(sqlite_get_value(res, 0, 2)); sqlite_free_results(res); if( (id == NULL) && (mode == ATTEMPT_REGISTER) ) { // Only insert record when we are in registering mode res = sqlite_query(ctx, "INSERT INTO openvpn_attempts (%s, attempts) VALUES ('%q', 1)", eDBattempt_types[type].colname, value); } else if( id != NULL ){ // if a attempt record exists, update it according to mode switch( mode ) { case ATTEMPT_RESET: res = sqlite_query(ctx, "UPDATE openvpn_attempts " " SET attempts = 0 " " WHERE atpid = '%q'", id); break; default: res = sqlite_query(ctx, "UPDATE openvpn_attempts " " SET last_attempt = CURRENT_TIMESTAMP, attempts = attempts + 1" " WHERE atpid = '%q'", id); break; } } if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not update openvpn_attempts for %s = %s", eDBattempt_types[type].colname, value); sqlite_log_error(ctx, res); } sqlite_free_results(res); // If attempts have exceeded attempt limit, blacklist it immediately if not already registered if( (mode == ATTEMPT_REGISTER) && (blid == NULL) && (atmpt_block != NULL) && (atoi_nullsafe(atmpt_block) > 0) ) { eurephia_log(ctx, LOG_WARNING, 0, "Blacklisting %s due to too many attempts: %s", eDBattempt_types[type].descr, value); res = sqlite_query(ctx, "INSERT INTO openvpn_blacklist (%s) VALUES ('%q')", eDBattempt_types[type].colname, value); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not blacklist %s: %s", eDBattempt_types[type].descr, value); sqlite_log_error(ctx, res); } sqlite_free_results(res); } free_nullsafe(ctx, id); free_nullsafe(ctx, atmpt_block); free_nullsafe(ctx, blid); } /** * @copydoc eDBregister_login() */ int eDBregister_login(eurephiaCTX *ctx, eurephiaSESSION *skey, const int certid, const int uid, const char *proto, const char *remipaddr, const char *remport, const char *vpnipaddr, const char *vpnipmask) { dbresult *res = NULL; int ret = 0; DEBUG(ctx, 20, "Function call: eDBregister_login(ctx, '%s', %i, %i, '%s','%s','%s','%s','%s')", skey->sessionkey, certid, uid, proto, remipaddr, remport, vpnipaddr, vpnipmask); if( skey->sessionstatus != SESSION_NEW ) { eurephia_log(ctx, LOG_ERROR, 5, "Not a new session, will not register it again"); return 1; } res = sqlite_query(ctx, "INSERT INTO openvpn_lastlog (uid, certid, " " protocol, remotehost, remoteport," " vpnipaddr, vpnipmask," " sessionstatus, sessionkey, login) " "VALUES (%i, %i, '%q','%q','%q','%q','%q', 1,'%q', CURRENT_TIMESTAMP)", uid, certid, proto, remipaddr, remport, vpnipaddr, vpnipmask, skey->sessionkey); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_FATAL, 0, "Could not insert new session into openvpn_lastlog"); sqlite_log_error(ctx, res); ret = 0; } else { skey->sessionstatus = SESSION_REGISTERED; ret = 1; } sqlite_free_results(res); return ret; } /** * @copydoc eDBregister_vpnmacaddr() */ int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const char *macaddr) { dbresult *res = NULL; int ret = 0; DEBUG(ctx, 20, "Function call: eDBregister_vpnmacaddr(ctx, '%s', '%s')", session->sessionkey, macaddr); if( (macaddr == NULL) && (strlen_nullsafe(macaddr) > 18) ) { eurephia_log(ctx, LOG_FATAL, 0, "Invalid MAC address"); return 0; } // Register MAC address into history table res = sqlite_query(ctx, "INSERT INTO openvpn_macaddr_history (sessionkey, macaddr) VALUES ('%q','%q')", session->sessionkey, macaddr); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_FATAL, 0, "Failed to log new MAC address 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' " " WHERE sessionkey = '%q' AND sessionstatus = 1", macaddr, session->sessionkey); if( sqlite_query_status(res) == dbSUCCESS ) { // Save the MAC address in the session values register - needed for the destroy session if( 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 MAC address for session"); sqlite_log_error(ctx, res); ret = 0; } exit: sqlite_free_results(res); return ret; } /** * @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, const char *bytes_sent, const char *bytes_received, const char *duration) { dbresult *res = NULL; int ret = 0; DEBUG(ctx, 20, "Function call: eDBregister_logout(ctx, '%s', %s, %s)", skey->sessionkey, bytes_sent, bytes_received); res = sqlite_query(ctx, "UPDATE openvpn_lastlog " " SET sessionstatus = 3, logout = CURRENT_TIMESTAMP, " " bytes_sent = '%i', bytes_received = '%i', session_duration = '%i' " " WHERE sessionkey = '%q' AND sessionstatus = 2", atoi_nullsafe(bytes_sent), atoi_nullsafe(bytes_received), atoi_nullsafe(duration), skey->sessionkey); if( sqlite_query_status(res) == dbSUCCESS ) { skey->sessionstatus = SESSION_LOGGEDOUT; ret = 1; } else { eurephia_log(ctx, LOG_FATAL, 0, "Could not update lastlog with logout information (%s)", skey->sessionkey); ret = 0; } sqlite_free_results(res); return ret; } /** * @copydoc eDBget_sessionkey_seed() */ char *eDBget_sessionkey_seed(eurephiaCTX *ctx, sessionType type, const char *sessionseed) { dbresult *res = NULL; char *skey = NULL; DEBUG(ctx, 20, "eDBget_sessionkey_seed(ctx, %i, '%s')", type, sessionseed); if( sessionseed == NULL ) { eurephia_log(ctx, LOG_FATAL, 1, "eDBget_sessionkey: No session seed given - cannot locate sessionkey"); return NULL; } switch( type ) { case stSESSION: res = sqlite_query(ctx, "SELECT sessionkey " " FROM openvpn_sessionkeys " " JOIN openvpn_lastlog USING (sessionkey)" " WHERE sessionstatus IN (1,2)" " AND sessionseed = '%q'", sessionseed); break; case stAUTHENTICATION: res = sqlite_query(ctx, "SELECT sessionkey" " FROM openvpn_sessionkeys" " LEFT JOIN openvpn_lastlog USING(sessionkey)" " WHERE sessionstatus IS NULL" " AND sessionseed = '%q'", sessionseed); break; default: eurephia_log(ctx, LOG_ERROR, 0, "Invalid session type: %i", type); return NULL; } if( sqlite_query_status(res) == dbSUCCESS ) { if( sqlite_get_numtuples(res) == 1 ) { skey = strdup_nullsafe(sqlite_get_value(res, 0, 0)); } else { skey = NULL; } } else { eurephia_log(ctx, LOG_FATAL, 0,"Could not retrieve sessionkey from openvpn_sessionkeys (%s)", sessionseed); sqlite_log_error(ctx, res); skey = NULL; } sqlite_free_results(res); return skey; } /** * @copydoc eDBget_sessionkey_macaddr() */ char *eDBget_sessionkey_macaddr(eurephiaCTX *ctx, const char *macaddr) { dbresult *res = NULL; char *skey = NULL; DEBUG(ctx, 20, "eDBget_sessionkey_macaddr(ctx, '%s')", macaddr); // Find sessionkey from MAC address res = sqlite_query(ctx, "SELECT sessionkey " " FROM openvpn_sessions " " JOIN openvpn_lastlog USING (sessionkey)" " WHERE sessionstatus = 3 " " AND datakey = 'macaddr'" " AND dataval = '%q'", macaddr); if( sqlite_query_status(res) == dbSUCCESS ) { skey = strdup_nullsafe(sqlite_get_value(res, 0, 0)); } else { eurephia_log(ctx, LOG_FATAL, 0, "Could not remove session from database (MAC addr: %s)", macaddr); sqlite_log_error(ctx, res); skey = NULL; } sqlite_free_results(res); return skey; } /** * @copydoc eDBcheck_sessionkey_uniqueness() */ int eDBcheck_sessionkey_uniqueness(eurephiaCTX *ctx, const char *seskey) { dbresult *res; int uniq = 0; DEBUG(ctx, 20, "eDBcheck_sessionkey_uniqueness(ctx, '%s')", seskey); if( seskey == NULL ) { eurephia_log(ctx, LOG_FATAL, 1, "eDBcheck_sessionkey_uniqness: Invalid session key given"); return 0; } switch( ctx->context_type ) { case ECTX_NO_PRIVILEGES: return 0; break; case ECTX_ADMIN_CONSOLE: case ECTX_ADMIN_WEB: res = sqlite_query(ctx, "SELECT count(sessionkey) = 0 " "FROM eurephia_adminlog WHERE sessionkey = '%q'", seskey); break; case ECTX_PLUGIN_AUTH: default: res = sqlite_query(ctx, "SELECT count(sessionkey) = 0 " "FROM openvpn_lastlog WHERE sessionkey = '%q'", seskey); break; } if( sqlite_query_status(res) == dbSUCCESS ) { uniq = atoi_nullsafe(sqlite_get_value(res, 0, 0)); } else { eurephia_log(ctx, LOG_FATAL, 0, "eDBcheck_sessionkey_uniqness: Could not check uniqueness of sessionkey"); sqlite_log_error(ctx, res); uniq = 0; } sqlite_free_results(res); return uniq; } /** * @copydoc eDBregister_sessionkey() */ int eDBregister_sessionkey(eurephiaCTX *ctx, const char *seed, const char *seskey) { dbresult *res; int ret = 0; DEBUG(ctx, 20, "eDBregister_sessionkey(ctx, '%s', '%s')", seed, seskey); if( (seed == NULL) || (seskey == NULL) ) { eurephia_log(ctx, LOG_FATAL, 1, "eDBregister_sessionkey: Invalid session seed or session key given"); return 0; } res = sqlite_query(ctx, "INSERT INTO openvpn_sessionkeys (sessionseed, sessionkey) VALUES('%q','%q')", seed, seskey); if( sqlite_query_status(res) == dbSUCCESS ) { ret = 1; } else { eurephia_log(ctx, LOG_FATAL, 0, "eDBregister_sessionkey: Error registering sessionkey into openvpn_sessionkeys"); sqlite_log_error(ctx, res); ret = 0; } sqlite_free_results(res); return ret; } /** * Removes a session key reference to a short time session seed conversion table * * @param ctx eurephiaCTX * @param seskey String containing the session key reference to remove * * @return Returns 1 on success, otherwise 0. * */ int eDBremove_sessionkey(eurephiaCTX *ctx, const char *seskey) { dbresult *res; int ret = 0; DEBUG(ctx, 20, "eDBremove_sessionkey(ctx, '%s')", seskey); if( seskey == NULL ) { eurephia_log(ctx, LOG_FATAL, 1, "eDBremove_sessionkey: Invalid session key given"); return 0; } res = sqlite_query(ctx, "DELETE FROM openvpn_sessionkeys WHERE sessionkey = '%q'", seskey); if( sqlite_query_status(res) == dbSUCCESS ) { ret = 1; } else { eurephia_log(ctx, LOG_FATAL, 0, "eDBremove_sessionkey: Error removing sessionkey from openvpn_sessionkeys"); ret = 0; } sqlite_free_results(res); return ret; } /** * @copydoc eDBload_sessiondata() */ eurephiaVALUES *eDBload_sessiondata(eurephiaCTX *ctx, const char *sesskey) { dbresult *res = NULL; eurephiaVALUES *sessvals = NULL; int i; if( (ctx == NULL) || (sesskey == NULL) ) { return NULL; } DEBUG(ctx, 20, "Function call: eDBload_sessiondata(ctx, '%s')", sesskey); sessvals = eCreate_value_space(ctx, 10); res = sqlite_query(ctx, "SELECT datakey, dataval FROM openvpn_sessions WHERE sessionkey = '%q'", sesskey); if( sqlite_query_status(res) == dbSUCCESS ) { for( i = 0; i < sqlite_get_numtuples(res); i++ ) { eAdd_value(ctx, sessvals, sqlite_get_value(res, i, 0), sqlite_get_value(res, i, 1)); } } else { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not load session values for session '%s'", sesskey); sqlite_log_error(ctx,res); } sqlite_free_results(res); return sessvals; } /** * @copydoc eDBstore_session_value() */ int eDBstore_session_value(eurephiaCTX *ctx, eurephiaSESSION *session, int mode, const char *key, const char *val) { dbresult *res = NULL; int ret = 0; if( session == NULL ) { DEBUG(ctx, 20, "Function call failed to eDBstore_session_value(ctx, ...): Non-existing session key"); return 0; } DEBUG(ctx, 20, "Function call: eDBstore_session_value(ctx, '%s', %i, '%s', '%s')", session->sessionkey, mode, key, val); switch( mode ) { case SESSVAL_NEW: res = sqlite_query(ctx, "INSERT INTO openvpn_sessions (sessionkey, datakey, dataval) " "VALUES ('%q','%q','%q')", session->sessionkey, key, val); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_FATAL, 0, "Could not register new session variable into database: [%s] %s = %s", session->sessionkey, key, val); sqlite_log_error(ctx, res); goto exit; } break; case SESSVAL_UPDATE: res = sqlite_query(ctx, "UPDATE openvpn_sessions SET dataval = '%q' " " WHERE sessionkey = '%q' AND datakey = '%q'", val, session->sessionkey, key); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_FATAL, 0, "Could not update session variable: [%s] %s = %s ", session->sessionkey, key, val); sqlite_log_error(ctx, res); goto exit; } break; case SESSVAL_DELETE: res = sqlite_query(ctx, "DELETE FROM openvpn_sessions " " WHERE sessionkey = '%q' AND datakey = '%q'", session->sessionkey, key); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_FATAL, 0, "Could not delete session variable: [%s] %s", session->sessionkey, key); sqlite_log_error(ctx, res); goto exit; } break; default: eurephia_log(ctx, LOG_FATAL, 0, "Unknown eDBstore_session_value mode '%i'", mode); return 0; } exit: ret = (sqlite_query_status(res) == dbSUCCESS ? 1 : 0); sqlite_free_results(res); return ret; } /** * @copydoc eDBdestroy_session() */ int eDBdestroy_session(eurephiaCTX *ctx, eurephiaSESSION *session) { dbresult *res = NULL; DEBUG(ctx, 20, "Function call: eDBdestroy_session(ctx, '%s')", session->sessionkey); if( (session == NULL) || (session->sessionkey == NULL) ) { eurephia_log(ctx, LOG_WARNING, 1, "No active session given to be destroyed"); return 1; } // Update session status - if we have a "real" session (not auth-session) if( session->type == stSESSION ) { res = sqlite_query(ctx, "UPDATE openvpn_lastlog " " SET sessionstatus = 4, session_deleted = CURRENT_TIMESTAMP " " WHERE sessionkey = '%q' AND sessionstatus = 3", session->sessionkey); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_FATAL, 0, "Could not update session status in lastlog (%s))", session->sessionkey); sqlite_log_error(ctx, res); sqlite_free_results(res); return 0; } sqlite_free_results(res); } // Delete session variables res = sqlite_query(ctx, "DELETE FROM openvpn_sessions WHERE sessionkey = '%q'", session->sessionkey); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_FATAL, 0, "Could not delete session variables (%s))", session->sessionkey); sqlite_log_error(ctx, res); sqlite_free_results(res); return 0; } sqlite_free_results(res); // Remove the sessionkey from openvpn_sessions if( eDBremove_sessionkey(ctx, session->sessionkey) == 0 ) { return 0; } return 1; } /** * @copydoc eDBget_firewall_profile() */ char *eDBget_firewall_profile(eurephiaCTX *ctx, eurephiaSESSION *session) { char *ret = NULL; dbresult *res = NULL; DEBUG(ctx, 20, "Function call: eDBget_firewall_profile(ctx, {session}'%s')", session->sessionkey); res = sqlite_query(ctx, "SELECT fw_profile " " FROM openvpn_lastlog " " JOIN openvpn_usercerts USING(certid, uid)" " JOIN openvpn_accesses USING(accessprofile)" " WHERE sessionkey = '%q'", session->sessionkey); if( sqlite_query_status(res) == dbSUCCESS ) { ret = strdup_nullsafe(sqlite_get_value(res, 0, 0)); } else { eurephia_log(ctx, LOG_FATAL, 0, "Could not retrieve firewall profile for session '%s'", session->sessionkey); sqlite_log_error(ctx, res); ret = NULL; } sqlite_free_results(res); return ret; } /** * @copydoc eDBget_blacklisted_ip() */ eurephiaVALUES *eDBget_blacklisted_ip(eurephiaCTX *ctx) { eurephiaVALUES *ret = NULL; dbresult *res = NULL; int i = 0; char *ip = NULL; DEBUG(ctx, 20, "Function call: eDBget_blacklisted_ip(ctx)"); res = sqlite_query(ctx, "SELECT remoteip FROM openvpn_blacklist WHERE remoteip IS NOT NULL"); if( sqlite_query_status(res) == dbSUCCESS ) { ret = eCreate_value_space(ctx, 21); for( i = 0; i < sqlite_get_numtuples(res); i++ ) { if( (ip = sqlite_get_value(res, i, 0)) != NULL ) { eAdd_value(ctx, ret, NULL, ip); } } } else { eurephia_log(ctx, LOG_FATAL, 0, "Could not retrieve blacklisted IP addresses from the database"); sqlite_log_error(ctx, res); ret = NULL; } sqlite_free_results(res); return ret; }