src/auth/passdb-sql.c

	passdb-sql.c revision 7bafda1813454621e03615e83d55bccfa7cc56bd
76b43e4417bab52e913da39b5f5bc2a130d3f149Timo Sirainen/* Copyright (c) 2004-2009 Dovecot authors, see the included COPYING file */
25757faf029c369a8318349dafe952e2358df1d8Timo Sirainen
25757faf029c369a8318349dafe952e2358df1d8Timo Sirainen#include "auth-common.h"
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen#include "passdb.h"
eff552f5fdc275c940c4c709eeeddb833bc51b40Timo Sirainen
25757faf029c369a8318349dafe952e2358df1d8Timo Sirainen#ifdef PASSDB_SQL
25757faf029c369a8318349dafe952e2358df1d8Timo Sirainen
25757faf029c369a8318349dafe952e2358df1d8Timo Sirainen#include "str.h"
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen#include "strescape.h"
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen#include "var-expand.h"
25757faf029c369a8318349dafe952e2358df1d8Timo Sirainen#include "safe-memset.h"
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen#include "password-scheme.h"
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainen#include "auth-cache.h"
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen#include "db-sql.h"
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen#include <stdlib.h>
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen#include <string.h>
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainenstruct sql_passdb_module {
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    struct passdb_module module;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    struct sql_connection *conn;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen};
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainenstruct passdb_sql_request {
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    struct auth_request *auth_request;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    union {
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen        verify_plain_callback_t *verify_plain;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen                lookup_credentials_callback_t *lookup_credentials;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen        set_credentials_callback_t *set_credentials;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    } callback;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen};
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainenstatic void sql_query_save_results(struct sql_result *result,
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen                   struct passdb_sql_request *sql_request)
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen{
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    struct auth_request *auth_request = sql_request->auth_request;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    struct passdb_module *_module = auth_request->passdb->passdb;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    struct sql_passdb_module *module = (struct sql_passdb_module *)_module;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    unsigned int i, fields_count;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    const char *name, *value;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen        fields_count = sql_result_get_fields_count(result);
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    for (i = 0; i < fields_count; i++) {
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen        name = sql_result_get_field_name(result, i);
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen        value = sql_result_get_field_value(result, i);
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen        if (*name != '\0' && value != NULL) {
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen            auth_request_set_field(auth_request, name, value,
1f1e81aab38d833d1c9cdc244c91fd762e0080d4Timo Sirainen                module->conn->set.default_pass_scheme);
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen        }
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen    }
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen}
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainenstatic void sql_query_callback(struct sql_result *result,
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen                   struct passdb_sql_request *sql_request)
137ea7ca34005345aa2304a940149b7f3774d727Timo Sirainen{
137ea7ca34005345aa2304a940149b7f3774d727Timo Sirainen    struct auth_request *auth_request = sql_request->auth_request;
137ea7ca34005345aa2304a940149b7f3774d727Timo Sirainen    enum passdb_result passdb_result;
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen    const char *user, *password, *scheme;
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen    int ret;
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen    passdb_result = PASSDB_RESULT_INTERNAL_FAILURE;
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen    user = auth_request->user;
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen    password = NULL;
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen    ret = sql_result_next_row(result);
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen    if (ret < 0) {
49e513d090753ccbf95560b2f3a21f081a5b6c51Timo Sirainen        auth_request_log_error(auth_request, "sql",
73e19ec2d5069ea125dcd1ede5d8a70f701fd9a8Timo Sirainen                       "Password query failed: %s",
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen                       sql_result_get_error(result));
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen    } else if (ret == 0) {
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen        auth_request_log_info(auth_request, "sql", "unknown user");
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen        passdb_result = PASSDB_RESULT_USER_UNKNOWN;
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen    } else {
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen        sql_query_save_results(result, sql_request);
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen        /* Note that we really want to check if the password field is
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen           found. Just checking if password is set isn't enough,
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen           because with proxies we might want to return NULL as
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen           password. */
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen        if (sql_result_find_field(result, "password") < 0) {
137ea7ca34005345aa2304a940149b7f3774d727Timo Sirainen            auth_request_log_error(auth_request, "sql",
ccfc6441cadb577084daeb1f0aa3dd7bdfa2a220Timo Sirainen                "Password query must return a field named "
137ea7ca34005345aa2304a940149b7f3774d727Timo Sirainen                "'password'");
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen        } else if (sql_result_next_row(result) > 0) {
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen            auth_request_log_error(auth_request, "sql",
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen                "Password query returned multiple matches");
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen        } else if (auth_request->passdb_password == NULL &&
2521482f3f897c83f7d5a2f9e17fe99fa4ba2cbeTimo Sirainen               !auth_request->no_password) {
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen            auth_request_log_info(auth_request, "sql",
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen                "Empty password returned without nopassword");
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen            passdb_result = PASSDB_RESULT_PASSWORD_MISMATCH;
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen        } else {
49e513d090753ccbf95560b2f3a21f081a5b6c51Timo Sirainen            /* passdb_password may change on the way,
73e19ec2d5069ea125dcd1ede5d8a70f701fd9a8Timo Sirainen               so we'll need to strdup. */
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen            password = t_strdup(auth_request->passdb_password);
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen            passdb_result = PASSDB_RESULT_OK;
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen        }
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen    }
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen
12aad74464367f7e8be11eafe1af985bf7b1adecTimo Sirainen    scheme = password_get_scheme(&password);
e9503210d3521a6833ed62dc332fc42ffb0e7a13Timo Sirainen    /* auth_request_set_field() sets scheme */
25757faf029c369a8318349dafe952e2358df1d8Timo Sirainen    i_assert(password == NULL || scheme != NULL);
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
e9503210d3521a6833ed62dc332fc42ffb0e7a13Timo Sirainen    if (auth_request->credentials_scheme != NULL) {
965ed6ea3fc8f7637bd0d159d2fdb283a191ce34Timo Sirainen        passdb_handle_credentials(passdb_result, password, scheme,
91e4199476cb2add8143c18583fa57e1decfea88Timo Sirainen            sql_request->callback.lookup_credentials,
0727e38ac12efb8963a339daf56255e2be1f29fcTimo Sirainen            auth_request);
e9503210d3521a6833ed62dc332fc42ffb0e7a13Timo Sirainen        auth_request_unref(&auth_request);
e9503210d3521a6833ed62dc332fc42ffb0e7a13Timo Sirainen        return;
e9503210d3521a6833ed62dc332fc42ffb0e7a13Timo Sirainen    }
e9503210d3521a6833ed62dc332fc42ffb0e7a13Timo Sirainen
e9503210d3521a6833ed62dc332fc42ffb0e7a13Timo Sirainen    /* verify plain */
e9503210d3521a6833ed62dc332fc42ffb0e7a13Timo Sirainen    if (password == NULL) {
e9503210d3521a6833ed62dc332fc42ffb0e7a13Timo Sirainen        sql_request->callback.verify_plain(passdb_result, auth_request);
08aea01ef9a9d20703e0fcf8618e6195c0037a44Timo Sirainen        auth_request_unref(&auth_request);
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen        return;
d5abbb932a0a598f002da39a8b3326643b1b5efcTimo Sirainen    }
d5abbb932a0a598f002da39a8b3326643b1b5efcTimo Sirainen
d5abbb932a0a598f002da39a8b3326643b1b5efcTimo Sirainen    ret = auth_request_password_verify(auth_request,
d5abbb932a0a598f002da39a8b3326643b1b5efcTimo Sirainen                       auth_request->mech_password,
747e77e3ab073a8e9e69c7a3e71b4593c5655d03Timo Sirainen                       password, scheme, "sql");
747e77e3ab073a8e9e69c7a3e71b4593c5655d03Timo Sirainen
25757faf029c369a8318349dafe952e2358df1d8Timo Sirainen    sql_request->callback.verify_plain(ret > 0 ? PASSDB_RESULT_OK :
dd93aba1901a457346990f49c54a738947dc7128Timo Sirainen                       PASSDB_RESULT_PASSWORD_MISMATCH,
dd93aba1901a457346990f49c54a738947dc7128Timo Sirainen                       auth_request);
dd93aba1901a457346990f49c54a738947dc7128Timo Sirainen    auth_request_unref(&auth_request);
dd93aba1901a457346990f49c54a738947dc7128Timo Sirainen}
dd93aba1901a457346990f49c54a738947dc7128Timo Sirainen
dd93aba1901a457346990f49c54a738947dc7128Timo Sirainenstatic const char *
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainenpassdb_sql_escape(const char *str, const struct auth_request *auth_request)
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainen{
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainen    struct passdb_module *_module = auth_request->passdb->passdb;
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainen    struct sql_passdb_module *module = (struct sql_passdb_module *)_module;
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainen
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainen    return sql_escape_string(module->conn->db, str);
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainen}
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainen
08aea01ef9a9d20703e0fcf8618e6195c0037a44Timo Sirainenstatic void sql_lookup_pass(struct passdb_sql_request *sql_request)
08aea01ef9a9d20703e0fcf8618e6195c0037a44Timo Sirainen{
e9503210d3521a6833ed62dc332fc42ffb0e7a13Timo Sirainen    struct passdb_module *_module =
08aea01ef9a9d20703e0fcf8618e6195c0037a44Timo Sirainen        sql_request->auth_request->passdb->passdb;
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainen    struct sql_passdb_module *module = (struct sql_passdb_module *)_module;
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainen    string_t *query;
eff552f5fdc275c940c4c709eeeddb833bc51b40Timo Sirainen
eff552f5fdc275c940c4c709eeeddb833bc51b40Timo Sirainen    query = t_str_new(512);
eff552f5fdc275c940c4c709eeeddb833bc51b40Timo Sirainen    var_expand(query, module->conn->set.password_query,
eff552f5fdc275c940c4c709eeeddb833bc51b40Timo Sirainen           auth_request_get_var_expand_table(sql_request->auth_request,
eff552f5fdc275c940c4c709eeeddb833bc51b40Timo Sirainen                             passdb_sql_escape));
25757faf029c369a8318349dafe952e2358df1d8Timo Sirainen
25757faf029c369a8318349dafe952e2358df1d8Timo Sirainen    auth_request_log_debug(sql_request->auth_request, "sql",
e9503210d3521a6833ed62dc332fc42ffb0e7a13Timo Sirainen                   "query: %s", str_c(query));
25757faf029c369a8318349dafe952e2358df1d8Timo Sirainen
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainen    auth_request_ref(sql_request->auth_request);
b42f37ae6f65ed986315b6885568d32115e589b1Timo Sirainen    sql_query(module->conn->db, str_c(query),
25757faf029c369a8318349dafe952e2358df1d8Timo Sirainen          sql_query_callback, sql_request);
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen}
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainenstatic void sql_verify_plain(struct auth_request *request,
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen                 const char *password ATTR_UNUSED,
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen                 verify_plain_callback_t *callback)
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen{
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    struct passdb_sql_request *sql_request;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    sql_request = p_new(request->pool, struct passdb_sql_request, 1);
51ead2f4c04ee85615d23c453924633b9ed8a4c2Timo Sirainen    sql_request->auth_request = request;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    sql_request->callback.verify_plain = callback;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    sql_lookup_pass(sql_request);
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen}
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainenstatic void sql_lookup_credentials(struct auth_request *request,
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen                   lookup_credentials_callback_t *callback)
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen{
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    struct passdb_sql_request *sql_request;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    sql_request = p_new(request->pool, struct passdb_sql_request, 1);
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen    sql_request->auth_request = request;
51ead2f4c04ee85615d23c453924633b9ed8a4c2Timo Sirainen    sql_request->callback.lookup_credentials = callback;
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen        sql_lookup_pass(sql_request);
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen}
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainenstatic void sql_set_credentials_callback(const char *error,
43a66a0b16299bd4f7615acd85e98bd3832c54d5Timo Sirainen                     struct passdb_sql_request *sql_request)
{
    if (error != NULL) {
        auth_request_log_error(sql_request->auth_request, "sql",
                       "Set credentials query failed: %s",
                       error);
    }

    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(struct auth_passdb *auth_passdb, const char *args)
{
    struct sql_passdb_module *module;
    struct sql_connection *conn;

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

    module->module.cache_key =
        auth_cache_parse_key(auth_passdb->auth->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,
                const char *args ATTR_UNUSED)
{
    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)
                sql_connect(module->conn->db);
}

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