1 /*
2 * sql_postgresql.c Postgresql rlm_sql driver
3 *
4 * Version: $Id: a152f74099c7dffc3c693f40c33075a537c200b7 $
5 *
6 * This program is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 2 of the License, or
9 * (at your option) any later 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., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
19 *
20 * Copyright 2000,2006 The FreeRADIUS server project
21 * Copyright 2000 Mike Machado <mike@innercite.com>
22 * Copyright 2000 Alan DeKok <aland@ox.org>
23 */
24
25 /*
26 * April 2001:
27 *
28 * Use blocking queries and delete unused functions. In
29 * rlm_sql_postgresql replace all functions that are not really used
30 * with the not_implemented function.
31 *
32 * Add a new field to the rlm_sql_postgres_conn_t struct to store the
33 * number of rows affected by a query because the sql module calls
34 * finish_query before it retrieves the number of affected rows from the
35 * driver
36 *
37 * Bernhard Herzog <bh@intevation.de>
38 */
39
40 RCSID("$Id: a152f74099c7dffc3c693f40c33075a537c200b7 $")
41
42 #include <freeradius-devel/radiusd.h>
43 #include <freeradius-devel/rad_assert.h>
44
45 #include <sys/stat.h>
46
47 #include <libpq-fe.h>
48 #include <postgres_ext.h>
49
50 #include "config.h"
51 #include "rlm_sql.h"
52 #include "sql_postgresql.h"
53
54 #ifndef NAMEDATALEN
55 # define NAMEDATALEN 64
56 #endif
57
58 typedef struct rlm_sql_postgres_config {
59 char const *db_string;
60 bool send_application_name;
61 } rlm_sql_postgres_config_t;
62
63 typedef struct rlm_sql_postgres_conn {
64 PGconn *db;
65 PGresult *result;
66 int cur_row;
67 int num_fields;
68 int affected_rows;
69 char **row;
70 } rlm_sql_postgres_conn_t;
71
72 static const CONF_PARSER driver_config[] = {
73 { "send_application_name", FR_CONF_OFFSET(PW_TYPE_BOOLEAN, rlm_sql_postgres_config_t, send_application_name), "no" },
74 CONF_PARSER_TERMINATOR
75 };
76
mod_instantiate(CONF_SECTION * conf,rlm_sql_config_t * config)77 static int mod_instantiate(CONF_SECTION *conf, rlm_sql_config_t *config)
78 {
79 #if defined(HAVE_OPENSSL_CRYPTO_H) && (defined(HAVE_PQINITOPENSSL) || defined(HAVE_PQINITSSL))
80 static bool ssl_init = false;
81 #endif
82
83 rlm_sql_postgres_config_t *driver;
84 char application_name[NAMEDATALEN];
85 char *db_string;
86
87 #if defined(HAVE_OPENSSL_CRYPTO_H) && (defined(HAVE_PQINITOPENSSL) || defined(HAVE_PQINITSSL))
88 if (!ssl_init) {
89 # ifdef HAVE_PQINITOPENSSL
90 PQinitOpenSSL(0, 0);
91 # else
92 PQinitSSL(0);
93 # endif
94 ssl_init = true;
95 }
96 #endif
97
98 MEM(driver = config->driver = talloc_zero(config, rlm_sql_postgres_config_t));
99 if (cf_section_parse(conf, driver, driver_config) < 0) {
100 return -1;
101 }
102
103 /*
104 * Allow the user to set their own, or disable it
105 */
106 if (driver->send_application_name) {
107 CONF_SECTION *cs;
108 char const *name;
109
110 cs = cf_item_parent(cf_section_to_item(conf));
111
112 name = cf_section_name2(cs);
113 if (!name) name = cf_section_name1(cs);
114
115 snprintf(application_name, sizeof(application_name),
116 "FreeRADIUS " RADIUSD_VERSION_STRING " - %s (%s)", main_config.name, name);
117 }
118
119 /*
120 * Old style database name
121 *
122 * Append options if they were set in the config
123 */
124 if (!strchr(config->sql_db, '=')) {
125 db_string = talloc_typed_asprintf(driver, "dbname='%s'", config->sql_db);
126
127 if (config->sql_server[0] != '\0') {
128 db_string = talloc_asprintf_append(db_string, " host='%s'", config->sql_server);
129 }
130
131 if (config->sql_port) {
132 db_string = talloc_asprintf_append(db_string, " port=%i", config->sql_port);
133 }
134
135 if (config->sql_login[0] != '\0') {
136 db_string = talloc_asprintf_append(db_string, " user='%s'", config->sql_login);
137 }
138
139 if (config->sql_password[0] != '\0') {
140 db_string = talloc_asprintf_append(db_string, " password='%s'", config->sql_password);
141 }
142
143 if (config->query_timeout) {
144 db_string = talloc_asprintf_append(db_string, " connect_timeout=%d", config->query_timeout);
145 }
146
147 if (driver->send_application_name) {
148 db_string = talloc_asprintf_append(db_string, " application_name='%s'", application_name);
149 }
150
151 /*
152 * New style parameter string
153 *
154 * Only append options when not already present
155 */
156 } else {
157 db_string = talloc_typed_strdup(driver, config->sql_db);
158
159 if ((config->sql_server[0] != '\0') && !strstr(db_string, "host=")) {
160 db_string = talloc_asprintf_append(db_string, " host='%s'", config->sql_server);
161 }
162
163 if (config->sql_port && !strstr(db_string, "port=")) {
164 db_string = talloc_asprintf_append(db_string, " port=%i", config->sql_port);
165 }
166
167 if ((config->sql_login[0] != '\0') && !strstr(db_string, "user=")) {
168 db_string = talloc_asprintf_append(db_string, " user='%s'", config->sql_login);
169 }
170
171 if ((config->sql_password[0] != '\0') && !strstr(db_string, "password=")) {
172 db_string = talloc_asprintf_append(db_string, " password='%s'", config->sql_password);
173 }
174
175 if ((config->query_timeout) && !strstr(db_string, "connect_timeout=")) {
176 db_string = talloc_asprintf_append(db_string, " connect_timeout=%d", config->query_timeout);
177 }
178
179 if (driver->send_application_name && !strstr(db_string, "application_name=")) {
180 db_string = talloc_asprintf_append(db_string, " application_name='%s'", application_name);
181 }
182 }
183 driver->db_string = db_string;
184
185 return 0;
186 }
187
188 /** Return the number of affected rows of the result as an int instead of the string that postgresql provides
189 *
190 */
affected_rows(PGresult * result)191 static int affected_rows(PGresult * result)
192 {
193 return atoi(PQcmdTuples(result));
194 }
195
196 /** Free the row of the current result that's stored in the conn struct
197 *
198 */
free_result_row(rlm_sql_postgres_conn_t * conn)199 static void free_result_row(rlm_sql_postgres_conn_t *conn)
200 {
201 TALLOC_FREE(conn->row);
202 conn->num_fields = 0;
203 }
204
205 #if defined(PG_DIAG_SQLSTATE) && defined(PG_DIAG_MESSAGE_PRIMARY)
sql_classify_error(PGresult const * result)206 static sql_rcode_t sql_classify_error(PGresult const *result)
207 {
208 int i;
209
210 char *errorcode;
211 char *errormsg;
212
213 /*
214 * Check the error code to see if we should reconnect or not
215 * Error Code table taken from:
216 * http://www.postgresql.org/docs/8.1/interactive/errcodes-appendix.html
217 */
218 errorcode = PQresultErrorField(result, PG_DIAG_SQLSTATE);
219 errormsg = PQresultErrorField(result, PG_DIAG_MESSAGE_PRIMARY);
220 if (!errorcode) {
221 ERROR("rlm_sql_postgresql: Error occurred, but unable to retrieve error code");
222 return RLM_SQL_ERROR;
223 }
224
225 /* SUCCESSFUL COMPLETION */
226 if (strcmp("00000", errorcode) == 0) {
227 return RLM_SQL_OK;
228 }
229
230 /* WARNING */
231 if (strcmp("01000", errorcode) == 0) {
232 WARN("%s", errormsg);
233 return RLM_SQL_OK;
234 }
235
236 /* UNIQUE VIOLATION */
237 if (strcmp("23505", errorcode) == 0) {
238 return RLM_SQL_ALT_QUERY;
239 }
240
241 /* others */
242 for (i = 0; errorcodes[i].errorcode != NULL; i++) {
243 if (strcmp(errorcodes[i].errorcode, errorcode) == 0) {
244 ERROR("rlm_sql_postgresql: %s: %s", errorcode, errorcodes[i].meaning);
245
246 return (errorcodes[i].reconnect == true) ?
247 RLM_SQL_RECONNECT :
248 RLM_SQL_ERROR;
249 }
250 }
251
252 ERROR("rlm_sql_postgresql: Can't classify: %s", errorcode);
253 return RLM_SQL_ERROR;
254 }
255 # else
sql_classify_error(UNUSED PGresult const * result)256 static sql_rcode_t sql_classify_error(UNUSED PGresult const *result)
257 {
258 ERROR("rlm_sql_postgresql: Error occurred, no more information available, rebuild with newer libpq");
259 return RLM_SQL_ERROR;
260 }
261 #endif
262
_sql_socket_destructor(rlm_sql_postgres_conn_t * conn)263 static int _sql_socket_destructor(rlm_sql_postgres_conn_t *conn)
264 {
265 DEBUG2("rlm_sql_postgresql: Socket destructor called, closing socket");
266
267 if (!conn->db) return 0;
268
269 /* PQfinish also frees the memory used by the PGconn structure */
270 PQfinish(conn->db);
271
272 return 0;
273 }
274
CC_HINT(nonnull)275 static int CC_HINT(nonnull) sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
276 {
277 rlm_sql_postgres_config_t *driver = config->driver;
278 rlm_sql_postgres_conn_t *conn;
279
280 MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_postgres_conn_t));
281 talloc_set_destructor(conn, _sql_socket_destructor);
282
283 DEBUG2("rlm_sql_postgresql: Connecting using parameters: %s", driver->db_string);
284 conn->db = PQconnectdb(driver->db_string);
285 if (!conn->db) {
286 ERROR("rlm_sql_postgresql: Connection failed: Out of memory");
287 return -1;
288 }
289 if (PQstatus(conn->db) != CONNECTION_OK) {
290 ERROR("rlm_sql_postgresql: Connection failed: %s", PQerrorMessage(conn->db));
291 PQfinish(conn->db);
292 conn->db = NULL;
293 return -1;
294 }
295
296 DEBUG2("Connected to database '%s' on '%s' server version %i, protocol version %i, backend PID %i ",
297 PQdb(conn->db), PQhost(conn->db), PQserverVersion(conn->db), PQprotocolVersion(conn->db),
298 PQbackendPID(conn->db));
299
300 return 0;
301 }
302
CC_HINT(nonnull)303 static CC_HINT(nonnull) sql_rcode_t sql_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config,
304 char const *query)
305 {
306 rlm_sql_postgres_conn_t *conn = handle->conn;
307 struct timeval start;
308 int sockfd;
309 ExecStatusType status;
310 int numfields = 0;
311 PGresult *tmp_result;
312
313 if (!conn->db) {
314 ERROR("rlm_sql_postgresql: Socket not connected");
315 return RLM_SQL_RECONNECT;
316 }
317
318 sockfd = PQsocket(conn->db);
319 if (sockfd < 0) {
320 ERROR("rlm_sql_postgresql: Unable to obtain socket: %s", PQerrorMessage(conn->db));
321 return RLM_SQL_RECONNECT;
322 }
323
324 if (!PQsendQuery(conn->db, query)) {
325 ERROR("rlm_sql_postgresql: Failed to send query: %s", PQerrorMessage(conn->db));
326 return RLM_SQL_RECONNECT;
327 }
328
329 /*
330 * We try to avoid blocking by waiting until the driver indicates that
331 * the result is ready or our timeout expires
332 */
333 gettimeofday(&start, NULL);
334 while (PQisBusy(conn->db)) {
335 int r;
336 fd_set read_fd;
337 struct timeval when, elapsed, wake;
338
339 FD_ZERO(&read_fd);
340 FD_SET(sockfd, &read_fd);
341
342 if (config->query_timeout) {
343 gettimeofday(&when, NULL);
344 rad_tv_sub(&when, &start, &elapsed);
345 if (elapsed.tv_sec >= config->query_timeout) goto too_long;
346
347 when.tv_sec = config->query_timeout;
348 when.tv_usec = 0;
349 rad_tv_sub(&when, &elapsed, &wake);
350 }
351
352 r = select(sockfd + 1, &read_fd, NULL, NULL, config->query_timeout ? &wake : NULL);
353 if (r == 0) {
354 too_long:
355 ERROR("rlm_sql_postgresql: Socket read timeout after %d seconds", config->query_timeout);
356 return RLM_SQL_RECONNECT;
357 }
358 if (r < 0) {
359 if (errno == EINTR) continue;
360 ERROR("rlm_sql_postgresql: Failed in select: %s", fr_syserror(errno));
361 return RLM_SQL_RECONNECT;
362 }
363 if (!PQconsumeInput(conn->db)) {
364 ERROR("rlm_sql_postgresql: Failed reading input: %s", PQerrorMessage(conn->db));
365 return RLM_SQL_RECONNECT;
366 }
367 }
368
369 /*
370 * Returns a PGresult pointer or possibly a null pointer.
371 * A non-null pointer will generally be returned except in
372 * out-of-memory conditions or serious errors such as inability
373 * to send the command to the server. If a null pointer is
374 * returned, it should be treated like a PGRES_FATAL_ERROR
375 * result.
376 */
377 conn->result = PQgetResult(conn->db);
378
379 /* Discard results for appended queries */
380 while ((tmp_result = PQgetResult(conn->db)) != NULL)
381 PQclear(tmp_result);
382
383 /*
384 * As this error COULD be a connection error OR an out-of-memory
385 * condition return value WILL be wrong SOME of the time
386 * regardless! Pick your poison...
387 */
388 if (!conn->result) {
389 ERROR("rlm_sql_postgresql: Failed getting query result: %s", PQerrorMessage(conn->db));
390 return RLM_SQL_RECONNECT;
391 }
392
393 status = PQresultStatus(conn->result);
394 DEBUG("rlm_sql_postgresql: Status: %s", PQresStatus(status));
395
396 switch (status){
397 /*
398 * Successful completion of a command returning no data.
399 */
400 case PGRES_COMMAND_OK:
401 /*
402 * Affected_rows function only returns the number of affected rows of a command
403 * returning no data...
404 */
405 conn->affected_rows = affected_rows(conn->result);
406 DEBUG("rlm_sql_postgresql: query affected rows = %i", conn->affected_rows);
407 return RLM_SQL_OK;
408 /*
409 * Successful completion of a command returning data (such as a SELECT or SHOW).
410 */
411 #ifdef HAVE_PGRES_SINGLE_TUPLE
412 case PGRES_SINGLE_TUPLE:
413 #endif
414 case PGRES_TUPLES_OK:
415 conn->cur_row = 0;
416 conn->affected_rows = PQntuples(conn->result);
417 numfields = PQnfields(conn->result); /*Check row storing functions..*/
418 DEBUG("rlm_sql_postgresql: query affected rows = %i , fields = %i", conn->affected_rows, numfields);
419 return RLM_SQL_OK;
420
421 #ifdef HAVE_PGRES_COPY_BOTH
422 case PGRES_COPY_BOTH:
423 #endif
424 case PGRES_COPY_OUT:
425 case PGRES_COPY_IN:
426 DEBUG("rlm_sql_postgresql: Data transfer started");
427 return RLM_SQL_OK;
428
429 /*
430 * Weird.. this shouldn't happen.
431 */
432 case PGRES_EMPTY_QUERY:
433 ERROR("rlm_sql_postgresql: Empty query");
434 return RLM_SQL_QUERY_INVALID;
435
436 /*
437 * The server's response was not understood.
438 */
439 case PGRES_BAD_RESPONSE:
440 ERROR("rlm_sql_postgresql: Bad Response From Server");
441 return RLM_SQL_RECONNECT;
442
443
444 case PGRES_NONFATAL_ERROR:
445 case PGRES_FATAL_ERROR:
446 return sql_classify_error(conn->result);
447
448 #ifdef HAVE_PGRES_PIPELINE_SYNC
449 case PGRES_PIPELINE_SYNC:
450 case PGRES_PIPELINE_ABORTED:
451 ERROR("rlm_sql_postgresql: Pipeline flagged as aborted");
452 return RLM_SQL_ERROR;
453 #endif
454 }
455
456 return RLM_SQL_ERROR;
457 }
458
sql_select_query(rlm_sql_handle_t * handle,rlm_sql_config_t * config,char const * query)459 static sql_rcode_t sql_select_query(rlm_sql_handle_t * handle, rlm_sql_config_t *config, char const *query)
460 {
461 return sql_query(handle, config, query);
462 }
463
sql_fetch_row(rlm_sql_handle_t * handle,UNUSED rlm_sql_config_t * config)464 static sql_rcode_t sql_fetch_row(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
465 {
466
467 int records, i, len;
468 rlm_sql_postgres_conn_t *conn = handle->conn;
469
470 handle->row = NULL;
471
472 if (conn->cur_row >= PQntuples(conn->result)) return RLM_SQL_NO_MORE_ROWS;
473
474 free_result_row(conn);
475
476 records = PQnfields(conn->result);
477 conn->num_fields = records;
478
479 if ((PQntuples(conn->result) > 0) && (records > 0)) {
480 conn->row = talloc_zero_array(conn, char *, records + 1);
481 for (i = 0; i < records; i++) {
482 len = PQgetlength(conn->result, conn->cur_row, i);
483 conn->row[i] = talloc_array(conn->row, char, len + 1);
484 strlcpy(conn->row[i], PQgetvalue(conn->result, conn->cur_row, i), len + 1);
485 }
486 conn->cur_row++;
487 handle->row = conn->row;
488 } else {
489 return RLM_SQL_NO_MORE_ROWS;
490 }
491
492 return RLM_SQL_OK;
493 }
494
sql_num_fields(rlm_sql_handle_t * handle,UNUSED rlm_sql_config_t * config)495 static int sql_num_fields(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
496 {
497 rlm_sql_postgres_conn_t *conn = handle->conn;
498
499 conn->affected_rows = PQntuples(conn->result);
500 if (conn->result)
501 return PQnfields(conn->result);
502
503 return 0;
504 }
505
sql_free_result(rlm_sql_handle_t * handle,UNUSED rlm_sql_config_t * config)506 static sql_rcode_t sql_free_result(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
507 {
508 rlm_sql_postgres_conn_t *conn = handle->conn;
509
510 if (conn->result != NULL) {
511 PQclear(conn->result);
512 conn->result = NULL;
513 }
514
515 free_result_row(conn);
516
517 return 0;
518 }
519
520 /** Retrieves any errors associated with the connection handle
521 *
522 * @note Caller will free any memory allocated in ctx.
523 *
524 * @param ctx to allocate temporary error buffers in.
525 * @param out Array of sql_log_entrys to fill.
526 * @param outlen Length of out array.
527 * @param handle rlm_sql connection handle.
528 * @param config rlm_sql config.
529 * @return number of errors written to the sql_log_entry array.
530 */
sql_error(TALLOC_CTX * ctx,sql_log_entry_t out[],size_t outlen,rlm_sql_handle_t * handle,UNUSED rlm_sql_config_t * config)531 static size_t sql_error(TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen,
532 rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
533 {
534 rlm_sql_postgres_conn_t *conn = handle->conn;
535 char const *p, *q;
536 size_t i = 0;
537
538 rad_assert(outlen > 0);
539
540 p = PQerrorMessage(conn->db);
541 while ((q = strchr(p, '\n'))) {
542 out[i].type = L_ERR;
543 out[i].msg = talloc_asprintf(ctx, "%.*s", (int) (q - p), p);
544 p = q + 1;
545 if (++i == outlen) return outlen;
546 }
547 if (*p != '\0') {
548 out[i].type = L_ERR;
549 out[i].msg = p;
550 i++;
551 }
552
553 return i;
554 }
555
sql_affected_rows(rlm_sql_handle_t * handle,UNUSED rlm_sql_config_t * config)556 static int sql_affected_rows(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
557 {
558 rlm_sql_postgres_conn_t *conn = handle->conn;
559
560 return conn->affected_rows;
561 }
562
sql_escape_func(UNUSED REQUEST * request,char * out,size_t outlen,char const * in,void * arg)563 static size_t sql_escape_func(UNUSED REQUEST *request, char *out, size_t outlen, char const *in, void *arg)
564 {
565 size_t inlen, ret;
566 rlm_sql_handle_t *handle = talloc_get_type_abort(arg, rlm_sql_handle_t);
567 rlm_sql_postgres_conn_t *conn = handle->conn;
568 int err;
569
570 /* Check for potential buffer overflow */
571 inlen = strlen(in);
572 if ((inlen * 2 + 1) > outlen) return 0;
573 /* Prevent integer overflow */
574 if ((inlen * 2 + 1) <= inlen) return 0;
575
576 ret = PQescapeStringConn(conn->db, out, in, inlen, &err);
577 if (err) {
578 REDEBUG("Error escaping string \"%s\": %s", in, PQerrorMessage(conn->db));
579 return 0;
580 }
581
582 return ret;
583 }
584
585 /* Exported to rlm_sql */
586 extern rlm_sql_module_t rlm_sql_postgresql;
587 rlm_sql_module_t rlm_sql_postgresql = {
588 .name = "rlm_sql_postgresql",
589 // .flags = RLM_SQL_RCODE_FLAGS_ALT_QUERY, /* Needs more testing */
590 .mod_instantiate = mod_instantiate,
591 .sql_socket_init = sql_socket_init,
592 .sql_query = sql_query,
593 .sql_select_query = sql_select_query,
594 .sql_num_fields = sql_num_fields,
595 .sql_fetch_row = sql_fetch_row,
596 .sql_error = sql_error,
597 .sql_finish_query = sql_free_result,
598 .sql_finish_select_query = sql_free_result,
599 .sql_affected_rows = sql_affected_rows,
600 .sql_escape_func = sql_escape_func
601 };
602