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 "sockunconnected"
  58  * and set a grace period, during which we won't try connecting again (to prevent unduly
  59  * lagging the server and being impolite to a DB server that may be having other
  60  * issues).  If successful in connecting, set state to sockconnected.   - chad
  61  */
  62 static int connect_single_socket(SQLSOCK *sqlsocket, SQL_INST *inst) {
  63         if ((inst->module->sql_init_socket)(sqlsocket, inst->config) < 0) {
  64                 radlog(L_CONS | L_ERR, "rlm_sql:  Failed to connect DB handle #%d", sqlsocket->id);
  65                 inst->connect_after = time(NULL) + inst->config->connect_failure_retry_delay;
  66                 sqlsocket->state = sockunconnected;
  67                 return(-1);
  68         } else {
  69                 radlog(L_DBG, "rlm_sql:  Connected new DB handle, #%d", sqlsocket->id);
  70                 sqlsocket->state = sockconnected;
  71                 return(0);
  72         }
  73 }
  74
  75
  76 /*************************************************************************
  77  *
  78  *      Function: sql_init_socketpool
  79  *
  80  *      Purpose: Connect to the sql server, if possible
  81  *
  82  *************************************************************************/
  83 int sql_init_socketpool(SQL_INST * inst) {
  84
  85         SQLSOCK *sqlsocket;
  86         int     i;
  87
  88         inst->connect_after = 0;
  89         inst->used = 0;
  90         inst->sqlpool = NULL;
  91         inst->socknr = 0;
  92
  93         for (i = 0; i < inst->config->num_sql_socks; i++) {
  94
  95                 sqlsocket = rad_malloc(sizeof(SQLSOCK));
  96                 if (sqlsocket == NULL) {
  97                         return -1;
  98                 }
  99                 sqlsocket->conn = NULL;
 100                 sqlsocket->id = i;
 101                 sqlsocket->state = sockunconnected;
 102
 103 #if HAVE_SEMAPHORE_H
 104                 /*
 105                  *  FIXME! Check return codes!
 106                  */
 107                 sqlsocket->semaphore = (sem_t *) rad_malloc(sizeof(sem_t));
 108                 sem_init(sqlsocket->semaphore, 0, SQLSOCK_UNLOCKED);
 109 #else
 110                 sqlsocket->in_use = SQLSOCK_UNLOCKED;
 111 #endif
 112
 113                 if (time(NULL) > inst->connect_after) {
 114                         /* this sets the sqlsocket->state, and possibly sets inst->connect_after */
 115                         connect_single_socket(sqlsocket, inst);
 116                 }
 117
 118                 /* Add this socket to the list of sockets */
 119                 sqlsocket->next = inst->sqlpool;
 120                 inst->sqlpool = sqlsocket;
 121         }
 122
 123 #if HAVE_PTHREAD_H
 124         pthread_mutex_init(&inst->mutex, NULL);
 125 #endif
 126
 127         return 1;
 128 }
 129
 130 /*************************************************************************
 131  *
 132  *     Function: sql_poolfree
 133  *
 134  *     Purpose: Clean up and free sql pool
 135  *
 136  *************************************************************************/
 137 void sql_poolfree(SQL_INST * inst) {
 138
 139         SQLSOCK *cur;
 140
 141         for (cur = inst->sqlpool; cur; cur = cur->next) {
 142                 sql_close_socket(inst, cur);
 143         }
 144 #if HAVE_PTHREAD_H
 145         pthread_mutex_destroy(&inst->mutex);
 146 #endif
 147 }
 148
 149
 150 /*************************************************************************
 151  *
 152  *      Function: sql_close_socket
 153  *
 154  *      Purpose: Close and free a sql sqlsocket
 155  *
 156  *************************************************************************/
 157 int sql_close_socket(SQL_INST *inst, SQLSOCK * sqlsocket) {
 158
 159         radlog(L_DBG, "rlm_sql: Closing sqlsocket %d", sqlsocket->id);
 160         (inst->module->sql_close)(sqlsocket, inst->config);
 161 #if HAVE_SEMAPHORE_H
 162         sem_destroy(sqlsocket->semaphore);
 163 #endif
 164         free(sqlsocket);
 165         return 1;
 166 }
 167
 168
 169 /*************************************************************************
 170  *
 171  *      Function: sql_get_socket
 172  *
 173  *      Purpose: Return a SQL sqlsocket from the connection pool
 174  *
 175  *************************************************************************/
 176 SQLSOCK * sql_get_socket(SQL_INST * inst) {
 177         SQLSOCK *cur, *cur2;
 178         int tried_to_connect = 0;
 179
 180         while (inst->used == inst->config->num_sql_socks) {
 181                 radlog(L_ERR, "rlm_sql: All sockets are being used! Please increase the maximum number of sockets!");
 182                 return NULL;
 183         }
 184
 185         /*
 186          * Rotating the socket so that all get used and none get closed due to
 187          * inactivity from the SQL server ( such as mySQL ).
 188          */
 189 #if HAVE_PTHREAD_H
 190         pthread_mutex_lock(&inst->mutex);
 191 #endif
 192
 193         if(inst->socknr == 0) {
 194                 inst->socknr = inst->config->num_sql_socks;
 195         }
 196         inst->socknr--;
 197         cur2 = inst->sqlpool;
 198         while (inst->socknr != cur2->id) {
 199                 cur2 = cur2->next;
 200         }
 201 #if HAVE_PTHREAD_H
 202         pthread_mutex_unlock(&inst->mutex);
 203 #endif
 204
 205         for (cur = cur2; cur; cur = cur->next) {
 206
 207                 /* if we happen upon an unconnected socket, and this instance's grace
 208                  * period on (re)connecting has expired, then try to connect it.  This
 209                  * should be really rare.  - chad
 210                  */
 211                 if ((cur->state == sockunconnected) && (time(NULL) > inst->connect_after)) {
 212                         tried_to_connect = 1;
 213                         radlog(L_INFO, "rlm_sql: Trying to (re)connect an unconnected handle...");
 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: Ignoring unconnected handle");
 220                         continue;
 221                 }
 222
 223 #if HAVE_SEMAPHORE_H
 224                 if (sem_trywait(cur->semaphore) == 0) {
 225 #else
 226                 if (cur->in_use == SQLSOCK_UNLOCKED) {
 227 #endif
 228                         (inst->used)++;
 229 #ifndef HAVE_SEMAPHORE_H
 230                         cur->in_use = SQLSOCK_LOCKED;
 231 #endif
 232                         radlog(L_DBG, "rlm_sql: Reserving sql socket id: %d", cur->id);
 233                         return cur;
 234                 }
 235         }
 236
 237         /* We get here if every DB handle is unconnected and unconnectABLE */
 238         radlog((tried_to_connect = 0) ? (L_DBG) : (L_CONS | L_ERR), "rlm_sql:  There are no DB handles to use!");
 239         return NULL;
 240 }
 241
 242 /*************************************************************************
 243  *
 244  *      Function: sql_release_socket
 245  *
 246  *      Purpose: Frees a SQL sqlsocket back to the connection pool
 247  *
 248  *************************************************************************/
 249 int sql_release_socket(SQL_INST * inst, SQLSOCK * sqlsocket) {
 250
 251         (inst->used)--;
 252 #if HAVE_SEMAPHORE_H
 253         sem_post(sqlsocket->semaphore);
 254 #else
 255         sqlsocket->in_use = SQLSOCK_UNLOCKED;
 256 #endif
 257
 258         radlog(L_DBG, "rlm_sql: Released sql socket id: %d", sqlsocket->id);
 259
 260         return 1;
 261 }
 262
 263
 264 /*************************************************************************
 265  *
 266  *      Function: sql_userparse
 267  *
 268  *      Purpose: Read entries from the database and fill VALUE_PAIR structures
 269  *
 270  *************************************************************************/
 271 int sql_userparse(VALUE_PAIR ** first_pair, SQL_ROW row, int querymode) {
 272
 273         DICT_ATTR *attr;
 274         VALUE_PAIR *pair, *check;
 275         char *ptr;
 276         char buf[128];
 277         int pairmode = T_EOL;
 278
 279         if ((attr = dict_attrbyname(row[2])) == (DICT_ATTR *) NULL) {
 280                 radlog(L_ERR | L_CONS, "rlm_sql: unknown attribute %s", row[2]);
 281                 return (-1);
 282         }
 283
 284         if (row[4] != NULL && strlen(row[4]) > 0) {
 285                 ptr = row[4];
 286                 pairmode = gettoken(&ptr, buf, sizeof(buf));
 287         }
 288         if (pairmode <= T_EOL) pairmode = T_OP_CMP_EQ;
 289
 290         /*
 291          * If attribute is already there, skip it because we checked usercheck first
 292          * and we want user settings to over ride group settings
 293          */
 294         if (pairmode != T_OP_ADD && (check = pairfind(*first_pair, attr->attr)) != NULL &&
 295 #ifdef ASCEND_BINARY
 296                         attr->type != PW_TYPE_ABINARY &&
 297 #endif
 298                         querymode == PW_VP_GROUPDATA)
 299                 return 0;
 300
 301         pair = pairmake(row[2], row[3], pairmode);
 302         pairadd(first_pair, pair);
 303
 304         return 0;
 305 }
 306
 307
 308 /*************************************************************************
 309  *
 310  *      Function: sql_getvpdata
 311  *
 312  *      Purpose: Get any group check or reply pairs
 313  *
 314  *************************************************************************/
 315 int sql_getvpdata(SQL_INST * inst, SQLSOCK * sqlsocket, VALUE_PAIR **pair, char *query, int mode) {
 316
 317         SQL_ROW row;
 318         int     rows = 0;
 319
 320         if ((inst->module->sql_select_query)(sqlsocket, inst->config, query) < 0) {
 321                 radlog(L_ERR, "rlm_sql_getvpdata: database query error");
 322                 return -1;
 323         }
 324         while ((row = (inst->module->sql_fetch_row)(sqlsocket, inst->config))) {
 325                 if (sql_userparse(pair, row, mode) != 0) {
 326                         radlog(L_ERR | L_CONS, "rlm_sql:  Error getting data from database");
 327                         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 328                         return -1;
 329                 }
 330                 rows++;
 331         }
 332         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 333
 334         return rows;
 335 }
 336
 337
 338 static int got_alrm;
 339 static void
 340 alrm_handler(int i) {
 341         got_alrm = 1;
 342 }
 343
 344 /*************************************************************************
 345  *
 346  *      Function: sql_check_ts
 347  *
 348  *      Purpose: Checks the terminal server for a spacific login entry
 349  *
 350  *************************************************************************/
 351 static int sql_check_ts(SQL_ROW row) {
 352
 353         int     pid, st, e;
 354         int     n;
 355         NAS    *nas;
 356         char    session_id[12];
 357         char   *s;
 358         void    (*handler) (int);
 359
 360         /*
 361          *      Find NAS type.
 362          */
 363         if ((nas = nas_find(ip_addr(row[4]))) == NULL) {
 364                 radlog(L_ERR, "rlm_sql:  unknown NAS [%s]", row[4]);
 365                 return -1;
 366         }
 367
 368         /*
 369          *      Fork.
 370          */
 371         handler = signal(SIGCHLD, SIG_DFL);
 372         if ((pid = fork()) < 0) {
 373                 radlog(L_ERR, "rlm_sql: fork: %s", strerror(errno));
 374                 signal(SIGCHLD, handler);
 375                 return -1;
 376         }
 377
 378         if (pid > 0) {
 379                 /*
 380                  *      Parent - Wait for checkrad to terminate.
 381                  *      We timeout in 10 seconds.
 382                  */
 383                 got_alrm = 0;
 384                 signal(SIGALRM, alrm_handler);
 385                 alarm(10);
 386                 while ((e = waitpid(pid, &st, 0)) != pid)
 387                         if (e < 0 && (errno != EINTR || got_alrm))
 388                                 break;
 389                 alarm(0);
 390                 signal(SIGCHLD, handler);
 391                 if (got_alrm) {
 392                         kill(pid, SIGTERM);
 393                         sleep(1);
 394                         kill(pid, SIGKILL);
 395                         radlog(L_ERR, "rlm_sql:  Check-TS: timeout waiting for checkrad");
 396                         return 2;
 397                 }
 398                 if (e < 0) {
 399                         radlog(L_ERR, "rlm_sql:  Check-TS: unknown error in waitpid()");
 400                         return 2;
 401                 }
 402                 return WEXITSTATUS(st);
 403         }
 404
 405         /*
 406          *      Child - exec checklogin with the right parameters.
 407          */
 408         for (n = 32; n >= 3; n--)
 409                 close(n);
 410
 411         sprintf(session_id, "%.8s", row[1]);
 412
 413         s = CHECKRAD2;
 414         execl(CHECKRAD2, "checkrad", nas->nastype, row[4], row[5],
 415                         row[2], session_id, NULL);
 416         if (errno == ENOENT) {
 417                 s = CHECKRAD1;
 418                 execl(CHECKRAD1, "checklogin", nas->nastype, row[4], row[5],
 419                                 row[2], session_id, NULL);
 420         }
 421         radlog(L_ERR, "rlm_sql:  Check-TS: exec %s: %s", s, strerror(errno));
 422
 423         /*
 424          *      Exit - 2 means "some error occured".
 425          */
 426         exit(2);
 427         return -1;
 428 }
 429
 430
 431 /*************************************************************************
 432  *
 433  *      Function: sql_check_multi
 434  *
 435  *      Purpose: Check radius accounting for duplicate logins
 436  *
 437  *************************************************************************/
 438 int sql_check_multi(SQL_INST * inst, SQLSOCK * sqlsocket, char *name, VALUE_PAIR * request, int maxsimul) {
 439
 440         char    querystr[MAX_QUERY_LEN];
 441         char    authstr[256];
 442         VALUE_PAIR *fra;
 443         SQL_ROW row;
 444         int     count = 0;
 445         uint32_t ipno = 0;
 446         int     mpp = 1;
 447
 448         sprintf(authstr, "UserName = '%s'", name);
 449         sprintf(querystr, "SELECT COUNT(*) FROM %s WHERE %s AND AcctStopTime = 0", inst->config->sql_acct_table, authstr);
 450         if ((inst->module->sql_select_query)(sqlsocket, inst->config, querystr) < 0) {
 451                 radlog(L_ERR, "sql_check_multi: database query error");
 452                 return -1;
 453         }
 454
 455         row = (inst->module->sql_fetch_row)(sqlsocket, inst->config);
 456         count = atoi(row[0]);
 457         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 458
 459         if (count < maxsimul)
 460                 return 0;
 461
 462         /*
 463          * *      Setup some stuff, like for MPP detection.
 464          */
 465         if ((fra = pairfind(request, PW_FRAMED_IP_ADDRESS)) != NULL)
 466                 ipno = htonl(fra->lvalue);
 467
 468         count = 0;
 469         sprintf(querystr, "SELECT * FROM %s WHERE %s AND AcctStopTime = 0", inst->config->sql_acct_table, authstr);
 470         if ((inst->module->sql_select_query)(sqlsocket, inst->config, querystr) < 0) {
 471                 radlog(L_ERR, "sql_check_multi: database query error");
 472                 return -1;
 473         }
 474         while ((row = (inst->module->sql_fetch_row)(sqlsocket, inst->config))) {
 475                 int     check = sql_check_ts(row);
 476
 477                 if (check == 1) {
 478                         count++;
 479
 480                         if (ipno && atoi(row[19]) == ipno)
 481                                 mpp = 2;
 482
 483                 } else if (check == 2)
 484                         radlog(L_ERR, "rlm_sql:  Problem with checkrad [%s] (from nas %s)", name, row[4]);
 485                 else {
 486                         /*
 487                          *      False record - zap it
 488                          */
 489
 490                         if (inst->config->deletestalesessions) {
 491                                 SQLSOCK *sqlsocket1;
 492
 493                                 radlog(L_ERR, "rlm_sql:  Deleteing stale session [%s] (from nas %s/%s)", row[2], row[4], row[5]);
 494                                 sqlsocket1 = sql_get_socket(inst);
 495                                 sprintf(querystr, "DELETE FROM %s WHERE RadAcctId = '%s'", inst->config->sql_acct_table, row[0]);
 496                                 (inst->module->sql_query)(sqlsocket1, inst->config, querystr);
 497                                 (inst->module->sql_finish_query)(sqlsocket1, inst->config);
 498                                 sql_release_socket(inst, sqlsocket1);
 499                         }
 500                 }
 501         }
 502         (inst->module->sql_finish_select_query)(sqlsocket, inst->config);
 503
 504         return (count < maxsimul) ? 0 : mpp;
 505 }
 506
 507 void query_log(SQL_INST * inst, char *querystr) {
 508         FILE   *sqlfile = NULL;
 509
 510         if (inst->config->sqltrace) {
 511                 if ((sqlfile = fopen(inst->config->tracefile, "a")) == (FILE *) NULL) {
 512                         radlog(L_ERR, "rlm_sql: Couldn't open file %s",
 513                                         inst->config->tracefile);
 514                 } else {
 515                         int fd = fileno(sqlfile);
 516
 517                         rad_lockfd(fd, MAX_QUERY_LEN);
 518                         fputs(querystr, sqlfile);
 519                         fputs(";\n", sqlfile);
 520                         fclose(sqlfile); /* and release the lock */
 521                 }
 522         }
 523 }