authdb-soci.cc 17.3 KB
Newer Older
1
/*
2 3
	Flexisip, a flexible SIP proxy server with media capabilities.
	Copyright (C) 2010-2015  Belledonne Communications SARL, All rights reserved.
4

5 6 7 8
	This program is free software: you can redistribute it and/or modify
	it under the terms of the GNU Affero General Public License as
	published by the Free Software Foundation, either version 3 of the
	License, or (at your option) any later version.
9

10 11 12 13
	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 Affero General Public License for more details.
14

15 16
	You should have received a copy of the GNU Affero General Public License
	along with this program.  If not, see <http://www.gnu.org/licenses/>.
17 18 19
*/

#include "authdb.hh"
20
#include "soci/mysql/soci-mysql.h"
21 22 23
#include <thread>

using namespace soci;
24 25 26

// The dreaded chrono::steady_clock which is not supported for gcc < 4.7
#include <chrono>
27
using namespace std;
28
using namespace chrono;
29 30 31 32 33
#ifdef USE_MONOTONIC_CLOCK
namespace std {
typedef monotonic_clock steady_clock;
}
#endif
34
using namespace flexisip;
35

36 37
void SociAuthDB::declareConfig(GenericStruct *mc) {
	// ODBC-specific configuration keys
38 39 40
	ConfigItemDescriptor items[] = {

		{String, "soci-password-request",
41 42 43 44 45 46 47 48 49 50 51 52 53
			"Soci SQL request to execute to obtain the password and algorithm.\n"
			"Named parameters are:\n -':id' : the user found in the from header,\n -':domain' : the authorization realm, "
			"and\n -':authid' : the authorization username.\n"
			"The use of the :id parameter is mandatory.\n"
			"The output of this request MUST contain two columns in this order:\n"
			"\t- the password column\n"
			"\t- the algorithm associated column: it can be a column in the database or an explicitly specified value among these ('CLRTXT', 'MD5', 'SHA-256')\n"
			"Examples: \n"
			" - the password and algorithm are both available in the database\n"
			"\tselect password, algorithm from accounts where login = :id and domain = :domain\n"
			" - all the passwords from the database are MD5\n"
			"\t select password, 'MD5' from accounts where login = :id and domain = :domain",
			"select password, 'MD5' from accounts where login = :id and domain = :domain"},
54

55
		{String, "soci-user-with-phone-request",
56 57 58 59 60
			"Soci SQL request to execute to obtain the username associated with a phone alias.\n"
			"Named parameters are:\n -':phone' : the phone number to search for.\n"
			"The use of the :phone parameter is mandatory.\n"
			"Example : select login from accounts where phone = :phone ",
			""},
61

62
		{String, "soci-users-with-phones-request",
63 64 65 66 67 68
			"Soci SQL request to execute to obtain the usernames associated with phones aliases.\n"
			"Named parameters are:\n -':phones' : the phones to search for.\n"
			"The use of the :phones parameter is mandatory.\n"
			"If you use phone number linked accounts you'll need to select login, domain, phone in your request for flexisip to work."
			"Example : select login, domain, phone from accounts where phone in (:phones)",
			""},
69 70

		{Integer, "soci-poolsize",
71 72 73 74 75 76
			"Size of the pool of connections that Soci will use. We open a thread for each DB query, and this pool will "
			"allow each thread to get a connection.\n"
			"The threads are blocked until a connection is released back to the pool, so increasing the pool size will "
			"allow more connections to occur simultaneously.\n"
			"On the other hand, you should not keep too many open connections to your DB at the same time.",
			"100"},
77 78

		{String, "soci-backend", "Choose the type of backend that Soci will use for the connection.\n"
79 80 81
			"Depending on your Soci package and the modules you installed, this could be 'mysql', "
			"'oracle', 'postgresql' or something else.",
			"mysql"},
82 83

		{String, "soci-connection-string", "The configuration parameters of the Soci backend.\n"
84 85 86 87 88
			"The basic format is \"key=value key2=value2\". For a mysql backend, this "
			"is a valid config: \"db=mydb user=user password='pass' host=myhost.com\".\n"
			"Please refer to the Soci documentation of your backend, for intance: "
			"http://soci.sourceforge.net/doc/3.2/backends/mysql.html",
			"db=mydb user=myuser password='mypass' host=myhost.com"},
89

90
		{Integer, "soci-max-queue-size",
91 92 93 94 95 96
			"Amount of queries that will be allowed to be queued before bailing password "
			"requests.\n This value should be chosen accordingly with 'soci-poolsize', so "
			"that you have a coherent behavior.\n This limit is here mainly as a safeguard "
			"against out-of-control growth of the queue in the event of a flood or big "
			"delays in the database backend.",
			"1000"},
97

98
		config_item_end};
99 100 101 102

	mc->addChildrenValues(items);
}

