path: root/database/sqlite/administration.c

/* administration.c  --  Functions needed for administration tasks
 *
 *  GPLv2 - Copyright (C) 2008  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.
 *
 */

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <assert.h>

#ifndef DRIVERAPIVERSION
# define DRIVERAPIVERSION 2
#endif

#include <sqlite3.h>

#include <eurephia_nullsafe.h>
#include <eurephia_context.h>
#include <eurephia_admin_struct.h>
#include <eurephia_admin_common.h>
#include <eurephia_log.h>
#include <eurephia_values.h>
#include <eurephiadb_session_struct.h>
#include <eurephiadb_mapping.h>
#include <passwd.h>

#ifndef DRIVER_MODE
#define DRIVER_MODE
#endif
#include <eurephiadb_driver.h>

#include "sqlite.h"
#include "fieldmapping.h"

#if DRIVERAPIVERSION > 1
/*
 *  API Version 2 functions
 *
 */

// Authenticate admin user against user database
int eDBadminAuth(eurephiaCTX *ctx, const char *req_access, const char *uname, const char *pwd) {
        dbresult *res = NULL;
        char *crpwd = NULL;
        char *activated = NULL, *deactivated = NULL, *blid = NULL;
        int uid = -1, pwok = 0, access = 0;
        char interface;

        assert(ctx != NULL);

        switch( ctx->context_type ) {
        case ECTX_ADMIN_CONSOLE:
                interface = 'C';
                break;
        case ECTX_ADMIN_WEB:
                interface = 'W';
                break;
        default:
                eurephia_log(ctx, LOG_ERROR, 0, "Wrong eurephia context type (0x%04x)", ctx->context_type);
                return 0;
        }

        if( (strlen_nullsafe(uname) < 4) || (strlen_nullsafe(pwd) < 4) ) {
                eurephia_log(ctx, LOG_WARNING, 0, "User name and/or password is either null or less than 4 bytes");
                return 0;
        }

        //
        // Authenticate user and password
        //
        crpwd = passwdhash(pwd);
        assert(crpwd != NULL);
        res = sqlite_query(ctx,
                           "SELECT activated, deactivated, bl.blid, "
                           "       (password = '%q') AS pwok, uid "
                           "  FROM openvpn_users ou"
                           "  LEFT JOIN openvpn_blacklist bl USING (username)"
                           " WHERE ou.username = '%q'",
                           crpwd, uname);
        memset(crpwd, 0, strlen_nullsafe(crpwd));
        free_nullsafe(crpwd);

        if( res == NULL ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not authenticate user against the database");
                return 0;
        }

        if( sqlite_get_numtuples(res) == 1 ) {
                activated   = sqlite_get_value(res, 0, 0);
                deactivated = sqlite_get_value(res, 0, 1);
                blid        = sqlite_get_value(res, 0, 2);
                pwok        = atoi_nullsafe(sqlite_get_value(res, 0, 3));
                uid         = atoi_nullsafe(sqlite_get_value(res, 0, 4));
                sqlite_free_results(res);

                if( blid != NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0,
                                     "Your user account is BLACKLISTED.  You have no access.");
                        sqlite_free_results(res);
                        return 0;
                }

                if( activated == NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "Your user account is not yet activated.");
                        sqlite_free_results(res);
                        return 0;
                }

                if( deactivated != NULL ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "Your user account is deactivated.");
                        sqlite_free_results(res);
                        return 0;
                }

                if( pwok != 1 ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "Authentication failed,");
                        sqlite_free_results(res);
                        return 0;
                }

                // Check if access level is granted
                // (SQLite do not handle advanced joins so well, so we need to
                //  do this check with an extra query)
                res = sqlite_query(ctx,
                                   "SELECT (count(*) = 1) AS access "
                                   "  FROM eurephia_adminaccess"
                                   " WHERE uid = '%i' AND interface = '%c' AND access = '%q'",
                                   uid, interface, req_access);
                if( res == NULL ) {
                        eurephia_log(ctx, LOG_FATAL, 0, "Could not check access level");
                        return 0;
                }
                access = atoi_nullsafe(sqlite_get_value(res, 0, 0));
                sqlite_free_results(res);

                if( access == 0 ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "Your account is lacking privileges for this operation");
                        return 0;
                }
        } else {
                eurephia_log(ctx, LOG_WARNING, 0, "Authentication failed. No unique records found.");
                sqlite_free_results(res);
                return 0;
        }

        // If we reach this place, authentication was successful.  Return users uid
        return uid;
}

