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