diff options
Diffstat (limited to 'database/postgresql/edb-pgsql.c')
| -rw-r--r-- | database/postgresql/edb-pgsql.c | 1124 |
1 files changed, 1124 insertions, 0 deletions
diff --git a/database/postgresql/edb-pgsql.c b/database/postgresql/edb-pgsql.c new file mode 100644 index 0000000..1ea7367 --- /dev/null +++ b/database/postgresql/edb-pgsql.c @@ -0,0 +1,1124 @@ +/* edb-pgsql.c -- PostgreSQL database driver for eurephia + * + * GPLv2 only - Copyright (C) 2011 + * 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. + * + */ + +/** + * @file edb-pgsql.c + * @author David Sommerseth <dazo@users.sourceforge.net> + * @date 2011-01-15 + * + * @brief eurephia database driver for the PostgreSQL database. + * This file is the main API for the driver. + * + */ + +#include <string.h> +#include <unistd.h> +#include <assert.h> +#include <libpq-fe.h> + +#define DRIVERVERSION "1.0" +#ifndef DRIVERAPIVERSION +# define DRIVERAPIVERSION 1 +#endif + +#include <eurephiadb_driver.h> +#include <eurephia_nullsafe.h> +#include <eurephia_log.h> +#include <eurephia_values.h> +#include <eurephia_cfgfile.h> +#include <eurephiadb_session_common.h> +#include <eurephiadb_session_struct.h> +#include <passwd.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} +}; + + +static inline char *PGgetValue(PGresult *res, int row, int col) { + if( (res == NULL) || (PQgetisnull(res, row, col) == 1) ) { + return NULL; + } else { + return PQgetvalue(res, row, col); + } +} + +#define PREPSQL_TLS_AUTH "eurephia_tls_auth" +#define PREPSQL_USERPWD_AUTH "eurephia_userpwd_auth" +#define PREPSQL_BLACKLIST_ATTEMPTUPD "eurephia_blacklist_attupd" +#define PREPSQL_USERS_LASTACC_UPD "eurephia_users_lastacc_upd" +#define PREPSQL_USERS_GETUID "eurephia_getuid" +#define PREPSQL_BLACKLIST_CHECK "eurephia_blacklist_check" +#define PREPSQL_ATTEMPTS_CHECK "eurephia_attempts_check" +#define PREPSQL_BLACKLIST_REGISTER "eurephia_blacklist_reg" + +/** + * @copydoc eDB_DriverVersion() + */ +const char *eDB_DriverVersion(void) { + return "edb-pgsql (v"DRIVERVERSION") David Sommerseth 2011 (C) GPLv2"; +} + + +/** + * @copydoc eDB_DriverAPIVersion() + */ +int eDB_DriverAPIVersion() { + return DRIVERAPIVERSION; +} + + + +/** + * Internal driver function for simplifying update of the blacklist table. 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 + */ +static inline void update_blacklist_attempt(eurephiaCTX *ctx, const char *blid) { + if( blid != NULL ) { + PGresult *dbr = NULL; + + const char ** qry_args = NULL; + + dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_ATTEMPTUPD, + "UPDATE blacklist " + " SET last_accessed = CURRENT_TIMESTAMP WHERE blid = $1", 1, NULL); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + eurephia_log(ctx, LOG_CRITICAL, 0, + "Could not prepare blacklist last attempt update query: %s", + (dbr == NULL ? PQresultErrorMessage(dbr) + : PQerrorMessage(ctx->dbc->dbhandle))); + goto exit; + } + PQclear(dbr); + + qry_args = calloc(1, sizeof(char *)); + qry_args[0] = blid; + dbr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_ATTEMPTUPD, 1, + qry_args, NULL, NULL, 0); + free_nullsafe(ctx, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + eurephia_log(ctx, LOG_CRITICAL, 0, + "Failed to update blaclist.last_access timestamp for blid %s (%s)", + blid, + (dbr ? PQresultErrorMessage(dbr) + : PQerrorMessage(ctx->dbc->dbhandle))); + } + exit: + if( dbr ) { + PQclear(dbr); + } + } + return; +} + + + +/** + * @copydoc eDBconnect() + * Connect to the database ... connection is stored in the eurephiaCTX context + */ +int eDBconnect(eurephiaCTX *ctx, const int argc, const char **argv) +{ + char *dbname = NULL, *dbhost = NULL, *dbport = NULL, *dbuser = NULL, *dbpwd = NULL; + eDBconn *dbc = NULL; + PGresult *res = NULL; + + DEBUG(ctx, 20, "Function call: eDBconnect(ctx, %i, '%s')", argc, argv[0]); + if( (ctx == NULL) || (argc != 1) || (argv[0] == NULL) || (strlen(argv[0]) < 1) ) { + eurephia_log(ctx, LOG_PANIC, 0, + "edb-pgsql: Missing configuration file argument. " + "No database configured."); + return 0; + } + + // Parse the config file with PostgreSQL connection information + dbc = (eDBconn *) malloc_nullsafe(ctx, sizeof(eDBconn)+2); + assert( dbc != NULL ); + dbc->dbparams = ecfg_ReadConfig(ctx, argv[0]); + if( dbc->dbparams == NULL ) { + eurephia_log(ctx, LOG_FATAL, 0, + "edb-pgsql: Failed to parse the edb-pgsql configuration file (%s)", + argv[0]); + free_nullsafe(ctx, dbc); + return 0; + } + dbname = eGet_value(dbc->dbparams, "database"); + dbhost = eGet_value(dbc->dbparams, "host"); + dbport = eGet_value(dbc->dbparams, "port"); + dbuser = eGet_value(dbc->dbparams, "user"); + dbpwd = eGet_value(dbc->dbparams, "password"); + if( dbname == NULL ) { + eurephia_log(ctx, LOG_FATAL, 0, "edb-pgsql: Missing required database name"); + return 0; + } + + dbc->dbhandle = PQsetdbLogin(dbhost, dbport, NULL, NULL, dbname, dbuser, dbpwd); + if( dbc->dbhandle == NULL ) { + eurephia_log(ctx, LOG_FATAL, 0, + "Failed to connect to the PostgreSQL database: Unknown error"); + free_nullsafe(ctx, dbc); + return 0; + } + + if( PQstatus(dbc->dbhandle) != CONNECTION_OK ) { + eurephia_log(ctx, LOG_FATAL, 0, "Failed to connect to the PostgreSQL database: %s", + PQerrorMessage(dbc->dbhandle)); + free_nullsafe(ctx, dbc); + return 0; + } + dbc->dbname = strdup(dbname); + + eurephia_log(ctx, LOG_INFO, 1, "Reading config from database"); + dbc->config = NULL; + res = PQexec(dbc->dbhandle, "SELECT datakey, dataval FROM configuration"); + if( res && (PQresultStatus(res) == PGRES_TUPLES_OK) ) { + 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"); + PQclear(res); + PQfinish(dbc->dbhandle); + free_nullsafe(ctx, dbc); + return 0; + } + for( i = 0; i < PQntuples(res); i++ ) { + eAdd_value(ctx, cfg, PGgetValue(res, i, 0), PGgetValue(res, i, 1)); + } + dbc->config = cfg; + PQclear(res); + } else { + eurephia_log(ctx, LOG_FATAL, 0, + "Failed to query the eurephia configuration from the database: %s", + (res ? PQresultErrorMessage(res) + : PQerrorMessage(ctx->dbc->dbhandle))); + PQclear(res); + PQfinish(dbc->dbhandle); + eFree_values(ctx, dbc->dbparams); + free_nullsafe(ctx, dbc); + + } + ctx->dbc = dbc; + return 1; +} + + +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 + PQfinish((PGconn *) dbc->dbhandle); + free_nullsafe(ctx, dbc->dbname); + dbc->dbhandle = NULL; + + // Free up config memory + eFree_values(ctx, dbc->dbparams); + 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) +{ + int certid = 0; + PGresult *dbr = NULL; + char *blid = NULL; + char depth_str[5]; + const char **qry_args = NULL; + + DEBUG(ctx, 20, "Function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %i)", + org, cname, email, digest, depth); + + if( !ctx || !ctx->dbc || !ctx->dbc->dbhandle ) { + eurephia_log(ctx, LOG_FATAL, 0, "System error in eDBauth_TLS()"); + return 0; + } + + dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_TLS_AUTH, + "SELECT cert.certid, blid " + " FROM certificates cert" + " LEFT JOIN blacklist bl USING(digest)" + " WHERE organisation=$1::varchar AND common_name=$2::varchar " + " AND email=$3::varchar AND depth=$4::varchar " + " AND lower(cert.digest)=lower($5::varchar)\0", + 5, NULL); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, "Failed to prepare " PREPSQL_TLS_AUTH " SQL query: %s", + (dbr ? PQresultErrorMessage(dbr) : PQerrorMessage(ctx->dbc->dbhandle))); + if( dbr ) { + PQclear(dbr); + } + return 0; + } + PQclear(dbr); + + // Check if certificate is valid, and not too many attempts has been tried with + // the given certificate + snprintf(depth_str, 4, "%3i%c", depth, 0); + qry_args = calloc(5, sizeof(char *)); + qry_args[0] = org; + qry_args[1] = cname; + qry_args[2] = email; + qry_args[3] = depth_str; + qry_args[4] = digest; + dbr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_TLS_AUTH, 5, qry_args, NULL, NULL, 0); + free_nullsafe(ctx, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, "Could not lookup certificate information: %s", + (dbr ? PQresultErrorMessage(dbr) : PQerrorMessage(ctx->dbc->dbhandle))); + if( dbr ) { + PQclear(dbr); + } + return 0; + } + + certid = atoi_nullsafe(PGgetValue(dbr, 0, 0)); + blid = strdup_nullsafe(PGgetValue(dbr, 0, 1)); + + if( blid != NULL ) { + // If the certificate is blacklisted, update last access status and deny access. + eurephia_log(ctx, LOG_WARNING, 0, + "Attempt with BLACKLISTED certificate (certid %i)", certid); + update_blacklist_attempt(ctx, blid); + certid = -1; + } + PQclear(dbr); + free_nullsafe(ctx, blid); + + 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) +{ + int uicid = 0, pwdok = 0; + PGresult *dbr = NULL; + char certid_str[10]; + char *crpwd = NULL, *activated = NULL, *deactivated = NULL; + char *blid_uname = NULL, *blid_cert = NULL; + char *dbpwd = NULL, *uid = NULL; + const char **qry_args = NULL; + + DEBUG(ctx, 20, "Function call: eDBauth_user(ctx, %i, '%s','xxxxxxxx')", certid, username); + + if( !ctx || !ctx->dbc || !ctx->dbc->dbhandle ) { + eurephia_log(ctx, LOG_FATAL, 0, "System error in eDBauth_user()"); + return 0; + } + + dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_USERPWD_AUTH, + "SELECT uicid, ou.uid, activated, deactivated, bl1.blid, bl2.blid, password " + " FROM users ou" + " JOIN usercerts uc USING(uid) " + " LEFT JOIN blacklist bl1 ON( ou.username = bl1.username) " + " LEFT JOIN (SELECT blid, certid " + " FROM certificates " + " JOIN blacklist USING(digest)) bl2 ON(uc.certid = bl2.certid)" + " WHERE uc.certid = $1::INTEGER AND ou.username = $2::VARCHAR", 2, NULL); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, + "Failed to prepare " PREPSQL_USERPWD_AUTH " SQL query: %s", + (dbr ? PQresultErrorMessage(dbr) : PQerrorMessage(ctx->dbc->dbhandle))); + uicid = 0; + goto exit; + } + PQclear(dbr); + + + // Look up the user in the database + snprintf(certid_str, 9, "%8i%c", certid, 0); + qry_args = calloc(2, sizeof(char *)); + qry_args[0] = certid_str; + qry_args[1] = username; + dbr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_USERPWD_AUTH, 2, qry_args, NULL, NULL, 0); + free_nullsafe(ctx, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, "Could not lookup user account information: %s", + (dbr ? PQresultErrorMessage(dbr) : PQerrorMessage(ctx->dbc->dbhandle))); + uicid = 0; + goto exit; + } + + if( PQntuples(dbr) != 1 ) { + eurephia_log(ctx, LOG_WARNING, 0, "Authentication failed for user '%s'. " + "Could not find user or user-certificate link.", username); + sleep(2); + uicid = 0; + goto exit; + } + + // Check if the user account is valid and that the password is correct + uid = PGgetValue(dbr, 0, 1); + activated = PGgetValue(dbr, 0, 2); + deactivated = PGgetValue(dbr, 0, 3); + blid_uname = PGgetValue(dbr, 0, 4); + blid_cert = PGgetValue(dbr, 0, 5); + dbpwd = PGgetValue(dbr, 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: %s, %s)", + uid, username); + uicid = -1; + } else if( blid_cert != NULL ) { + eurephia_log(ctx, LOG_WARNING, 0, + "User account linked with a BLACKLISTED certificate " + "(uid: %s, %s) - certid: %s", + uid, username, certid); + uicid = -1; + } else if( activated == NULL ) { + eurephia_log(ctx, LOG_WARNING, 0, "User account is not activated (uid: %s, %s)", + uid, username); + uicid = -1; + } else if( deactivated != NULL ) { + eurephia_log(ctx, LOG_WARNING, 0, "User account is deactivated (uid: %s, %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 { + PGresult *upd = NULL; + + uicid = atoi_nullsafe(PGgetValue(dbr, 0, 0)); + + // Update last accessed status + upd = PQprepare(ctx->dbc->dbhandle, PREPSQL_USERS_LASTACC_UPD, + "UPDATE users SET last_accessed = CURRENT_TIMESTAMP" + " WHERE uid = $1::INTEGER", 1, NULL); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, + "Failed to prepare " PREPSQL_USERS_LASTACC_UPD " SQL query: %s", + (dbr ? PQresultErrorMessage(dbr) + : PQerrorMessage(ctx->dbc->dbhandle))); + } else if( dbr ) { + PQclear(upd); + + qry_args = calloc(1, sizeof(char *)); + qry_args[0] = uid; + upd = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_USERS_LASTACC_UPD, + 1, qry_args, NULL, NULL, 0); + free_nullsafe(ctx, qry_args); + if( !upd || (PQresultStatus(upd) != PGRES_TUPLES_OK) ) { + eurephia_log(ctx, LOG_ERROR, 0, + "Could not update last access status for uid %s", uid); + } + if( upd ) { + PQclear(upd); + } + } + } + + exit: + if( dbr ) { + PQclear(dbr); + } + 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) +{ + int ret = 0; + PGresult *dbr = NULL; + const char **qry_args = NULL; + char certid_str[10]; + + DEBUG(ctx, 20, "Function call: eDBget_uid(ctx, %i, '%s')", certid, username); + + if( !ctx || !ctx->dbc || !ctx->dbc->dbhandle ) { + eurephia_log(ctx, LOG_FATAL, 0, "System error in eDBget_uid()"); + return 0; + } + + dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_USERS_GETUID, + "SELECT uid " + " FROM usercerts " + " JOIN users USING (uid) " + " WHERE certid = $1::INTEGER AND username = $1::VARCHAR", + 2, NULL); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, + "Failed to prepare " PREPSQL_USERS_GETUID " SQL query: %s", + (dbr ? PQresultErrorMessage(dbr) : PQerrorMessage(ctx->dbc->dbhandle))); + ret = -1; + goto exit; + } + PQclear(dbr); + + snprintf(certid_str, 9, "%8i%c", certid, 0); + qry_args = calloc(2, sizeof(char *)); + qry_args[0] = certid_str; + qry_args[1] = username; + dbr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_USERPWD_AUTH, 2, qry_args, NULL, NULL, 0); + free_nullsafe(ctx, qry_args); + if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, "Failed to lookup userid for user '%s': %s", + username, + (dbr ? PQresultErrorMessage(dbr) : PQerrorMessage(ctx->dbc->dbhandle))); + ret = -1; + goto exit; + } + + switch( PQntuples(dbr) > 1 ) { + case 0: + eurephia_log(ctx, LOG_WARNING,0, "Failed to find userid for certid %i and username '%s'", + certid, username); + ret = 0; + break; + + case 1: + ret = atoi_nullsafe(PGgetValue(dbr,0, 0)); + break; + default: + eurephia_log(ctx, LOG_FATAL, 0, "Failed to lookup userid for user '%s'", + username); + ret = -1; + break; + } + + exit: + if( dbr ) { + PQclear(dbr); + } + + DEBUG(ctx, 20, "Result function call: eDBget_uid(ctx, %i, '%s') - %i", certid, username, ret); + return ret; +} + + +/** + * @copydoc eDBblacklist_check() + */ +int eDBblacklist_check(eurephiaCTX *ctx, const int type, const char *val) +{ + int blacklisted = 0; + char sql[4098], *blid = NULL; + const char **qry_args = NULL; + PGresult *dbr = NULL; + + DEBUG(ctx, 20, "Function call: eDBblacklist_check(ctx, '%s', '%s')", + eDBattempt_types[type].descr, val); + + qry_args = calloc(1, sizeof(char *)); + qry_args[0] = val; + + snprintf(sql, 4096, + "SELECT blid" + " FROM blacklist" + " WHERE %s = %s%s$1%s%c", + eDBattempt_types[type].colname_where, + defaultValue(eDBattempt_types[type].value_func, ""), + (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? "(" : ""), + (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? ")" : ""), 0); + + dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_CHECK, sql, 1, NULL); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, + "Failed to prepare " PREPSQL_BLACKLIST_CHECK " SQL query: %s", + (dbr ? PQresultErrorMessage(dbr) : PQerrorMessage(ctx->dbc->dbhandle))); + blacklisted = -1; + goto exit; + } + PQclear(dbr); + + dbr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_CHECK, 2, qry_args, NULL, NULL, 0); + if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, "Failed to lookup %s in the blacklist for '%s': %s", + eDBattempt_types[type].descr, val, + (dbr ? PQresultErrorMessage(dbr) : PQerrorMessage(ctx->dbc->dbhandle))); + blacklisted = -1; + goto exit; + } + + if( dbr && PQntuples(dbr) > 0 ) { + blid = PQgetvalue(dbr, 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_blacklist_attempt(ctx, blid); + } else { + eurephia_log(ctx, LOG_FATAL, 0, "Querying blacklist for %s failed", + eDBattempt_types[type].descr); + }; + PQclear(dbr); + + // Check if this [type] have been tried before and consider if it should be blacklisted + if( blacklisted == 0) { + char *atpid = NULL; + int atpexceed = -1; + + snprintf(sql, 4096, + "SELECT atpid, attempts >= '%s' " + " FROM attempts " + " WHERE %s = $1", + defaultValue(eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg), + eDBattempt_types[type].default_value), + eDBattempt_types[type].colname_where); + dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_ATTEMPTS_CHECK, sql, 1, NULL); + if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, + "Failed to prepare " PREPSQL_ATTEMPTS_CHECK " SQL query: %s", + (dbr ? PQresultErrorMessage(dbr) : PQerrorMessage(ctx->dbc->dbhandle))); + blacklisted = -1; + goto exit; + } + PQclear(dbr); + + dbr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_CHECK, 1, qry_args, NULL, NULL, 0); + if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, + "Failed look up attempts counter for %s in the blacklist check on '%s': %s", + eDBattempt_types[type].descr, val, + (dbr ? PQresultErrorMessage(dbr) : PQerrorMessage(ctx->dbc->dbhandle))); + blacklisted = -1; + goto exit; + } + + if( dbr && (PQntuples(dbr) > 0) ) { + atpid = PQgetvalue(dbr, 0, 0); + atpexceed = atoi_nullsafe(PQgetvalue(dbr, 0, 1)); + + // If [type] has reached attempt limit and it is not black listed, black list it + if( (atpexceed > 0) && (blid == NULL) ) { + PGresult *blr = NULL; + + eurephia_log(ctx, LOG_WARNING, 0, + "%s got BLACKLISTED due to too many failed attempts: %s", + eDBattempt_types[type].descr, val); + + snprintf(sql, 4096, "INSERT INTO blacklist (%s) VALUES ($1)", + eDBattempt_types[type].colname); + blr = PQprepare(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_REGISTER, sql, 1, NULL); + if( !blr || (PQresultStatus(blr) != PGRES_COMMAND_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, + "Failed to prepare " PREPSQL_ATTEMPTS_CHECK " SQL query: %s", + (blr ? PQresultErrorMessage(blr) : PQerrorMessage(ctx->dbc->dbhandle))); + } else { + PQclear(blr); + blr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_REGISTER, 1, qry_args, + NULL, NULL, 0); + if( !blr || (PQresultStatus(blr) != PGRES_COMMAND_OK) ) { + eurephia_log(ctx, LOG_FATAL, 0, + "Failed to register %s value '%s' in the blacklist: %s", + eDBattempt_types[type].descr, val, + (blr ? PQresultErrorMessage(blr) + : PQerrorMessage(ctx->dbc->dbhandle))); + } + } + PQclear(blr); + blacklisted = 1; // [type] is blacklisted + } + atpid = NULL; + } else { + eurephia_log(ctx, LOG_CRITICAL, 0, "Querying attempts counts for blacklisted %s failed", + eDBattempt_types[type].descr); + } + } + DEBUG(ctx, 20, "Result - function call: eDBblacklist_check(ctx, '%s', '%s') - %i", + eDBattempt_types[type].descr, val, blacklisted); + + exit: + blid = NULL; + PQclear(dbr); + free_nullsafe(ctx, qry_args); + + return blacklisted; +} + + +/** + * @copydoc eDBregister_attempt() + */ +void eDBregister_attempt(eurephiaCTX *ctx, int type, int mode, const char *value) +{ + DEBUG(ctx, 20, "Function call: eDBregister_attempt(ctx, %s, %s, '%s')", + eDBattempt_types[type].colname, + (mode == ATTEMPT_RESET ? "ATTEMPT_RESET" : "ATTEMPT_REGISTER"), + value); + + /* + "SELECT atpid, attempts > %s, blid, attempts " + " FROM attempts " + " LEFT JOIN 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 ? ")" : "") + ); + */ +} + +/** + * @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) +{ + 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); + + /* + "INSERT INTO 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->sessionke + */ + return 0; +} + + +/** + * @copydoc eDBregister_vpnmacaddr() + */ +int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const char *macaddr) +{ + DEBUG(ctx, 20, "Function call: eDBregister_vpnmacaddr(ctx, '%s', '%s')", + session->sessionkey, macaddr); + + /* + "INSERT INTO macaddr_history (sessionkey, macaddr) VALUES ('%q','%q')", + session->sessionkey, macaddr); + + "UPDATE lastlog SET sessionstatus = 2, macaddr = '%q' " + " WHERE sessionkey = '%q' AND sessionstatus = 1", macaddr, session->sessionkey); + + */ + + // 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"); + return 0; + } + + return 1; +} + + +/** + * @copydoc eDBregister_logout() + */ +int eDBregister_logout(eurephiaCTX *ctx, eurephiaSESSION *skey, + const char *bytes_sent, const char *bytes_received, const char *duration) +{ + DEBUG(ctx, 20, "Function call: eDBregister_logout(ctx, '%s', %s, %s)", + skey->sessionkey, bytes_sent, bytes_received); + + /* + "UPDATE 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); + */ + + skey->sessionstatus = SESSION_LOGGEDOUT; + return 1; +} + + +/** + * @copydoc eDBget_sessionkey_seed() + */ +char *eDBget_sessionkey_seed(eurephiaCTX *ctx, sessionType type, const char *sessionseed) +{ + 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: + /* + "SELECT sessionkey " + " FROM sessionkeys " + " JOIN lastlog USING (sessionkey)" + " WHERE sessionstatus IN (1,2)" + " AND sessionseed = '%q'", + sessionseed); + */ + break; + + case stAUTHENTICATION: + /* + "SELECT sessionkey" + " FROM sessionkeys" + " LEFT JOIN 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; + } + + return skey; +} + + +/** + * @copydoc eDBget_sessionkey_macaddr() + */ +char *eDBget_sessionkey_macaddr(eurephiaCTX *ctx, const char *macaddr) +{ + char *skey = NULL; + + DEBUG(ctx, 20, "eDBget_sessionkey_macaddr(ctx, '%s')", macaddr); + + // Find sessionkey from MAC address + /* + "SELECT sessionkey " + " FROM sessions " + " JOIN lastlog USING (sessionkey)" + " WHERE sessionstatus = 3 " + " AND datakey = 'macaddr'" + " AND dataval = '%q'", macaddr); + */ + + return skey; +} + + +/** + * @copydoc eDBcheck_sessionkey_uniqueness() + */ +int eDBcheck_sessionkey_uniqueness(eurephiaCTX *ctx, const char *seskey) +{ + 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: + /* + "SELECT count(sessionkey) = 0 " + "FROM eurephia_adminlog WHERE sessionkey = '%q'", seskey); + */ + break; + + case ECTX_PLUGIN_AUTH: + default: + /* + "SELECT count(sessionkey) = 0 " + "FROM lastlog WHERE sessionkey = '%q'", seskey); + */ + break; + } + return uniq; + +} + + +/** + * @copydoc eDBregister_sessionkey() + */ +int eDBregister_sessionkey(eurephiaCTX *ctx, const char *seed, const char *seskey) { + // dbresult *res; + + 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; + } + + /* + * "INSERT INTO openvpn_sessionkeys (sessionseed, sessionkey) VALUES('%q','%q')", + + if( res == NULL ) { + eurephia_log(ctx, LOG_FATAL, 0, + "eDBregister_sessionkey: Error registering sessionkey into 'sessionkeys'"); + return 0; + } + */ + return 1; +} + +/** + * 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; + + 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; + } + + /* + * "DELETE FROM openvpn_sessionkeys WHERE sessionkey = '%q'", seskey); + if( res == NULL ) { + eurephia_log(ctx, LOG_FATAL, 0, + "eDBremove_sessionkey: Error removing sessionkey from openvpn_sessionkeys"); + return 0; + } + */ + return 1; +} + + +/** + * @copydoc eDBload_sessiondata() + */ +eurephiaVALUES *eDBload_sessiondata(eurephiaCTX *ctx, const char *sesskey) +{ + eurephiaVALUES *sessvals = NULL; + + + DEBUG(ctx, 20, "Function call: eDBload_sessiondata(ctx, '%s')", sesskey); + + sessvals = eCreate_value_space(ctx, 10); + + /* + "SELECT datakey, dataval FROM sessions WHERE sessionkey = '%q'", + sesskey + */ + + return sessvals; +} + + +/** + * @copydoc eDBstore_session_value() + */ +int eDBstore_session_value(eurephiaCTX *ctx, eurephiaSESSION *session, int mode, + const char *key, const char *val) +{ + 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: + /* + "INSERT INTO sessions (sessionkey, datakey, dataval) " + "VALUES ('%q','%q','%q')", session->sessionkey, key, val); + */ + break; + + case SESSVAL_UPDATE: + /* + "UPDATE sessions SET dataval = '%q' " + " WHERE sessionkey = '%q' AND datakey = '%q'", + val, session->sessionkey, key); + */ + + case SESSVAL_DELETE: + /* + "DELETE FROM sessions " + " WHERE sessionkey = '%q' AND datakey = '%q'", + session->sessionkey, key); + */ + break; + + default: + eurephia_log(ctx, LOG_FATAL, 0, "Unknown eDBstore_session_value mode '%i'", mode); + return 0; + } + return 1; +} + + +/** + * @copydoc eDBdestroy_session() + */ +int eDBdestroy_session(eurephiaCTX *ctx, eurephiaSESSION *session) +{ + 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 ) { + /* + "UPDATE lastlog " + " SET sessionstatus = 4, session_deleted = CURRENT_TIMESTAMP " + " WHERE sessionkey = '%q' AND sessionstatus = 3", session->sessionkey); + */ + } + + // Delete session variables + /* + "DELETE FROM sessions WHERE sessionkey = '%q'", session->sessionkey); + */ + + // Delete the session key + /* + "DELETE FROM sessionkeys WHERE sessionkey = '%q'", seskey); + */ + return 0; +} + + +/** + * @copydoc eDBget_firewall_profile() + */ +char *eDBget_firewall_profile(eurephiaCTX *ctx, eurephiaSESSION *session) +{ + char *ret = NULL; + + + DEBUG(ctx, 20, "Function call: eDBget_firewall_profile(ctx, {session}'%s')", + session->sessionkey); + + /* + "SELECT fw_profile " + " FROM lastlog " + " JOIN usercerts USING(certid, uid)" + " JOIN accesses USING(accessprofile)" + " WHERE sessionkey = '%q'", session->sessionkey); + */ + + return ret; +} + + +/** + * @copydoc eDBget_blacklisted_ip() + */ +eurephiaVALUES *eDBget_blacklisted_ip(eurephiaCTX *ctx) +{ + eurephiaVALUES *ret = NULL; + + DEBUG(ctx, 20, "Function call: eDBget_blacklisted_ip(ctx)"); + + /* + "SELECT remoteip FROM blacklist WHERE remoteip IS NOT NULL"); + */ + + ret = eCreate_value_space(ctx, 21); + // copy SQL result into eurephiaVALUES chain + + return ret; + +} |