src/auth/passdb-sql.c

	passdb-sql.c revision f93c833d644ecff0b0f80bee4f1cdde3e697b5c8
bcb4e51a409d94ae670de96afb8483a4f7855294Stephan Bosch/* Copyright (c) 2004-2011 Dovecot authors, see the included COPYING file */
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen#include "auth-common.h"
d6499957ea59e6d9729d3350d9ac5eae992635f6Timo Sirainen#include "passdb.h"
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen#ifdef PASSDB_SQL
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen#include "str.h"
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen#include "strescape.h"
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen#include "var-expand.h"
de62ce819d59a529530da4b57be1b8d6dad13d6bTimo Sirainen#include "safe-memset.h"
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen#include "password-scheme.h"
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen#include "auth-cache.h"
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen#include "db-sql.h"
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen
de62ce819d59a529530da4b57be1b8d6dad13d6bTimo Sirainen#include <stdlib.h>
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen#include <string.h>
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen
678d0463849ba777106eb7875f27db07a5d8e3dfTimo Sirainenstruct sql_passdb_module {
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    struct passdb_module module;
4c0aff96fc7e6d779be43458f96cbf015849a3deTimo Sirainen
4c0aff96fc7e6d779be43458f96cbf015849a3deTimo Sirainen    struct sql_connection *conn;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen};
678d0463849ba777106eb7875f27db07a5d8e3dfTimo Sirainen
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainenstruct passdb_sql_request {
678d0463849ba777106eb7875f27db07a5d8e3dfTimo Sirainen    struct auth_request *auth_request;
4c0aff96fc7e6d779be43458f96cbf015849a3deTimo Sirainen    union {
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen        verify_plain_callback_t *verify_plain;
678d0463849ba777106eb7875f27db07a5d8e3dfTimo Sirainen                lookup_credentials_callback_t *lookup_credentials;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen        set_credentials_callback_t *set_credentials;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    } callback;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen};
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainenstatic void sql_query_save_results(struct sql_result *result,
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen                   struct passdb_sql_request *sql_request)
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen{
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    struct auth_request *auth_request = sql_request->auth_request;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    struct passdb_module *_module = auth_request->passdb->passdb;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    struct sql_passdb_module *module = (struct sql_passdb_module *)_module;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    unsigned int i, fields_count;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    const char *name, *value;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen        fields_count = sql_result_get_fields_count(result);
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    for (i = 0; i < fields_count; i++) {
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen        name = sql_result_get_field_name(result, i);
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen        value = sql_result_get_field_value(result, i);
678d0463849ba777106eb7875f27db07a5d8e3dfTimo Sirainen
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen        if (*name != '\0' && value != NULL) {
678d0463849ba777106eb7875f27db07a5d8e3dfTimo Sirainen            auth_request_set_field(auth_request, name, value,
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen                module->conn->set.default_pass_scheme);
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen        }
678d0463849ba777106eb7875f27db07a5d8e3dfTimo Sirainen    }
678d0463849ba777106eb7875f27db07a5d8e3dfTimo Sirainen}
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainenstatic void sql_query_callback(struct sql_result *result,
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen                   struct passdb_sql_request *sql_request)
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen{
d6499957ea59e6d9729d3350d9ac5eae992635f6Timo Sirainen    struct auth_request *auth_request = sql_request->auth_request;
4c0aff96fc7e6d779be43458f96cbf015849a3deTimo Sirainen    struct passdb_module *_module = auth_request->passdb->passdb;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    struct sql_passdb_module *module = (struct sql_passdb_module *)_module;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    enum passdb_result passdb_result;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    const char *password, *scheme;
77b5fd56e5a06d624f3ab92198272287333114f4Timo Sirainen    int ret;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    passdb_result = PASSDB_RESULT_INTERNAL_FAILURE;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    password = NULL;
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    ret = sql_result_next_row(result);
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    if (ret >= 0)
402e999a878e0cc41a0afb830fea0a93afc75f0dTimo Sirainen        db_sql_success(module->conn);
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    if (ret < 0) {
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen        if (!module->conn->default_password_query) {
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen            auth_request_log_error(auth_request, "sql",
bf7dc750b95039981c0e9d728f313d50cf38a156Martti Rannanjärvi                           "Password query failed: %s",
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen                           sql_result_get_error(result));
2a6c02a5e7e5cdf4980befbfaf372603ce338a25Timo Sirainen        } else {
2a6c02a5e7e5cdf4980befbfaf372603ce338a25Timo Sirainen            auth_request_log_error(auth_request, "sql",
2a6c02a5e7e5cdf4980befbfaf372603ce338a25Timo Sirainen                "Password query failed: %s "
2a6c02a5e7e5cdf4980befbfaf372603ce338a25Timo Sirainen                "(using built-in default password_query: %s)",
2a6c02a5e7e5cdf4980befbfaf372603ce338a25Timo Sirainen                sql_result_get_error(result),
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen                module->conn->set.password_query);
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen        }
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen    } else if (ret == 0) {
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen        auth_request_log_info(auth_request, "sql", "unknown user");
402e999a878e0cc41a0afb830fea0a93afc75f0dTimo Sirainen        passdb_result = PASSDB_RESULT_USER_UNKNOWN;
678d0463849ba777106eb7875f27db07a5d8e3dfTimo Sirainen    } else {
678d0463849ba777106eb7875f27db07a5d8e3dfTimo Sirainen        sql_query_save_results(result, sql_request);
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen        /* Note that we really want to check if the password field is
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen           found. Just checking if password is set isn't enough,
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen           because with proxies we might want to return NULL as
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen           password. */
bd4e36a8cd7257cca7d1434c49a1e343ed7c5100Timo Sirainen        if (sql_result_find_field(result, "password") < 0 &&
            sql_result_find_field(result, "password_noscheme") < 0) {
            auth_request_log_error(auth_request, "sql",
                "Password query must return a field named "
                "'password'");
        } else if (sql_result_next_row(result) > 0) {
            auth_request_log_error(auth_request, "sql",
                "Password query returned multiple matches");
        } else if (auth_request->passdb_password == NULL &&
               !auth_request->no_password) {
            auth_request_log_info(auth_request, "sql",
                "Empty password returned without nopassword");
            passdb_result = PASSDB_RESULT_PASSWORD_MISMATCH;
        } else {
            /* passdb_password may change on the way,
               so we'll need to strdup. */
            password = t_strdup(auth_request->passdb_password);
            passdb_result = PASSDB_RESULT_OK;
        }
    }

    scheme = password_get_scheme(&password);
    /* auth_request_set_field() sets scheme */
    i_assert(password == NULL || scheme != NULL);

    if (auth_request->credentials_scheme != NULL) {
        passdb_handle_credentials(passdb_result, password, scheme,
            sql_request->callback.lookup_credentials,
            auth_request);
        auth_request_unref(&auth_request);
        return;
    }

    /* verify plain */
    if (password == NULL) {
        sql_request->callback.verify_plain(passdb_result, auth_request);
        auth_request_unref(&auth_request);
        return;
    }

    ret = auth_request_password_verify(auth_request,
                       auth_request->mech_password,
                       password, scheme, "sql");

    sql_request->callback.verify_plain(ret > 0 ? PASSDB_RESULT_OK :
                       PASSDB_RESULT_PASSWORD_MISMATCH,
                       auth_request);
    auth_request_unref(&auth_request);
}

