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 #include "sql_postgresql.h"
  49
  50 /* Internal function. Return true if the postgresql status value
  51  * indicates successful completion of the query. Return false otherwise
  52  */
  53 static int
  54 status_is_ok(ExecStatusType status)
  55 {
  56         return status == PGRES_COMMAND_OK || status == PGRES_TUPLES_OK;
  57 }
  58
  59
  60 /* Internal function. Return the number of affected rows of the result
  61  * as an int instead of the string that postgresql provides */
  62 static int
  63 affected_rows(PGresult * result)
  64 {
  65         return atoi(PQcmdTuples(result));
  66 }
  67
  68 /* Internal function. Free the row of the current result that's stored
  69  * in the pg_sock struct. */
  70 static void
  71 free_result_row(rlm_sql_postgres_sock * pg_sock)
  72 {
  73         int i;
  74         if (pg_sock->row != NULL) {
  75                 for (i = pg_sock->num_fields-1; i >= 0; i--) {
  76                         if (pg_sock->row[i] != NULL) {
  77                                 xfree(pg_sock->row[i]);
  78                         }
  79                 }
  80                 xfree((char*)pg_sock->row);
  81                 pg_sock->row = NULL;
  82                 pg_sock->num_fields = 0;
  83         }
  84 }
  85
  86 /*************************************************************************
  87  *
  88  *      Function: sql_create_socket
  89  *
  90  *      Purpose: Establish connection to the db
  91  *
  92  *************************************************************************/
  93 int sql_init_socket(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
  94         char connstring[2048];
  95         char *port, *host;
  96         rlm_sql_postgres_sock *pg_sock;
  97
  98         if (config->sql_server[0] != '\0') {
  99                 host = " host=";
 100         } else {
 101                 host = "";
 102         }
 103
 104         if (config->sql_port[0] != '\0') {
 105                 port = " port=";
 106         } else {
 107                 port = "";
 108         }
 109
 110         sqlsocket->conn = (rlm_sql_postgres_sock *)rad_malloc(sizeof(rlm_sql_postgres_sock));
 111
 112         pg_sock = sqlsocket->conn;
 113
 114         snprintf(connstring, sizeof(connstring),
 115                         "dbname=%s%s%s%s%s user=%s password=%s",
 116                         config->sql_db, host, config->sql_server,
 117                         port, config->sql_port,
 118                         config->sql_login, config->sql_password);
 119         pg_sock->row=NULL;
 120         pg_sock->result=NULL;
 121         pg_sock->conn=PQconnectdb(connstring);
 122
 123         if (PQstatus(sqlsocket->conn) == CONNECTION_BAD) {
 124                 radlog(L_ERR, "sql_postgresql: Couldn't connect socket to PostgreSQL server %s@%s:%s", config->sql_login, config->sql_server, config->sql_db);
 125                 radlog(L_ERR, "sql_postgresql: Postgresql error '%s'", PQerrorMessage(pg_sock->conn));
 126                 PQfinish(pg_sock->conn);
 127                 return -1;
 128         }
 129
 130         return 0;
 131 }
 132
 133 /*************************************************************************
 134  *
 135  *      Function: sql_query
 136  *
 137  *      Purpose: Issue a query to the database
 138  *
 139  *************************************************************************/
 140 int sql_query(SQLSOCK * sqlsocket, SQL_CONFIG *config, char *querystr) {
 141
 142         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 143
 144         if (config->sqltrace)
 145                 radlog(L_DBG,"query:\n%s", querystr);
 146
 147         if (pg_sock->conn == NULL) {
 148                 radlog(L_ERR, "Socket not connected");
 149                 return -1;
 150         }
 151
 152         pg_sock->result = PQexec(pg_sock->conn, querystr);
 153         if (!pg_sock->result)
 154         {
 155                 radlog(L_ERR, "PostgreSQL Query failed Error: %s",
 156                                 PQerrorMessage(pg_sock->conn));
 157                 return  -1;
 158         } else {
 159                 ExecStatusType status = PQresultStatus(pg_sock->result);
 160
 161                 radlog(L_DBG, "rlm_postgresql Status: %s", PQresStatus(status));
 162
 163                 radlog(L_DBG, "sql_postgresql: affected rows = %s",
 164                                 PQcmdTuples(pg_sock->result));
 165
 166                 if (!status_is_ok(status))
 167                         return -1;
 168
 169                 if (strncasecmp("select", querystr, 6) != 0) {
 170                         /* store the number of affected rows because the sql module
 171                          * calls finish_query before it retrieves the number of affected
 172                          * rows from the driver */
 173                         pg_sock->affected_rows = affected_rows(pg_sock->result);
 174                         return 0;
 175                 } else {
 176                         if ((sql_store_result(sqlsocket, config) == 0)
 177                                         && (sql_num_fields(sqlsocket, config) >= 0))
 178                                 return 0;
 179                         else
 180                                 return -1;
 181                 }
 182         }
 183 }
 184
 185
 186 /*************************************************************************
 187  *
 188  *      Function: sql_select_query
 189  *
 190  *      Purpose: Issue a select query to the database
 191  *
 192  *************************************************************************/
 193 int sql_select_query(SQLSOCK * sqlsocket, SQL_CONFIG *config, char *querystr) {
 194         sql_query(sqlsocket, config, querystr);
 195 }
 196
 197
 198 /*************************************************************************
 199  *
 200  *      Function: sql_store_result
 201  *
 202  *      Purpose: database specific store_result function. Returns a result
 203  *               set for the query.
 204  *
 205  *************************************************************************/
 206 int sql_store_result(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 207         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 208
 209         pg_sock->cur_row = 0;
 210         pg_sock->affected_rows = affected_rows(pg_sock->result);
 211         return 0;
 212 }
 213
 214
 215 /*************************************************************************
 216  *
 217  *      Function: sql_num_fields
 218  *
 219  *      Purpose: database specific num_fields function. Returns number
 220  *               of columns from query
 221  *
 222  *************************************************************************/
 223 int sql_num_fields(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 224
 225         int num = 0;
 226         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 227
 228         if (!(num = PQnfields(pg_sock->result))) {
 229                 radlog(L_ERR, "PostgreSQL Error: Cannot get result");
 230                 radlog(L_ERR, "PostgreSQL error: %s", PQerrorMessage(pg_sock->conn));
 231         }
 232         return num;
 233 }
 234
 235
 236 /*************************************************************************
 237  *
 238  *      Function: sql_fetch_row
 239  *
 240  *      Purpose: database specific fetch_row. Returns a SQL_ROW struct
 241  *               with all the data for the query
 242  *
 243  *************************************************************************/
 244 SQL_ROW sql_fetch_row(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 245
 246         int records, i, len;
 247         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 248
 249         if (pg_sock->cur_row >= PQntuples(pg_sock->result))
 250                 return NULL;
 251
 252         free_result_row(pg_sock);
 253
 254         records = PQnfields(pg_sock->result);
 255         pg_sock->num_fields = records;
 256
 257         if ((PQntuples(pg_sock->result) > 0) && (records > 0)) {
 258                 pg_sock->row = (char **)rad_malloc(records*sizeof(char *)+1);
 259                 memset(pg_sock->row, '\0', records*sizeof(char *)+1);
 260
 261                 for (i = 0; i < records; i++) {
 262                         len = PQgetlength(pg_sock->result, pg_sock->cur_row, i);
 263                         pg_sock->row[i] = (char *)rad_malloc(len+1);
 264                         memset(pg_sock->row[i], '\0', len+1);
 265                         strncpy(pg_sock->row[i], PQgetvalue(pg_sock->result, pg_sock->cur_row,i),len);
 266                 }
 267                 pg_sock->cur_row++;
 268                 return pg_sock->row;
 269         } else {
 270                 return NULL;
 271         }
 272 }
 273
 274
 275
 276 /*************************************************************************
 277  *
 278  *      Function: sql_free_result
 279  *
 280  *      Purpose: database specific free_result. Frees memory allocated
 281  *               for a result set
 282  *
 283  *************************************************************************/
 284 int sql_free_result(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 285
 286         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 287
 288         if (pg_sock->result) {
 289                 PQclear(pg_sock->result);
 290                 pg_sock->result = NULL;
 291         }
 292         free_result_row(pg_sock);
 293
 294         return 0;
 295 }
 296
 297
 298
 299 /*************************************************************************
 300  *
 301  *      Function: sql_error
 302  *
 303  *      Purpose: database specific error. Returns error associated with
 304  *               connection
 305  *
 306  *************************************************************************/
 307 char *sql_error(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 308
 309         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 310
 311         return PQerrorMessage(pg_sock->conn);
 312 }
 313
 314
 315 /*************************************************************************
 316  *
 317  *      Function: sql_close
 318  *
 319  *      Purpose: database specific close. Closes an open database
 320  *               connection
 321  *
 322  *************************************************************************/
 323 int sql_close(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 324
 325         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 326
 327         PQfinish(pg_sock->conn);
 328         pg_sock->conn = NULL;
 329
 330         return 0;
 331 }
 332
 333
 334 /*************************************************************************
 335  *
 336  *      Function: sql_finish_query
 337  *
 338  *      Purpose: End the query, such as freeing memory
 339  *
 340  *************************************************************************/
 341 int sql_finish_query(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 342
 343         return sql_free_result(sqlsocket, config);
 344 }
 345
 346
 347
 348 /*************************************************************************
 349  *
 350  *      Function: sql_finish_select_query
 351  *
 352  *      Purpose: End the select query, such as freeing memory or result
 353  *
 354  *************************************************************************/
 355 int sql_finish_select_query(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 356
 357         return sql_free_result(sqlsocket, config);
 358 }
 359
 360
 361 /*************************************************************************
 362  *
 363  *      Function: sql_affected_rows
 364  *
 365  *      Purpose: Return the number of rows affected by the last query.
 366  *
 367  *************************************************************************/
 368 int sql_affected_rows(SQLSOCK * sqlsocket, SQL_CONFIG *config) {
 369         rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
 370
 371         return pg_sock->affected_rows;
 372 }
 373
 374
 375 static int
 376 not_implemented(SQLSOCK * sqlsocket, SQL_CONFIG *config)
 377 {
 378         radlog(L_ERR, "sql_postgresql: calling unimplemented function");
 379         exit(1);
 380 }
 381
 382
 383 /* Exported to rlm_sql */
 384 rlm_sql_module_t rlm_sql_postgresql = {
 385         "rlm_sql_postgresql",
 386         sql_init_socket,
 387         not_implemented, /* sql_destroy_socket*/
 388         sql_query,
 389         sql_select_query,
 390         not_implemented, /* sql_store_result */
 391         not_implemented, /* sql_num_fields */
 392         not_implemented, /* sql_num_rows */
 393         sql_fetch_row,
 394         not_implemented, /* sql_free_result */
 395         sql_error,
 396         sql_close,
 397         sql_finish_query,
 398         sql_finish_select_query,
 399         sql_affected_rows,
 400 };