src/modules/rlm_sql/sql.c

   1 /*
   2  *  sql.c               rlm_sql - FreeRADIUS SQL Module
   3  *              Main code directly taken from ICRADIUS
   4  *
   5  * Version:     $Id$
   6  *
   7  *   This program is free software; you can redistribute it and/or modify
   8  *   it under the terms of the GNU General Public License as published by
   9  *   the Free Software Foundation; either version 2 of the License, or
  10  *   (at your option) any later version.
  11  *
  12  *   This program is distributed in the hope that it will be useful,
  13  *   but WITHOUT ANY WARRANTY; without even the implied warranty of
  14  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15  *   GNU General Public License for more details.
  16  *
  17  *   You should have received a copy of the GNU General Public License
  18  *   along with this program; if not, write to the Free Software
  19  *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  20  *
  21  * Copyright 2001  The FreeRADIUS server project
  22  * Copyright 2000  Mike Machado <mike@innercite.com>
  23  * Copyright 2000  Alan DeKok <aland@ox.org>
  24  * Copyright 2001  Chad Miller <cmiller@surfsouth.com>
  25  */
  26
  27
  28 #include        <sys/types.h>
  29 #include        <sys/socket.h>
  30 #include        <sys/time.h>
  31 #include        <sys/file.h>
  32 #include        <string.h>
  33 #include        <sys/stat.h>
  34 #include        <netinet/in.h>
  35
  36 #include        <stdio.h>
  37 #include        <stdlib.h>
  38 #include        <netdb.h>
  39 #include        <pwd.h>
  40 #include        <time.h>
  41 #include        <ctype.h>
  42 #include        <unistd.h>
  43 #include        <signal.h>
  44 #include        <errno.h>
  45 #include        <sys/wait.h>
  46
  47 #if HAVE_PTHREAD_H
  48 #include        <pthread.h>
  49 #endif
  50
  51 #include        "radiusd.h"
  52 #include        "conffile.h"
  53 #include        "rlm_sql.h"
  54
  55
  56 /*
  57  * Connect to a server.  If error, set this socket's state to be
  58  * "sockunconnected" and set a grace period, during which we won't try
  59  * connecting again (to prevent unduly lagging the server and being
  60  * impolite to a DB server that may be having other issues).  If
  61  * successful in connecting, set state to sockconnected.
  62  * - chad
  63  */
  64 static int connect_single_socket(SQLSOCK *sqlsocket, SQL_INST *inst)
  65 {
  66         radlog(L_DBG, "rlm_sql (%s): Attempting to connect %s #%d",
  67                inst->config->xlat_name, inst->module->name, sqlsocket->id);
  68
  69         if ((inst->module->sql_init_socket)(sqlsocket, inst->config) < 0) {
  70                 radlog(L_CONS | L_ERR, "rlm_sql (%s): Failed to connect DB handle #%d", inst->config->xlat_name, sqlsocket->id);
  71                 inst->connect_after = time(NULL) + inst->config->connect_failure_retry_delay;
  72                 sqlsocket->state = sockunconnected;
  73                 return(-1);
  74         } else {
  75                 radlog(L_DBG, "rlm_sql (%s): Connected new DB handle, #%d",
  76                        inst->config->xlat_name, sqlsocket->id);
  77                 sqlsocket->state = sockconnected;
  78                 return(0);
  79         }
  80 }
  81
  82
  83 /*************************************************************************
  84  *
  85  *      Function: sql_init_socketpool
  86  *
  87  *      Purpose: Connect to the sql server, if possible
  88  *
  89  *************************************************************************/
  90 int sql_init_socketpool(SQL_INST * inst)
  91 {
  92         int i, rcode;
  93         SQLSOCK *sqlsocket;
  94
  95         inst->connect_after = 0;
  96         inst->sqlpool = NULL;
  97
  98         for (i = 0; i < inst->config->num_sql_socks; i++) {
  99                 radlog(L_DBG, "rlm_sql (%s): starting %d",
 100                        inst->config->xlat_name, i);
 101
 102                 sqlsocket = rad_malloc(sizeof(SQLSOCK));
 103                 if (sqlsocket == NULL) {
 104                         return -1;
 105                 }
 106                 sqlsocket->conn = NULL;
 107                 sqlsocket->id = i;
 108                 sqlsocket->state = sockunconnected;
 109
 110 #if HAVE_PTHREAD_H
 111                 rcode = pthread_mutex_init(&sqlsocket->mutex,NULL);
 112                 if (rcode != 0) {
 113                         radlog(L_ERR, "rlm_sql: Failed to init lock: %s",
 114                                strerror(errno));
 115                         return 0;
 116                 }
 117 #endif
 118
 119                 if (time(NULL) > inst->connect_after) {
 120                         /* this sets the sqlsocket->state, and possibly sets inst->connect_after */
 121                         /* FIXME! check return code */
 122                         connect_single_socket(sqlsocket, inst);
 123                 }
 124
 125                 /* Add this socket to the list of sockets */
 126                 sqlsocket->next = inst->sqlpool;
 127                 inst->sqlpool = sqlsocket;
 128         }
 129         inst->last_used = NULL;
 130
 131         return 1;
 132 }
 133
 134 /*************************************************************************
 135  *
 136  *     Function: sql_poolfree
 137  *
 138  *     Purpose: Clean up and free sql pool
 139  *
 140  *************************************************************************/
 141 void sql_poolfree(SQL_INST * inst)
 142 {
 143         SQLSOCK *cur;
 144
 145         for (cur = inst->sqlpool; cur; cur = cur->next) {
 146                 sql_close_socket(inst, cur);
 147         }
 148 }
 149
 150
 151 /*************************************************************************
 152  *
 153  *      Function: sql_close_socket
 154  *
 155  *      Purpose: Close and free a sql sqlsocket
 156  *
 157  *************************************************************************/
 158 int sql_close_socket(SQL_INST *inst, SQLSOCK * sqlsocket)
 159 {
 160         radlog(L_DBG, "rlm_sql (%s): Closing sqlsocket %d",
 161                inst->config->xlat_name, sqlsocket->id);
 162         (inst->module->sql_close)(sqlsocket, inst->config);
 163 #if HAVE_PTHREAD_H
 164         pthread_mutex_destroy(&sqlsocket->mutex);
 165 #endif
 166         free(sqlsocket);
 167         return 1;
 168 }
 169
 170
 171 /*************************************************************************
 172  *
 173  *      Function: sql_get_socket
 174  *
 175  *      Purpose: Return a SQL sqlsocket from the connection pool
 176  *
 177  *************************************************************************/
 178 SQLSOCK * sql_get_socket(SQL_INST * inst)
 179 {
 180         SQLSOCK *cur, *start;
 181         int tried_to_connect = 0;
 182         int unconnected = 0;
 183
 184         /*
 185          *      Start at the last place we left off.
 186          */
 187         start = inst->last_used;
 188         if (!start) start = inst->sqlpool;
 189
 190         cur = start;
 191
 192         while (cur) {
 193 #if HAVE_PTHREAD_H
 194                 /*
 195                  *      If this socket is in use by another thread,
 196                  *      skip it, and try another socket.
 197                  *
 198                  *      If it isn't used, then grab it ourselves.
 199                  */
 200                 if (pthread_mutex_trylock(&cur->mutex) < 0) {
 201                         goto next;
 202                 } /* else we now have the lock */
 203 #endif
 204
 205                 /*
 206                  *      If we happen upon an unconnected socket, and
 207                  *      this instance's grace period on
 208                  *      (re)connecting has expired, then try to
 209                  *      connect it.  This should be really rare.
 210                  */
 211                 if ((cur->state == sockunconnected) && (time(NULL) > inst->connect_after)) {
 212                         radlog(L_INFO, "rlm_sql (%s): Trying to (re)connect unconnected handle %d..", inst->config->xlat_name, cur->id);
 213                         tried_to_connect++;
 214                         connect_single_socket(cur, inst);
 215                 }
 216
 217                 /* if we still aren't connected, ignore this handle */
 218                 if (cur->state == sockunconnected) {
 219                         radlog(L_DBG, "rlm_sql (%s): Ignoring unconnected handle %d..", inst->config->xlat_name, cur->id);
 220                         unconnected++;
 221 #if HAVE_PTHREAD_H
 222                         pthread_mutex_unlock(&cur->mutex);
 223 #endif
 224                         goto next;
 225                 }
 226
 227                 /* should be connected, grab it */
 228                 radlog(L_DBG, "rlm_sql (%s): Reserving sql socket id: %d", inst->config->xlat_name, cur->id);
 229
 230                 if (unconnected != 0 || tried_to_connect != 0) {
 231                         radlog(L_INFO, "rlm_sql (%s): got socket %d after skipping %d unconnected handles, tried to reconnect %d though", inst->config->xlat_name, cur->id, unconnected, tried_to_connect);
 232
 233                         /*
 234                          *      The socket is returned in the locked
 235                          *      state.
 236                          *
 237                          *      We also remember where we left off,
 238                          *      so that the next search can start from
 239                          *      here.
 240                          *
 241                          *      Note that multiple threads MAY over-write
 242                          *      the 'inst->last_used' variable.  This is OK,
 243                          *      as it's a pointer only used for reading.
 244                          */
 245                         inst->last_used = cur->next;
 246                         return cur;
 247                 }
 248
 249 #if HAVE_PTHREAD_H
 250                 /*
 251                  *      This was locked above, in 'trylock', so
 252                  *      we've got to release it here.
 253                  */
 254                 pthread_mutex_unlock(&cur->mutex);
 255 #endif
 256
 257                 /* move along the list */
 258         next:
 259                 cur = cur->next;
 260
 261                 /*
 262                  *      Because we didnt start at the start, once we
 263                  *      hit the end of the linklist, we should go
 264                  *      back to the beginning and work toward the
 265                  *      middle!
 266                  */
 267                 if (!cur) {
 268                         cur = inst->sqlpool;
 269                 }
 270
 271                 /*
 272                  *      If we're at the socket we started
 273                  */
 274                 if (cur == start) {
 275                         break;
 276                 }
 277         }
 278
 279         /* We get here if every DB handle is unconnected and unconnectABLE */
 280         radlog(L_INFO, "rlm_sql (%s): There are no DB handles to use! skipped %d, tried to connect %d", inst->config->xlat_name, unconnected, tried_to_connect);
 281         return NULL;
 282 }
 283
 284 /*************************************************************************
 285  *
 286  *      Function: sql_release_socket
 287  *
 288  *      Purpose: Frees a SQL sqlsocket back to the connection pool
 289  *
 290  *************************************************************************/
 291 int sql_release_socket(SQL_INST * inst, SQLSOCK * sqlsocket)
 292 {
 293 #if HAVE_PTHREAD_H
 294         pthread_mutex_unlock(&sqlsocket->mutex);
 295 #endif
 296
 297         radlog(L_DBG, "rlm_sql (%s): Released sql socket id: %d",
 298                inst->config->xlat_name, sqlsocket->id);
 299
 300         return 0;
 301 }
 302
 303
 304 /*************************************************************************
 305  *
 306  *      Function: sql_userparse
 307  *
 308  *      Purpose: Read entries from the database and fill VALUE_PAIR structures
 309  *
 310  *************************************************************************/
 311 int sql_userparse(VALUE_PAIR ** first_pair, SQL_ROW row, int querymode)
 312 {
 313         DICT_ATTR *attr;
 314         VALUE_PAIR *pair, *check;
 315         char *ptr;
 316         char buf[128];
 317         int pairmode = T_EOL;
 318
 319         if ((attr = dict_attrbyname(row[2])) == (DICT_ATTR *) NULL) {
 320                 radlog(L_ERR | L_CONS, "rlm_sql: unknown attribute %s",
 321                        row[2]);
 322                 return (-1);
 323         }
 324
 325         if (row[4] != NULL && strlen(row[4]) > 0) {
 326                 ptr = row[4];
 327                 pairmode = gettoken(&ptr, buf, sizeof(buf));
 328         } else {
 329                 /*
 330                  *  'op' fields of NULL are a plague, and a bane on the
 331                  *  existence of mankind.
 332                  */
 333                 radlog(L_ERR, "rlm_sql: The 'op' field for attribute '%s = %s' is NULL, or non-existent.", row[2], row[3]);
 334                 radlog(L_ERR, "rlm_sql: You MUST FIX THIS if you want the configuration to behave as you expect.");
 335         }
 336         if (pairmode <= T_EOL) pairmode = T_OP_CMP_EQ;
 337
 338         /*
 339          * If attribute is already there, skip it because we checked usercheck first
 340          * and we want user settings to over ride group settings
 341          */
 342         if (pairmode != T_OP_ADD && (check = pairfind(*first_pair, attr->attr)) != NULL &&
 343 #ifdef ASCEND_BINARY
 344                         attr->type != PW_TYPE_ABINARY &&
 345 #endif
 346                         querymode == PW_VP_GROUPDATA)
 347                 return 0;
 348
 349         pair = pairmake(row[2], row[3], pairmode);
 350         pairadd(first_pair, pair);
 351
 352         return 0;
 353 }
 354
 355
 356 /*************************************************************************
 357  *
 358  *      Function: rlm_sql_fetch_row
 359  *
 360  *      Purpose: call the module's sql_fetch_row and implement re-connect
 361  *
 362  *************************************************************************/
 363 int rlm_sql_fetch_row(SQLSOCK *sqlsocket, SQL_INST *inst)
 364 {
 365         int ret;
 366
 367         ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 368
 369         if (ret == SQL_DOWN) {
 370                 /* close the socket that failed */
 371                 (inst->module->sql_close)(sqlsocket, inst->config);
 372
 373                 /* reconnect the socket */
 374                 if (connect_single_socket(sqlsocket, inst) < 0) {
 375                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 376                         return -1;
 377                 }
 378
 379                 /* retry the query on the newly connected socket */
 380                 ret = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 381
 382                 if (ret) {
 383                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 384                                inst->config->xlat_name);
 385                         return -1;
 386                 }
 387         }
 388
 389         return ret;
 390 }
 391
 392 /*************************************************************************
 393  *
 394  *      Function: rlm_sql_query
 395  *
 396  *      Purpose: call the module's sql_query and implement re-connect
 397  *
 398  *************************************************************************/
 399 int rlm_sql_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 400 {
 401         int ret;
 402
 403         /*
 404          *      If there's no query, return an error.
 405          */
 406         if (!query || !*query) {
 407                 return -1;
 408         }
 409
 410         ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 411
 412         if (ret == SQL_DOWN) {
 413                 /* close the socket that failed */
 414                 (inst->module->sql_close)(sqlsocket, inst->config);
 415
 416                 /* reconnect the socket */
 417                 if (connect_single_socket(sqlsocket, inst) < 0) {
 418                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 419                         return -1;
 420                 }
 421
 422                 /* retry the query on the newly connected socket */
 423                 ret = (inst->module->sql_query)(sqlsocket, inst->config, query);
 424
 425                 if (ret) {
 426                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 427                                inst->config->xlat_name);
 428                         return -1;
 429                 }
 430         }
 431
 432         return ret;
 433 }
 434
 435 /*************************************************************************
 436  *
 437  *      Function: rlm_sql_select_query
 438  *
 439  *      Purpose: call the module's sql_select_query and implement re-connect
 440  *
 441  *************************************************************************/
 442 int rlm_sql_select_query(SQLSOCK *sqlsocket, SQL_INST *inst, char *query)
 443 {
 444         int ret;
 445
 446         /*
 447          *      If there's no query, return an error.
 448          */
 449         if (!query || !*query) {
 450                 return -1;
 451         }
 452
 453         ret = (inst->module->sql_select_query)(sqlsocket, inst->config, query);
 454
 455         if (ret == SQL_DOWN) {
 456                 /* close the socket that failed */
 457                 (inst->module->sql_close)(sqlsocket, inst->config);
 458
 459                 /* reconnect the socket */
 460                 if (connect_single_socket(sqlsocket, inst) < 0) {
 461                         radlog(L_ERR, "rlm_sql (%s): reconnect failed, database down?", inst->config->xlat_name);
 462                         return -1;
 463                 }
 464
 465                 /* retry the query on the newly connected socket */
 466                 ret = (inst->module->sql_select_query)(sqlsocket, inst->config, query);
 467
 468                 if (ret) {
 469                         radlog(L_ERR, "rlm_sql (%s): failed after re-connect",
 470                                inst->config->xlat_name);
 471                         return -1;
 472                 }
 473         }
 474
 475         return ret;
 476 }
 477
 478
 479 /*************************************************************************
 480  *
 481  *      Function: sql_getvpdata
 482  *
 483  *      Purpose: Get any group check or reply pairs
 484  *
 485  *************************************************************************/
 486 int sql_getvpdata(SQL_INST * inst, SQLSOCK * sqlsocket, VALUE_PAIR **pair, char *query, int mode)
 487 {
 488         SQL_ROW row;
 489         int     rows = 0;
 490
 491         /*
 492          *      If there's no query, return an error.
 493          */
 494         if (!query || !*query) {
 495                 return -1;
 496         }
 497
 498         if (rlm_sql_select_query(sqlsocket, inst, query)) {
 499                 radlog(L_ERR, "rlm_sql_getvpdata: database query error");
 500                 return -1;
 501         }
 502         while (rlm_sql_fetch_row(sqlsocket, inst)==0) {
 503                 row = sqlsocket->row;
 504                 if (!row)
 505                         break;
 506                 if (sql_userparse(pair, row, mode) != 0) {
 507                         radlog(L_ERR | L_CONS, "rlm_sql (%s): Error getting data from database", inst->config->xlat_name);
 508                         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 509                         return -1;
 510                 }
 511                 rows++;
 512         }
 513         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 514
 515         return rows;
 516 }
 517
 518 void query_log(REQUEST *request, SQL_INST *inst, char *querystr)
 519 {
 520         FILE   *sqlfile = NULL;
 521
 522         if (inst->config->sqltrace) {
 523                 char buffer[8192];
 524
 525                 if (!radius_xlat(buffer, sizeof(buffer),
 526                                  inst->config->tracefile, request, NULL)) {
 527                   radlog(L_ERR, "rlm_sql (%s): xlat failed.",
 528                          inst->config->xlat_name);
 529                   return;
 530                 }
 531
 532                 if ((sqlfile = fopen(buffer, "a")) == (FILE *) NULL) {
 533                         radlog(L_ERR, "rlm_sql (%s): Couldn't open file %s",
 534                                inst->config->xlat_name,
 535                                buffer);
 536                 } else {
 537                         int fd = fileno(sqlfile);
 538
 539                         rad_lockfd(fd, MAX_QUERY_LEN);
 540                         fputs(querystr, sqlfile);
 541                         fputs(";\n", sqlfile);
 542                         fclose(sqlfile); /* and release the lock */
 543                 }
 544         }
 545 }