static const char *
passdb_sql_escape(const char *str, const struct auth_request *auth_request)
{
    struct passdb_module *_module = auth_request->passdb->passdb;
    struct sql_passdb_module *module = (struct sql_passdb_module *)_module;

    return sql_escape_string(module->conn->db, str);
}

static void sql_lookup_pass(struct passdb_sql_request *sql_request)
{
    struct passdb_module *_module =
        sql_request->auth_request->passdb->passdb;
    struct sql_passdb_module *module = (struct sql_passdb_module *)_module;
    string_t *query;

    query = t_str_new(512);
    var_expand(query, module->conn->set.password_query,
           auth_request_get_var_expand_table(sql_request->auth_request,
                             passdb_sql_escape));

    auth_request_log_debug(sql_request->auth_request, "sql",
                   "query: %s", str_c(query));

    auth_request_ref(sql_request->auth_request);
    sql_query(module->conn->db, str_c(query),
          sql_query_callback, sql_request);
}

static void sql_verify_plain(struct auth_request *request,
                 const char *password ATTR_UNUSED,
                 verify_plain_callback_t *callback)
{
    struct passdb_sql_request *sql_request;

    sql_request = p_new(request->pool, struct passdb_sql_request, 1);
    sql_request->auth_request = request;
    sql_request->callback.verify_plain = callback;

    sql_lookup_pass(sql_request);
}

