src/modules/rlm_sql/drivers/rlm_sql_postgresql/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., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  19  *
  20  * Copyright 2000  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 /* Modification of rlm_sql_mysql to handle postgres */
  41
  42 #include <stdio.h>
  43 #include <sys/stat.h>
  44 #include <stdlib.h>
  45 #include <string.h>
  46
  47 #include "radiusd.h"
  48
  49 #include        <libpq-fe.h>
  50 #include        "rlm_sql.h"
  51
  52 typedef struct rlm_sql_postgres_sock {
  53    PGconn          *conn;
  54    PGresult        *result;
  55    int             cur_row;
  56    int             num_fields;
  57     int            affected_rows;
  58    char            **row;
  59 } rlm_sql_postgres_sock;
  60
  61 /* Prototypes */
  62 static int sql_store_result(SQLSOCK * sqlsocket, SQL_CONFIG *config);
  63 static int sql_num_fields(SQLSOCK * sqlsocket, SQL_CONFIG *config);
  64 static int sql_close(SQLSOCK * sqlsocket, SQL_CONFIG *config);
  65
  66 /* Internal function. Return true if the postgresql status value
  67  * indicates successful completion of the query. Return false otherwise
  68  */
  69 static int
  70 status_is_ok(ExecStatusType status)
  71 {
  72         return status == PGRES_COMMAND_OK || status == PGRES_TUPLES_OK;
  73 }
  74
  75
  76 /* Internal function. Return the number of affected rows of the result
  77  * as an int instead of the string that postgresql provides */
  78 static int
  79 affected_rows(PGresult * result)
  80 {
  81         return atoi(PQcmdTuples(result));
  82 }
  83
  84 /* Internal function. Free the row of the current result that's stored
  85  * in the pg_sock struct. */
  86 static void
  87 free_result_row(rlm_sql_postgres_sock * pg_sock)
  88 {
  89         int i;
  90         if (pg_sock->row != NULL) {
  91                 for (i = pg_sock->num_fields-1; i >= 0; i--) {
  92                         if (pg_sock->row[i] != NULL) {
  93                                 xfree(pg_sock->row[i]);
  94                         }
  95                 }
  96                 xfree((char*)pg_sock->row);
  97                 pg_sock->row = NULL;
  98                 pg_sock->num_fields = 0;
  99         }
 100 }
 101
 102 /*************************************************************************
 103  *
 104  *      Function: sql_check_error
 105  *
 106  *      Purpose: check the error to see if the server is down
 107  *
 108  *      Note: It is possible that something other than a connection error
 109  *      could cause PGRES_FATAL_ERROR. If that happens a reconnect will
 110  *      occur anyway. Not optimal, but I couldn't find a way to check it.
 111  *                              Peter Nixon <codemonkey@peternixon.net>
 112  *
 113
 114 ************************************************************************/
 115 static int sql_check_error(int error) {
 116         switch(error) {
 117         case PGRES_FATAL_ERROR:
 118         case -1:
 119                 radlog(L_DBG, "rlm_sql_postgresql: Postgresql check_error: %s, returning SQL_DOWN", PQresStatus(error));
 120                 return SQL_DOWN;
 121                 break;
 122
 123         case PGRES_COMMAND_OK:
 124         case PGRES_TUPLES_OK:
 125         case 0:
 126                 return 0;
 127                 break;
 128
 129         case PGRES_NONFATAL_ERROR:
 130         case PGRES_BAD_RESPONSE:
 131         default:
 132                 radlog(L_DBG, "rlm_sql_postgresql: Postgresql check_error: %s received", PQresStatus(error));
 133                 return -1;
 134                 break;
 135         }
 136 }
 137
 138
 139 /*************************************************************************
 140  *
 141  *      Function: sql_create_socket
 142  *
 143  *      Purpose: Establish connection to the db
 144  *
 145  *************************************************************************/
 146 static int sql_init_socket(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
 147         char connstring[2048];
 148         char *port, *host;
 149         rlm_sql_postgres_sock *pg_sock;
 150
 151         if (config->sql_server[0] != '\0') {
 152                 host = " host=";
 153         } else {
 154                 host = "";
 155         }
 156
 157         if (config->sql_port[0] != '\0') {
 158                 port = " port=";
 159         } else {
 160                 port = "";
 161         }
 162
 163         if (!sqlsocket->conn) {
 164                 sqlsocket->conn = (rlm_sql_postgres_sock *)rad_malloc(sizeof(rlm_sql_postgres_sock));
 165                 if (!sqlsocket->conn) {
 166                         return -1;
 167                 }
 168         }
 169
 170         pg_sock = sqlsocket->conn;
 171         memset(pg_sock, 0, sizeof(*pg_sock));
 172
 173         snprintf(connstring, sizeof(connstring),
 174                         "dbname=%s%s%s%s%s user=%s password=%s",
 175                         config->sql_db, host, config->sql_server,
 176                         port, config->sql_port,
 177                         config->sql_login, config->sql_password);
 178         pg_sock->row=NULL;
 179         pg_sock->result=NULL;
 180         pg_sock->conn=PQconnectdb(connstring);
 181
 182         if (PQstatus(pg_sock->conn) == CONNECTION_BAD) {
 183                 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);
 184                 radlog(L_ERR, "rlm_sql_postgresql: Postgresql error '%s'", PQerrorMessage(pg_sock->conn));
 185                 sql_close(sqlsocket, config);
 186                 return SQL_DOWN;
 187         }
 188
 189         return 0;
 190 }
 191
 192 /*************************************************************************
 193  *
 194  *      Function: sql_query
 195  *
 196  *      Purpose: Issue a query to the database
 197  *
 198  *************************************************************************/
 199 static int sql_query(SQLSOCK * sqlsocket, SQL_CONFIG *config, char *querystr) {
 200
 201         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 202
 203         if (config->sqltrace)
 204                 radlog(L_DBG,"rlm_sql_postgresql: query:\n%s", querystr);
 205
 206         if (pg_sock->conn == NULL) {
 207                 radlog(L_ERR, "rlm_sql_postgresql: Socket not connected");
 208                 return SQL_DOWN;
 209         }
 210
 211         pg_sock->result = PQexec(pg_sock->conn, querystr);
 212                 /* Returns a result pointer or possibly a NULL pointer.
 213                  * A non-NULL pointer will generally be returned except in
 214                  * out-of-memory conditions or serious errors such as inability
 215                  * to send the command to the backend. If a NULL is returned,
 216                  *  it should be treated like a PGRES_FATAL_ERROR result.
 217                  * Use PQerrorMessage to get more information about the error.
 218                  */
 219         if (!pg_sock->result)
 220         {
 221                 radlog(L_ERR, "rlm_sql_postgresql: PostgreSQL Query failed Error: %s",
 222                                 PQerrorMessage(pg_sock->conn));
 223                 return  SQL_DOWN;
 224         } else {
 225                 ExecStatusType status = PQresultStatus(pg_sock->result);
 226
 227                 radlog(L_DBG, "rlm_sql_postgresql: Status: %s", PQresStatus(status));
 228
 229                 radlog(L_DBG, "rlm_sql_postgresql: affected rows = %s",
 230                                 PQcmdTuples(pg_sock->result));
 231
 232                 if (!status_is_ok(status))
 233                         return sql_check_error(status);
 234
 235                 if (strncasecmp("select", querystr, 6) != 0) {
 236                         /* store the number of affected rows because the sql module
 237                          * calls finish_query before it retrieves the number of affected
 238                          * rows from the driver */
 239                         pg_sock->affected_rows = affected_rows(pg_sock->result);
 240                         return 0;
 241                 } else {
 242                         if ((sql_store_result(sqlsocket, config) == 0)
 243                                         && (sql_num_fields(sqlsocket, config) >= 0))
 244                                 return 0;
 245                         else
 246                                 return -1;
 247                 }
 248         }
 249 }
 250
 251
 252 /*************************************************************************
 253  *
 254  *      Function: sql_select_query
 255  *
 256  *      Purpose: Issue a select query to the database
 257  *
 258  *************************************************************************/
 259 static int sql_select_query(SQLSOCK * sqlsocket, SQL_CONFIG *config, char *querystr) {
 260         return sql_query(sqlsocket, config, querystr);
 261 }
 262
 263
 264 /*************************************************************************
 265  *
 266  *      Function: sql_store_result
 267  *
 268  *      Purpose: database specific store_result function. Returns a result
 269  *               set for the query.
 270  *
 271  *************************************************************************/
 272 static int sql_store_result(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 273         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 274
 275         pg_sock->cur_row = 0;
 276         pg_sock->affected_rows = PQntuples(pg_sock->result);
 277         return 0;
 278 }
 279
 280
 281 /*************************************************************************
 282  *
 283  *      Function: sql_destroy_socket
 284  *
 285  *      Purpose: Free socket and private connection data
 286  *
 287  *************************************************************************/
 288 static int sql_destroy_socket(SQLSOCK *sqlsocket, SQL_CONFIG *config)
 289 {
 290         free(sqlsocket->conn);
 291         sqlsocket->conn = NULL;
 292         return 0;
 293 }
 294
 295 /*************************************************************************
 296  *
 297  *      Function: sql_num_fields
 298  *
 299  *      Purpose: database specific num_fields function. Returns number
 300  *               of columns from query
 301  *
 302  *************************************************************************/
 303 static int sql_num_fields(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 304
 305         int num = 0;
 306         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 307
 308         if (!(num = PQnfields(pg_sock->result))) {
 309                 radlog(L_ERR, "rlm_sql_postgresql: PostgreSQL Error: Cannot get result");
 310                 radlog(L_ERR, "rlm_sql_postgresql: PostgreSQL error: %s", PQerrorMessage(pg_sock->conn));
 311         }
 312         return num;
 313 }
 314
 315
 316 /*************************************************************************
 317  *
 318  *      Function: sql_fetch_row
 319  *
 320  *      Purpose: database specific fetch_row. Returns a SQL_ROW struct
 321  *               with all the data for the query in 'sqlsocket->row'. Returns
 322  *               0 on success, -1 on failure, SQL_DOWN if 'database is down'.
 323  *
 324  *************************************************************************/
 325 static int sql_fetch_row(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 326
 327         int records, i, len;
 328         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 329
 330         sqlsocket->row = NULL;
 331
 332         if (pg_sock->cur_row >= PQntuples(pg_sock->result))
 333                 return 0;
 334
 335         free_result_row(pg_sock);
 336
 337         records = PQnfields(pg_sock->result);
 338         pg_sock->num_fields = records;
 339
 340         if ((PQntuples(pg_sock->result) > 0) && (records > 0)) {
 341                 pg_sock->row = (char **)rad_malloc((records+1)*sizeof(char *));
 342                 memset(pg_sock->row, '\0', (records+1)*sizeof(char *));
 343
 344                 for (i = 0; i < records; i++) {
 345                         len = PQgetlength(pg_sock->result, pg_sock->cur_row, i);
 346                         pg_sock->row[i] = (char *)rad_malloc(len+1);
 347                         memset(pg_sock->row[i], '\0', len+1);
 348                         strncpy(pg_sock->row[i], PQgetvalue(pg_sock->result, pg_sock->cur_row,i),len);
 349                 }
 350                 pg_sock->cur_row++;
 351                 sqlsocket->row = pg_sock->row;
 352                 return 0;
 353         } else {
 354                 return 0;
 355         }
 356 }
 357
 358
 359
 360 /*************************************************************************
 361  *
 362  *      Function: sql_free_result
 363  *
 364  *      Purpose: database specific free_result. Frees memory allocated
 365  *               for a result set
 366  *
 367  *************************************************************************/
 368 static int sql_free_result(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 369
 370         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 371
 372         if (pg_sock->result) {
 373                 PQclear(pg_sock->result);
 374                 pg_sock->result = NULL;
 375         }
 376 #if 0
 377         /*
 378          *  Commented out because it appears to free memory too early.
 379          */
 380         free_result_row(pg_sock);
 381 #endif
 382
 383         return 0;
 384 }
 385
 386
 387
 388 /*************************************************************************
 389  *
 390  *      Function: sql_error
 391  *
 392  *      Purpose: database specific error. Returns error associated with
 393  *               connection
 394  *
 395  *************************************************************************/
 396 static char *sql_error(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 397
 398         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 399
 400         return PQerrorMessage(pg_sock->conn);
 401 }
 402
 403
 404 /*************************************************************************
 405  *
 406  *      Function: sql_close
 407  *
 408  *      Purpose: database specific close. Closes an open database
 409  *               connection
 410  *
 411  *************************************************************************/
 412 static int sql_close(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 413
 414         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 415
 416         if (!pg_sock->conn) return 0;
 417
 418         /* PQfinish also frees the memory used by the PGconn structure */
 419         PQfinish(pg_sock->conn);
 420         pg_sock->conn = NULL;
 421
 422         return 0;
 423 }
 424
 425
 426 /*************************************************************************
 427  *
 428  *      Function: sql_finish_query
 429  *
 430  *      Purpose: End the query, such as freeing memory
 431  *
 432  *************************************************************************/
 433 static int sql_finish_query(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 434
 435         return sql_free_result(sqlsocket, config);
 436 }
 437
 438
 439
 440 /*************************************************************************
 441  *
 442  *      Function: sql_finish_select_query
 443  *
 444  *      Purpose: End the select query, such as freeing memory or result
 445  *
 446  *************************************************************************/
 447 static int sql_finish_select_query(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 448
 449         return sql_free_result(sqlsocket, config);
 450 }
 451
 452
 453 /*************************************************************************
 454  *
 455  *      Function: sql_affected_rows
 456  *
 457  *      Purpose: Return the number of rows affected by the last query.
 458  *
 459  *************************************************************************/
 460 static int sql_affected_rows(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 461         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 462
 463         return pg_sock->affected_rows;
 464 }
 465
 466
 467 static int NEVER_RETURNS
 468 not_implemented(SQLSOCK * sqlsocket, SQL_CONFIG *config)
 469 {
 470         radlog(L_ERR, "sql_postgresql: calling unimplemented function");
 471         exit(1);
 472 }
 473
 474
 475 /* Exported to rlm_sql */
 476 rlm_sql_module_t rlm_sql_postgresql = {
 477         "rlm_sql_postgresql",
 478         sql_init_socket,
 479         sql_destroy_socket,
 480         sql_query,
 481         sql_select_query,
 482         not_implemented, /* sql_store_result */
 483         not_implemented, /* sql_num_fields */
 484         not_implemented, /* sql_num_rows */
 485         sql_fetch_row,
 486         not_implemented, /* sql_free_result */
 487         sql_error,
 488         sql_close,
 489         sql_finish_query,
 490         sql_finish_select_query,
 491         sql_affected_rows,
 492 };