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