static void sql_lookup_credentials(struct auth_request *request,
                   lookup_credentials_callback_t *callback)
{
    struct passdb_sql_request *sql_request;

    sql_request = p_new(request->pool, struct passdb_sql_request, 1);
    sql_request->auth_request = request;
    sql_request->callback.lookup_credentials = callback;

        sql_lookup_pass(sql_request);
}

static void sql_set_credentials_callback(const char *error,
                     struct passdb_sql_request *sql_request)
{
    struct passdb_module *_module =
        sql_request->auth_request->passdb->passdb;
    struct sql_passdb_module *module = (struct sql_passdb_module *)_module;

    if (error != NULL) {
        if (!module->conn->default_update_query) {
            auth_request_log_error(sql_request->auth_request, "sql",
                "Set credentials query failed: %s", error);
        } else {
            auth_request_log_error(sql_request->auth_request, "sql",
                "Set credentials query failed: %s"
                "(using built-in default update_query: %s)",
                error, module->conn->set.update_query);
        }
    }

    sql_request->callback.
        set_credentials(error == NULL, sql_request->auth_request);
    i_free(sql_request);
}

static int sql_set_credentials(struct auth_request *request,
                   const char *new_credentials,
                   set_credentials_callback_t *callback)
{
    struct sql_passdb_module *module =
        (struct sql_passdb_module *) request->passdb->passdb;
    struct sql_transaction_context *transaction;
    struct passdb_sql_request *sql_request;
    string_t *query;

    request->mech_password = p_strdup(request->pool, new_credentials);

    query = t_str_new(512);
    var_expand(query, module->conn->set.update_query,
           auth_request_get_var_expand_table(request,
                             passdb_sql_escape));

    sql_request = i_new(struct passdb_sql_request, 1);
    sql_request->auth_request = request;
    sql_request->callback.set_credentials = callback;

    transaction = sql_transaction_begin(module->conn->db);
    sql_update(transaction, str_c(query));
    sql_transaction_commit(&transaction,
                   sql_set_credentials_callback, sql_request);
    return 0;
}

static struct passdb_module *
passdb_sql_preinit(pool_t pool, const char *args)
{
    struct sql_passdb_module *module;
    struct sql_connection *conn;

    module = p_new(pool, struct sql_passdb_module, 1);
    module->conn = conn = db_sql_init(args, FALSE);

    module->module.cache_key =
        auth_cache_parse_key(pool, conn->set.password_query);
    module->module.default_pass_scheme = conn->set.default_pass_scheme;
    return &module->module;
}

static void passdb_sql_init(struct passdb_module *_module)
{
    struct sql_passdb_module *module =
        (struct sql_passdb_module *)_module;
    enum sql_db_flags flags;

    flags = sql_get_flags(module->conn->db);
    module->module.blocking = (flags & SQL_DB_FLAG_BLOCKING) != 0;

    if (!module->module.blocking || worker)
        db_sql_connect(module->conn);
    db_sql_check_userdb_warning(module->conn);
}

static void passdb_sql_deinit(struct passdb_module *_module)
{
    struct sql_passdb_module *module =
        (struct sql_passdb_module *)_module;

    db_sql_unref(&module->conn);
}

struct passdb_module_interface passdb_sql = {
    "sql",

    passdb_sql_preinit,
    passdb_sql_init,
    passdb_sql_deinit,

    sql_verify_plain,
    sql_lookup_credentials,
    sql_set_credentials
};
#else
struct passdb_module_interface passdb_sql = {
    .name = "sql"
};
#endif