int eDBadminValidateSession(eurephiaCTX *ctx, const char *sesskey, const char *req_access) {
        dbresult *res = NULL;
        int valid = 0, access = 0, expire_time = 0;
        char interface;

        assert( (ctx != NULL) && (sesskey != NULL) );

        switch( ctx->context_type ) {
        case ECTX_ADMIN_CONSOLE:
                interface = 'C';
                break;
        case ECTX_ADMIN_WEB:
                interface = 'W';
                break;
        default:
                eurephia_log(ctx, LOG_ERROR, 0, "Wrong eurephia context type (0x%04x)", ctx->context_type);
                return 0;
        }

        // Check if the session is still valid (not expired) and that this session are allowed to access
        // the requested access level.
        expire_time = (60 * atoi_nullsafe(defaultValue(eGet_value(ctx->dbc->config, "eurephiadmin_autologout"),
                                                       "10")
                                          )
                       );
        res = sqlite_query(ctx,
                           "SELECT (strftime('%%s',CURRENT_TIMESTAMP)-strftime('%%s',last_action)) > %i AS exp,"
                           "       (access IS NOT NULL) AS access"
                           "  FROM eurephia_adminlog"
                           "  LEFT JOIN eurephia_adminaccess USING(uid,interface)"
                           " WHERE status IN (1,2)"
                           "       AND sessionkey = '%q'"
                           "       AND access = '%q'",
                           expire_time, sesskey, req_access);

        if( (res == NULL) ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not validate session");
                return 0;
        }

        valid  = (atoi_nullsafe(sqlite_get_value(res, 0, 0)) == 0);
        access = (atoi_nullsafe(sqlite_get_value(res, 0, 1)) == 1);
        sqlite_free_results(res);

        // If still valid, update last_action
        if( valid && access ) {
                res = sqlite_query(ctx,
                                   "UPDATE eurephia_adminlog"
                                   "   SET last_action = CURRENT_TIMESTAMP, status = 2"
                                   " WHERE sessionkey = '%q'", sesskey);
                if( res == NULL ) {
                        eurephia_log(ctx, LOG_ERROR, 0, "Could not register session activity");
                }
                sqlite_free_results(res);

        } else {
                // If not valid, register session as auto-logged out

                res = sqlite_query(ctx,
                                   "UPDATE eurephia_adminlog"
                                   "   SET logout = CURRENT_TIMESTAMP, status = %i"
                                   " WHERE sessionkey = '%q'",
                                   (access ? 4 : 5), sesskey);
                if( res == NULL ) {
                        eurephia_log(ctx, LOG_ERROR, 0, "Could not register old session as logged out");
                }
                sqlite_free_results(res);

                // Delete session variables
                res = sqlite_query(ctx, "DELETE FROM openvpn_sessions WHERE sessionkey = '%q'",
                                   sesskey);
                if( res == NULL ) {
                        eurephia_log(ctx, LOG_ERROR, 0,
                                     "Could not delete session variables (%s))", sesskey);
                        return 0;
                }
                sqlite_free_results(res);

                if( !access ) {
                        eurephia_log(ctx, LOG_WARNING, 0, "Your user account is lacking privileges");
                }

        }
        return (valid && access);
}

int eDBadminRegisterLogin(eurephiaCTX *ctx, eurephiaSESSION *session) {
        dbresult *res = NULL;
        char interface;
        int uid;

        assert((ctx != NULL) && (session != NULL));

        switch( ctx->context_type ) {
        case ECTX_ADMIN_CONSOLE:
                interface = 'C'; break;
        case ECTX_ADMIN_WEB:
                interface = 'W'; break;
        default:
                eurephia_log(ctx, LOG_ERROR, 0, "Wrong eurephia context type (0x%04x)", ctx->context_type);
                return 0;
        }

        // Register login into eurephia_adminlog ... uid, login, interface, sessionkey
        uid = atoi_nullsafe(eGet_value(session->sessvals, "uid"));
        res = sqlite_query(ctx,
                           "INSERT INTO eurephia_adminlog "
                           "       (uid, interface, status, login, last_action, sessionkey) "
                           "VALUES ('%i','%c',1,CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '%q')",
                           uid, interface, session->sessionkey);
        if( !res ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not manage to register the session in the database");
                return 0;
        }
        sqlite_free_results(res);
        return 1;
}

int eDBadminLogout(eurephiaCTX *ctx, const char *sessionkey) {
        dbresult *res = NULL;

        assert((ctx != NULL) && (sessionkey != NULL));

        // Update session as logged out
        res = sqlite_query(ctx,
                           "UPDATE eurephia_adminlog "
                           "   SET logout = CURRENT_TIMESTAMP, status = 3"
                           " WHERE sessionkey = '%q'",
                           sessionkey);
        if( !res ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not manage to register the session as logged out");
                return 0;
        }
        sqlite_free_results(res);

        // Delete session variables
        res = sqlite_query(ctx, "DELETE FROM openvpn_sessions WHERE sessionkey = '%q'", sessionkey);
        if( res == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0,
                             "Could not delete session variables (%s))", sessionkey);
                return 0;
        }
        sqlite_free_results(res);

        return 1;
}

