/* edb-pgsql.c -- PostgreSQL database driver for eurephia * * GPLv2 only - Copyright (C) 2011 * 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 1 #endif #include #include #include #include #include #include #include #include /** * 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 *ePGgetValue(PGresult *res, int row, int col) { if( (res == NULL) || (PQgetisnull(res, row, col) == 1) ) { return NULL; } else { return PQgetvalue(res, row, col); } } static void ePGerrorMessage(eurephiaCTX *ctx, PGresult *dbr, int logdst, int loglvl, const char *prepsql, const char *fmt, ...) { char msgfmt[514]; va_list ap; memset(&msgfmt, 0, 514); va_start(ap, fmt); snprintf(msgfmt, 512, "[%s] SQL query failed: %s: %s", prepsql, fmt, (dbr != NULL ? PQresultErrorMessage(dbr) : (ctx->dbc != NULL ? PQerrorMessage(ctx->dbc->dbhandle) : "[unknown error]") )); veurephia_log(ctx, logdst, loglvl, ap, msgfmt); va_end(ap); memset(&msgfmt, 0, 514); PQclear(dbr); if( (loglvl == LOG_EMERG) && (ctx->dbc != NULL) ) { PQfinish(ctx->dbc->dbhandle); } } #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) ) { ePGerrorMessage(ctx, dbr, LOG_CRITICAL, 0, PREPSQL_BLACKLIST_ATTEMPTUPD, "Preparing last attempt query update failed"); return; } 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) ) { 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, "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, "DB-INIT", "Failed to the configuration from the database"); eFree_values(ctx, dbc->dbparams); free_nullsafe(ctx, dbc); return 0; } 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_EMERG, 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) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_TLS_AUTH, "Failed to prepare SQL query"); 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) ) { 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; 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) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_USERPWD_AUTH, "Failed to prepare SQL query"); 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) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, "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 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(upd) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, upd, LOG_FATAL, 0, PREPSQL_USERS_LASTACC_UPD, "Failed to prepare SQL query"); } 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) ) { ePGerrorMessage(ctx, upd, LOG_FATAL, 0, PREPSQL_USERS_LASTACC_UPD "Failed to update last access status for uid %s", uid); } 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; 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) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_USERS_GETUID, "Failed to prepare SQL query"); 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) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_USERPWD_AUTH, "Failed to lookup userid for user '%s'", username); 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(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 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", eDBattempt_types[type].colname_where, defaultValue(eDBattempt_types[type].value_func, ""), (eDBattempt_types[type].value_func ? "(" : ""), (eDBattempt_types[type].value_func ? ")" : "")); dbr = PQprepare(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_CHECK, sql, 1, NULL); if( !dbr || (PQresultStatus(dbr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_BLACKLIST_CHECK, "Failed to prepare SQL query"); 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) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_BLACKLIST_CHECK, "Failed to lookup %s in the blacklist for '%s'", eDBattempt_types[type].descr, val); blacklisted = -1; goto exit; } if( dbr && PQntuples(dbr) > 0 ) { blid = ePGgetValue(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); PQclear(dbr); } else { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_BLACKLIST_CHECK, "Blacklist query for %s failed (%s)", eDBattempt_types[type].descr, val); }; // 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) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_ATTEMPTS_CHECK, "Failed to prepare SQL query"); 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) ) { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_BLACKLIST_CHECK, "Failed look up attempts counter for %s in the blacklist check on '%s'", eDBattempt_types[type].descr, val); blacklisted = -1; goto exit; } if( dbr && (PQntuples(dbr) > 0) ) { atpid = ePGgetValue(dbr, 0, 0); atpexceed = atoi_nullsafe(ePGgetValue(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) ) { ePGerrorMessage(ctx, blr, LOG_FATAL, 0, PREPSQL_BLACKLIST_REGISTER, "Failed to prepare SQL query"); } else { PQclear(blr); blr = PQexecPrepared(ctx->dbc->dbhandle, PREPSQL_BLACKLIST_REGISTER, 1, qry_args, NULL, NULL, 0); if( !blr || (PQresultStatus(blr) != PGRES_COMMAND_OK) ) { ePGerrorMessage(ctx, blr, LOG_FATAL, 0, PREPSQL_BLACKLIST_REGISTER, "Failed to register %s value '%s' in the " "blacklist:", eDBattempt_types[type].descr, val); } } PQclear(blr); blacklisted = 1; // [type] is blacklisted } atpid = NULL; PQclear(dbr); } else { ePGerrorMessage(ctx, dbr, LOG_FATAL, 0, PREPSQL_BLACKLIST_CHECK, "Querying attempts counts for blacklisted %s failed (%s)", eDBattempt_types[type].descr, val); } } DEBUG(ctx, 20, "Result - function call: eDBblacklist_check(ctx, '%s', '%s') - %i", eDBattempt_types[type].descr, val, blacklisted); exit: blid = NULL; 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; }