All Data Structures Files Functions Variables Typedefs Enumerations Enumerator Macros Groups Pages
rlm_sql_postgresql.c
Go to the documentation of this file.
1 /*
2  * sql_postgresql.c Postgresql rlm_sql driver
3  *
4  * Version: $Id: 7d33530a2085fa9531e77808c0f0192bd43857fa $
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: 7d33530a2085fa9531e77808c0f0192bd43857fa $")
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;
62 
63 typedef struct rlm_sql_postgres_conn {
64  PGconn *db;
65  PGresult *result;
66  int cur_row;
69  char **row;
71 
73  { FR_CONF_OFFSET("send_application_name", PW_TYPE_BOOLEAN, rlm_sql_postgres_config_t, send_application_name), .dflt = "no" },
75 };
76 
78 {
79 #if defined(HAVE_OPENSSL_CRYPTO_H) && (defined(HAVE_PQINITOPENSSL) || defined(HAVE_PQINITSSL))
80  static bool ssl_init = false;
81 #endif
82 
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 
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 (driver->send_application_name) {
144  db_string = talloc_asprintf_append(db_string, " application_name='%s'", application_name);
145  }
146 
147  /*
148  * New style parameter string
149  *
150  * Only append options when not already present
151  */
152  } else {
153  db_string = talloc_typed_strdup(driver, config->sql_db);
154 
155  if ((config->sql_server[0] != '\0') && !strstr(db_string, "host=")) {
156  db_string = talloc_asprintf_append(db_string, " host='%s'", config->sql_server);
157  }
158 
159  if (config->sql_port && !strstr(db_string, "port=")) {
160  db_string = talloc_asprintf_append(db_string, " port=%i", config->sql_port);
161  }
162 
163  if ((config->sql_login[0] != '\0') && !strstr(db_string, "user=")) {
164  db_string = talloc_asprintf_append(db_string, " user='%s'", config->sql_login);
165  }
166 
167  if ((config->sql_password[0] != '\0') && !strstr(db_string, "password=")) {
168  db_string = talloc_asprintf_append(db_string, " password='%s'", config->sql_password);
169  }
170 
171  if (driver->send_application_name && !strstr(db_string, "application_name=")) {
172  db_string = talloc_asprintf_append(db_string, " application_name='%s'", application_name);
173  }
174  }
175  driver->db_string = db_string;
176 
177  return 0;
178 }
179 
180 /** Return the number of affected rows of the result as an int instead of the string that postgresql provides
181  *
182  */
183 static int affected_rows(PGresult * result)
184 {
185  return atoi(PQcmdTuples(result));
186 }
187 
188 /** Free the row of the current result that's stored in the conn struct
189  *
190  */
192 {
193  TALLOC_FREE(conn->row);
194  conn->num_fields = 0;
195 }
196 
197 #if defined(PG_DIAG_SQLSTATE) && defined(PG_DIAG_MESSAGE_PRIMARY)
198 static sql_rcode_t sql_classify_error(PGresult const *result)
199 {
200  int i;
201 
202  char *errorcode;
203  char *errormsg;
204 
205  /*
206  * Check the error code to see if we should reconnect or not
207  * Error Code table taken from:
208  * http://www.postgresql.org/docs/8.1/interactive/errcodes-appendix.html
209  */
210  errorcode = PQresultErrorField(result, PG_DIAG_SQLSTATE);
211  errormsg = PQresultErrorField(result, PG_DIAG_MESSAGE_PRIMARY);
212  if (!errorcode) {
213  ERROR("rlm_sql_postgresql: Error occurred, but unable to retrieve error code");
214  return RLM_SQL_ERROR;
215  }
216 
217  /* SUCCESSFUL COMPLETION */
218  if (strcmp("00000", errorcode) == 0) {
219  return RLM_SQL_OK;
220  }
221 
222  /* WARNING */
223  if (strcmp("01000", errorcode) == 0) {
224  WARN("%s", errormsg);
225  return RLM_SQL_OK;
226  }
227 
228  /* UNIQUE VIOLATION */
229  if (strcmp("23505", errorcode) == 0) {
230  return RLM_SQL_ALT_QUERY;
231  }
232 
233  /* others */
234  for (i = 0; errorcodes[i].errorcode != NULL; i++) {
235  if (strcmp(errorcodes[i].errorcode, errorcode) == 0) {
236  ERROR("rlm_sql_postgresql: %s: %s", errorcode, errorcodes[i].meaning);
237 
238  return (errorcodes[i].reconnect == true) ?
241  }
242  }
243 
244  ERROR("rlm_sql_postgresql: Can't classify: %s", errorcode);
245  return RLM_SQL_ERROR;
246 }
247 # else
248 static sql_rcode_t sql_classify_error(UNUSED PGresult const *result)
249 {
250  ERROR("rlm_sql_postgresql: Error occurred, no more information available, rebuild with newer libpq");
251  return RLM_SQL_ERROR;
252 }
253 #endif
254 
256 {
257  DEBUG2("rlm_sql_postgresql: Socket destructor called, closing socket");
258 
259  if (!conn->db) return 0;
260 
261  /* PQfinish also frees the memory used by the PGconn structure */
262  PQfinish(conn->db);
263 
264  return 0;
265 }
266 
267 static int CC_HINT(nonnull) sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config,
268  UNUSED struct timeval const *timeout)
269 {
270  rlm_sql_postgres_config_t *driver = config->driver;
272 
273  MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_postgres_conn_t));
274  talloc_set_destructor(conn, _sql_socket_destructor);
275 
276  DEBUG2("rlm_sql_postgresql: Connecting using parameters: %s", driver->db_string);
277  conn->db = PQconnectdb(driver->db_string);
278  if (!conn->db) {
279  ERROR("rlm_sql_postgresql: Connection failed: Out of memory");
280  return -1;
281  }
282  if (PQstatus(conn->db) != CONNECTION_OK) {
283  ERROR("rlm_sql_postgresql: Connection failed: %s", PQerrorMessage(conn->db));
284  PQfinish(conn->db);
285  conn->db = NULL;
286  return -1;
287  }
288 
289  DEBUG2("Connected to database '%s' on '%s' server version %i, protocol version %i, backend PID %i ",
290  PQdb(conn->db), PQhost(conn->db), PQserverVersion(conn->db), PQprotocolVersion(conn->db),
291  PQbackendPID(conn->db));
292 
293  return 0;
294 }
295 
296 static CC_HINT(nonnull) sql_rcode_t sql_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config,
297  char const *query)
298 {
299  rlm_sql_postgres_conn_t *conn = handle->conn;
300  ExecStatusType status;
301  int numfields = 0;
302 
303  if (!conn->db) {
304  ERROR("rlm_sql_postgresql: Socket not connected");
305  return RLM_SQL_RECONNECT;
306  }
307 
308  /*
309  * Returns a PGresult pointer or possibly a null pointer.
310  * A non-null pointer will generally be returned except in
311  * out-of-memory conditions or serious errors such as inability
312  * to send the command to the server. If a null pointer is
313  * returned, it should be treated like a PGRES_FATAL_ERROR
314  * result.
315  */
316  conn->result = PQexec(conn->db, query);
317 
318  /*
319  * As this error COULD be a connection error OR an out-of-memory
320  * condition return value WILL be wrong SOME of the time
321  * regardless! Pick your poison...
322  */
323  if (!conn->result) {
324  ERROR("rlm_sql_postgresql: Failed getting query result: %s", PQerrorMessage(conn->db));
325  return RLM_SQL_RECONNECT;
326  }
327 
328  status = PQresultStatus(conn->result);
329  DEBUG("rlm_sql_postgresql: Status: %s", PQresStatus(status));
330 
331  switch (status){
332  /*
333  * Successful completion of a command returning no data.
334  */
335  case PGRES_COMMAND_OK:
336  /*
337  * Affected_rows function only returns the number of affected rows of a command
338  * returning no data...
339  */
340  conn->affected_rows = affected_rows(conn->result);
341  DEBUG("rlm_sql_postgresql: query affected rows = %i", conn->affected_rows);
342  return RLM_SQL_OK;
343  /*
344  * Successful completion of a command returning data (such as a SELECT or SHOW).
345  */
346 #ifdef HAVE_PGRES_SINGLE_TUPLE
347  case PGRES_SINGLE_TUPLE:
348 #endif
349  case PGRES_TUPLES_OK:
350  conn->cur_row = 0;
351  conn->affected_rows = PQntuples(conn->result);
352  numfields = PQnfields(conn->result); /*Check row storing functions..*/
353  DEBUG("rlm_sql_postgresql: query affected rows = %i , fields = %i", conn->affected_rows, numfields);
354  return RLM_SQL_OK;
355 
356 #ifdef HAVE_PGRES_COPY_BOTH
357  case PGRES_COPY_BOTH:
358 #endif
359  case PGRES_COPY_OUT:
360  case PGRES_COPY_IN:
361  DEBUG("rlm_sql_postgresql: Data transfer started");
362  return RLM_SQL_OK;
363 
364  /*
365  * Weird.. this shouldn't happen.
366  */
367  case PGRES_EMPTY_QUERY:
368  ERROR("rlm_sql_postgresql: Empty query");
369  return RLM_SQL_QUERY_INVALID;
370 
371  /*
372  * The server's response was not understood.
373  */
374  case PGRES_BAD_RESPONSE:
375  ERROR("rlm_sql_postgresql: Bad Response From Server");
376  return RLM_SQL_RECONNECT;
377 
378 
379  case PGRES_NONFATAL_ERROR:
380  case PGRES_FATAL_ERROR:
381  return sql_classify_error(conn->result);
382  }
383 
384  return RLM_SQL_ERROR;
385 }
386 
387 static sql_rcode_t sql_select_query(rlm_sql_handle_t * handle, rlm_sql_config_t *config, char const *query)
388 {
389  return sql_query(handle, config, query);
390 }
391 
392 static sql_rcode_t sql_fields(char const **out[], rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
393 {
394  rlm_sql_postgres_conn_t *conn = handle->conn;
395 
396  int fields, i;
397  char const **names;
398 
399  fields = PQnfields(conn->result);
400  if (fields <= 0) return RLM_SQL_ERROR;
401 
402  MEM(names = talloc_array(handle, char const *, fields));
403 
404  for (i = 0; i < fields; i++) names[i] = PQfname(conn->result, i);
405  *out = names;
406 
407  return RLM_SQL_OK;
408 }
409 
411 {
412 
413  int records, i, len;
414  rlm_sql_postgres_conn_t *conn = handle->conn;
415 
416  *out = NULL;
417  handle->row = NULL;
418 
419  if (conn->cur_row >= PQntuples(conn->result)) return RLM_SQL_OK;
420 
421  free_result_row(conn);
422 
423  records = PQnfields(conn->result);
424  conn->num_fields = records;
425 
426  if ((PQntuples(conn->result) > 0) && (records > 0)) {
427  conn->row = talloc_zero_array(conn, char *, records + 1);
428  for (i = 0; i < records; i++) {
429  len = PQgetlength(conn->result, conn->cur_row, i);
430  conn->row[i] = talloc_array(conn->row, char, len + 1);
431  strlcpy(conn->row[i], PQgetvalue(conn->result, conn->cur_row, i), len + 1);
432  }
433  conn->cur_row++;
434  *out = handle->row = conn->row;
435  }
436 
437  return RLM_SQL_OK;
438 }
439 
441 {
442  rlm_sql_postgres_conn_t *conn = handle->conn;
443 
444  conn->affected_rows = PQntuples(conn->result);
445  if (conn->result)
446  return PQnfields(conn->result);
447 
448  return 0;
449 }
450 
452 {
453  rlm_sql_postgres_conn_t *conn = handle->conn;
454 
455  if (conn->result != NULL) {
456  PQclear(conn->result);
457  conn->result = NULL;
458  }
459 
460  free_result_row(conn);
461 
462  return 0;
463 }
464 
465 /** Retrieves any errors associated with the connection handle
466  *
467  * @note Caller will free any memory allocated in ctx.
468  *
469  * @param ctx to allocate temporary error buffers in.
470  * @param out Array of sql_log_entrys to fill.
471  * @param outlen Length of out array.
472  * @param handle rlm_sql connection handle.
473  * @param config rlm_sql config.
474  * @return number of errors written to the #sql_log_entry_t array.
475  */
476 static size_t sql_error(TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen,
477  rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
478 {
479  rlm_sql_postgres_conn_t *conn = handle->conn;
480  char const *p, *q;
481  size_t i = 0;
482 
483  rad_assert(outlen > 0);
484 
485  p = PQerrorMessage(conn->db);
486  while ((q = strchr(p, '\n'))) {
487  out[i].type = L_ERR;
488  out[i].msg = talloc_asprintf(ctx, "%.*s", (int) (q - p), p);
489  p = q + 1;
490  if (++i == outlen) return outlen;
491  }
492  if (*p != '\0') {
493  out[i].type = L_ERR;
494  out[i].msg = p;
495  i++;
496  }
497 
498  return i;
499 }
500 
502 {
503  rlm_sql_postgres_conn_t *conn = handle->conn;
504 
505  return conn->affected_rows;
506 }
507 
508 static size_t sql_escape_func(REQUEST *request, char *out, size_t outlen, char const *in, void *arg)
509 {
510  size_t inlen, ret;
511  rlm_sql_handle_t *handle = talloc_get_type_abort(arg, rlm_sql_handle_t);
512  rlm_sql_postgres_conn_t *conn = handle->conn;
513  int err;
514 
515  /* Check for potential buffer overflow */
516  inlen = strlen(in);
517  if ((inlen * 2 + 1) > outlen) return 0;
518  /* Prevent integer overflow */
519  if ((inlen * 2 + 1) <= inlen) return 0;
520 
521  ret = PQescapeStringConn(conn->db, out, in, inlen, &err);
522  if (err) {
523  REDEBUG("Error escaping string \"%s\": %s", in, PQerrorMessage(conn->db));
524  return 0;
525  }
526 
527  return ret;
528 }
529 
530 /* Exported to rlm_sql */
532 rlm_sql_module_t rlm_sql_postgresql = {
533  .name = "rlm_sql_postgresql",
534 // .flags = RLM_SQL_RCODE_FLAGS_ALT_QUERY, /* Needs more testing */
535  .mod_instantiate = mod_instantiate,
536  .sql_socket_init = sql_socket_init,
537  .sql_query = sql_query,
538  .sql_select_query = sql_select_query,
539  .sql_num_fields = sql_num_fields,
540  .sql_fields = sql_fields,
541  .sql_fetch_row = sql_fetch_row,
542  .sql_error = sql_error,
543  .sql_finish_query = sql_free_result,
544  .sql_finish_select_query = sql_free_result,
545  .sql_affected_rows = sql_affected_rows,
546  .sql_escape_func = sql_escape_func
547 };
rlm_sql_module_t rlm_sql_postgresql
static void free_result_row(rlm_sql_postgres_conn_t *conn)
Free the row of the current result that's stored in the conn struct.
static size_t sql_error(TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen, rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
Retrieves any errors associated with the connection handle.
General connection/server error.
Definition: rlm_sql.h:46
static int CC_HINT(nonnull)
Main server configuration.
Definition: radiusd.h:108
Prototypes and functions for the SQL module.
char ** rlm_sql_row_t
Definition: rlm_sql.h:59
log_type_t type
Type of log entry L_ERR, L_WARN, L_INFO, L_DBG etc..
Definition: rlm_sql.h:62
static sql_rcode_t sql_select_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
#define MEM(x)
Definition: radiusd.h:396
static int _sql_socket_destructor(rlm_sql_postgres_conn_t *conn)
static char const * name
char const * msg
Log message.
Definition: rlm_sql.h:63
#define UNUSED
Definition: libradius.h:134
Error message.
Definition: log.h:36
#define CONF_PARSER_TERMINATOR
Definition: conffile.h:289
char const * sql_server
Server to connect to.
Definition: rlm_sql.h:85
PUBLIC int snprintf(char *string, size_t length, char *format, va_alist)
Definition: snprintf.c:686
static float timeout
Definition: radclient.c:43
char const * name
Name of the daemon, usually 'radiusd'.
Definition: radiusd.h:109
Defines a CONF_PAIR to C data type mapping.
Definition: conffile.h:267
Key constraint violation.
Definition: rlm_sql.h:49
static sql_rcode_t sql_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query)
#define rad_assert(expr)
Definition: rad_assert.h:38
Stale connection, should reconnect.
Definition: rlm_sql.h:48
char const * name
Definition: rlm_sql.h:191
static int sql_affected_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
#define DEBUG(fmt,...)
Definition: log.h:175
sql_rcode_t
Definition: rlm_sql.h:44
uint32_t sql_port
Port to connect to.
Definition: rlm_sql.h:86
static sql_rcode_t sql_classify_error(UNUSED PGresult const *result)
static int mod_instantiate(CONF_SECTION *conf, rlm_sql_config_t *config)
#define DEBUG2(fmt,...)
Definition: log.h:176
CONF_SECTION * cf_item_parent(CONF_ITEM const *ci)
Definition: conffile.c:3896
int cf_section_parse(CONF_SECTION *, void *base, CONF_PARSER const *variables)
Parse a configuration section into user-supplied variables.
Definition: conffile.c:2234
Definition: rlm_sql.h:61
CONF_ITEM * cf_section_to_item(CONF_SECTION const *cs)
Cast a CONF_SECTION to a CONF_ITEM.
Definition: conffile.c:224
Success.
Definition: rlm_sql.h:47
char * talloc_typed_asprintf(void const *t, char const *fmt,...)
Call talloc vasprintf, setting the type on the new chunk correctly.
Definition: missing.c:611
A truth value.
Definition: radius.h:56
char const * sql_password
Login password to use.
Definition: rlm_sql.h:88
void * driver
Where drivers should write a pointer to their configurations.
Definition: rlm_sql.h:135
static rs_t * conf
Definition: radsniff.c:46
void * conn
Database specific connection handle.
Definition: rlm_sql.h:153
struct rlm_sql_postgres_conn rlm_sql_postgres_conn_t
static pgerror errorcodes[]
char const * cf_section_name1(CONF_SECTION const *cs)
Definition: conffile.c:3592
rlm_sql_row_t row
Row data from the last query.
Definition: rlm_sql.h:154
static sql_rcode_t sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config, struct timeval const *timeout)
static sql_rcode_t sql_fetch_row(rlm_sql_row_t *out, rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
#define FR_CONF_OFFSET(_n, _t, _s, _f)
Definition: conffile.h:168
char const * sql_login
Login credentials to use.
Definition: rlm_sql.h:87
Query syntax error.
Definition: rlm_sql.h:45
#define WARN(fmt,...)
Definition: log.h:144
static sql_rcode_t sql_fields(char const **out[], rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
static int affected_rows(PGresult *result)
Return the number of affected rows of the result as an int instead of the string that postgresql prov...
#define REDEBUG(fmt,...)
Definition: log.h:254
struct rlm_sql_postgres_config rlm_sql_postgres_config_t
static int sql_num_fields(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
char const * sql_db
Database to run queries against.
Definition: rlm_sql.h:89
static size_t sql_escape_func(REQUEST *request, char *out, size_t outlen, char const *in, void *arg)
static CONF_PARSER driver_config[]
size_t strlcpy(char *dst, char const *src, size_t siz)
Definition: strlcpy.c:38
char const * errorcode
5 char error code from PG_DIAG_SQLSTATE.
#define RCSID(id)
Definition: build.h:135
static sql_rcode_t sql_free_result(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
#define NAMEDATALEN
char * talloc_typed_strdup(void const *t, char const *p)
Call talloc strdup, setting the type on the new chunk correctly.
Definition: missing.c:588
#define ERROR(fmt,...)
Definition: log.h:145
char const * cf_section_name2(CONF_SECTION const *cs)
Definition: conffile.c:3601