plugins/check_pgsql.c

   1 /*****************************************************************************
   2 *
   3 * Monitoring check_pgsql plugin
   4 *
   5 * License: GPL
   6 * Copyright (c) 1999-2011 Monitoring Plugins Development Team
   7 *
   8 * Description:
   9 *
  10 * This file contains the check_pgsql plugin
  11 *
  12 * Test whether a PostgreSQL Database is accepting connections.
  13 *
  14 *
  15 * This program is free software: you can redistribute it and/or modify
  16 * it under the terms of the GNU General Public License as published by
  17 * the Free Software Foundation, either version 3 of the License, or
  18 * (at your option) any later version.
  19 *
  20 * This program is distributed in the hope that it will be useful,
  21 * but WITHOUT ANY WARRANTY; without even the implied warranty of
  22 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  23 * GNU General Public License for more details.
  24 *
  25 * You should have received a copy of the GNU General Public License
  26 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
  27 *
  28 *
  29 *****************************************************************************/
  30
  31 const char *progname = "check_pgsql";
  32 const char *copyright = "1999-2011";
  33 const char *email = "devel@monitoring-plugins.org";
  34
  35 #include "common.h"
  36 #include "utils.h"
  37
  38 #include "netutils.h"
  39 #include <libpq-fe.h>
  40 #include <pg_config_manual.h>
  41
  42 #define DEFAULT_DB "template1"
  43 #define DEFAULT_HOST "127.0.0.1"
  44
  45 /* return the PSQL server version as a 3-tuple */
  46 #define PSQL_SERVER_VERSION3(server_version) \
  47         (server_version) / 10000, \
  48         (server_version) / 100 - (int)((server_version) / 10000) * 100, \
  49         (server_version) - (int)((server_version) / 100) * 100
  50 /* return true if the given host is a UNIX domain socket */
  51 #define PSQL_IS_UNIX_DOMAIN_SOCKET(host) \
  52         ((NULL == (host)) || ('\0' == *(host)) || ('/' == *(host)))
  53 /* return a 3-tuple identifying a host/port independent of the socket type */
  54 #define PSQL_SOCKET3(host, port) \
  55         ((NULL == (host)) || ('\0' == *(host))) ? DEFAULT_PGSOCKET_DIR : host, \
  56         PSQL_IS_UNIX_DOMAIN_SOCKET (host) ? "/.s.PGSQL." : ":", \
  57         port
  58
  59 enum {
  60         DEFAULT_PORT = 5432,
  61         DEFAULT_WARN = 2,
  62         DEFAULT_CRIT = 8
  63 };
  64
  65
  66
  67 int process_arguments (int, char **);
  68 int validate_arguments (void);
  69 void print_usage (void);
  70 void print_help (void);
  71 int is_pg_dbname (char *);
  72 int is_pg_logname (char *);
  73 int do_query (PGconn *, char *);
  74
  75 char *pghost = NULL;                                            /* host name of the backend server */
  76 char *pgport = NULL;                                            /* port of the backend server */
  77 int default_port = DEFAULT_PORT;
  78 char *pgoptions = NULL;
  79 char *pgtty = NULL;
  80 char dbName[NAMEDATALEN] = DEFAULT_DB;
  81 char *pguser = NULL;
  82 char *pgpasswd = NULL;
  83 char *pgparams = NULL;
  84 double twarn = (double)DEFAULT_WARN;
  85 double tcrit = (double)DEFAULT_CRIT;
  86 char *pgquery = NULL;
  87 char *query_warning = NULL;
  88 char *query_critical = NULL;
  89 thresholds *qthresholds = NULL;
  90 int verbose = 0;
  91
  92 /******************************************************************************
  93
  94 The (psuedo?)literate programming XML is contained within \@\@\- <XML> \-\@\@
  95 tags in the comments. With in the tags, the XML is assembled sequentially.
  96 You can define entities in tags. You also have all the #defines available as
  97 entities.
  98
  99 Please note that all tags must be lowercase to use the DocBook XML DTD.
 100
 101 @@-<article>
 102
 103 <sect1>
 104 <title>Quick Reference</title>
 105 <!-- The refentry forms a manpage -->
 106 <refentry>
 107 <refmeta>
 108 <manvolnum>5<manvolnum>
 109 </refmeta>
 110 <refnamdiv>
 111 <refname>&progname;</refname>
 112 <refpurpose>&SUMMARY;</refpurpose>
 113 </refnamdiv>
 114 </refentry>
 115 </sect1>
 116
 117 <sect1>
 118 <title>FAQ</title>
 119 </sect1>
 120
 121 <sect1>
 122 <title>Theory, Installation, and Operation</title>
 123
 124 <sect2>
 125 <title>General Description</title>
 126 <para>
 127 &DESCRIPTION;
 128 </para>
 129 </sect2>
 130
 131 <sect2>
 132 <title>Future Enhancements</title>
 133 <para>ToDo List</para>
 134 </sect2>
 135
 136
 137 <sect2>
 138 <title>Functions</title>
 139 -@@
 140 ******************************************************************************/
 141
 142
 143
 144 int
 145 main (int argc, char **argv)
 146 {
 147         PGconn *conn;
 148         char *conninfo = NULL;
 149
 150         struct timeval start_timeval;
 151         struct timeval end_timeval;
 152         double elapsed_time;
 153         int status = STATE_UNKNOWN;
 154         int query_status = STATE_UNKNOWN;
 155
 156         /* begin, by setting the parameters for a backend connection if the
 157          * parameters are null, then the system will try to use reasonable
 158          * defaults by looking up environment variables or, failing that,
 159          * using hardwired constants */
 160
 161         pgoptions = NULL;  /* special options to start up the backend server */
 162         pgtty = NULL;      /* debugging tty for the backend server */
 163
 164         setlocale (LC_ALL, "");
 165         bindtextdomain (PACKAGE, LOCALEDIR);
 166         textdomain (PACKAGE);
 167
 168         /* Parse extra opts if any */
 169         argv=np_extra_opts (&argc, argv, progname);
 170
 171         if (process_arguments (argc, argv) == ERROR)
 172                 usage4 (_("Could not parse arguments"));
 173         if (verbose > 2)
 174                 printf("Arguments initialized\n");
 175
 176         /* Set signal handling and alarm */
 177         if (signal (SIGALRM, timeout_alarm_handler) == SIG_ERR) {
 178                 usage4 (_("Cannot catch SIGALRM"));
 179         }
 180         alarm (timeout_interval);
 181
 182         if (pgparams)
 183                 asprintf (&conninfo, "%s ", pgparams);
 184
 185         asprintf (&conninfo, "%sdbname = '%s'", conninfo ? conninfo : "", dbName);
 186         if (pghost)
 187                 asprintf (&conninfo, "%s host = '%s'", conninfo, pghost);
 188         if (pgport)
 189                 asprintf (&conninfo, "%s port = '%s'", conninfo, pgport);
 190         if (pgoptions)
 191                 asprintf (&conninfo, "%s options = '%s'", conninfo, pgoptions);
 192         /* if (pgtty) -- ignored by PQconnectdb */
 193         if (pguser)
 194                 asprintf (&conninfo, "%s user = '%s'", conninfo, pguser);
 195
 196         if (verbose) /* do not include password (see right below) in output */
 197                 printf ("Connecting to PostgreSQL using conninfo: %s%s\n", conninfo,
 198                                 pgpasswd ? " password = <hidden>" : "");
 199
 200         if (pgpasswd)
 201                 asprintf (&conninfo, "%s password = '%s'", conninfo, pgpasswd);
 202
 203         /* make a connection to the database */
 204         gettimeofday (&start_timeval, NULL);
 205         conn = PQconnectdb (conninfo);
 206         gettimeofday (&end_timeval, NULL);
 207
 208         while (start_timeval.tv_usec > end_timeval.tv_usec) {
 209                 --end_timeval.tv_sec;
 210                 end_timeval.tv_usec += 1000000;
 211         }
 212         elapsed_time = (double)(end_timeval.tv_sec - start_timeval.tv_sec)
 213                 + (double)(end_timeval.tv_usec - start_timeval.tv_usec) / 1000000.0;
 214
 215         if (verbose)
 216                 printf("Time elapsed: %f\n", elapsed_time);
 217
 218         /* check to see that the backend connection was successfully made */
 219         if (verbose)
 220                 printf("Verifying connection\n");
 221         if (PQstatus (conn) == CONNECTION_BAD) {
 222                 printf (_("CRITICAL - no connection to '%s' (%s).\n"),
 223                         dbName, PQerrorMessage (conn));
 224                 PQfinish (conn);
 225                 return STATE_CRITICAL;
 226         }
 227         else if (elapsed_time > tcrit) {
 228                 status = STATE_CRITICAL;
 229         }
 230         else if (elapsed_time > twarn) {
 231                 status = STATE_WARNING;
 232         }
 233         else {
 234                 status = STATE_OK;
 235         }
 236
 237         if (verbose) {
 238                 char *server_host = PQhost (conn);
 239                 int server_version = PQserverVersion (conn);
 240
 241                 printf ("Successfully connected to database %s (user %s) "
 242                                 "at server %s%s%s (server version: %d.%d.%d, "
 243                                 "protocol version: %d, pid: %d)\n",
 244                                 PQdb (conn), PQuser (conn),
 245                                 PSQL_SOCKET3 (server_host, PQport (conn)),
 246                                 PSQL_SERVER_VERSION3 (server_version),
 247                                 PQprotocolVersion (conn), PQbackendPID (conn));
 248         }
 249
 250         printf (_(" %s - database %s (%f sec.)|%s\n"),
 251                 state_text(status), dbName, elapsed_time,
 252                 fperfdata("time", elapsed_time, "s",
 253                          !!(twarn > 0.0), twarn, !!(tcrit > 0.0), tcrit, TRUE, 0, FALSE,0));
 254
 255         if (pgquery)
 256                 query_status = do_query (conn, pgquery);
 257
 258         if (verbose)
 259                 printf("Closing connection\n");
 260         PQfinish (conn);
 261         return (pgquery && query_status > status) ? query_status : status;
 262 }
 263
 264
 265
 266 /* process command-line arguments */
 267 int
 268 process_arguments (int argc, char **argv)
 269 {
 270         int c;
 271
 272         int option = 0;
 273         static struct option longopts[] = {
 274                 {"help", no_argument, 0, 'h'},
 275                 {"version", no_argument, 0, 'V'},
 276                 {"timeout", required_argument, 0, 't'},
 277                 {"critical", required_argument, 0, 'c'},
 278                 {"warning", required_argument, 0, 'w'},
 279                 {"hostname", required_argument, 0, 'H'},
 280                 {"logname", required_argument, 0, 'l'},
 281                 {"password", required_argument, 0, 'p'},
 282                 {"authorization", required_argument, 0, 'a'},
 283                 {"port", required_argument, 0, 'P'},
 284                 {"database", required_argument, 0, 'd'},
 285                 {"option", required_argument, 0, 'o'},
 286                 {"query", required_argument, 0, 'q'},
 287                 {"query_critical", required_argument, 0, 'C'},
 288                 {"query_warning", required_argument, 0, 'W'},
 289                 {"verbose", no_argument, 0, 'v'},
 290                 {0, 0, 0, 0}
 291         };
 292
 293         while (1) {
 294                 c = getopt_long (argc, argv, "hVt:c:w:H:P:d:l:p:a:o:q:C:W:v",
 295                                  longopts, &option);
 296
 297                 if (c == EOF)
 298                         break;
 299
 300                 switch (c) {
 301                 case '?':     /* usage */
 302                         usage5 ();
 303                 case 'h':     /* help */
 304                         print_help ();
 305                         exit (STATE_UNKNOWN);
 306                 case 'V':     /* version */
 307                         print_revision (progname, NP_VERSION);
 308                         exit (STATE_UNKNOWN);
 309                 case 't':     /* timeout period */
 310                         if (!is_integer (optarg))
 311                                 usage2 (_("Timeout interval must be a positive integer"), optarg);
 312                         else
 313                                 timeout_interval = atoi (optarg);
 314                         break;
 315                 case 'c':     /* critical time threshold */
 316                         if (!is_nonnegative (optarg))
 317                                 usage2 (_("Critical threshold must be a positive integer"), optarg);
 318                         else
 319                                 tcrit = strtod (optarg, NULL);
 320                         break;
 321                 case 'w':     /* warning time threshold */
 322                         if (!is_nonnegative (optarg))
 323                                 usage2 (_("Warning threshold must be a positive integer"), optarg);
 324                         else
 325                                 twarn = strtod (optarg, NULL);
 326                         break;
 327                 case 'C':     /* critical query threshold */
 328                         query_critical = optarg;
 329                         break;
 330                 case 'W':     /* warning query threshold */
 331                         query_warning = optarg;
 332                         break;
 333                 case 'H':     /* host */
 334                         if ((*optarg != '/') && (!is_host (optarg)))
 335                                 usage2 (_("Invalid hostname/address"), optarg);
 336                         else
 337                                 pghost = optarg;
 338                         break;
 339                 case 'P':     /* port */
 340                         if (!is_integer (optarg))
 341                                 usage2 (_("Port must be a positive integer"), optarg);
 342                         else
 343                                 pgport = optarg;
 344                         break;
 345                 case 'd':     /* database name */
 346                         if (!is_pg_dbname (optarg)) /* checks length and valid chars */
 347                                 usage2 (_("Database name is not valid"), optarg);
 348                         else /* we know length, and know optarg is terminated, so us strcpy */
 349                                 strcpy (dbName, optarg);
 350                         break;
 351                 case 'l':     /* login name */
 352                         if (!is_pg_logname (optarg))
 353                                 usage2 (_("User name is not valid"), optarg);
 354                         else
 355                                 pguser = optarg;
 356                         break;
 357                 case 'p':     /* authentication password */
 358                 case 'a':
 359                         pgpasswd = optarg;
 360                         break;
 361                 case 'o':
 362                         if (pgparams)
 363                                 asprintf (&pgparams, "%s %s", pgparams, optarg);
 364                         else
 365                                 asprintf (&pgparams, "%s", optarg);
 366                         break;
 367                 case 'q':
 368                         pgquery = optarg;
 369                         break;
 370                 case 'v':
 371                         verbose++;
 372                         break;
 373                 }
 374         }
 375
 376         set_thresholds (&qthresholds, query_warning, query_critical);
 377
 378         return validate_arguments ();
 379 }
 380
 381
 382 /******************************************************************************
 383
 384 @@-
 385 <sect3>
 386 <title>validate_arguments</title>
 387
 388 <para>&PROTO_validate_arguments;</para>
 389
 390 <para>Given a database name, this function returns TRUE if the string
 391 is a valid PostgreSQL database name, and returns false if it is
 392 not.</para>
 393
 394 <para>Valid PostgreSQL database names are less than &NAMEDATALEN;
 395 characters long and consist of letters, numbers, and underscores. The
 396 first character cannot be a number, however.</para>
 397
 398 </sect3>
 399 -@@
 400 ******************************************************************************/
 401
 402
 403
 404 int
 405 validate_arguments ()
 406 {
 407         return OK;
 408 }
 409
 410
 411 /******************************************************************************
 412
 413 @@-
 414 <sect3>
 415 <title>is_pg_dbname</title>
 416
 417 <para>&PROTO_is_pg_dbname;</para>
 418
 419 <para>Given a database name, this function returns TRUE if the string
 420 is a valid PostgreSQL database name, and returns false if it is
 421 not.</para>
 422
 423 <para>Valid PostgreSQL database names are less than &NAMEDATALEN;
 424 characters long and consist of letters, numbers, and underscores. The
 425 first character cannot be a number, however.</para>
 426
 427 </sect3>
 428 -@@
 429 ******************************************************************************/
 430
 431
 432
 433 int
 434 is_pg_dbname (char *dbname)
 435 {
 436         char txt[NAMEDATALEN];
 437         char tmp[NAMEDATALEN];
 438         if (strlen (dbname) > NAMEDATALEN - 1)
 439                 return (FALSE);
 440         strncpy (txt, dbname, NAMEDATALEN - 1);
 441         txt[NAMEDATALEN - 1] = 0;
 442         if (sscanf (txt, "%[_a-zA-Z]%[^_a-zA-Z0-9-]", tmp, tmp) == 1)
 443                 return (TRUE);
 444         if (sscanf (txt, "%[_a-zA-Z]%[_a-zA-Z0-9-]%[^_a-zA-Z0-9-]", tmp, tmp, tmp) ==
 445                         2) return (TRUE);
 446         return (FALSE);
 447 }
 448
 449 /**
 450
 451 the tango program should eventually create an entity here based on the
 452 function prototype
 453
 454 @@-
 455 <sect3>
 456 <title>is_pg_logname</title>
 457
 458 <para>&PROTO_is_pg_logname;</para>
 459
 460 <para>Given a username, this function returns TRUE if the string is a
 461 valid PostgreSQL username, and returns false if it is not. Valid PostgreSQL
 462 usernames are less than &NAMEDATALEN; characters long and consist of
 463 letters, numbers, dashes, and underscores, plus possibly some other
 464 characters.</para>
 465
 466 <para>Currently this function only checks string length. Additional checks
 467 should be added.</para>
 468
 469 </sect3>
 470 -@@
 471 ******************************************************************************/
 472
 473
 474
 475 int
 476 is_pg_logname (char *username)
 477 {
 478         if (strlen (username) > NAMEDATALEN - 1)
 479                 return (FALSE);
 480         return (TRUE);
 481 }
 482
 483 /******************************************************************************
 484 @@-
 485 </sect2>
 486 </sect1>
 487 </article>
 488 -@@
 489 ******************************************************************************/
 490
 491
 492
 493 void
 494 print_help (void)
 495 {
 496         char *myport;
 497
 498         xasprintf (&myport, "%d", DEFAULT_PORT);
 499
 500         print_revision (progname, NP_VERSION);
 501
 502         printf (COPYRIGHT, copyright, email);
 503
 504         printf (_("Test whether a PostgreSQL Database is accepting connections."));
 505
 506         printf ("\n\n");
 507
 508         print_usage ();
 509
 510         printf (UT_HELP_VRSN);
 511         printf (UT_EXTRA_OPTS);
 512
 513         printf (UT_HOST_PORT, 'P', myport);
 514
 515         printf (" %s\n", "-d, --database=STRING");
 516         printf ("    %s", _("Database to check "));
 517         printf (_("(default: %s)\n"), DEFAULT_DB);
 518         printf (" %s\n", "-l, --logname = STRING");
 519         printf ("    %s\n", _("Login name of user"));
 520         printf (" %s\n", "-p, --password = STRING");
 521         printf ("    %s\n", _("Password (BIG SECURITY ISSUE)"));
 522         printf (" %s\n", "-o, --option = STRING");
 523         printf ("    %s\n", _("Connection parameters (keyword = value), see below"));
 524
 525         printf (UT_WARN_CRIT);
 526
 527         printf (UT_CONN_TIMEOUT, DEFAULT_SOCKET_TIMEOUT);
 528
 529         printf (" %s\n", "-q, --query=STRING");
 530         printf ("    %s\n", _("SQL query to run. Only first column in first row will be read"));
 531         printf (" %s\n", "-W, --query-warning=RANGE");
 532         printf ("    %s\n", _("SQL query value to result in warning status (double)"));
 533         printf (" %s\n", "-C, --query-critical=RANGE");
 534         printf ("    %s\n", _("SQL query value to result in critical status (double)"));
 535
 536         printf (UT_VERBOSE);
 537
 538         printf ("\n");
 539         printf (" %s\n", _("All parameters are optional."));
 540         printf (" %s\n", _("This plugin tests a PostgreSQL DBMS to determine whether it is active and"));
 541         printf (" %s\n", _("accepting queries. In its current operation, it simply connects to the"));
 542         printf (" %s\n", _("specified database, and then disconnects. If no database is specified, it"));
 543         printf (" %s\n", _("connects to the template1 database, which is present in every functioning"));
 544         printf (" %s\n\n", _("PostgreSQL DBMS."));
 545
 546         printf (" %s\n", _("If a query is specified using the -q option, it will be executed after"));
 547         printf (" %s\n", _("connecting to the server. The result from the query has to be numeric."));
 548         printf (" %s\n", _("Multiple SQL commands, separated by semicolon, are allowed but the result "));
 549         printf (" %s\n", _("of the last command is taken into account only. The value of the first"));
 550         printf (" %s\n\n", _("column in the first row is used as the check result."));
 551
 552         printf (" %s\n", _("See the chapter \"Monitoring Database Activity\" of the PostgreSQL manual"));
 553         printf (" %s\n\n", _("for details about how to access internal statistics of the database server."));
 554
 555         printf (" %s\n", _("For a list of available connection parameters which may be used with the -o"));
 556         printf (" %s\n", _("command line option, see the documentation for PQconnectdb() in the chapter"));
 557         printf (" %s\n", _("\"libpq - C Library\" of the PostgreSQL manual. For example, this may be"));
 558         printf (" %s\n", _("used to specify a service name in pg_service.conf to be used for additional"));
 559         printf (" %s\n", _("connection parameters: -o 'service=<name>' or to specify the SSL mode:"));
 560         printf (" %s\n\n", _("-o 'sslmode=require'."));
 561
 562         printf (" %s\n", _("The plugin will connect to a local postmaster if no host is specified. To"));
 563         printf (" %s\n", _("connect to a remote host, be sure that the remote postmaster accepts TCP/IP"));
 564         printf (" %s\n\n", _("connections (start the postmaster with the -i option)."));
 565
 566         printf (" %s\n", _("Typically, the monitoring user (unless the --logname option is used) should be"));
 567         printf (" %s\n", _("able to connect to the database without a password. The plugin can also send"));
 568         printf (" %s\n", _("a password, but no effort is made to obsure or encrypt the password."));
 569
 570         printf (UT_SUPPORT);
 571 }
 572
 573
 574
 575 void
 576 print_usage (void)
 577 {
 578         printf ("%s\n", _("Usage:"));
 579         printf ("%s [-H <host>] [-P <port>] [-c <critical time>] [-w <warning time>]\n", progname);
 580         printf (" [-t <timeout>] [-d <database>] [-l <logname>] [-p <password>]\n"
 581                         "[-q <query>] [-C <critical query range>] [-W <warning query range>]\n");
 582 }
 583
 584 int
 585 do_query (PGconn *conn, char *query)
 586 {
 587         PGresult *res;
 588
 589         char *val_str;
 590         double value;
 591
 592         char *endptr = NULL;
 593
 594         int my_status = STATE_UNKNOWN;
 595
 596         if (verbose)
 597                 printf ("Executing SQL query \"%s\".\n", query);
 598         res = PQexec (conn, query);
 599
 600         if (PGRES_TUPLES_OK != PQresultStatus (res)) {
 601                 printf (_("QUERY %s - %s: %s.\n"), _("CRITICAL"), _("Error with query"),
 602                                         PQerrorMessage (conn));
 603                 return STATE_CRITICAL;
 604         }
 605
 606         if (PQntuples (res) < 1) {
 607                 printf ("QUERY %s - %s.\n", _("WARNING"), _("No rows returned"));
 608                 return STATE_WARNING;
 609         }
 610
 611         if (PQnfields (res) < 1) {
 612                 printf ("QUERY %s - %s.\n", _("WARNING"), _("No columns returned"));
 613                 return STATE_WARNING;
 614         }
 615
 616         val_str = PQgetvalue (res, 0, 0);
 617         if (! val_str) {
 618                 printf ("QUERY %s - %s.\n", _("CRITICAL"), _("No data returned"));
 619                 return STATE_CRITICAL;
 620         }
 621
 622         value = strtod (val_str, &endptr);
 623         if (verbose)
 624                 printf ("Query result: %f\n", value);
 625
 626         if (endptr == val_str) {
 627                 printf ("QUERY %s - %s: %s\n", _("CRITICAL"), _("Is not a numeric"), val_str);
 628                 return STATE_CRITICAL;
 629         }
 630         else if ((endptr != NULL) && (*endptr != '\0')) {
 631                 if (verbose)
 632                         printf ("Garbage after value: %s.\n", endptr);
 633         }
 634
 635         my_status = get_status (value, qthresholds);
 636         printf ("QUERY %s - ",
 637                         (my_status == STATE_OK)
 638                                 ? _("OK")
 639                                 : (my_status == STATE_WARNING)
 640                                         ? _("WARNING")
 641                                         : (my_status == STATE_CRITICAL)
 642                                                 ? _("CRITICAL")
 643                                                 : _("UNKNOWN"));
 644         printf (_("'%s' returned %f"), query, value);
 645         printf ("|query=%f;%s;%s;;\n", value,
 646                         query_warning ? query_warning : "",
 647                         query_critical ? query_critical : "");
 648         return my_status;
 649 }
 650