103
SociAuthDB::SociAuthDB() : conn_pool(NULL) {
104

105 106
	GenericStruct *cr = GenericManager::get()->getRoot();
	GenericStruct *ma = cr->get<GenericStruct>("module::Authentication");
107
	GenericStruct *mp = cr->get<GenericStruct>("module::Presence");
108

109 110 111
	poolSize = ma->get<ConfigInt>("soci-poolsize")->read();
	connection_string = ma->get<ConfigString>("soci-connection-string")->read();
	backend = ma->get<ConfigString>("soci-backend")->read();
112
	get_password_request = ma->get<ConfigString>("soci-password-request")->read();
113
	get_user_with_phone_request = ma->get<ConfigString>("soci-user-with-phone-request")->read();
114
	get_users_with_phones_request = ma->get<ConfigString>("soci-users-with-phones-request")->read();
115
	unsigned int max_queue_size = (unsigned int)ma->get<ConfigInt>("soci-max-queue-size")->read();
116
	hashed_passwd = ma->get<ConfigBoolean>("hashed-passwords")->read();
117
	check_domain_in_presence_results = mp->get<ConfigBoolean>("check-domain-in-presence-results")->read();
118

119 120
	conn_pool = new connection_pool(poolSize);
	thread_pool = new ThreadPool(poolSize, max_queue_size);
121

122
	LOGD("[SOCI] Authentication provider for backend %s created. Pooled for %d connections", backend.c_str(), (int)poolSize);
123

124 125 126 127 128 129 130 131
	try {
		for (size_t i = 0; i < poolSize; i++) {
			conn_pool->at(i).open(backend, connection_string);
		}
	} catch (soci::mysql_soci_error const & e) {
		SLOGE << "[SOCI] connection pool open MySQL error: " << e.err_num_ << " " << e.what() << endl;
	} catch (exception const &e) {
		SLOGE << "[SOCI] connection pool open error: " << e.what() << endl;
132
	}
133 134 135
}

SociAuthDB::~SociAuthDB() {
136 137
	delete thread_pool; // will automatically shut it down, clearing threads
	delete conn_pool;
138 139
}

140
void SociAuthDB::reconnectSession(soci::session &session) {
141
	try {
142
		SLOGE << "[SOCI] Trying close/reconnect session";
143 144
		session.close();
		session.reconnect();
145
		SLOGD << "[SOCI] Session " << session.get_backend_name() << " successfully reconnected";
146 147 148 149 150
	} catch (soci::mysql_soci_error const & e) {
		SLOGE << "[SOCI] reconnectSession MySQL error: " << e.err_num_ << " " << e.what() << endl;
	} catch (exception const &e) {
		SLOGE << "[SOCI] reconnectSession error: " << e.what() << endl;
	}
151 152
}

153
#define DURATION_MS(start, stop) (unsigned long) duration_cast<milliseconds>((stop) - (start)).count()
154

