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