plugins/check_pgsql.c

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