src/modules/rlm_sql/drivers/rlm_sql_postgresql/rlm_sql_postgresql.c

   1 /*
   2  * sql_postgresql.c             Postgresql rlm_sql driver
   3  *
   4  * Version:     $Id$
   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$")
  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 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
  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)", progname, 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  */
 191 static void free_result_row(rlm_sql_postgres_conn_t *conn)
 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) ?
 239                                 RLM_SQL_RECONNECT :
 240                                 RLM_SQL_ERROR;
 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
 255 static int _sql_socket_destructor(rlm_sql_postgres_conn_t *conn)
 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 {
 269         rlm_sql_postgres_config_t *driver = config->driver;
 270         rlm_sql_postgres_conn_t *conn;
 271
 272         MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_postgres_conn_t));
 273         talloc_set_destructor(conn, _sql_socket_destructor);
 274
 275         DEBUG2("rlm_sql_postgresql: Connecting using parameters: %s", driver->db_string);
 276         conn->db = PQconnectdb(driver->db_string);
 277         if (!conn->db) {
 278                 ERROR("rlm_sql_postgresql: Connection failed: Out of memory");
 279                 return -1;
 280         }
 281         if (PQstatus(conn->db) != CONNECTION_OK) {
 282                 ERROR("rlm_sql_postgresql: Connection failed: %s", PQerrorMessage(conn->db));
 283                 PQfinish(conn->db);
 284                 conn->db = NULL;
 285                 return -1;
 286         }
 287
 288         DEBUG2("Connected to database '%s' on '%s' server version %i, protocol version %i, backend PID %i ",
 289                PQdb(conn->db), PQhost(conn->db), PQserverVersion(conn->db), PQprotocolVersion(conn->db),
 290                PQbackendPID(conn->db));
 291
 292         return 0;
 293 }
 294
 295 static CC_HINT(nonnull) sql_rcode_t sql_query(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config,
 296                                               char const *query)
 297 {
 298         rlm_sql_postgres_conn_t *conn = handle->conn;
 299         ExecStatusType status;
 300         int numfields = 0;
 301
 302         if (!conn->db) {
 303                 ERROR("rlm_sql_postgresql: Socket not connected");
 304                 return RLM_SQL_RECONNECT;
 305         }
 306
 307         /*
 308          *  Returns a PGresult pointer or possibly a null pointer.
 309          *  A non-null pointer will generally be returned except in
 310          *  out-of-memory conditions or serious errors such as inability
 311          *  to send the command to the server. If a null pointer is
 312          *  returned, it should be treated like a PGRES_FATAL_ERROR
 313          *  result.
 314          */
 315         conn->result = PQexec(conn->db, query);
 316
 317         /*
 318          *  As this error COULD be a connection error OR an out-of-memory
 319          *  condition return value WILL be wrong SOME of the time
 320          *  regardless! Pick your poison...
 321          */
 322         if (!conn->result) {
 323                 ERROR("rlm_sql_postgresql: Failed getting query result: %s", PQerrorMessage(conn->db));
 324                 return RLM_SQL_RECONNECT;
 325         }
 326
 327         status = PQresultStatus(conn->result);
 328         DEBUG("rlm_sql_postgresql: Status: %s", PQresStatus(status));
 329
 330         switch (status){
 331         /*
 332          *  Successful completion of a command returning no data.
 333          */
 334         case PGRES_COMMAND_OK:
 335                 /*
 336                  *  Affected_rows function only returns the number of affected rows of a command
 337                  *  returning no data...
 338                  */
 339                 conn->affected_rows = affected_rows(conn->result);
 340                 DEBUG("rlm_sql_postgresql: query affected rows = %i", conn->affected_rows);
 341                 return RLM_SQL_OK;
 342         /*
 343          *  Successful completion of a command returning data (such as a SELECT or SHOW).
 344          */
 345 #ifdef HAVE_PGRES_SINGLE_TUPLE
 346         case PGRES_SINGLE_TUPLE:
 347 #endif
 348         case PGRES_TUPLES_OK:
 349                 conn->cur_row = 0;
 350                 conn->affected_rows = PQntuples(conn->result);
 351                 numfields = PQnfields(conn->result); /*Check row storing functions..*/
 352                 DEBUG("rlm_sql_postgresql: query affected rows = %i , fields = %i", conn->affected_rows, numfields);
 353                 return RLM_SQL_OK;
 354
 355 #ifdef HAVE_PGRES_COPY_BOTH
 356         case PGRES_COPY_BOTH:
 357 #endif
 358         case PGRES_COPY_OUT:
 359         case PGRES_COPY_IN:
 360                 DEBUG("rlm_sql_postgresql: Data transfer started");
 361                 return RLM_SQL_OK;
 362
 363         /*
 364          *  Weird.. this shouldn't happen.
 365          */
 366         case PGRES_EMPTY_QUERY:
 367                 ERROR("rlm_sql_postgresql: Empty query");
 368                 return RLM_SQL_QUERY_INVALID;
 369
 370         /*
 371          *  The server's response was not understood.
 372          */
 373         case PGRES_BAD_RESPONSE:
 374                 ERROR("rlm_sql_postgresql: Bad Response From Server");
 375                 return RLM_SQL_RECONNECT;
 376
 377
 378         case PGRES_NONFATAL_ERROR:
 379         case PGRES_FATAL_ERROR:
 380                 return sql_classify_error(conn->result);
 381         }
 382
 383         return RLM_SQL_ERROR;
 384 }
 385
 386 static sql_rcode_t sql_select_query(rlm_sql_handle_t * handle, rlm_sql_config_t *config, char const *query)
 387 {
 388         return sql_query(handle, config, query);
 389 }
 390
 391 static sql_rcode_t sql_fields(char const **out[], rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 392 {
 393         rlm_sql_postgres_conn_t *conn = handle->conn;
 394
 395         int             fields, i;
 396         char const      **names;
 397
 398         fields = PQnfields(conn->result);
 399         if (fields <= 0) return RLM_SQL_ERROR;
 400
 401         MEM(names = talloc_zero_array(handle, char const *, fields + 1));
 402
 403         for (i = 0; i < fields; i++) names[i] = PQfname(conn->result, i);
 404         *out = names;
 405
 406         return RLM_SQL_OK;
 407 }
 408
 409 static sql_rcode_t sql_fetch_row(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 410 {
 411
 412         int records, i, len;
 413         rlm_sql_postgres_conn_t *conn = handle->conn;
 414
 415         handle->row = NULL;
 416
 417         if (conn->cur_row >= PQntuples(conn->result))
 418                 return 0;
 419
 420         free_result_row(conn);
 421
 422         records = PQnfields(conn->result);
 423         conn->num_fields = records;
 424
 425         if ((PQntuples(conn->result) > 0) && (records > 0)) {
 426                 conn->row = talloc_zero_array(conn, char *, records + 1);
 427                 for (i = 0; i < records; i++) {
 428                         len = PQgetlength(conn->result, conn->cur_row, i);
 429                         conn->row[i] = talloc_array(conn->row, char, len + 1);
 430                         strlcpy(conn->row[i], PQgetvalue(conn->result, conn->cur_row, i), len + 1);
 431                 }
 432                 conn->cur_row++;
 433                 handle->row = conn->row;
 434         }
 435
 436         return 0;
 437 }
 438
 439 static int sql_num_fields(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 440 {
 441         rlm_sql_postgres_conn_t *conn = handle->conn;
 442
 443         conn->affected_rows = PQntuples(conn->result);
 444         if (conn->result)
 445                 return PQnfields(conn->result);
 446
 447         return 0;
 448 }
 449
 450 static sql_rcode_t sql_free_result(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 451 {
 452         rlm_sql_postgres_conn_t *conn = handle->conn;
 453
 454         if (conn->result != NULL) {
 455                 PQclear(conn->result);
 456                 conn->result = NULL;
 457         }
 458
 459         free_result_row(conn);
 460
 461         return 0;
 462 }
 463
 464 /** Retrieves any errors associated with the connection handle
 465  *
 466  * @note Caller will free any memory allocated in ctx.
 467  *
 468  * @param ctx to allocate temporary error buffers in.
 469  * @param out Array of sql_log_entrys to fill.
 470  * @param outlen Length of out array.
 471  * @param handle rlm_sql connection handle.
 472  * @param config rlm_sql config.
 473  * @return number of errors written to the sql_log_entry array.
 474  */
 475 static size_t sql_error(TALLOC_CTX *ctx, sql_log_entry_t out[], size_t outlen,
 476                         rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 477 {
 478         rlm_sql_postgres_conn_t *conn = handle->conn;
 479         char const              *p, *q;
 480         size_t                  i = 0;
 481
 482         rad_assert(outlen > 0);
 483
 484         p = PQerrorMessage(conn->db);
 485         while ((q = strchr(p, '\n'))) {
 486                 out[i].type = L_ERR;
 487                 out[i].msg = talloc_asprintf(ctx, "%.*s", (int) (q - p), p);
 488                 p = q + 1;
 489                 if (++i == outlen) return outlen;
 490         }
 491         if (*p != '\0') {
 492                 out[i].type = L_ERR;
 493                 out[i].msg = p;
 494                 i++;
 495         }
 496
 497         return i;
 498 }
 499
 500 static int sql_affected_rows(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 501 {
 502         rlm_sql_postgres_conn_t *conn = handle->conn;
 503
 504         return conn->affected_rows;
 505 }
 506
 507 /* Exported to rlm_sql */
 508 extern rlm_sql_module_t rlm_sql_postgresql;
 509 rlm_sql_module_t rlm_sql_postgresql = {
 510         .name                           = "rlm_sql_postgresql",
 511 //      .flags                          = RLM_SQL_RCODE_FLAGS_ALT_QUERY,        /* Needs more testing */
 512         .mod_instantiate                = mod_instantiate,
 513         .sql_socket_init                = sql_socket_init,
 514         .sql_query                      = sql_query,
 515         .sql_select_query               = sql_select_query,
 516         .sql_num_fields                 = sql_num_fields,
 517         .sql_fields                     = sql_fields,
 518         .sql_fetch_row                  = sql_fetch_row,
 519         .sql_error                      = sql_error,
 520         .sql_finish_query               = sql_free_result,
 521         .sql_finish_select_query        = sql_free_result,
 522         .sql_affected_rows              = sql_affected_rows
 523 };