1 /*
2 Copyright (C) 1999-2004 IC & S dbmail@ic-s.nl
3 Copyright (c) 2004-2010 NFG Net Facilities Group BV support@nfg.nl
4
5 This program is free software; you can redistribute it and/or
6 modify it under the terms of the GNU General Public License
7 as published by the Free Software Foundation; either
8 version 2 of the License, or (at your option) any later
9 version.
10
11 This program is distributed in the hope that it will be useful,
12 but WITHOUT ANY WARRANTY; without even the implied warranty of
13 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 GNU General Public License for more details.
15
16 You should have received a copy of the GNU General Public License
17 along with this program; if not, write to the Free Software
18 Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
19 */
20
21 /**
22 * \file authsql.c
23 * \brief implements SQL authentication. Prototypes of these functions
24 * can be found in auth.h .
25 */
26
27 #include "dbmail.h"
28 #define THIS_MODULE "auth"
29
30 extern DBParam_T db_params;
31 #define DBPFX db_params.pfx
32 #define P ConnectionPool_T
33 #define S PreparedStatement_T
34 #define R ResultSet_T
35 #define C Connection_T
36 #define U URL_T
37
38
auth_connect()39 int auth_connect()
40 {
41 /* this function is only called after a connection has been made
42 * if, in the future this is not the case, db.h should export a
43 * function that enables checking for the database connection
44 */
45 return 0;
46 }
47
auth_disconnect()48 int auth_disconnect()
49 {
50 return 0;
51 }
52
auth_user_exists(const char * username,uint64_t * user_idnr)53 int auth_user_exists(const char *username, uint64_t * user_idnr)
54 {
55 return db_user_exists(username, user_idnr);
56 }
57
auth_get_known_users(void)58 GList * auth_get_known_users(void)
59 {
60 GList * users = NULL;
61 C c; R r;
62
63 c = db_con_get();
64 TRY
65 r = db_query(c, "SELECT userid FROM %susers ORDER BY userid",DBPFX);
66 while (db_result_next(r))
67 users = g_list_append(users, g_strdup(db_result_get(r, 0)));
68 CATCH(SQLException)
69 LOG_SQLERROR;
70 FINALLY
71 db_con_close(c);
72 END_TRY;
73
74 return users;
75 }
76
auth_get_known_aliases(void)77 GList * auth_get_known_aliases(void)
78 {
79 GList * aliases = NULL;
80 C c; R r;
81
82 c = db_con_get();
83 TRY
84 r = db_query(c,"SELECT alias FROM %saliases ORDER BY alias",DBPFX);
85 while (db_result_next(r))
86 aliases = g_list_append(aliases, g_strdup(db_result_get(r,0)));
87 CATCH(SQLException)
88 LOG_SQLERROR;
89 FINALLY
90 db_con_close(c);
91 END_TRY;
92
93 return aliases;
94 }
95
auth_getclientid(uint64_t user_idnr,uint64_t * client_idnr)96 int auth_getclientid(uint64_t user_idnr, uint64_t * client_idnr)
97 {
98 assert(client_idnr != NULL);
99 *client_idnr = 0;
100 C c; R r; volatile int t = TRUE;
101
102 c = db_con_get();
103 TRY
104 r = db_query(c, "SELECT client_idnr FROM %susers WHERE user_idnr = %" PRIu64 "",DBPFX, user_idnr);
105 if (db_result_next(r))
106 *client_idnr = db_result_get_u64(r,0);
107 CATCH(SQLException)
108 LOG_SQLERROR;
109 t = DM_EQUERY;
110 FINALLY
111 db_con_close(c);
112 END_TRY;
113
114 return t;
115 }
116
auth_getmaxmailsize(uint64_t user_idnr,uint64_t * maxmail_size)117 int auth_getmaxmailsize(uint64_t user_idnr, uint64_t * maxmail_size)
118 {
119 assert(maxmail_size != NULL);
120 *maxmail_size = 0;
121 C c; R r; volatile int t = TRUE;
122
123 c = db_con_get();
124 TRY
125 r = db_query(c, "SELECT maxmail_size FROM %susers WHERE user_idnr = %" PRIu64 "",DBPFX, user_idnr);
126 if (db_result_next(r))
127 *maxmail_size = db_result_get_u64(r,0);
128 CATCH(SQLException)
129 LOG_SQLERROR;
130 t = DM_EQUERY;
131 FINALLY
132 db_con_close(c);
133 END_TRY;
134
135 return t;
136 }
137
138
auth_getencryption(uint64_t user_idnr)139 char *auth_getencryption(uint64_t user_idnr)
140 {
141 char *res = NULL;
142 C c; R r;
143
144 assert(user_idnr > 0);
145 c = db_con_get();
146 TRY
147 r = db_query(c, "SELECT encryption_type FROM %susers WHERE user_idnr = %" PRIu64 "",DBPFX, user_idnr);
148 if (db_result_next(r))
149 res = g_strdup(db_result_get(r,0));
150 CATCH(SQLException)
151 LOG_SQLERROR;
152 FINALLY
153 db_con_close(c);
154 END_TRY;
155
156 return res;
157 }
158
159
user_get_deliver_to(const char * username)160 static GList *user_get_deliver_to(const char *username)
161 {
162 INIT_QUERY;
163 C c; R r; S s;
164 GList *d = NULL;
165
166 snprintf(query, DEF_QUERYSIZE-1,
167 "SELECT deliver_to FROM %saliases "
168 "WHERE lower(alias) = lower(?) "
169 "AND lower(alias) <> lower(deliver_to)",
170 DBPFX);
171
172 c = db_con_get();
173 TRY
174 s = db_stmt_prepare(c, query);
175 db_stmt_set_str(s, 1, username);
176
177 r = db_stmt_query(s);
178 while (db_result_next(r))
179 d = g_list_prepend(d, g_strdup(db_result_get(r,0)));
180 CATCH(SQLException)
181 LOG_SQLERROR;
182 FINALLY
183 db_con_close(c);
184 END_TRY;
185
186 return d;
187 }
188
189
auth_check_user_ext(const char * username,GList ** userids,GList ** fwds,int checks)190 int auth_check_user_ext(const char *username, GList **userids, GList **fwds, int checks)
191 {
192 int occurences = 0;
193 GList *d = NULL;
194 char *endptr;
195 uint64_t id, *uid;
196
197 if (checks > 20) {
198 TRACE(TRACE_ERR,"too many checks. Possible loop detected.");
199 return 0;
200 }
201
202 TRACE(TRACE_DEBUG, "[%d] checking user [%s] in alias table", checks, username);
203
204 d = user_get_deliver_to(username);
205
206 if (! d) {
207 if (checks == 0) {
208 TRACE(TRACE_DEBUG, "user %s not in aliases table", username);
209 return 0;
210 }
211 /* found the last one, this is the deliver to
212 * but checks needs to be bigger then 0 because
213 * else it could be the first query failure */
214 id = strtoull(username, &endptr, 10);
215 if (*endptr == 0) {
216 /* numeric deliver-to --> this is a userid */
217 uid = g_new0(uint64_t,1);
218 *uid = id;
219 *(GList **)userids = g_list_prepend(*(GList **)userids, uid);
220
221 } else {
222 *(GList **)fwds = g_list_prepend(*(GList **)fwds, g_strdup(username));
223 }
224 TRACE(TRACE_DEBUG, "adding [%s] to deliver_to address", username);
225 return 1;
226 }
227
228 while (d) {
229 /* do a recursive search for deliver_to */
230 char *deliver_to = (char *)d->data;
231 TRACE(TRACE_DEBUG, "checking user %s to %s", username, deliver_to);
232
233 occurences += auth_check_user_ext(deliver_to, userids, fwds, checks+1);
234
235 if (! g_list_next(d)) break;
236 d = g_list_next(d);
237 }
238
239 g_list_destroy(d);
240
241 return occurences;
242 }
243
auth_adduser(const char * username,const char * password,const char * enctype,uint64_t clientid,uint64_t maxmail,uint64_t * user_idnr)244 int auth_adduser(const char *username, const char *password, const char *enctype,
245 uint64_t clientid, uint64_t maxmail, uint64_t * user_idnr)
246 {
247 *user_idnr=0;
248 return db_user_create(username, password, enctype, clientid, maxmail, user_idnr);
249 }
250
251
auth_delete_user(const char * username)252 int auth_delete_user(const char *username)
253 {
254 return db_user_delete(username);
255 }
256
257
auth_change_username(uint64_t user_idnr,const char * new_name)258 int auth_change_username(uint64_t user_idnr, const char *new_name)
259 {
260 return db_user_rename(user_idnr, new_name);
261 }
262
auth_change_password(uint64_t user_idnr,const char * new_pass,const char * enctype)263 int auth_change_password(uint64_t user_idnr, const char *new_pass, const char *enctype)
264 {
265 C c; S s; volatile int t = FALSE;
266 const char *encoding = enctype?enctype:"";
267
268 if (strlen(new_pass) > 128) {
269 TRACE(TRACE_ERR, "new password length is insane");
270 return -1;
271 }
272
273 c = db_con_get();
274 TRY
275 s = db_stmt_prepare(c, "UPDATE %susers SET passwd = ?, encryption_type = ? WHERE user_idnr=?", DBPFX);
276 db_stmt_set_str(s, 1, new_pass);
277 db_stmt_set_str(s, 2, encoding);
278 db_stmt_set_u64(s, 3, user_idnr);
279 db_stmt_exec(s);
280 t = TRUE;
281 CATCH(SQLException)
282 LOG_SQLERROR;
283 t = DM_EQUERY;
284 FINALLY
285 db_con_close(c);
286 END_TRY;
287
288 return t;
289 }
290
auth_change_clientid(uint64_t user_idnr,uint64_t new_cid)291 int auth_change_clientid(uint64_t user_idnr, uint64_t new_cid)
292 {
293 return db_update("UPDATE %susers SET client_idnr = %" PRIu64 " WHERE user_idnr=%" PRIu64 "", DBPFX, new_cid, user_idnr);
294 }
295
auth_change_mailboxsize(uint64_t user_idnr,uint64_t new_size)296 int auth_change_mailboxsize(uint64_t user_idnr, uint64_t new_size)
297 {
298 return db_change_mailboxsize(user_idnr, new_size);
299 }
300
301 #define CONSTNULL(a) ((! a) || (a && (! a[0])))
302
auth_validate(ClientBase_T * ci,const char * username,const char * password,uint64_t * user_idnr)303 int auth_validate(ClientBase_T *ci, const char *username, const char *password, uint64_t * user_idnr)
304 {
305 char real_username[DM_USERNAME_LEN];
306 const char *tuser;
307 int result;
308
309 memset(real_username,0,sizeof(real_username));
310
311 assert(user_idnr != NULL);
312 *user_idnr = 0;
313
314 tuser = username;
315 if (CONSTNULL(tuser) || CONSTNULL(password)) {
316 if (ci && ci->auth) { // CRAM-MD5
317 tuser = (char *)Cram_getUsername(ci->auth);
318 } else {
319 TRACE(TRACE_DEBUG, "username or password is empty");
320 return FALSE;
321 }
322 }
323
324 /* the shared mailbox user should not log in! */
325 if (strcmp(tuser, PUBLIC_FOLDER_USER) == 0) return 0;
326
327 strncpy(real_username, tuser, DM_USERNAME_LEN-1);
328 if (db_use_usermap()) { /* use usermap */
329 result = db_usermap_resolve(ci, tuser, real_username);
330 if (result == DM_EGENERAL)
331 return 0;
332 if (result == DM_EQUERY)
333 return DM_EQUERY;
334 }
335
336 /* lookup the user_idnr */
337 if (! auth_user_exists(real_username, user_idnr))
338 return FALSE;
339
340 if (! db_user_active(*user_idnr))
341 return FALSE;
342
343 int valid = 0;
344 if (! (valid = db_user_validate(ci, "passwd", user_idnr, password))) {
345 if ((valid = db_user_validate(ci, "spasswd", user_idnr, password)))
346 db_user_security_trigger(*user_idnr);
347 }
348 if (! valid)
349 *user_idnr = 0;
350
351 return valid;
352
353 }
354
auth_md5_validate(ClientBase_T * ci UNUSED,char * username,unsigned char * md5_apop_he,char * apop_stamp)355 uint64_t auth_md5_validate(ClientBase_T *ci UNUSED, char *username,
356 unsigned char *md5_apop_he, char *apop_stamp)
357 {
358 /* returns useridnr on OK, 0 on validation failed, -1 on error */
359 char checkstring[FIELDSIZE];
360 char hash[FIELDSIZE];
361 uint64_t user_idnr = 0;
362 const char *dbpass;
363 C c; R r;
364 volatile int t = FALSE;
365
366 /* lookup the user_idnr */
367 if (! auth_user_exists(username, &user_idnr))
368 return DM_EQUERY;
369
370 c = db_con_get();
371 TRY
372 r = db_query(c, "SELECT passwd FROM %susers WHERE user_idnr = %" PRIu64 "", DBPFX, user_idnr);
373 if (db_result_next(r)) { /* user found */
374 /* now authenticate using MD5 hash comparisation */
375 dbpass = db_result_get(r,0); /* value holds the password */
376
377 TRACE(TRACE_DEBUG, "apop_stamp=[%s], userpw=[%s]", apop_stamp, dbpass);
378
379 memset(hash, 0, sizeof(hash));
380 memset(checkstring, 0, sizeof(checkstring));
381 g_snprintf(checkstring, FIELDSIZE-1, "%s%s", apop_stamp, dbpass);
382 dm_md5(checkstring, hash);
383
384 TRACE(TRACE_DEBUG, "checkstring for md5 [%s] -> result [%s]", checkstring, hash);
385 TRACE(TRACE_DEBUG, "validating md5_apop_we=[%s] md5_apop_he=[%s]", hash, md5_apop_he);
386
387 if (strcmp((char *)md5_apop_he, hash) == 0) {
388 TRACE(TRACE_NOTICE, "user [%s] is validated using APOP", username);
389 } else {
390 user_idnr = 0; // failed
391 }
392 } else {
393 user_idnr = 0;
394 }
395 CATCH(SQLException)
396 LOG_SQLERROR;
397 t = DM_EQUERY;
398 FINALLY
399 db_con_close(c);
400 END_TRY;
401
402 if (t == DM_EQUERY) return t;
403
404 if (user_idnr == 0)
405 TRACE(TRACE_NOTICE, "user [%s] could not be validated", username);
406 else
407 db_user_log_login(user_idnr);
408
409 return user_idnr;
410 }
411
auth_get_userid(uint64_t user_idnr)412 char *auth_get_userid(uint64_t user_idnr)
413 {
414 C c; R r;
415 char *result = NULL;
416 c = db_con_get();
417
418 TRY
419 r = db_query(c, "SELECT userid FROM %susers WHERE user_idnr = %" PRIu64 "", DBPFX, user_idnr);
420 if (db_result_next(r))
421 result = g_strdup(db_result_get(r,0));
422 CATCH(SQLException)
423 LOG_SQLERROR;
424 FINALLY
425 db_con_close(c);
426 END_TRY;
427
428 return result;
429 }
430
auth_check_userid(uint64_t user_idnr)431 int auth_check_userid(uint64_t user_idnr)
432 {
433 C c; R r; volatile gboolean t = TRUE;
434
435 c = db_con_get();
436 TRY
437 r = db_query(c, "SELECT userid FROM %susers WHERE user_idnr = %" PRIu64 "", DBPFX, user_idnr);
438 if (db_result_next(r))
439 t = FALSE;
440 CATCH(SQLException)
441 LOG_SQLERROR;
442 FINALLY
443 db_con_close(c);
444 END_TRY;
445
446 return t;
447 }
448
auth_addalias(uint64_t user_idnr,const char * alias,uint64_t clientid)449 int auth_addalias(uint64_t user_idnr, const char *alias, uint64_t clientid)
450 {
451 C c; R r; S s; volatile int t = FALSE;
452 INIT_QUERY;
453
454 /* check if this alias already exists */
455 snprintf(query, DEF_QUERYSIZE-1,
456 "SELECT alias_idnr FROM %saliases "
457 "WHERE lower(alias) = lower(?) AND deliver_to = ? "
458 "AND client_idnr = ?",DBPFX);
459
460 c = db_con_get();
461 TRY
462 s = db_stmt_prepare(c,query);
463 db_stmt_set_str(s, 1, alias);
464 db_stmt_set_u64(s, 2, user_idnr);
465 db_stmt_set_u64(s, 3, clientid);
466
467 r = db_stmt_query(s);
468
469 if (db_result_next(r)) {
470 TRACE(TRACE_INFO, "alias [%s] for user [%" PRIu64 "] already exists", alias, user_idnr);
471 t = TRUE;
472 }
473 CATCH(SQLException)
474 LOG_SQLERROR;
475 t = DM_EQUERY;
476 END_TRY;
477
478 if (t) {
479 db_con_close(c);
480 return t;
481 }
482
483 db_con_clear(c);
484
485 TRY
486 s = db_stmt_prepare(c, "INSERT INTO %saliases (alias,deliver_to,client_idnr) VALUES (?,?,?)",DBPFX);
487 db_stmt_set_str(s, 1, alias);
488 db_stmt_set_u64(s, 2, user_idnr);
489 db_stmt_set_u64(s, 3, clientid);
490
491 db_stmt_exec(s);
492 t = TRUE;
493 CATCH(SQLException)
494 LOG_SQLERROR;
495 t = DM_EQUERY;
496 FINALLY
497 db_con_close(c);
498 END_TRY;
499
500 return t;
501 }
502
auth_addalias_ext(const char * alias,const char * deliver_to,uint64_t clientid)503 int auth_addalias_ext(const char *alias,
504 const char *deliver_to, uint64_t clientid)
505 {
506 C c; R r; S s;
507 volatile int t = FALSE;
508 INIT_QUERY;
509
510 c = db_con_get();
511 TRY
512 /* check if this alias already exists */
513 if (clientid != 0) {
514 snprintf(query, DEF_QUERYSIZE-1,
515 "SELECT alias_idnr FROM %saliases "
516 "WHERE lower(alias) = lower(?) AND "
517 "lower(deliver_to) = lower(?) "
518 "AND client_idnr = ? ",DBPFX);
519
520 s = db_stmt_prepare(c, query);
521 db_stmt_set_str(s, 1, alias);
522 db_stmt_set_str(s, 2, deliver_to);
523 db_stmt_set_u64(s, 3, clientid);
524
525 } else {
526 snprintf(query, DEF_QUERYSIZE-1,
527 "SELECT alias_idnr FROM %saliases "
528 "WHERE lower(alias) = lower(?) "
529 "AND lower(deliver_to) = lower(?) ",DBPFX);
530
531 s = db_stmt_prepare(c,query);
532 db_stmt_set_str(s, 1, alias);
533 db_stmt_set_str(s, 2, deliver_to);
534 }
535
536 r = db_stmt_query(s);
537 if (db_result_next(r)) {
538 TRACE(TRACE_INFO, "alias [%s] --> [%s] already exists", alias, deliver_to);
539 t = TRUE;
540 }
541 CATCH(SQLException)
542 LOG_SQLERROR;
543 t = DM_EQUERY;
544 END_TRY;
545
546 if (t) {
547 db_con_close(c);
548 return t;
549 }
550
551 db_con_clear(c);
552
553 TRY
554 s = db_stmt_prepare(c, "INSERT INTO %saliases (alias,deliver_to,client_idnr) VALUES (?,?,?)",DBPFX);
555 db_stmt_set_str(s, 1, alias);
556 db_stmt_set_str(s, 2, deliver_to);
557 db_stmt_set_u64(s, 3, clientid);
558
559 db_stmt_exec(s);
560 t = TRUE;
561 CATCH(SQLException)
562 LOG_SQLERROR;
563 t = DM_EQUERY;
564 FINALLY
565 db_con_close(c);
566 END_TRY;
567
568 return t;
569 }
570
auth_removealias(uint64_t user_idnr,const char * alias)571 int auth_removealias(uint64_t user_idnr, const char *alias)
572 {
573 C c; S s; volatile gboolean t = FALSE;
574
575 c = db_con_get();
576 TRY
577 s = db_stmt_prepare(c, "DELETE FROM %saliases WHERE deliver_to=? AND lower(alias) = lower(?)",DBPFX);
578 /* convert to string to string, due to the nature of deliver_to field */
579 char user_idnr_str[12];
580 sprintf(user_idnr_str, "%ld", user_idnr);
581 db_stmt_set_str(s, 1, user_idnr_str);
582 db_stmt_set_str(s, 2, alias);
583 db_stmt_exec(s);
584 t = TRUE;
585 CATCH(SQLException)
586 LOG_SQLERROR;
587 FINALLY
588 db_con_close(c);
589 END_TRY;
590
591 return t;
592 }
593
auth_removealias_ext(const char * alias,const char * deliver_to)594 int auth_removealias_ext(const char *alias, const char *deliver_to)
595 {
596 C c; S s; volatile gboolean t = FALSE;
597
598 c = db_con_get();
599 TRY
600 s = db_stmt_prepare(c, "DELETE FROM %saliases WHERE lower(deliver_to) = lower(?) AND lower(alias) = lower(?)", DBPFX);
601 db_stmt_set_str(s, 1, deliver_to);
602 db_stmt_set_str(s, 2, alias);
603 db_stmt_exec(s);
604 t = TRUE;
605 CATCH(SQLException)
606 LOG_SQLERROR;
607 FINALLY
608 db_con_close(c);
609 END_TRY;
610
611 return t;
612 }
613
auth_get_user_aliases(uint64_t user_idnr)614 GList * auth_get_user_aliases(uint64_t user_idnr)
615 {
616 C c; R r;
617 GList *l = NULL;
618
619 c = db_con_get();
620 TRY
621 r = db_query(c, "SELECT alias FROM %saliases WHERE deliver_to = '%" PRIu64 "' "
622 "UNION SELECT a2.alias FROM %saliases a1 JOIN %saliases a2 "
623 "ON (a1.alias = a2.deliver_to) "
624 "WHERE a1.deliver_to='%" PRIu64 "' AND a2.deliver_to IS NOT NULL "
625 "ORDER BY alias DESC",
626 DBPFX, user_idnr, DBPFX, DBPFX, user_idnr);
627 while (db_result_next(r))
628 l = g_list_prepend(l,g_strdup(db_result_get(r,0)));
629 CATCH(SQLException)
630 LOG_SQLERROR;
631 FINALLY
632 db_con_close(c);
633 END_TRY;
634
635 return l;
636 }
637
auth_get_aliases_ext(const char * alias)638 GList * auth_get_aliases_ext(const char *alias)
639 {
640 C c; R r;
641 GList *l = NULL;
642
643 c = db_con_get();
644 TRY
645 r = db_query(c, "SELECT deliver_to FROM %saliases WHERE alias = '%s' ORDER BY alias DESC",DBPFX, alias);
646 while (db_result_next(r))
647 l = g_list_prepend(l,g_strdup(db_result_get(r,0)));
648 CATCH(SQLException)
649 LOG_SQLERROR;
650 FINALLY
651 db_con_close(c);
652 END_TRY;
653
654 return l;
655 }
656
auth_requires_shadow_user(void)657 gboolean auth_requires_shadow_user(void)
658 {
659 return FALSE;
660 }
661
662 #undef P
663 #undef S
664 #undef R
665 #undef C
666 #undef U
667
668
669