/* edb-pgsql.c -- PostgreSQL database driver for eurephia * * GPLv2 only - Copyright (C) 2011, 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-pgsql.c * @author David Sommerseth * @date 2011-01-15 * * @brief eurephia database driver for the PostgreSQL database. * This file is the main API for the driver. * */ #include #include #include #include #include #define DRIVERVERSION "1.0" #ifndef DRIVERAPIVERSION # define DRIVERAPIVERSION 2 #endif #include #include #include #include #include #include #include #include #include "pgsql-common.h" #include "prepared-sql.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 *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 */ ePG_prepID sql_blacklist_check; /** Prepared SQL ID reference for blacklist check queries */ ePG_prepID sql_blacklist_reg; /** Prepared SQL ID reference for blacklist register queries */ ePG_prepID sql_attempts_reg; /** Prepared SQL ID reference for attempts register queries */ } eDBattempt_types_t; /** * Static mapping table with the needed values. Uses the eDBattempt_types_t struct. */ static const eDBattempt_types_t eDBattempt_types[] = { {NULL}, {.allow_cfg = "allow_ipaddr_attempts", .descr = "IP Address", .default_value = "10", .sql_blacklist_check = PREPSQL_BLACKLIST_CHECK_IPADDR, .sql_blacklist_reg = PREPSQL_BLACKLIST_REG_IPADDR, .sql_attempts_reg = PREPSQL_ATTEMPTS_REG_IPADDR }, {.allow_cfg = "allow_cert_attempts", .descr = "Certificate", .default_value = "5", .sql_blacklist_check = PREPSQL_BLACKLIST_CHECK_CERT, .sql_blacklist_reg = PREPSQL_BLACKLIST_REG_CERT, .sql_attempts_reg = PREPSQL_ATTEMPTS_REG_CERT }, {.allow_cfg = "allow_username_attempts", .descr = "Username", .default_value = "3", .sql_blacklist_check = PREPSQL_BLACKLIST_CHECK_USERNAME, .sql_blacklist_reg = PREPSQL_BLACKLIST_REG_USERNAME, .sql_attempts_reg = PREPSQL_ATTEMPTS_REG_USERNAME }, {NULL} }; /** * @copydoc eDB_DriverVersion() */ const char *eDB_DriverVersion(void) { return "edb-pgsql (v"DRIVERVERSION") David Sommerseth 2012 (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; ePGprepParams *qry_args = NULL; qry_args = ePGprepParamsAlloc(ctx, PREPSQL_BLACKLIST_ATTEMPTUPD); ePGprepParamsAddArgument(ctx, qry_args, blid); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_BLACKLIST_ATTEMPTUPD, "Failed to update blaclist.last_access timestamp for blid %s", blid); return; } PQclear(dbr); } } /** * @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_EMERG, 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); ctx->dbc = dbc; assert( dbc != NULL ); dbc->dbparams = ecfg_ReadConfig(ctx, argv[0]); if( dbc->dbparams == NULL ) { eurephia_log(ctx, LOG_EMERG, 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_EMERG, 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_EMERG, 0, "Failed to connect to the PostgreSQL database: Unknown error"); ctx->dbc = NULL; free_nullsafe(ctx, dbc); return 0; } if( PQstatus(dbc->dbhandle) != CONNECTION_OK ) { ePGerrorMessage(ctx, NULL, LOG_EMERG, 0, PREPSQL_NONE, "[DB-INIT] Failed to connect to the PostgreSQL database '%s'", dbname); ctx->dbc = NULL; 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_EMERG, 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, ePGgetValue(res, i, 0), ePGgetValue(res, i, 1)); } dbc->config = cfg; PQclear(res); } else { ePGerrorMessage(ctx, res, LOG_EMERG, 0, PREPSQL_NONE, "[DB-INIT] Failed to the configuration from the database"); eFree_values(ctx, dbc->dbparams); free_nullsafe(ctx, dbc); return 0; } // Load all prepared SQL statements return ePGprepLoadStatements(ctx); } /** * @copydoc eDBdisconnect() */ 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 eDBdisconnect_firewall() */ void eDBdisconnect_firewall(eurephiaCTX *ctx) { DEBUG(ctx, 20, "Function call: eDBdisconnect_firewall(ctx)"); DEBUG(ctx, 21, "Closing PostgreSQL file descriptor (%ld) for firewall thread", PQsocket(ctx->dbc->dbhandle)); /* Is there a better way how to avoid the firewall thread to close the inherited * PostgreSQL connection? ... this is pretty brute force, but seems to work */ close(PQsocket(ctx->dbc->dbhandle)); ctx->dbc->dbhandle = 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]; ePGprepParams *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_EMERG, 0, "System error in eDBauth_TLS()"); return 0; } // 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 = ePGprepParamsAlloc(ctx, PREPSQL_TLS_AUTH); ePGprepParamsAddArgument(ctx, qry_args, org); ePGprepParamsAddArgument(ctx, qry_args, cname); ePGprepParamsAddArgument(ctx, qry_args, email); ePGprepParamsAddArgument(ctx, qry_args, depth_str); ePGprepParamsAddArgument(ctx, qry_args, digest); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_TLS_AUTH, "Could not lookup certificate information " "[O=%s/CN=%s/emailAddress=%s - depth %s, digest=%s]", org, cname, email, depth_str, digest); return 0; } certid = atoi_nullsafe(ePGgetValue(dbr, 0, 0)); blid = strdup_nullsafe(ePGgetValue(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; ePGprepParams *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; } // Look up the user in the database snprintf(certid_str, 9, "%8i%c", certid, 0); qry_args = ePGprepParamsAlloc(ctx, PREPSQL_USERPWD_AUTH); ePGprepParamsAddArgument(ctx, qry_args, certid_str); ePGprepParamsAddArgument(ctx, qry_args, username); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_USERPWD_AUTH, "Failed to lookup user account information " "[username=%s, certid=%s]", username, certid_str); 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 = ePGgetValue(dbr, 0, 1); activated = ePGgetValue(dbr, 0, 2); deactivated = ePGgetValue(dbr, 0, 3); blid_uname = ePGgetValue(dbr, 0, 4); blid_cert = ePGgetValue(dbr, 0, 5); dbpwd = ePGgetValue(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(ePGgetValue(dbr, 0, 0)); // Update last accessed status qry_args = ePGprepParamsAlloc(ctx, PREPSQL_USERS_LASTACC_UPD); ePGprepParamsAddArgument(ctx, qry_args, uid); upd = ePGprepExec(ctx, qry_args); if( !upd || (PQresultStatus(upd) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, upd, LOG_FATAL, 0, PREPSQL_USERS_LASTACC_UPD, "Failed to update last access status " "for uid %s", uid); } else if( upd ) { PQclear(upd); } } if( dbr ) { PQclear(dbr); } exit: 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; ePGprepParams*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; } snprintf(certid_str, 9, "%8i%c", certid, 0); qry_args = ePGprepParamsAlloc(ctx, PREPSQL_USERS_GETUID); ePGprepParamsAddArgument(ctx, qry_args, certid_str); ePGprepParamsAddArgument(ctx, qry_args, username); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_USERS_GETUID, "Failed to lookup userid for user '%s'", username); ret = -1; goto exit; } switch(PQntuples(dbr)) { 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(ePGgetValue(dbr,0, 0)); break; default: eurephia_log(ctx, LOG_CRIT, 0, "Failed to lookup userid for user '%s'", username); ret = -1; break; } if( dbr ) { PQclear(dbr); } exit: 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 *blid = NULL; ePGprepParams *qry_args = NULL; PGresult *dbr = NULL; const eDBattempt_types_t *atmptype = &eDBattempt_types[type]; DEBUG(ctx, 20, "Function call: eDBblacklist_check(ctx, '%s', '%s')", atmptype->descr, val); qry_args = ePGprepParamsAlloc(ctx, atmptype->sql_blacklist_check); ePGprepParamsAddArgument(ctx, qry_args, val); ePGprepParamsAddArgument(ctx, qry_args, defaultValue(eGet_value(ctx->dbc->config, atmptype->allow_cfg), atmptype->default_value)); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, atmptype->sql_blacklist_check, "Failed to lookup %s in the blacklist table for '%s'", atmptype->descr, val); blacklisted = -1; goto error; } if( dbr && PQntuples(dbr) > 0 ) { char *atpid = NULL; int atpexceed = -1, atpcount = 0; blid = ePGgetValue(dbr, 0, 2); if( blid != NULL ) { eurephia_log(ctx, LOG_WARNING, 0, "Attempt from blacklisted %s: %s", atmptype->descr, val); blacklisted = 1; // [type] is blacklisted update_blacklist_attempt(ctx, blid); goto exit; } // Check if this [type] have been tried before and consider if it should be blacklisted atpid = ePGgetValue(dbr, 0, 0); atpexceed = atoi_nullsafe(ePGgetValue(dbr, 0, 1)); atpcount = atoi_nullsafe(ePGgetValue(dbr, 0, 3)); // 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 (%i) failed attempts: %s", atmptype->descr, atpcount, val); qry_args = ePGprepParamsAlloc(ctx, atmptype->sql_blacklist_reg); ePGprepParamsAddArgument(ctx, qry_args, val); blr = ePGprepExec(ctx, qry_args); if( !blr || (PQresultStatus(blr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, blr, LOG_FATAL, 0, atmptype->sql_blacklist_reg, "Failed to register %s value '%s' in the blacklist:", atmptype->descr, val); } PQclear(blr); blacklisted = 1; // [type] is blacklisted } atpid = NULL; PQclear(dbr); } exit: DEBUG(ctx, 20, "Result - function call: eDBblacklist_check(ctx, '%s', '%s') - %i", atmptype->descr, val, blacklisted); return blacklisted; error: DEBUG(ctx, 20, "Result - function call: eDBblacklist_check(ctx, '%s', '%s') - ERROR: %i", atmptype->descr, val, blacklisted); blid = NULL; return blacklisted; } /** * @copydoc eDBregister_attempt() */ void eDBregister_attempt(eurephiaCTX *ctx, int type, int mode, const char *value) { const eDBattempt_types_t *atmptype = &eDBattempt_types[type]; ePGprepParams *qry_args = NULL; char *id = NULL, *atmpt_block = NULL, *blid = NULL; int attempts = 0; PGresult *dbr = NULL; DEBUG(ctx, 20, "Function call: eDBregister_attempt(ctx, %s, %s, '%s')", atmptype->descr, (mode == ATTEMPT_RESET ? "ATTEMPT_RESET" : "ATTEMPT_REGISTER"), value); qry_args = ePGprepParamsAlloc(ctx, atmptype->sql_blacklist_check); ePGprepParamsAddArgument(ctx, qry_args, value); ePGprepParamsAddArgument(ctx, qry_args, defaultValue(eGet_value(ctx->dbc->config, atmptype->allow_cfg), atmptype->default_value)); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, atmptype->sql_blacklist_check, "Failed to lookup %s in the attempts/blacklist table for '%s'", atmptype->descr, value); return; } // FIXME: Will we always have a record from this query? attempts = atoi_nullsafe(ePGgetValue(dbr, 0, 3)); // If we are asked to reset the attempt counter and we do not find any attempts, exit here if( (mode == ATTEMPT_RESET) && ((PQntuples(dbr) == 0) || (attempts == 0))) { PQclear(dbr); return; } id = strdup_nullsafe(ePGgetValue(dbr, 0, 0)); atmpt_block = strdup_nullsafe(ePGgetValue(dbr, 0, 1)); blid = strdup_nullsafe(ePGgetValue(dbr, 0, 2)); PQclear(dbr); if( (id == NULL) && (mode == ATTEMPT_REGISTER) ) { // Only insert record when we are in registering mode qry_args = ePGprepParamsAlloc(ctx, atmptype->sql_attempts_reg); ePGprepParamsAddArgument(ctx, qry_args, value); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, atmptype->sql_attempts_reg, "Failed to register failed attempt for %s '%s'", atmptype->descr, value); goto error; } PQclear(dbr); } else if( id != NULL ){ // if a attempt record exists, update it according to mode switch( mode ) { case ATTEMPT_RESET: qry_args = ePGprepParamsAlloc(ctx, PREPSQL_ATTEMPTS_RESET); ePGprepParamsAddArgument(ctx, qry_args, id); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_ATTEMPTS_RESET, "Failed to reset attempts counter for atpid '%s' (%s %s)", id, atmptype->descr, value); goto error; } PQclear(dbr); break; default: qry_args = ePGprepParamsAlloc(ctx, PREPSQL_ATTEMPTS_INCR); ePGprepParamsAddArgument(ctx, qry_args, id); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_ATTEMPTS_INCR, "Failed to increment attempts counter for " "atpid '%s' (%s %s)", id, atmptype->descr, value); goto error; } PQclear(dbr); break; } } // If attempts have exceeded attempt limit, blacklist it immediately if not already registered if( (mode == ATTEMPT_REGISTER) && (blid == NULL) && (atmpt_block != NULL) && (atmpt_block[0] == 't') ) { eurephia_log(ctx, LOG_WARNING, 0, "Blacklisting %s due to too many attempts: %s", eDBattempt_types[type].descr, value); qry_args = ePGprepParamsAlloc(ctx, atmptype->sql_blacklist_reg); ePGprepParamsAddArgument(ctx, qry_args, value); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, atmptype->sql_blacklist_reg, "Failed to blacklist %s value " "'%s':", atmptype->descr, value); goto error; } PQclear(dbr); } error: 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) { ePGprepParams*qry_args = NULL; int ret = 0; PGresult *dbr = NULL; 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); qry_args = ePGprepParamsAlloc(ctx, PREPSQL_REGISTER_LOGIN); ePGprepParamsAddArgumentInt(ctx, qry_args, uid); ePGprepParamsAddArgumentInt(ctx, qry_args, certid); ePGprepParamsAddArgument(ctx, qry_args, proto); ePGprepParamsAddArgument(ctx, qry_args, remipaddr); ePGprepParamsAddArgument(ctx, qry_args, remport); ePGprepParamsAddArgument(ctx, qry_args, vpnipaddr); ePGprepParamsAddArgument(ctx, qry_args, vpnipmask); ePGprepParamsAddArgument(ctx, qry_args, skey->sessionkey); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_REGISTER_LOGIN, "Failed to register session login for session key '%s'", skey->sessionkey); ret = 0; } else { ret = 1; PQclear(dbr); } return ret; } /** * @copydoc eDBregister_vpnmacaddr() */ int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const char *macaddr) { ePGprepParams *qry_args = NULL; int ret = 0; PGresult *dbr = NULL; DEBUG(ctx, 20, "Function call: eDBregister_vpnmacaddr(ctx, '%s', '%s')", session->sessionkey, macaddr); /* TODO: Use transaction */ qry_args = ePGprepParamsAlloc(ctx, PREPSQL_MACHISTORY_REGISTER); ePGprepParamsAddArgument(ctx, qry_args, session->sessionkey); ePGprepParamsAddArgument(ctx, qry_args, macaddr); // Register the MAC address into the macaddr_history table dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_MACHISTORY_REGISTER, "eDBregister_vpnmacaddr: Failed to register VPN client " "MAC address (%s/%s)", session->sessionkey, macaddr); ret = 0; goto exit; } PQclear(dbr); // Update lastlog table with the latest used MAC address qry_args = ePGprepParamsAlloc(ctx, PREPSQL_MACHISTORY_LASTLOG); ePGprepParamsAddArgument(ctx, qry_args, session->sessionkey); ePGprepParamsAddArgument(ctx, qry_args, macaddr); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_MACHISTORY_LASTLOG, "eDBregister_vpnmacaddr: Failed to update " "lastlog with MAC address info (%s/%s)", session->sessionkey, macaddr); ret = 0; goto exit; } PQclear(dbr); // 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; } exit: return 1; } /** * @copydoc eDBregister_logout() */ int eDBregister_logout(eurephiaCTX *ctx, eurephiaSESSION *skey, const char *bytes_sent, const char *bytes_received, const char *duration) { ePGprepParams *qry_args = NULL; PGresult *dbr = NULL; DEBUG(ctx, 20, "Function call: eDBregister_logout(ctx, '%s', %s, %s)", skey->sessionkey, bytes_sent, bytes_received); qry_args = ePGprepParamsAlloc(ctx, PREPSQL_REGISTER_LOGOUT); ePGprepParamsAddArgument(ctx, qry_args, skey->sessionkey); ePGprepParamsAddArgument(ctx, qry_args, bytes_sent); ePGprepParamsAddArgument(ctx, qry_args, bytes_received); ePGprepParamsAddArgument(ctx, qry_args, duration); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_REGISTER_LOGOUT, "Failed to lastlog with logout info (%s)", skey->sessionkey); return 0; } PQclear(dbr); skey->sessionstatus = SESSION_LOGGEDOUT; return 1; } /** * @copydoc eDBget_sessionkey_seed() */ char *eDBget_sessionkey_seed(eurephiaCTX *ctx, sessionType type, const char *sessionseed) { ePGprepParams *qry_args = NULL; char *skey = NULL; ePG_prepID preptype = PREPSQL_NONE; PGresult *dbr = 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: preptype = PREPSQL_SESSIONKEY_GETSEED_SESSION; break; case stAUTHENTICATION: preptype = PREPSQL_SESSIONKEY_GETSEED_AUTH; break; default: eurephia_log(ctx, LOG_ERROR, 0, "Invalid session type: %i", type); return NULL; } qry_args = ePGprepParamsAlloc(ctx, preptype); ePGprepParamsAddArgument(ctx, qry_args, sessionseed); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, preptype, "Failed to lookup session key from session seed '%s'", sessionseed); return NULL; } skey = (PQntuples(dbr) == 1 ? strdup_nullsafe(PQgetvalue(dbr, 0, 0)) : NULL); PQclear(dbr); return skey; } /** * @copydoc eDBget_sessionkey_macaddr() */ char *eDBget_sessionkey_macaddr(eurephiaCTX *ctx, const char *macaddr) { char *skey = NULL; ePGprepParams *qry_args = NULL; PGresult *dbr = NULL; DEBUG(ctx, 20, "eDBget_sessionkey_macaddr(ctx, '%s')", macaddr); // Find sessionkey from MAC address qry_args = ePGprepParamsAlloc(ctx, PREPSQL_SESSIONKEY_GETMAC); ePGprepParamsAddArgument(ctx, qry_args, macaddr); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, PREPSQL_SESSIONKEY_GETMAC, "Failed to lookup session key from MAC address '%s'", macaddr); return NULL; } skey = (PQntuples(dbr) == 1 ? strdup_nullsafe(PQgetvalue(dbr, 0, 0)) : NULL); PQclear(dbr); return skey; } /** * @copydoc eDBcheck_sessionkey_uniqueness() */ int eDBcheck_sessionkey_uniqueness(eurephiaCTX *ctx, const char *seskey) { int uniq = 0; ePGprepParams *qry_args = NULL; ePG_prepID preptype; PGresult *dbr = NULL; 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: preptype = PREPSQL_SESSIONKEY_UNIQ_ADMIN; break; case ECTX_PLUGIN_AUTH: default: preptype = PREPSQL_SESSIONKEY_UNIQ_PLAUTH; break; } qry_args = ePGprepParamsAlloc(ctx, preptype); ePGprepParamsAddArgument(ctx, qry_args, seskey); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { ePGerrorMessage(ctx, NULL, LOG_FATAL, 0, preptype, "Failed to check session key uniqueness for '%s'", seskey); return 0; } else if( PQntuples(dbr) == 1 ) { char *res = PQgetvalue(dbr, 0, 0); uniq = (*res == 't'); } PQclear(dbr); return uniq; } /** * @copydoc eDBregister_sessionkey() */ int eDBregister_sessionkey(eurephiaCTX *ctx, const char *seed, const char *seskey) { ePGprepParams *qry_args = NULL; PGresult *dbr = NULL; 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; } qry_args = ePGprepParamsAlloc(ctx, PREPSQL_SESSIONKEY_REGISTER); ePGprepParamsAddArgument(ctx, qry_args, seed); ePGprepParamsAddArgument(ctx, qry_args, seskey); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_SESSIONKEY_REGISTER, "eDBregister_sessionkey: Error registering sessionkey" " into 'sessionkeys'"); return 0; } PQclear(dbr); 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) { ePGprepParams *qry_args = NULL; PGresult *dbr = NULL; 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; } qry_args = ePGprepParamsAlloc(ctx, PREPSQL_SESSIONKEY_REMOVE); ePGprepParamsAddArgument(ctx, qry_args, seskey); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_SESSIONKEY_REMOVE, "eDBremove_sessionkey: Error removing sessionkey '%s'", seskey); return 0; } PQclear(dbr); return 1; } /** * @copydoc eDBload_sessiondata() */ eurephiaVALUES *eDBload_sessiondata(eurephiaCTX *ctx, const char *seskey) { eurephiaVALUES *sessvals = NULL; ePGprepParams *qry_args = NULL; int i = 0; PGresult *dbr = NULL; DEBUG(ctx, 20, "Function call: eDBload_sessiondata(ctx, '%s')", seskey); qry_args = ePGprepParamsAlloc(ctx, PREPSQL_SESSIONVARS_LOAD); ePGprepParamsAddArgument(ctx, qry_args, seskey); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_SESSIONVARS_LOAD, "eDBload_sessiondata: Failed to load session variables for session '%s'", seskey); return NULL; } sessvals = eCreate_value_space(ctx, 10); if( !sessvals ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Failed to create value storage for session variables for session '%s'", seskey); return NULL; } for( i = 0; i < PQntuples(dbr); i++ ) { eAdd_value(ctx, sessvals, ePGgetValue(dbr, i, 0), ePGgetValue(dbr, i, 1)); } PQclear(dbr); return sessvals; } /** * @copydoc eDBstore_session_value() */ int eDBstore_session_value(eurephiaCTX *ctx, eurephiaSESSION *session, int mode, const char *key, const char *val) { ePGprepParams *qry_args = NULL; ePG_prepID preptype = PREPSQL_NONE; PGresult *dbr = NULL; 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: preptype = PREPSQL_SESSIONVARS_STORE_NEW; break; case SESSVAL_UPDATE: preptype = PREPSQL_SESSIONVARS_STORE_UPDATE; break; case SESSVAL_DELETE: preptype = PREPSQL_SESSIONVARS_STORE_DELETE; break; default: eurephia_log(ctx, LOG_FATAL, 0, "Unknown eDBstore_session_value mode '%i'", mode); return 0; } qry_args = ePGprepParamsAlloc(ctx, preptype); ePGprepParamsAddArgument(ctx, qry_args, session->sessionkey); ePGprepParamsAddArgument(ctx, qry_args, key); ePGprepParamsAddArgument(ctx, qry_args, val); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, preptype, "eDBstore_session_value: Failed to update session value [%s] %s", session->sessionkey, key); return 0; } PQclear(dbr); return 1; } /** * @copydoc eDBdestroy_session() */ int eDBdestroy_session(eurephiaCTX *ctx, eurephiaSESSION *session) { ePGprepParams *qry_args = NULL; int ret = 0; PGresult *dbr = 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; } qry_args = ePGprepParamsAlloc(ctx, PREPSQL_SESSIONS_DESTROY_LASTLOG); ePGprepParamsAddArgument(ctx, qry_args, session->sessionkey); // Update session status - if we have a "real" session (not auth-session) if( session->type == stSESSION ) { dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_SESSIONS_DESTROY_LASTLOG, "eDBdestroy_session: Failed to update " "update lastlog with session deleted status (%s)", session->sessionkey); return 0; } PQclear(dbr); } // Delete session variables qry_args = ePGprepParamsAlloc(ctx, PREPSQL_SESSIONS_DESTROY_SESS); ePGprepParamsAddArgument(ctx, qry_args, session->sessionkey); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_SESSIONS_DESTROY_SESS, "eDBdestroy_session: Failed to delete " "session variables (%s)", session->sessionkey); return 0; } PQclear(dbr); // Delete the session seed/session key relation if( eDBremove_sessionkey(ctx, session->sessionkey) == 0) { eurephia_log(ctx, LOG_CRITICAL, 0, "Failed to delete sessionkey/sessionseed relation (%s)", session->sessionkey); ret = 0; } else { ret = 1; } return ret; } /** * @copydoc eDBget_firewall_profile() */ char *eDBget_firewall_profile(eurephiaCTX *ctx, eurephiaSESSION *session) { ePGprepParams *qry_args = NULL; char *ret = NULL; PGresult *dbr = NULL; DEBUG(ctx, 20, "Function call: eDBget_firewall_profile(ctx, {session}'%s')", session->sessionkey); qry_args = ePGprepParamsAlloc(ctx, PREPSQL_FIREWALL_GETPROFILE); ePGprepParamsAddArgument(ctx, qry_args, session->sessionkey); dbr = ePGprepExec(ctx, qry_args); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) || (PQntuples(dbr) > 1) ) { ePGerrorMessage(ctx, dbr, LOG_WARNING, 0, PREPSQL_FIREWALL_GETPROFILE, "Failed to retrieve the firewall profile for session '%s'", session->sessionkey); return NULL; } ret = (PQntuples(dbr) == 1 ? strdup_nullsafe(ePGgetValue(dbr, 0, 0)) : NULL); PQclear(dbr); return ret; } /** * @copydoc eDBget_blacklisted_ip() */ eurephiaVALUES *eDBget_blacklisted_ip(eurephiaCTX *ctx) { int i = 0; PGresult *dbr = NULL; eurephiaVALUES *ret = NULL; DEBUG(ctx, 20, "Function call: eDBget_blacklisted_ip(ctx)"); if( !ctx || !ctx->dbc || !ctx->dbc->dbhandle ) { eurephia_log(ctx, LOG_FATAL, 0, "System error in eDBget_blacklisted_ip()"); return NULL; } dbr = PQexec(ctx->dbc->dbhandle, "SELECT remoteip FROM blacklist WHERE remoteip IS NOT NULL"); if( !dbr || (PQresultStatus(dbr) != PGRES_TUPLES_OK) ) { ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_NONE, "eDBget_blaclisted_ip: Failed to query IP blacklist"); return NULL; } // Copy blacklisted IP addresses into a eurephiaVALUES chain without key names ret = eCreate_value_space(ctx, 21); for( i = 0; i < PQntuples(dbr); i++ ) { char *ip = ePGgetValue(dbr, i, 0); if( (ip != NULL) && (*ip != '\0') ) { eAdd_value(ctx, ret, NULL, ip); } } PQclear(dbr); return ret; }