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