155 156
void SociAuthDB::getPasswordWithPool(const string &id, const string &domain,
									const string &authid, AuthDbListener *listener, AuthDbListener *listener_ref) {
157 158
	steady_clock::time_point start;
	steady_clock::time_point stop;
159

160
	session *sql = NULL;
161
	vector<passwd_algo_t> passwd;
162 163
	int errorCount = 0;
	bool retry = false;
164

165
	while (errorCount < 2) {
166 167 168 169 170 171 172 173 174 175
		retry = false;
		try {
			start = steady_clock::now();
			// will grab a connection from the pool. This is thread safe
			sql = new session(*conn_pool); //this may raise a soci_error exception, so keep it in the try block.

			stop = steady_clock::now();

			SLOGD << "[SOCI] Pool acquired in " << DURATION_MS(start, stop) << "ms";
			start = stop;
176

177
			string unescapedIdStr = urlUnescape(id);
178

179
			rowset<row> results = (sql->prepare << get_password_request, use(unescapedIdStr, "id"), use(domain, "domain"), use(authid, "authid"));
180

181 182
			for (rowset<row>::const_iterator it = results.begin(); it != results.end(); it++) {
				row const& r = *it;
183 184
				passwd_algo_t pass;

185 186
				/* If size == 1 then we only have the password so we assume MD5 */
				if (r.size() == 1) {
187 188 189
					pass.algo = "MD5";

					if (hashed_passwd) {
190
						pass.pass = r.get<string>(0);
191
					} else {
192
						string input = unescapedIdStr + ":" + domain + ":" + r.get<string>(0);
193 194
						pass.pass = syncMd5(input.c_str(), 16);
					}
195 196 197
				} else if (r.size() > 1) {
					string password = r.get<string>(0);
					string algo = r.get<string>(1);
198

199 200 201 202 203
					if (algo == "CLRTXT") {
						if (passwd.empty()) {
							pass.algo = algo;
							pass.pass = password;
							passwd.push_back(pass);
204

205 206
							string input;
							input = unescapedIdStr + ":" + domain + ":" + password;
207

208 209 210
							pass.pass = syncMd5(input.c_str(), 16);
							pass.algo = "MD5";
							passwd.push_back(pass);
211

212 213 214 215 216 217 218 219 220
							pass.pass = syncSha256(input.c_str(), 32);
							pass.algo = "SHA-256";
							passwd.push_back(pass);

							break;
						}
					} else {
						pass.algo = algo;
						pass.pass = password;
221
					}
222
				}
223 224

				passwd.push_back(pass);
225
			}
226

227
			if(listener_ref) listener_ref->finishVerifyAlgos(passwd);
228

229 230
			stop = steady_clock::now();
			SLOGD << "[SOCI] Got pass for " << id << " in " << DURATION_MS(start, stop) << "ms";
231
			if (!passwd.empty()) cachePassword(createPasswordKey(id, authid), domain, passwd, mCacheExpire);
232
			if (listener){
233
				listener->onResult(passwd.empty() ? PASSWORD_NOT_FOUND : PASSWORD_FOUND, passwd);
234 235 236 237 238
			}
			errorCount = 0;
		} catch (mysql_soci_error const &e) {
			errorCount++;
			stop = steady_clock::now();
239
			SLOGE << "[SOCI] getPasswordWithPool MySQL error after " << DURATION_MS(start, stop) << "ms : " << e.err_num_ << " " << e.what();
240
			if (sql) reconnectSession(*sql);
241

242 243
			if ((e.err_num_ == 2014 || e.err_num_ == 2006) && errorCount == 1){
				/* 2014 is the infamous "Commands out of sync; you can't run this command now" mysql error,
Mickaël Turnel's avatar
Mickaël Turnel committed
244 245 246 247 248
				* which is retryable.
				* At this time we don't know if it is a soci or mysql bug, or bug with the sql request being executed.
				*
				* 2006 is "MySQL server has gone away" which is also retryable.
				*/
249
				SLOGE << "[SOCI] retrying mysql error " << e.err_num_;
250 251
				retry = true;
			}
252
		} catch (const runtime_error &e) {
253 254
			errorCount++;
			stop = steady_clock::now();
255
			SLOGE << "[SOCI] getPasswordWithPool error after " << DURATION_MS(start, stop) << "ms : " << e.what();
256 257 258 259 260
			if (sql) reconnectSession(*sql);
		}
		if (sql) delete sql;
		if (!retry){
			if (errorCount){
261
				if (listener) listener->onResult(AUTH_ERROR, passwd);
262 263
			}
			break;
264
		}
265 266 267
	}
}