int eDBadminConfigSet(eurephiaCTX *ctx, const char *key, const char *val) {
        dbresult *res = NULL;
        int found = 0;

        assert((ctx != NULL) && (ctx->dbc != NULL));

        res = sqlite_query(ctx, "SELECT count(*) FROM openvpn_config WHERE datakey = '%q'", key);
        if( !res ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not query configuration table");
                return 0;
        }
        found = atoi_nullsafe(sqlite_get_value(res, 0, 0));
        sqlite_free_results(res);

        if( found == 0 ) {
                res = sqlite_query(ctx,
                                   "INSERT INTO openvpn_config (datakey, dataval) VALUES ('%q','%q')",
                                   key, val);
        } else {
                res = sqlite_query(ctx, "UPDATE openvpn_config SET dataval = '%q' WHERE datakey = '%q'",
                                   val, key);
        }

        if( res == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not register configuration entry (%s = '%s'", key, val);
                return 0;
        }
        sqlite_free_results(res);
        eAdd_value(ctx, ctx->dbc->config, key, val);
        return 1;
}

int eDBadminConfigDelete(eurephiaCTX *ctx, const char *key) {
        dbresult *res = NULL;

        assert((ctx != NULL) && (ctx->dbc != NULL));

        res = sqlite_query(ctx, "DELETE FROM openvpn_config WHERE datakey = '%q'", key);
        if( !res ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could delete config configuration entry (%s)", key);
                return 0;
        }
        sqlite_free_results(res);
        return 1;
}

eurephiaUSERLIST *eDBadminGetUserList(eurephiaCTX *ctx, const char *sortkeys) {
        eurephiaUSERINFO *rec = NULL, *last = NULL;
        eurephiaUSERLIST *ret = NULL;
        dbresult *res = NULL;
        char *dbsort = NULL;
        int i = 0;

        assert((ctx != NULL) && (ctx->dbc != 0));

        // Convert the input sort keys to the proper database field names
        dbsort = eDBmkSortKeyString(tbl_sqlite_users, sortkeys);

        // Query database for all users
        res = sqlite_query(ctx,
                           "SELECT username, activated, deactivated, last_accessed, uid"
                           "  FROM openvpn_users "
                           "ORDER BY %s", (sortkeys != NULL ? dbsort : "uid"));
        if( res == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Error querying the user database");
                return NULL;
        }

        // Prepare a list with all users
        ret = (eurephiaUSERLIST *) malloc(sizeof(eurephiaUSERLIST)+2);
        assert(ret != NULL);
        memset(ret, 0, sizeof(eurephiaUSERLIST)+2);

        ret->num_users = sqlite_get_numtuples(res);

        // Register all records
        for( i = 0; i < ret->num_users; i++ ) {
                rec = eAdminPopulateUSERINFO(
                                             atoi_nullsafe(sqlite_get_value(res, i, 4)), // uid
                                             sqlite_get_value(res, i, 0),                // username
                                             NULL,                                       // passwd
                                             sqlite_get_value(res, i, 1),                // activated
                                             sqlite_get_value(res, i, 2),                // deactivated
                                             sqlite_get_value(res, i, 3)                 // last accessed
                                             );
                if( ret->users == NULL ) {
                        ret->users = rec;
                        last = ret->users;
                } else {
                        last->next = rec;
                        last = rec;
                }
        }
        sqlite_free_results(res);

        // Return a user list
        return ret;
}

// This function will read out all info from eurephiaINFO *user which is not NULL and create a query based
// on these values.  If a user is found, the *user struct will be updated with the user found.  On success
// the function returns 1.
int eDBadminGetUserInfo(eurephiaCTX *ctx, int getInfo, eurephiaUSERINFO *user) {
        dbresult *uinf = NULL, *certinf = NULL;
        eDBfieldMap *uinfo_map = NULL;

        uinfo_map = eDBmkMapping_USERINFO(ctx, tbl_sqlite_users, user);

        // Query the database, find the user defined in the user map
        uinf = sqlite_query_mapped(ctx, SQL_SELECT,
                                   "SELECT username, activated, deactivated, last_accessed, uid"
                                   "  FROM openvpn_users", NULL, uinfo_map);

        if( uinf == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Error querying the database for a user");
                return 0;
        }
        eDBfreeMapping(uinfo_map);

        switch( sqlite_get_numtuples(uinf) ) {
        case 0:
                sqlite_free_results(uinf);
                return 0; // No user found

        case 1:
                free_nullsafe(user->username);
                user->username      = strdup_nullsafe(sqlite_get_value(uinf, 0, 0));
                user->uid           = atoi_nullsafe(sqlite_get_value(uinf, 0, 4));

                if( (getInfo & USERINFO_user) == USERINFO_user ) {
                        free_nullsafe(user->password);
                        free_nullsafe(user->activated);
                        free_nullsafe(user->deactivated);
                        free_nullsafe(user->last_accessed);

                        user->activated     = strdup_nullsafe(sqlite_get_value(uinf, 0, 1));
                        user->deactivated   = strdup_nullsafe(sqlite_get_value(uinf, 0, 2));
                        user->last_accessed = strdup_nullsafe(sqlite_get_value(uinf, 0, 3));
                        sqlite_free_results(uinf);
                }

                if( (getInfo & USERINFO_certs) == USERINFO_certs ) {
                        // Extract certificate info
                        certinf = sqlite_query(ctx,
                                               "SELECT depth, digest, common_name, organisation, email, "
                                               "       c.registered, c.certid,  access_descr"
                                               "  FROM openvpn_certificates c"
                                               "  LEFT JOIN openvpn_usercerts uc ON (c.certid = uc.certid)"
                                               "  LEFT JOIN openvpn_accesses a "
                                               "         ON (uc.accessprofile = a.accessprofile)"
                                               " WHERE uid = %i ORDER BY c.certid DESC", user->uid);
                        if( (certinf != NULL) && (sqlite_get_numtuples(certinf) > 0) ) {
                                eurephiaCERTINFO *rec = NULL;
                                int i;

                                user->certlist = eAdminCreateCERTLIST();

                                for( i = 0; i < sqlite_get_numtuples(certinf); i++ ) {
                                        rec = eAdminPopulateCERTINFO(
                                                                     atoi_nullsafe(sqlite_get_value(certinf, i, 6)),
                                                                     atoi_nullsafe(sqlite_get_value(certinf, i, 0)),
                                                                     sqlite_get_value(certinf, i, 1),
                                                                     sqlite_get_value(certinf, i, 2),
                                                                     sqlite_get_value(certinf, i, 3),
                                                                     sqlite_get_value(certinf, i, 4),
                                                                     sqlite_get_value(certinf, i, 5));
                                        eAdminInsertCERTINFO(user->certlist, rec);
                                }
                                sqlite_free_results(certinf);
                        } else {
                                user->certlist = NULL;
                        }
                } else {
                        user->certlist = NULL;
                }
                return 1;

        default:
                sqlite_free_results(uinf);
                eurephia_log(ctx, LOG_ERROR, 0, "Too many user records was found.");
                return 0;
        }
}

int eDBadminAddUser(eurephiaCTX *ctx, eurephiaUSERINFO *usrinf) {
        return 0;
}

int eDBadminUpdateUser(eurephiaCTX *ctx, const int uid, eurephiaUSERINFO *usrinf) {
        dbresult *uinf = NULL;
        eurephiaUSERINFO *srch = NULL;
        eDBfieldMap *data_map = NULL, *srch_map = NULL;

        assert( (ctx != NULL) && (uid == usrinf->uid) );

        srch = eAdminPopulateUSERINFO(uid, NULL, NULL, NULL, NULL, NULL);
        srch_map = eDBmkMapping_USERINFO(ctx, tbl_sqlite_users, srch);

        data_map = eDBmkMapping_USERINFO(ctx, tbl_sqlite_users, usrinf);

        // Query the database, find the user defined in the user map
        uinf = sqlite_query_mapped(ctx, SQL_UPDATE, "UPDATE openvpn_users", data_map, srch_map);

        if( uinf == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Error querying the database for a user");
                return 0;
        }
        sqlite_free_results(uinf);

        eDBfreeMapping(srch_map);
        eAdminFreeUSERINFO(srch);

        eDBfreeMapping(data_map);
        return 1;
}

int eDBadminDeleteUser(eurephiaCTX *ctx, const int uid, eurephiaUSERINFO *usrinf) {
        return 0;
}


eurephiaCERTLIST *eDBadminGetCertificateList(eurephiaCTX *ctx, const char *sortkeys) {
        return NULL;
}

eurephiaCERTINFO *eDBadminGetCertificateInfo(eurephiaCTX *ctx, eurephiaCERTINFO *srchkey) {
        return NULL;
}

int eDBadminAddCertificate(eurephiaCTX *ctx, eurephiaCERTINFO *crtinf) {
        return 0;
}

int eDBadminDeleteCertificate(eurephiaCTX *ctx, const int uid, eurephiaCERTINFO *crtinf) {
        return 0;
}

eurephiaLOGLIST *eDBadminGetLastlog(eurephiaCTX *ctx, eurephiaUSERINFO *usersrch, eurephiaCERTINFO *certsrch,
                                    const char *sortkeys)
{
        return NULL;
}

eurephiaLOGLIST *eDBadminGetAttemptsLog(eurephiaCTX *ctx, eurephiaUSERINFO *usersrch, eurephiaCERTINFO *certsrch,
                                        const char *sortkeys)
{
        return NULL;
}

#endif