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_sock 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 #include <freeradius-devel/ident.h>
  41 RCSID("$Id$")
  42
  43 #include <freeradius-devel/radiusd.h>
  44
  45 #include <sys/stat.h>
  46
  47 #include <libpq-fe.h>
  48 #include "rlm_sql.h"
  49 #include "sql_postgresql.h"
  50
  51 typedef struct rlm_sql_postgres_sock {
  52    PGconn          *conn;
  53    PGresult        *result;
  54    int             cur_row;
  55    int             num_fields;
  56    int             affected_rows;
  57    char            **row;
  58 } rlm_sql_postgres_sock;
  59
  60 /* Prototypes */
  61 static int sql_close(rlm_sql_handle_t *handle, rlm_sql_config_t *config);
  62
  63 /* Internal function. Return true if the postgresql status value
  64  * indicates successful completion of the query. Return false otherwise
  65 static int
  66 status_is_ok(ExecStatusType status)
  67 {
  68         return status == PGRES_COMMAND_OK || status == PGRES_TUPLES_OK;
  69 }
  70 */
  71
  72
  73 /* Internal function. Return the number of affected rows of the result
  74  * as an int instead of the string that postgresql provides */
  75 static int
  76 affected_rows(PGresult * result)
  77 {
  78         return atoi(PQcmdTuples(result));
  79 }
  80
  81 /* Internal function. Free the row of the current result that's stored
  82  * in the pg_sock struct. */
  83 static void
  84 free_result_row(rlm_sql_postgres_sock * pg_sock)
  85 {
  86         int i;
  87         if (pg_sock->row != NULL) {
  88                 for (i = pg_sock->num_fields-1; i >= 0; i--) {
  89                         if (pg_sock->row[i] != NULL) {
  90                                 free(pg_sock->row[i]);
  91                         }
  92                 }
  93                 free((char*)pg_sock->row);
  94                 pg_sock->row = NULL;
  95                 pg_sock->num_fields = 0;
  96         }
  97 }
  98
  99
 100 /*************************************************************************
 101 *       Function: check_fatal_error
 102 *
 103 *       Purpose:  Check error type and behave accordingly
 104 *
 105 *************************************************************************/
 106
 107 static int check_fatal_error (char *errorcode)
 108 {
 109         int x = 0;
 110
 111         /*
 112         Check the error code to see if we should reconnect or not
 113         Error Code table taken from
 114         http://www.postgresql.org/docs/8.1/interactive/errcodes-appendix.html
 115         */
 116
 117         if (!errorcode) return -1;
 118
 119         while(errorcodes[x].errorcode != NULL){
 120                 if (strcmp(errorcodes[x].errorcode, errorcode) == 0){
 121                         radlog(L_DBG, "rlm_sql_postgresql: Postgresql Fatal Error: [%s: %s] Occurred!!", errorcode, errorcodes[x].meaning);
 122                         if (errorcodes[x].shouldreconnect == 1)
 123                                 return SQL_DOWN;
 124                         else
 125                                 return -1;
 126                 }
 127                 x++;
 128         }
 129
 130         radlog(L_DBG, "rlm_sql_postgresql: Postgresql Fatal Error: [%s] Occurred!!", errorcode);
 131         /*      We don't seem to have a matching error class/code */
 132         return -1;
 133 }
 134
 135
 136
 137 /*************************************************************************
 138  *
 139  *      Function: sql_create_socket
 140  *
 141  *      Purpose: Establish connection to the db
 142  *
 143  *************************************************************************/
 144 static int sql_init_socket(rlm_sql_handle_t *handle, rlm_sql_config_t *config) {
 145         char connstring[2048];
 146         const char *port, *host;
 147         rlm_sql_postgres_sock *pg_sock;
 148
 149 #ifdef HAVE_OPENSSL_CRYPTO_H
 150         static int ssl_init = 0;
 151
 152         if (!ssl_init) {
 153                 PQinitSSL(0);
 154                 ssl_init = 1;
 155         }
 156
 157 #endif
 158
 159         if (config->sql_server[0] != '\0') {
 160                 host = " host=";
 161         } else {
 162                 host = "";
 163         }
 164
 165         if (config->sql_port[0] != '\0') {
 166                 port = " port=";
 167         } else {
 168                 port = "";
 169         }
 170
 171         if (!handle->conn) {
 172                 handle->conn = (rlm_sql_postgres_sock *)rad_malloc(sizeof(rlm_sql_postgres_sock));
 173                 if (!handle->conn) {
 174                         return -1;
 175                 }
 176         }
 177
 178         pg_sock = handle->conn;
 179         memset(pg_sock, 0, sizeof(*pg_sock));
 180
 181         snprintf(connstring, sizeof(connstring),
 182                         "dbname=%s%s%s%s%s user=%s password=%s",
 183                         config->sql_db, host, config->sql_server,
 184                         port, config->sql_port,
 185                         config->sql_login, config->sql_password);
 186         pg_sock->row=NULL;
 187         pg_sock->result=NULL;
 188         pg_sock->conn=PQconnectdb(connstring);
 189
 190         if (PQstatus(pg_sock->conn) != CONNECTION_OK) {
 191                 radlog(L_ERR, "rlm_sql_postgresql: Couldn't connect socket to PostgreSQL server %s@%s:%s", config->sql_login, config->sql_server, config->sql_db);
 192                 /*radlog(L_ERR, "rlm_sql_postgresql: Postgresql error '%s'", PQerrorMessage(pg_sock->conn));*/
 193                 sql_close(handle, config);
 194                 return SQL_DOWN;
 195         }
 196
 197         return 0;
 198 }
 199
 200 /*************************************************************************
 201  *
 202  *      Function: sql_query
 203  *
 204  *      Purpose: Issue a query to the database
 205  *
 206  *************************************************************************/
 207 static int sql_query(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config,
 208                      char *querystr) {
 209
 210         rlm_sql_postgres_sock *pg_sock = handle->conn;
 211         int numfields = 0;
 212         char *errorcode;
 213         char *errormsg;
 214
 215         if (pg_sock->conn == NULL) {
 216                 radlog(L_ERR, "rlm_sql_postgresql: Socket not connected");
 217                 return SQL_DOWN;
 218         }
 219
 220         pg_sock->result = PQexec(pg_sock->conn, querystr);
 221                 /*
 222                  * Returns a PGresult pointer or possibly a null pointer.
 223                  * A non-null pointer will generally be returned except in
 224                  * out-of-memory conditions or serious errors such as inability
 225                  * to send the command to the server. If a null pointer is
 226                  * returned, it should be treated like a PGRES_FATAL_ERROR
 227                  * result.
 228                  */
 229         if (!pg_sock->result)
 230         {
 231                 radlog(L_ERR, "rlm_sql_postgresql: PostgreSQL Query failed Error: %s",
 232                                 PQerrorMessage(pg_sock->conn));
 233                 /* As this error COULD be a connection error OR an out-of-memory
 234                  * condition return value WILL be wrong SOME of the time regardless!
 235                  * Pick your poison....
 236                  */
 237                 return  SQL_DOWN;
 238         } else {
 239                 ExecStatusType status = PQresultStatus(pg_sock->result);
 240                 radlog(L_DBG, "rlm_sql_postgresql: Status: %s", PQresStatus(status));
 241
 242                 switch (status){
 243
 244                         case PGRES_COMMAND_OK:
 245                                 /*Successful completion of a command returning no data.*/
 246
 247                                 /*affected_rows function only returns
 248                                 the number of affected rows of a command
 249                                 returning no data...
 250                                 */
 251                                 pg_sock->affected_rows  = affected_rows(pg_sock->result);
 252                                 radlog(L_DBG, "rlm_sql_postgresql: query affected rows = %i", pg_sock->affected_rows);
 253                                 return 0;
 254
 255                         break;
 256
 257                         case PGRES_TUPLES_OK:
 258                                 /*Successful completion of a command returning data (such as a SELECT or SHOW).*/
 259
 260                                 pg_sock->cur_row = 0;
 261                                 pg_sock->affected_rows = PQntuples(pg_sock->result);
 262                                 numfields = PQnfields(pg_sock->result); /*Check row storing functions..*/
 263                                 radlog(L_DBG, "rlm_sql_postgresql: query affected rows = %i , fields = %i", pg_sock->affected_rows, numfields);
 264                                 return 0;
 265
 266                         break;
 267
 268                         case PGRES_BAD_RESPONSE:
 269                                 /*The server's response was not understood.*/
 270                                 radlog(L_DBG, "rlm_sql_postgresql: Bad Response From Server!!");
 271                                 return -1;
 272
 273                         break;
 274
 275                         case PGRES_NONFATAL_ERROR:
 276                                 /*A nonfatal error (a notice or warning) occurred. Possibly never returns*/
 277
 278                                 return -1;
 279
 280                         break;
 281
 282                         case PGRES_FATAL_ERROR:
 283 #if defined(PG_DIAG_SQLSTATE) && defined(PG_DIAG_MESSAGE_PRIMARY)
 284                                 /*A fatal error occurred.*/
 285
 286                                 errorcode = PQresultErrorField(pg_sock->result, PG_DIAG_SQLSTATE);
 287                                 errormsg  = PQresultErrorField(pg_sock->result, PG_DIAG_MESSAGE_PRIMARY);
 288                                 radlog(L_DBG, "rlm_sql_postgresql: Error %s", errormsg);
 289                                 return check_fatal_error(errorcode);
 290 #endif
 291
 292                         break;
 293
 294                         default:
 295                                 /* FIXME: An unhandled error occurred.*/
 296
 297                                 /* PGRES_EMPTY_QUERY PGRES_COPY_OUT PGRES_COPY_IN */
 298
 299                                 return -1;
 300
 301                         break;
 302
 303
 304                 }
 305
 306                 /*
 307                         Note to self ... sql_store_result returns 0 anyway
 308                         after setting the handle->affected_rows..
 309                         sql_num_fields returns 0 at worst case which means the check below
 310                         has a really small chance to return false..
 311                         lets remove it then .. yuck!!
 312                 */
 313                 /*
 314                 } else {
 315                         if ((sql_store_result(handle, config) == 0)
 316                                         && (sql_num_fields(handle, config) >= 0))
 317                                 return 0;
 318                         else
 319                                 return -1;
 320                 }
 321                 */
 322         }
 323         return -1;
 324 }
 325
 326
 327 /*************************************************************************
 328  *
 329  *      Function: sql_select_query
 330  *
 331  *      Purpose: Issue a select query to the database
 332  *
 333  *************************************************************************/
 334 static int sql_select_query(rlm_sql_handle_t * handle, rlm_sql_config_t *config, char *querystr) {
 335         return sql_query(handle, config, querystr);
 336 }
 337
 338
 339 /*************************************************************************
 340  *
 341  *      Function: sql_destroy_socket
 342  *
 343  *      Purpose: Free socket and private connection data
 344  *
 345  *************************************************************************/
 346 static int sql_destroy_socket(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
 347 {
 348         free(handle->conn);
 349         handle->conn = NULL;
 350         return 0;
 351 }
 352
 353 /*************************************************************************
 354  *
 355  *      Function: sql_fetch_row
 356  *
 357  *      Purpose: database specific fetch_row. Returns a rlm_sql_row_t struct
 358  *      with all the data for the query in 'handle->row'. Returns
 359  *      0 on success, -1 on failure, SQL_DOWN if 'database is down'.
 360  *
 361  *************************************************************************/
 362 static int sql_fetch_row(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config) {
 363
 364         int records, i, len;
 365         rlm_sql_postgres_sock *pg_sock = handle->conn;
 366
 367         handle->row = NULL;
 368
 369         if (pg_sock->cur_row >= PQntuples(pg_sock->result))
 370                 return 0;
 371
 372         free_result_row(pg_sock);
 373
 374         records = PQnfields(pg_sock->result);
 375         pg_sock->num_fields = records;
 376
 377         if ((PQntuples(pg_sock->result) > 0) && (records > 0)) {
 378                 pg_sock->row = (char **)rad_malloc((records+1)*sizeof(char *));
 379                 memset(pg_sock->row, '\0', (records+1)*sizeof(char *));
 380
 381                 for (i = 0; i < records; i++) {
 382                         len = PQgetlength(pg_sock->result, pg_sock->cur_row, i);
 383                         pg_sock->row[i] = (char *)rad_malloc(len+1);
 384                         memset(pg_sock->row[i], '\0', len+1);
 385                         strlcpy(pg_sock->row[i], PQgetvalue(pg_sock->result, pg_sock->cur_row,i),len + 1);
 386                 }
 387                 pg_sock->cur_row++;
 388                 handle->row = pg_sock->row;
 389         }
 390
 391         return 0;
 392 }
 393
 394 /*************************************************************************
 395  *
 396  *      Function: sql_num_fields
 397  *
 398  *      Purpose: database specific num_fields. Returns number of rows in
 399  *               query
 400  *
 401  *************************************************************************/
 402 static int sql_num_fields(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 403 {
 404         rlm_sql_postgres_sock *pg_sock = handle->conn;
 405
 406         pg_sock->affected_rows = PQntuples(pg_sock->result);
 407         if (pg_sock->result)
 408                 return PQnfields(pg_sock->result);
 409
 410         return 0;
 411 }
 412
 413
 414
 415 /*************************************************************************
 416  *
 417  *      Function: sql_free_result
 418  *
 419  *      Purpose: database specific free_result. Frees memory allocated
 420  *               for a result set
 421  *
 422  *************************************************************************/
 423 static int sql_free_result(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config) {
 424
 425         rlm_sql_postgres_sock *pg_sock = handle->conn;
 426
 427         if (pg_sock->result) {
 428                 PQclear(pg_sock->result);
 429                 pg_sock->result = NULL;
 430         }
 431
 432         free_result_row(pg_sock);
 433
 434         return 0;
 435 }
 436
 437
 438
 439 /*************************************************************************
 440  *
 441  *      Function: sql_error
 442  *
 443  *      Purpose: database specific error. Returns error associated with
 444  *               connection
 445  *
 446  *************************************************************************/
 447 static const char *sql_error(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config) {
 448
 449         rlm_sql_postgres_sock *pg_sock = handle->conn;
 450
 451         return PQerrorMessage(pg_sock->conn);
 452 }
 453
 454
 455 /*************************************************************************
 456  *
 457  *      Function: sql_close
 458  *
 459  *      Purpose: database specific close. Closes an open database
 460  *               connection
 461  *
 462  *************************************************************************/
 463 static int sql_close(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config) {
 464
 465         rlm_sql_postgres_sock *pg_sock = handle->conn;
 466
 467         if (!pg_sock->conn) return 0;
 468
 469         /* PQfinish also frees the memory used by the PGconn structure */
 470         PQfinish(pg_sock->conn);
 471         pg_sock->conn = NULL;
 472
 473         return 0;
 474 }
 475
 476
 477 /*************************************************************************
 478  *
 479  *      Function: sql_finish_query
 480  *
 481  *      Purpose: End the query, such as freeing memory
 482  *
 483  *************************************************************************/
 484 static int sql_finish_query(rlm_sql_handle_t * handle, rlm_sql_config_t *config) {
 485
 486         return sql_free_result(handle, config);
 487 }
 488
 489
 490
 491 /*************************************************************************
 492  *
 493  *      Function: sql_finish_select_query
 494  *
 495  *      Purpose: End the select query, such as freeing memory or result
 496  *
 497  *************************************************************************/
 498 static int sql_finish_select_query(rlm_sql_handle_t * handle, rlm_sql_config_t *config) {
 499
 500         return sql_free_result(handle, config);
 501 }
 502
 503
 504 /*************************************************************************
 505  *
 506  *      Function: sql_affected_rows
 507  *
 508  *      Purpose: Return the number of rows affected by the last query.
 509  *
 510  *************************************************************************/
 511 static int sql_affected_rows(rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config) {
 512         rlm_sql_postgres_sock *pg_sock = handle->conn;
 513
 514         return pg_sock->affected_rows;
 515 }
 516
 517
 518 static int NEVER_RETURNS
 519 not_implemented(UNUSED rlm_sql_handle_t * handle, UNUSED rlm_sql_config_t *config)
 520 {
 521         radlog(L_ERR, "sql_postgresql: calling unimplemented function");
 522         exit(1);
 523 }
 524
 525
 526 /* Exported to rlm_sql */
 527 rlm_sql_module_t rlm_sql_postgresql = {
 528         "rlm_sql_postgresql",
 529         sql_init_socket,
 530         sql_destroy_socket,
 531         sql_query,
 532         sql_select_query,
 533         not_implemented, /* sql_store_result */
 534         sql_num_fields,
 535         not_implemented, /* sql_num_rows */
 536         sql_fetch_row,
 537         not_implemented, /* sql_free_result */
 538         sql_error,
 539         sql_close,
 540         sql_finish_query,
 541         sql_finish_select_query,
 542         sql_affected_rows,
 543 };