268
void SociAuthDB::getUserWithPhoneWithPool(const string &phone, const string &domain, AuthDbListener *listener) {
269 270
	steady_clock::time_point start;
	steady_clock::time_point stop;
271
	string user;
272 273 274 275 276 277 278 279 280 281 282
	session *sql = NULL;

	try {
		start = steady_clock::now();
		// will grab a connection from the pool. This is thread safe
		sql = new session(*conn_pool); //this may raise a soci_error exception, so keep it in the try block.

		stop = steady_clock::now();

		SLOGD << "[SOCI] Pool acquired in " << DURATION_MS(start, stop) << "ms";
		start = stop;
283 284 285
		if(get_user_with_phone_request != "") {
			*sql << get_user_with_phone_request, into(user), use(phone, "phone");
		} else {
Benjamin REIS's avatar
Benjamin REIS committed
286 287 288 289 290 291 292
			string s = get_users_with_phones_request;
			int index = s.find(":phones");
			while(index > -1) {
				s = s.replace(index, 7, phone);
				index = s.find(":phones");
			}
			rowset<row> ret = (sql->prepare << s);
Benjamin REIS's avatar
Benjamin REIS committed
293 294 295 296
			for (rowset<row>::const_iterator it = ret.begin(); it != ret.end(); ++it) {
				row const& row = *it;
				user = row.get<string>(0);
			}
297
		}
298 299 300 301 302 303 304 305 306 307 308
		stop = steady_clock::now();
		if (!user.empty())  {
			SLOGD << "[SOCI] Got user for " << phone << " in " << DURATION_MS(start, stop) << "ms";
			cacheUserWithPhone(phone, domain, user);
		}
		if (listener){
			listener->onResult(user.empty() ? PASSWORD_NOT_FOUND : PASSWORD_FOUND, user);
		}
	} catch (mysql_soci_error const &e) {

		stop = steady_clock::now();
309
		SLOGE << "[SOCI] getUserWithPhoneWithPool MySQL error after " << DURATION_MS(start, stop) << "ms : " << e.err_num_ << " " << e.what();
310 311 312 313 314 315
		if (listener) listener->onResult(PASSWORD_NOT_FOUND, user);

		if (sql) reconnectSession(*sql);

	} catch (exception const &e) {
		stop = steady_clock::now();
316
		SLOGE << "[SOCI] getUserWithPhoneWithPool error after " << DURATION_MS(start, stop) << "ms : " << e.what();
317 318 319 320 321 322
		if (listener) listener->onResult(PASSWORD_NOT_FOUND, user);
		if (sql) reconnectSession(*sql);
	}
	if (sql) delete sql;
}

323
void SociAuthDB::getUsersWithPhonesWithPool(list<tuple<string, string,AuthDbListener*>> &creds) {
324 325
	steady_clock::time_point start;
	steady_clock::time_point stop;
326 327
	set<pair<string, string>> presences;

328
	ostringstream in;
329
	session *sql = NULL;
330 331
	list<string> phones;
	list<string> domains;
Benjamin REIS's avatar
Benjamin REIS committed
332
	bool first = true;
333 334 335
	for(const auto &cred : creds) {
		const auto &phone = std::get<0>(cred);
		phones.push_back(phone);
336
		domains.push_back(std::get<1>(cred));
337
		if(first) {
Benjamin REIS's avatar
Benjamin REIS committed
338
			first = false;
339
			in << "'" << phone << "'";
340
		} else {
341
			in << ",'" << phone << "'";
342 343
		}
	}
344

Benjamin REIS's avatar
Benjamin REIS committed
345 346 347 348 349 350 351
	string s = get_users_with_phones_request;
	int index = s.find(":phones");
	while(index > -1) {
		s = s.replace(index, 7, in.str());
		index = s.find(":phones");
	}

352 353 354 355
	try {
		start = steady_clock::now();
		// will grab a connection from the pool. This is thread safe
		sql = new session(*conn_pool); //this may raise a soci_error exception, so keep it in the try block.
356

357
		stop = steady_clock::now();
358

359 360
		SLOGD << "[SOCI] Pool acquired in " << DURATION_MS(start, stop) << "ms";
		start = stop;
Benjamin REIS's avatar
Benjamin REIS committed
361
		rowset<row> ret = (sql->prepare << s);
362
		stop = steady_clock::now();
363

Benjamin REIS's avatar
Benjamin REIS committed
364
		SLOGD << "[SOCI] Got users in " << DURATION_MS(start, stop) << "ms";
365

366 367 368
		for (rowset<row>::const_iterator it = ret.begin(); it != ret.end(); ++it) {
			row const& row = *it;
			string user = row.get<string>(0);
369
			string domain = row.get<string>(1);
Benjamin REIS's avatar
Benjamin REIS committed
370
			string phone = (row.size() > 2) ? row.get<string>(2) : "";
371 372 373 374 375

			bool domain_match = false;
			if (check_domain_in_presence_results) {
				domain_match = find(domains.begin(), domains.end(), domain) != domains.end();
			}
376

377
			if (!check_domain_in_presence_results || domain_match) {
378
				if (!phone.empty()) {
379 380 381 382 383
					cacheUserWithPhone(phone, domain, user);
					presences.insert(make_pair(user, phone));
				} else {
					presences.insert(make_pair(user, user));
				}
Benjamin REIS's avatar
Benjamin REIS committed
384
			}
385 386
		}

387 388
		notifyAllListeners(creds, presences);

389 390 391
	} catch (mysql_soci_error const &e) {
		stop = steady_clock::now();
		SLOGE << "[SOCI] getUsersWithPhonesWithPool MySQL error after " << DURATION_MS(start, stop) << "ms : " << e.err_num_ << " " << e.what();
Benjamin REIS's avatar
Benjamin REIS committed
392
		SLOGE << "[SOCI] MySQL request causing the error was : " << s;
393
		presences.clear();
394
		notifyAllListeners(creds, presences);
395

396
		if (sql) reconnectSession(*sql);
397

398 399 400
	} catch (exception const &e) {
		stop = steady_clock::now();
		SLOGE << "[SOCI] getUsersWithPhonesWithPool error after " << DURATION_MS(start, stop) << "ms : " << e.what();
401
		presences.clear();
402
		notifyAllListeners(creds, presences);
403 404 405 406
		if (sql) reconnectSession(*sql);
	}
	if (sql) delete sql;
}
407

408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423
void SociAuthDB::notifyAllListeners(std::list<std::tuple<std::string, std::string, AuthDbListener *>> &creds, const std::set<std::pair<std::string, std::string>> &presences) {
	for(const auto &cred : creds) {
		const string &phone = std::get<0>(cred);
		AuthDbListener *listener = std::get<2>(cred);
		auto presence = find_if(presences.cbegin(), presences.cend(),
							 [&phone](const pair<string, string> &p){return p.second == phone;}
		);
		if (presence != presences.cend()) {
			// 				mDInfo[presence->first] = mDInfo[phone];
			if (listener) listener->onResult(PASSWORD_FOUND, presence->first);
		} else {
			if (listener) listener->onResult(PASSWORD_NOT_FOUND, phone);
		}
	}
}

424
#ifdef __clang__
425
#pragma mark - Inherited virtuals
426
#endif
427

428 429
void SociAuthDB::getPasswordFromBackend(const string &id, const string &domain,
										const string &authid, AuthDbListener *listener, AuthDbListener *listener_ref) {
430 431

	// create a thread to grab a pool connection and use it to retrieve the auth information
432
	auto func = bind(&SociAuthDB::getPasswordWithPool, this, id, domain, authid, listener, listener_ref);
433

434
	bool success = thread_pool->Enqueue(func);
435
	if (success == FALSE) {
436
		// Enqueue() can fail when the queue is full, so we have to act on that
437
		SLOGE << "[SOCI] Auth queue is full, cannot fullfil password request for " << id << " / " << domain << " / "
Mickaël Turnel's avatar
Mickaël Turnel committed
438
			<< authid;
439
		if (listener) listener->onResult(AUTH_ERROR, "");
440
	}
441 442
}

443
void SociAuthDB::getUserWithPhoneFromBackend(const string &phone, const string &domain, AuthDbListener *listener) {
444 445

	// create a thread to grab a pool connection and use it to retrieve the auth information
446
	auto func = bind(&SociAuthDB::getUserWithPhoneWithPool, this, phone, domain, listener);
447

448 449 450 451 452 453
	bool success = thread_pool->Enqueue(func);
	if (success == FALSE) {
		// Enqueue() can fail when the queue is full, so we have to act on that
		SLOGE << "[SOCI] Auth queue is full, cannot fullfil user request for " << phone;
		if (listener) listener->onResult(AUTH_ERROR, "");
	}
454
}
455

456
void SociAuthDB::getUsersWithPhonesFromBackend(list<tuple<string, string, AuthDbListener*>> &creds) {
457

458
	// create a thread to grab a pool connection and use it to retrieve the auth information
459
	auto func = bind(&SociAuthDB::getUsersWithPhonesWithPool, this, creds);
460

461 462 463 464
	bool success = thread_pool->Enqueue(func);
	if (success == FALSE) {
		// Enqueue() can fail when the queue is full, so we have to act on that
		SLOGE << "[SOCI] Auth queue is full, cannot fullfil user request for " << &creds;
465 466 467 468
		for (const auto &cred : creds) {
			AuthDbListener *listener = std::get<2>(cred);
			if (listener) listener->onResult(AUTH_ERROR, "");
		}
469 470
	}
}