1 /*-------------------------------------------------------------------------
2 *
3 * pg_dumpall.c
4 *
5 * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
6 * Portions Copyright (c) 1994, Regents of the University of California
7 *
8 * pg_dumpall forces all pg_dump output to be text, since it also outputs
9 * text into the same output stream.
10 *
11 * src/bin/pg_dump/pg_dumpall.c
12 *
13 *-------------------------------------------------------------------------
14 */
15
16 #include "postgres_fe.h"
17
18 #include <time.h>
19 #include <unistd.h>
20
21 #include "getopt_long.h"
22
23 #include "dumputils.h"
24 #include "pg_backup.h"
25 #include "common/file_utils.h"
26 #include "fe_utils/connect.h"
27 #include "fe_utils/string_utils.h"
28
29 /* version string we expect back from pg_dump */
30 #define PGDUMP_VERSIONSTR "pg_dump (PostgreSQL) " PG_VERSION "\n"
31
32
33 static void help(void);
34
35 static void dropRoles(PGconn *conn);
36 static void dumpRoles(PGconn *conn);
37 static void dumpRoleMembership(PGconn *conn);
38 static void dumpGroups(PGconn *conn);
39 static void dropTablespaces(PGconn *conn);
40 static void dumpTablespaces(PGconn *conn);
41 static void dropDBs(PGconn *conn);
42 static void dumpUserConfig(PGconn *conn, const char *username);
43 static void dumpDatabases(PGconn *conn);
44 static void dumpTimestamp(const char *msg);
45 static int runPgDump(const char *dbname, const char *create_opts);
46 static void buildShSecLabels(PGconn *conn,
47 const char *catalog_name, Oid objectId,
48 const char *objtype, const char *objname,
49 PQExpBuffer buffer);
50 static PGconn *connectDatabase(const char *dbname, const char *connstr, const char *pghost, const char *pgport,
51 const char *pguser, trivalue prompt_password, bool fail_on_error);
52 static char *constructConnStr(const char **keywords, const char **values);
53 static PGresult *executeQuery(PGconn *conn, const char *query);
54 static void executeCommand(PGconn *conn, const char *query);
55
56 static char pg_dump_bin[MAXPGPATH];
57 static const char *progname;
58 static PQExpBuffer pgdumpopts;
59 static char *connstr = "";
60 static bool output_clean = false;
61 static bool skip_acls = false;
62 static bool verbose = false;
63 static bool dosync = true;
64
65 static int binary_upgrade = 0;
66 static int column_inserts = 0;
67 static int disable_dollar_quoting = 0;
68 static int disable_triggers = 0;
69 static int if_exists = 0;
70 static int inserts = 0;
71 static int no_tablespaces = 0;
72 static int use_setsessauth = 0;
73 static int no_comments = 0;
74 static int no_publications = 0;
75 static int no_security_labels = 0;
76 static int no_subscriptions = 0;
77 static int no_unlogged_table_data = 0;
78 static int no_role_passwords = 0;
79 static int server_version;
80 static int load_via_partition_root = 0;
81
82 static char role_catalog[10];
83 #define PG_AUTHID "pg_authid"
84 #define PG_ROLES "pg_roles "
85
86 static FILE *OPF;
87 static char *filename = NULL;
88
89 #define exit_nicely(code) exit(code)
90
91 int
main(int argc,char * argv[])92 main(int argc, char *argv[])
93 {
94 static struct option long_options[] = {
95 {"data-only", no_argument, NULL, 'a'},
96 {"clean", no_argument, NULL, 'c'},
97 {"encoding", required_argument, NULL, 'E'},
98 {"file", required_argument, NULL, 'f'},
99 {"globals-only", no_argument, NULL, 'g'},
100 {"host", required_argument, NULL, 'h'},
101 {"dbname", required_argument, NULL, 'd'},
102 {"database", required_argument, NULL, 'l'},
103 {"oids", no_argument, NULL, 'o'},
104 {"no-owner", no_argument, NULL, 'O'},
105 {"port", required_argument, NULL, 'p'},
106 {"roles-only", no_argument, NULL, 'r'},
107 {"schema-only", no_argument, NULL, 's'},
108 {"superuser", required_argument, NULL, 'S'},
109 {"tablespaces-only", no_argument, NULL, 't'},
110 {"username", required_argument, NULL, 'U'},
111 {"verbose", no_argument, NULL, 'v'},
112 {"no-password", no_argument, NULL, 'w'},
113 {"password", no_argument, NULL, 'W'},
114 {"no-privileges", no_argument, NULL, 'x'},
115 {"no-acl", no_argument, NULL, 'x'},
116
117 /*
118 * the following options don't have an equivalent short option letter
119 */
120 {"attribute-inserts", no_argument, &column_inserts, 1},
121 {"binary-upgrade", no_argument, &binary_upgrade, 1},
122 {"column-inserts", no_argument, &column_inserts, 1},
123 {"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
124 {"disable-triggers", no_argument, &disable_triggers, 1},
125 {"if-exists", no_argument, &if_exists, 1},
126 {"inserts", no_argument, &inserts, 1},
127 {"lock-wait-timeout", required_argument, NULL, 2},
128 {"no-tablespaces", no_argument, &no_tablespaces, 1},
129 {"quote-all-identifiers", no_argument, "e_all_identifiers, 1},
130 {"load-via-partition-root", no_argument, &load_via_partition_root, 1},
131 {"role", required_argument, NULL, 3},
132 {"use-set-session-authorization", no_argument, &use_setsessauth, 1},
133 {"no-comments", no_argument, &no_comments, 1},
134 {"no-publications", no_argument, &no_publications, 1},
135 {"no-role-passwords", no_argument, &no_role_passwords, 1},
136 {"no-security-labels", no_argument, &no_security_labels, 1},
137 {"no-subscriptions", no_argument, &no_subscriptions, 1},
138 {"no-sync", no_argument, NULL, 4},
139 {"no-unlogged-table-data", no_argument, &no_unlogged_table_data, 1},
140
141 {NULL, 0, NULL, 0}
142 };
143
144 char *pghost = NULL;
145 char *pgport = NULL;
146 char *pguser = NULL;
147 char *pgdb = NULL;
148 char *use_role = NULL;
149 const char *dumpencoding = NULL;
150 trivalue prompt_password = TRI_DEFAULT;
151 bool data_only = false;
152 bool globals_only = false;
153 bool roles_only = false;
154 bool tablespaces_only = false;
155 PGconn *conn;
156 int encoding;
157 const char *std_strings;
158 int c,
159 ret;
160 int optindex;
161
162 set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pg_dump"));
163
164 progname = get_progname(argv[0]);
165
166 if (argc > 1)
167 {
168 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
169 {
170 help();
171 exit_nicely(0);
172 }
173 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
174 {
175 puts("pg_dumpall (PostgreSQL) " PG_VERSION);
176 exit_nicely(0);
177 }
178 }
179
180 if ((ret = find_other_exec(argv[0], "pg_dump", PGDUMP_VERSIONSTR,
181 pg_dump_bin)) < 0)
182 {
183 char full_path[MAXPGPATH];
184
185 if (find_my_exec(argv[0], full_path) < 0)
186 strlcpy(full_path, progname, sizeof(full_path));
187
188 if (ret == -1)
189 fprintf(stderr,
190 _("The program \"pg_dump\" is needed by %s "
191 "but was not found in the\n"
192 "same directory as \"%s\".\n"
193 "Check your installation.\n"),
194 progname, full_path);
195 else
196 fprintf(stderr,
197 _("The program \"pg_dump\" was found by \"%s\"\n"
198 "but was not the same version as %s.\n"
199 "Check your installation.\n"),
200 full_path, progname);
201 exit_nicely(1);
202 }
203
204 pgdumpopts = createPQExpBuffer();
205
206 while ((c = getopt_long(argc, argv, "acd:E:f:gh:l:oOp:rsS:tU:vwWx", long_options, &optindex)) != -1)
207 {
208 switch (c)
209 {
210 case 'a':
211 data_only = true;
212 appendPQExpBufferStr(pgdumpopts, " -a");
213 break;
214
215 case 'c':
216 output_clean = true;
217 break;
218
219 case 'd':
220 connstr = pg_strdup(optarg);
221 break;
222
223 case 'E':
224 dumpencoding = pg_strdup(optarg);
225 appendPQExpBufferStr(pgdumpopts, " -E ");
226 appendShellString(pgdumpopts, optarg);
227 break;
228
229 case 'f':
230 filename = pg_strdup(optarg);
231 appendPQExpBufferStr(pgdumpopts, " -f ");
232 appendShellString(pgdumpopts, filename);
233 break;
234
235 case 'g':
236 globals_only = true;
237 break;
238
239 case 'h':
240 pghost = pg_strdup(optarg);
241 break;
242
243 case 'l':
244 pgdb = pg_strdup(optarg);
245 break;
246
247 case 'o':
248 appendPQExpBufferStr(pgdumpopts, " -o");
249 break;
250
251 case 'O':
252 appendPQExpBufferStr(pgdumpopts, " -O");
253 break;
254
255 case 'p':
256 pgport = pg_strdup(optarg);
257 break;
258
259 case 'r':
260 roles_only = true;
261 break;
262
263 case 's':
264 appendPQExpBufferStr(pgdumpopts, " -s");
265 break;
266
267 case 'S':
268 appendPQExpBufferStr(pgdumpopts, " -S ");
269 appendShellString(pgdumpopts, optarg);
270 break;
271
272 case 't':
273 tablespaces_only = true;
274 break;
275
276 case 'U':
277 pguser = pg_strdup(optarg);
278 break;
279
280 case 'v':
281 verbose = true;
282 appendPQExpBufferStr(pgdumpopts, " -v");
283 break;
284
285 case 'w':
286 prompt_password = TRI_NO;
287 appendPQExpBufferStr(pgdumpopts, " -w");
288 break;
289
290 case 'W':
291 prompt_password = TRI_YES;
292 appendPQExpBufferStr(pgdumpopts, " -W");
293 break;
294
295 case 'x':
296 skip_acls = true;
297 appendPQExpBufferStr(pgdumpopts, " -x");
298 break;
299
300 case 0:
301 break;
302
303 case 2:
304 appendPQExpBufferStr(pgdumpopts, " --lock-wait-timeout ");
305 appendShellString(pgdumpopts, optarg);
306 break;
307
308 case 3:
309 use_role = pg_strdup(optarg);
310 appendPQExpBufferStr(pgdumpopts, " --role ");
311 appendShellString(pgdumpopts, use_role);
312 break;
313
314 case 4:
315 dosync = false;
316 appendPQExpBufferStr(pgdumpopts, " --no-sync");
317 break;
318
319 default:
320 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
321 exit_nicely(1);
322 }
323 }
324
325 /* Complain if any arguments remain */
326 if (optind < argc)
327 {
328 fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
329 progname, argv[optind]);
330 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
331 progname);
332 exit_nicely(1);
333 }
334
335 /* Make sure the user hasn't specified a mix of globals-only options */
336 if (globals_only && roles_only)
337 {
338 fprintf(stderr, _("%s: options -g/--globals-only and -r/--roles-only cannot be used together\n"),
339 progname);
340 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
341 progname);
342 exit_nicely(1);
343 }
344
345 if (globals_only && tablespaces_only)
346 {
347 fprintf(stderr, _("%s: options -g/--globals-only and -t/--tablespaces-only cannot be used together\n"),
348 progname);
349 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
350 progname);
351 exit_nicely(1);
352 }
353
354 if (if_exists && !output_clean)
355 {
356 fprintf(stderr, _("%s: option --if-exists requires option -c/--clean\n"),
357 progname);
358 exit_nicely(1);
359 }
360
361 if (roles_only && tablespaces_only)
362 {
363 fprintf(stderr, _("%s: options -r/--roles-only and -t/--tablespaces-only cannot be used together\n"),
364 progname);
365 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
366 progname);
367 exit_nicely(1);
368 }
369
370 /*
371 * If password values are not required in the dump, switch to using
372 * pg_roles which is equally useful, just more likely to have unrestricted
373 * access than pg_authid.
374 */
375 if (no_role_passwords)
376 sprintf(role_catalog, "%s", PG_ROLES);
377 else
378 sprintf(role_catalog, "%s", PG_AUTHID);
379
380 /* Add long options to the pg_dump argument list */
381 if (binary_upgrade)
382 appendPQExpBufferStr(pgdumpopts, " --binary-upgrade");
383 if (column_inserts)
384 appendPQExpBufferStr(pgdumpopts, " --column-inserts");
385 if (disable_dollar_quoting)
386 appendPQExpBufferStr(pgdumpopts, " --disable-dollar-quoting");
387 if (disable_triggers)
388 appendPQExpBufferStr(pgdumpopts, " --disable-triggers");
389 if (inserts)
390 appendPQExpBufferStr(pgdumpopts, " --inserts");
391 if (no_tablespaces)
392 appendPQExpBufferStr(pgdumpopts, " --no-tablespaces");
393 if (quote_all_identifiers)
394 appendPQExpBufferStr(pgdumpopts, " --quote-all-identifiers");
395 if (load_via_partition_root)
396 appendPQExpBufferStr(pgdumpopts, " --load-via-partition-root");
397 if (use_setsessauth)
398 appendPQExpBufferStr(pgdumpopts, " --use-set-session-authorization");
399 if (no_comments)
400 appendPQExpBufferStr(pgdumpopts, " --no-comments");
401 if (no_publications)
402 appendPQExpBufferStr(pgdumpopts, " --no-publications");
403 if (no_security_labels)
404 appendPQExpBufferStr(pgdumpopts, " --no-security-labels");
405 if (no_subscriptions)
406 appendPQExpBufferStr(pgdumpopts, " --no-subscriptions");
407 if (no_unlogged_table_data)
408 appendPQExpBufferStr(pgdumpopts, " --no-unlogged-table-data");
409
410 /*
411 * If there was a database specified on the command line, use that,
412 * otherwise try to connect to database "postgres", and failing that
413 * "template1". "postgres" is the preferred choice for 8.1 and later
414 * servers, but it usually will not exist on older ones.
415 */
416 if (pgdb)
417 {
418 conn = connectDatabase(pgdb, connstr, pghost, pgport, pguser,
419 prompt_password, false);
420
421 if (!conn)
422 {
423 fprintf(stderr, _("%s: could not connect to database \"%s\"\n"),
424 progname, pgdb);
425 exit_nicely(1);
426 }
427 }
428 else
429 {
430 conn = connectDatabase("postgres", connstr, pghost, pgport, pguser,
431 prompt_password, false);
432 if (!conn)
433 conn = connectDatabase("template1", connstr, pghost, pgport, pguser,
434 prompt_password, true);
435
436 if (!conn)
437 {
438 fprintf(stderr, _("%s: could not connect to databases \"postgres\" or \"template1\"\n"
439 "Please specify an alternative database.\n"),
440 progname);
441 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
442 progname);
443 exit_nicely(1);
444 }
445 }
446
447 /*
448 * Open the output file if required, otherwise use stdout
449 */
450 if (filename)
451 {
452 OPF = fopen(filename, PG_BINARY_W);
453 if (!OPF)
454 {
455 fprintf(stderr, _("%s: could not open the output file \"%s\": %s\n"),
456 progname, filename, strerror(errno));
457 exit_nicely(1);
458 }
459 }
460 else
461 OPF = stdout;
462
463 /*
464 * Set the client encoding if requested.
465 */
466 if (dumpencoding)
467 {
468 if (PQsetClientEncoding(conn, dumpencoding) < 0)
469 {
470 fprintf(stderr, _("%s: invalid client encoding \"%s\" specified\n"),
471 progname, dumpencoding);
472 exit_nicely(1);
473 }
474 }
475
476 /*
477 * Get the active encoding and the standard_conforming_strings setting, so
478 * we know how to escape strings.
479 */
480 encoding = PQclientEncoding(conn);
481 std_strings = PQparameterStatus(conn, "standard_conforming_strings");
482 if (!std_strings)
483 std_strings = "off";
484
485 /* Set the role if requested */
486 if (use_role && server_version >= 80100)
487 {
488 PQExpBuffer query = createPQExpBuffer();
489
490 appendPQExpBuffer(query, "SET ROLE %s", fmtId(use_role));
491 executeCommand(conn, query->data);
492 destroyPQExpBuffer(query);
493 }
494
495 /* Force quoting of all identifiers if requested. */
496 if (quote_all_identifiers && server_version >= 90100)
497 executeCommand(conn, "SET quote_all_identifiers = true");
498
499 fprintf(OPF, "--\n-- PostgreSQL database cluster dump\n--\n\n");
500 if (verbose)
501 dumpTimestamp("Started on");
502
503 /*
504 * We used to emit \connect postgres here, but that served no purpose
505 * other than to break things for installations without a postgres
506 * database. Everything we're restoring here is a global, so whichever
507 * database we're connected to at the moment is fine.
508 */
509
510 /* Restore will need to write to the target cluster */
511 fprintf(OPF, "SET default_transaction_read_only = off;\n\n");
512
513 /* Replicate encoding and std_strings in output */
514 fprintf(OPF, "SET client_encoding = '%s';\n",
515 pg_encoding_to_char(encoding));
516 fprintf(OPF, "SET standard_conforming_strings = %s;\n", std_strings);
517 if (strcmp(std_strings, "off") == 0)
518 fprintf(OPF, "SET escape_string_warning = off;\n");
519 fprintf(OPF, "\n");
520
521 if (!data_only)
522 {
523 /*
524 * If asked to --clean, do that first. We can avoid detailed
525 * dependency analysis because databases never depend on each other,
526 * and tablespaces never depend on each other. Roles could have
527 * grants to each other, but DROP ROLE will clean those up silently.
528 */
529 if (output_clean)
530 {
531 if (!globals_only && !roles_only && !tablespaces_only)
532 dropDBs(conn);
533
534 if (!roles_only && !no_tablespaces)
535 dropTablespaces(conn);
536
537 if (!tablespaces_only)
538 dropRoles(conn);
539 }
540
541 /*
542 * Now create objects as requested. Be careful that option logic here
543 * is the same as for drops above.
544 */
545 if (!tablespaces_only)
546 {
547 /* Dump roles (users) */
548 dumpRoles(conn);
549
550 /* Dump role memberships --- need different method for pre-8.1 */
551 if (server_version >= 80100)
552 dumpRoleMembership(conn);
553 else
554 dumpGroups(conn);
555 }
556
557 /* Dump tablespaces */
558 if (!roles_only && !no_tablespaces)
559 dumpTablespaces(conn);
560 }
561
562 if (!globals_only && !roles_only && !tablespaces_only)
563 dumpDatabases(conn);
564
565 PQfinish(conn);
566
567 if (verbose)
568 dumpTimestamp("Completed on");
569 fprintf(OPF, "--\n-- PostgreSQL database cluster dump complete\n--\n\n");
570
571 if (filename)
572 {
573 fclose(OPF);
574
575 /* sync the resulting file, errors are not fatal */
576 if (dosync)
577 (void) fsync_fname(filename, false, progname);
578 }
579
580 exit_nicely(0);
581 }
582
583
584 static void
help(void)585 help(void)
586 {
587 printf(_("%s extracts a PostgreSQL database cluster into an SQL script file.\n\n"), progname);
588 printf(_("Usage:\n"));
589 printf(_(" %s [OPTION]...\n"), progname);
590
591 printf(_("\nGeneral options:\n"));
592 printf(_(" -f, --file=FILENAME output file name\n"));
593 printf(_(" -v, --verbose verbose mode\n"));
594 printf(_(" -V, --version output version information, then exit\n"));
595 printf(_(" --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock\n"));
596 printf(_(" -?, --help show this help, then exit\n"));
597 printf(_("\nOptions controlling the output content:\n"));
598 printf(_(" -a, --data-only dump only the data, not the schema\n"));
599 printf(_(" -c, --clean clean (drop) databases before recreating\n"));
600 printf(_(" -E, --encoding=ENCODING dump the data in encoding ENCODING\n"));
601 printf(_(" -g, --globals-only dump only global objects, no databases\n"));
602 printf(_(" -o, --oids include OIDs in dump\n"));
603 printf(_(" -O, --no-owner skip restoration of object ownership\n"));
604 printf(_(" -r, --roles-only dump only roles, no databases or tablespaces\n"));
605 printf(_(" -s, --schema-only dump only the schema, no data\n"));
606 printf(_(" -S, --superuser=NAME superuser user name to use in the dump\n"));
607 printf(_(" -t, --tablespaces-only dump only tablespaces, no databases or roles\n"));
608 printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
609 printf(_(" --binary-upgrade for use by upgrade utilities only\n"));
610 printf(_(" --column-inserts dump data as INSERT commands with column names\n"));
611 printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n"));
612 printf(_(" --disable-triggers disable triggers during data-only restore\n"));
613 printf(_(" --if-exists use IF EXISTS when dropping objects\n"));
614 printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));
615 printf(_(" --load-via-partition-root load partitions via the root table\n"));
616 printf(_(" --no-comments do not dump comments\n"));
617 printf(_(" --no-publications do not dump publications\n"));
618 printf(_(" --no-role-passwords do not dump passwords for roles\n"));
619 printf(_(" --no-security-labels do not dump security label assignments\n"));
620 printf(_(" --no-subscriptions do not dump subscriptions\n"));
621 printf(_(" --no-sync do not wait for changes to be written safely to disk\n"));
622 printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
623 printf(_(" --no-unlogged-table-data do not dump unlogged table data\n"));
624 printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n"));
625 printf(_(" --use-set-session-authorization\n"
626 " use SET SESSION AUTHORIZATION commands instead of\n"
627 " ALTER OWNER commands to set ownership\n"));
628
629 printf(_("\nConnection options:\n"));
630 printf(_(" -d, --dbname=CONNSTR connect using connection string\n"));
631 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
632 printf(_(" -l, --database=DBNAME alternative default database\n"));
633 printf(_(" -p, --port=PORT database server port number\n"));
634 printf(_(" -U, --username=NAME connect as specified database user\n"));
635 printf(_(" -w, --no-password never prompt for password\n"));
636 printf(_(" -W, --password force password prompt (should happen automatically)\n"));
637 printf(_(" --role=ROLENAME do SET ROLE before dump\n"));
638
639 printf(_("\nIf -f/--file is not used, then the SQL script will be written to the standard\n"
640 "output.\n\n"));
641 printf(_("Report bugs to <pgsql-bugs@postgresql.org>.\n"));
642 }
643
644
645 /*
646 * Drop roles
647 */
648 static void
dropRoles(PGconn * conn)649 dropRoles(PGconn *conn)
650 {
651 PQExpBuffer buf = createPQExpBuffer();
652 PGresult *res;
653 int i_rolname;
654 int i;
655
656 if (server_version >= 90600)
657 printfPQExpBuffer(buf,
658 "SELECT rolname "
659 "FROM %s "
660 "WHERE rolname !~ '^pg_' "
661 "ORDER BY 1", role_catalog);
662 else if (server_version >= 80100)
663 printfPQExpBuffer(buf,
664 "SELECT rolname "
665 "FROM %s "
666 "ORDER BY 1", role_catalog);
667 else
668 printfPQExpBuffer(buf,
669 "SELECT usename as rolname "
670 "FROM pg_shadow "
671 "UNION "
672 "SELECT groname as rolname "
673 "FROM pg_group "
674 "ORDER BY 1");
675
676 res = executeQuery(conn, buf->data);
677
678 i_rolname = PQfnumber(res, "rolname");
679
680 if (PQntuples(res) > 0)
681 fprintf(OPF, "--\n-- Drop roles\n--\n\n");
682
683 for (i = 0; i < PQntuples(res); i++)
684 {
685 const char *rolename;
686
687 rolename = PQgetvalue(res, i, i_rolname);
688
689 fprintf(OPF, "DROP ROLE %s%s;\n",
690 if_exists ? "IF EXISTS " : "",
691 fmtId(rolename));
692 }
693
694 PQclear(res);
695 destroyPQExpBuffer(buf);
696
697 fprintf(OPF, "\n\n");
698 }
699
700 /*
701 * Dump roles
702 */
703 static void
dumpRoles(PGconn * conn)704 dumpRoles(PGconn *conn)
705 {
706 PQExpBuffer buf = createPQExpBuffer();
707 PGresult *res;
708 int i_oid,
709 i_rolname,
710 i_rolsuper,
711 i_rolinherit,
712 i_rolcreaterole,
713 i_rolcreatedb,
714 i_rolcanlogin,
715 i_rolconnlimit,
716 i_rolpassword,
717 i_rolvaliduntil,
718 i_rolreplication,
719 i_rolbypassrls,
720 i_rolcomment,
721 i_is_current_user;
722 int i;
723
724 /* note: rolconfig is dumped later */
725 if (server_version >= 90600)
726 printfPQExpBuffer(buf,
727 "SELECT oid, rolname, rolsuper, rolinherit, "
728 "rolcreaterole, rolcreatedb, "
729 "rolcanlogin, rolconnlimit, rolpassword, "
730 "rolvaliduntil, rolreplication, rolbypassrls, "
731 "pg_catalog.shobj_description(oid, '%s') as rolcomment, "
732 "rolname = current_user AS is_current_user "
733 "FROM %s "
734 "WHERE rolname !~ '^pg_' "
735 "ORDER BY 2", role_catalog, role_catalog);
736 else if (server_version >= 90500)
737 printfPQExpBuffer(buf,
738 "SELECT oid, rolname, rolsuper, rolinherit, "
739 "rolcreaterole, rolcreatedb, "
740 "rolcanlogin, rolconnlimit, rolpassword, "
741 "rolvaliduntil, rolreplication, rolbypassrls, "
742 "pg_catalog.shobj_description(oid, '%s') as rolcomment, "
743 "rolname = current_user AS is_current_user "
744 "FROM %s "
745 "ORDER BY 2", role_catalog, role_catalog);
746 else if (server_version >= 90100)
747 printfPQExpBuffer(buf,
748 "SELECT oid, rolname, rolsuper, rolinherit, "
749 "rolcreaterole, rolcreatedb, "
750 "rolcanlogin, rolconnlimit, rolpassword, "
751 "rolvaliduntil, rolreplication, "
752 "false as rolbypassrls, "
753 "pg_catalog.shobj_description(oid, '%s') as rolcomment, "
754 "rolname = current_user AS is_current_user "
755 "FROM %s "
756 "ORDER BY 2", role_catalog, role_catalog);
757 else if (server_version >= 80200)
758 printfPQExpBuffer(buf,
759 "SELECT oid, rolname, rolsuper, rolinherit, "
760 "rolcreaterole, rolcreatedb, "
761 "rolcanlogin, rolconnlimit, rolpassword, "
762 "rolvaliduntil, false as rolreplication, "
763 "false as rolbypassrls, "
764 "pg_catalog.shobj_description(oid, '%s') as rolcomment, "
765 "rolname = current_user AS is_current_user "
766 "FROM %s "
767 "ORDER BY 2", role_catalog, role_catalog);
768 else if (server_version >= 80100)
769 printfPQExpBuffer(buf,
770 "SELECT oid, rolname, rolsuper, rolinherit, "
771 "rolcreaterole, rolcreatedb, "
772 "rolcanlogin, rolconnlimit, rolpassword, "
773 "rolvaliduntil, false as rolreplication, "
774 "false as rolbypassrls, "
775 "null as rolcomment, "
776 "rolname = current_user AS is_current_user "
777 "FROM %s "
778 "ORDER BY 2", role_catalog);
779 else
780 printfPQExpBuffer(buf,
781 "SELECT 0 as oid, usename as rolname, "
782 "usesuper as rolsuper, "
783 "true as rolinherit, "
784 "usesuper as rolcreaterole, "
785 "usecreatedb as rolcreatedb, "
786 "true as rolcanlogin, "
787 "-1 as rolconnlimit, "
788 "passwd as rolpassword, "
789 "valuntil as rolvaliduntil, "
790 "false as rolreplication, "
791 "false as rolbypassrls, "
792 "null as rolcomment, "
793 "usename = current_user AS is_current_user "
794 "FROM pg_shadow "
795 "UNION ALL "
796 "SELECT 0 as oid, groname as rolname, "
797 "false as rolsuper, "
798 "true as rolinherit, "
799 "false as rolcreaterole, "
800 "false as rolcreatedb, "
801 "false as rolcanlogin, "
802 "-1 as rolconnlimit, "
803 "null::text as rolpassword, "
804 "null::abstime as rolvaliduntil, "
805 "false as rolreplication, "
806 "false as rolbypassrls, "
807 "null as rolcomment, "
808 "false AS is_current_user "
809 "FROM pg_group "
810 "WHERE NOT EXISTS (SELECT 1 FROM pg_shadow "
811 " WHERE usename = groname) "
812 "ORDER BY 2");
813
814 res = executeQuery(conn, buf->data);
815
816 i_oid = PQfnumber(res, "oid");
817 i_rolname = PQfnumber(res, "rolname");
818 i_rolsuper = PQfnumber(res, "rolsuper");
819 i_rolinherit = PQfnumber(res, "rolinherit");
820 i_rolcreaterole = PQfnumber(res, "rolcreaterole");
821 i_rolcreatedb = PQfnumber(res, "rolcreatedb");
822 i_rolcanlogin = PQfnumber(res, "rolcanlogin");
823 i_rolconnlimit = PQfnumber(res, "rolconnlimit");
824 i_rolpassword = PQfnumber(res, "rolpassword");
825 i_rolvaliduntil = PQfnumber(res, "rolvaliduntil");
826 i_rolreplication = PQfnumber(res, "rolreplication");
827 i_rolbypassrls = PQfnumber(res, "rolbypassrls");
828 i_rolcomment = PQfnumber(res, "rolcomment");
829 i_is_current_user = PQfnumber(res, "is_current_user");
830
831 if (PQntuples(res) > 0)
832 fprintf(OPF, "--\n-- Roles\n--\n\n");
833
834 for (i = 0; i < PQntuples(res); i++)
835 {
836 const char *rolename;
837 Oid auth_oid;
838
839 auth_oid = atooid(PQgetvalue(res, i, i_oid));
840 rolename = PQgetvalue(res, i, i_rolname);
841
842 if (strncmp(rolename, "pg_", 3) == 0)
843 {
844 fprintf(stderr, _("%s: role name starting with \"pg_\" skipped (%s)\n"),
845 progname, rolename);
846 continue;
847 }
848
849 resetPQExpBuffer(buf);
850
851 if (binary_upgrade)
852 {
853 appendPQExpBufferStr(buf, "\n-- For binary upgrade, must preserve pg_authid.oid\n");
854 appendPQExpBuffer(buf,
855 "SELECT pg_catalog.binary_upgrade_set_next_pg_authid_oid('%u'::pg_catalog.oid);\n\n",
856 auth_oid);
857 }
858
859 /*
860 * We dump CREATE ROLE followed by ALTER ROLE to ensure that the role
861 * will acquire the right properties even if it already exists (ie, it
862 * won't hurt for the CREATE to fail). This is particularly important
863 * for the role we are connected as, since even with --clean we will
864 * have failed to drop it. binary_upgrade cannot generate any errors,
865 * so we assume the current role is already created.
866 */
867 if (!binary_upgrade ||
868 strcmp(PQgetvalue(res, i, i_is_current_user), "f") == 0)
869 appendPQExpBuffer(buf, "CREATE ROLE %s;\n", fmtId(rolename));
870 appendPQExpBuffer(buf, "ALTER ROLE %s WITH", fmtId(rolename));
871
872 if (strcmp(PQgetvalue(res, i, i_rolsuper), "t") == 0)
873 appendPQExpBufferStr(buf, " SUPERUSER");
874 else
875 appendPQExpBufferStr(buf, " NOSUPERUSER");
876
877 if (strcmp(PQgetvalue(res, i, i_rolinherit), "t") == 0)
878 appendPQExpBufferStr(buf, " INHERIT");
879 else
880 appendPQExpBufferStr(buf, " NOINHERIT");
881
882 if (strcmp(PQgetvalue(res, i, i_rolcreaterole), "t") == 0)
883 appendPQExpBufferStr(buf, " CREATEROLE");
884 else
885 appendPQExpBufferStr(buf, " NOCREATEROLE");
886
887 if (strcmp(PQgetvalue(res, i, i_rolcreatedb), "t") == 0)
888 appendPQExpBufferStr(buf, " CREATEDB");
889 else
890 appendPQExpBufferStr(buf, " NOCREATEDB");
891
892 if (strcmp(PQgetvalue(res, i, i_rolcanlogin), "t") == 0)
893 appendPQExpBufferStr(buf, " LOGIN");
894 else
895 appendPQExpBufferStr(buf, " NOLOGIN");
896
897 if (strcmp(PQgetvalue(res, i, i_rolreplication), "t") == 0)
898 appendPQExpBufferStr(buf, " REPLICATION");
899 else
900 appendPQExpBufferStr(buf, " NOREPLICATION");
901
902 if (strcmp(PQgetvalue(res, i, i_rolbypassrls), "t") == 0)
903 appendPQExpBufferStr(buf, " BYPASSRLS");
904 else
905 appendPQExpBufferStr(buf, " NOBYPASSRLS");
906
907 if (strcmp(PQgetvalue(res, i, i_rolconnlimit), "-1") != 0)
908 appendPQExpBuffer(buf, " CONNECTION LIMIT %s",
909 PQgetvalue(res, i, i_rolconnlimit));
910
911
912 if (!PQgetisnull(res, i, i_rolpassword) && !no_role_passwords)
913 {
914 appendPQExpBufferStr(buf, " PASSWORD ");
915 appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolpassword), conn);
916 }
917
918 if (!PQgetisnull(res, i, i_rolvaliduntil))
919 appendPQExpBuffer(buf, " VALID UNTIL '%s'",
920 PQgetvalue(res, i, i_rolvaliduntil));
921
922 appendPQExpBufferStr(buf, ";\n");
923
924 if (!no_comments && !PQgetisnull(res, i, i_rolcomment))
925 {
926 appendPQExpBuffer(buf, "COMMENT ON ROLE %s IS ", fmtId(rolename));
927 appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolcomment), conn);
928 appendPQExpBufferStr(buf, ";\n");
929 }
930
931 if (!no_security_labels && server_version >= 90200)
932 buildShSecLabels(conn, "pg_authid", auth_oid,
933 "ROLE", rolename,
934 buf);
935
936 fprintf(OPF, "%s", buf->data);
937 }
938
939 /*
940 * Dump configuration settings for roles after all roles have been dumped.
941 * We do it this way because config settings for roles could mention the
942 * names of other roles.
943 */
944 for (i = 0; i < PQntuples(res); i++)
945 dumpUserConfig(conn, PQgetvalue(res, i, i_rolname));
946
947 PQclear(res);
948
949 fprintf(OPF, "\n\n");
950
951 destroyPQExpBuffer(buf);
952 }
953
954
955 /*
956 * Dump role memberships. This code is used for 8.1 and later servers.
957 *
958 * Note: we expect dumpRoles already created all the roles, but there is
959 * no membership yet.
960 */
961 static void
dumpRoleMembership(PGconn * conn)962 dumpRoleMembership(PGconn *conn)
963 {
964 PQExpBuffer buf = createPQExpBuffer();
965 PGresult *res;
966 int i;
967
968 printfPQExpBuffer(buf, "SELECT ur.rolname AS roleid, "
969 "um.rolname AS member, "
970 "a.admin_option, "
971 "ug.rolname AS grantor "
972 "FROM pg_auth_members a "
973 "LEFT JOIN %s ur on ur.oid = a.roleid "
974 "LEFT JOIN %s um on um.oid = a.member "
975 "LEFT JOIN %s ug on ug.oid = a.grantor "
976 "WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')"
977 "ORDER BY 1,2,3", role_catalog, role_catalog, role_catalog);
978 res = executeQuery(conn, buf->data);
979
980 if (PQntuples(res) > 0)
981 fprintf(OPF, "--\n-- Role memberships\n--\n\n");
982
983 for (i = 0; i < PQntuples(res); i++)
984 {
985 char *roleid = PQgetvalue(res, i, 0);
986 char *member = PQgetvalue(res, i, 1);
987 char *option = PQgetvalue(res, i, 2);
988
989 fprintf(OPF, "GRANT %s", fmtId(roleid));
990 fprintf(OPF, " TO %s", fmtId(member));
991 if (*option == 't')
992 fprintf(OPF, " WITH ADMIN OPTION");
993
994 /*
995 * We don't track the grantor very carefully in the backend, so cope
996 * with the possibility that it has been dropped.
997 */
998 if (!PQgetisnull(res, i, 3))
999 {
1000 char *grantor = PQgetvalue(res, i, 3);
1001
1002 fprintf(OPF, " GRANTED BY %s", fmtId(grantor));
1003 }
1004 fprintf(OPF, ";\n");
1005 }
1006
1007 PQclear(res);
1008 destroyPQExpBuffer(buf);
1009
1010 fprintf(OPF, "\n\n");
1011 }
1012
1013 /*
1014 * Dump group memberships from a pre-8.1 server. It's annoying that we
1015 * can't share any useful amount of code with the post-8.1 case, but
1016 * the catalog representations are too different.
1017 *
1018 * Note: we expect dumpRoles already created all the roles, but there is
1019 * no membership yet.
1020 */
1021 static void
dumpGroups(PGconn * conn)1022 dumpGroups(PGconn *conn)
1023 {
1024 PQExpBuffer buf = createPQExpBuffer();
1025 PGresult *res;
1026 int i;
1027
1028 res = executeQuery(conn,
1029 "SELECT groname, grolist FROM pg_group ORDER BY 1");
1030
1031 if (PQntuples(res) > 0)
1032 fprintf(OPF, "--\n-- Role memberships\n--\n\n");
1033
1034 for (i = 0; i < PQntuples(res); i++)
1035 {
1036 char *groname = PQgetvalue(res, i, 0);
1037 char *grolist = PQgetvalue(res, i, 1);
1038 PGresult *res2;
1039 int j;
1040
1041 /*
1042 * Array representation is {1,2,3} ... convert to (1,2,3)
1043 */
1044 if (strlen(grolist) < 3)
1045 continue;
1046
1047 grolist = pg_strdup(grolist);
1048 grolist[0] = '(';
1049 grolist[strlen(grolist) - 1] = ')';
1050 printfPQExpBuffer(buf,
1051 "SELECT usename FROM pg_shadow "
1052 "WHERE usesysid IN %s ORDER BY 1",
1053 grolist);
1054 free(grolist);
1055
1056 res2 = executeQuery(conn, buf->data);
1057
1058 for (j = 0; j < PQntuples(res2); j++)
1059 {
1060 char *usename = PQgetvalue(res2, j, 0);
1061
1062 /*
1063 * Don't try to grant a role to itself; can happen if old
1064 * installation has identically named user and group.
1065 */
1066 if (strcmp(groname, usename) == 0)
1067 continue;
1068
1069 fprintf(OPF, "GRANT %s", fmtId(groname));
1070 fprintf(OPF, " TO %s;\n", fmtId(usename));
1071 }
1072
1073 PQclear(res2);
1074 }
1075
1076 PQclear(res);
1077 destroyPQExpBuffer(buf);
1078
1079 fprintf(OPF, "\n\n");
1080 }
1081
1082
1083 /*
1084 * Drop tablespaces.
1085 */
1086 static void
dropTablespaces(PGconn * conn)1087 dropTablespaces(PGconn *conn)
1088 {
1089 PGresult *res;
1090 int i;
1091
1092 /*
1093 * Get all tablespaces except built-in ones (which we assume are named
1094 * pg_xxx)
1095 */
1096 res = executeQuery(conn, "SELECT spcname "
1097 "FROM pg_catalog.pg_tablespace "
1098 "WHERE spcname !~ '^pg_' "
1099 "ORDER BY 1");
1100
1101 if (PQntuples(res) > 0)
1102 fprintf(OPF, "--\n-- Drop tablespaces\n--\n\n");
1103
1104 for (i = 0; i < PQntuples(res); i++)
1105 {
1106 char *spcname = PQgetvalue(res, i, 0);
1107
1108 fprintf(OPF, "DROP TABLESPACE %s%s;\n",
1109 if_exists ? "IF EXISTS " : "",
1110 fmtId(spcname));
1111 }
1112
1113 PQclear(res);
1114
1115 fprintf(OPF, "\n\n");
1116 }
1117
1118 /*
1119 * Dump tablespaces.
1120 */
1121 static void
dumpTablespaces(PGconn * conn)1122 dumpTablespaces(PGconn *conn)
1123 {
1124 PGresult *res;
1125 int i;
1126
1127 /*
1128 * Get all tablespaces except built-in ones (which we assume are named
1129 * pg_xxx)
1130 *
1131 * For the tablespace ACLs, as of 9.6, we extract both the positive (as
1132 * spcacl) and negative (as rspcacl) ACLs, relative to the default ACL for
1133 * tablespaces, which are then passed to buildACLCommands() below.
1134 *
1135 * See buildACLQueries() and buildACLCommands().
1136 *
1137 * The order in which privileges are in the ACL string (the order they
1138 * have been GRANT'd in, which the backend maintains) must be preserved to
1139 * ensure that GRANTs WITH GRANT OPTION and subsequent GRANTs based on
1140 * those are dumped in the correct order.
1141 *
1142 * Note that we do not support initial privileges (pg_init_privs) on
1143 * tablespaces, so this logic cannot make use of buildACLQueries().
1144 */
1145 if (server_version >= 90600)
1146 res = executeQuery(conn, "SELECT oid, spcname, "
1147 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
1148 "pg_catalog.pg_tablespace_location(oid), "
1149 "(SELECT array_agg(acl ORDER BY row_n) FROM "
1150 " (SELECT acl, row_n FROM "
1151 " unnest(coalesce(spcacl,acldefault('t',spcowner))) "
1152 " WITH ORDINALITY AS perm(acl,row_n) "
1153 " WHERE NOT EXISTS ( "
1154 " SELECT 1 "
1155 " FROM unnest(acldefault('t',spcowner)) "
1156 " AS init(init_acl) "
1157 " WHERE acl = init_acl)) AS spcacls) "
1158 " AS spcacl, "
1159 "(SELECT array_agg(acl ORDER BY row_n) FROM "
1160 " (SELECT acl, row_n FROM "
1161 " unnest(acldefault('t',spcowner)) "
1162 " WITH ORDINALITY AS initp(acl,row_n) "
1163 " WHERE NOT EXISTS ( "
1164 " SELECT 1 "
1165 " FROM unnest(coalesce(spcacl,acldefault('t',spcowner))) "
1166 " AS permp(orig_acl) "
1167 " WHERE acl = orig_acl)) AS rspcacls) "
1168 " AS rspcacl, "
1169 "array_to_string(spcoptions, ', '),"
1170 "pg_catalog.shobj_description(oid, 'pg_tablespace') "
1171 "FROM pg_catalog.pg_tablespace "
1172 "WHERE spcname !~ '^pg_' "
1173 "ORDER BY 1");
1174 else if (server_version >= 90200)
1175 res = executeQuery(conn, "SELECT oid, spcname, "
1176 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
1177 "pg_catalog.pg_tablespace_location(oid), "
1178 "spcacl, '' as rspcacl, "
1179 "array_to_string(spcoptions, ', '),"
1180 "pg_catalog.shobj_description(oid, 'pg_tablespace') "
1181 "FROM pg_catalog.pg_tablespace "
1182 "WHERE spcname !~ '^pg_' "
1183 "ORDER BY 1");
1184 else if (server_version >= 90000)
1185 res = executeQuery(conn, "SELECT oid, spcname, "
1186 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
1187 "spclocation, spcacl, '' as rspcacl, "
1188 "array_to_string(spcoptions, ', '),"
1189 "pg_catalog.shobj_description(oid, 'pg_tablespace') "
1190 "FROM pg_catalog.pg_tablespace "
1191 "WHERE spcname !~ '^pg_' "
1192 "ORDER BY 1");
1193 else if (server_version >= 80200)
1194 res = executeQuery(conn, "SELECT oid, spcname, "
1195 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
1196 "spclocation, spcacl, '' as rspcacl, null, "
1197 "pg_catalog.shobj_description(oid, 'pg_tablespace') "
1198 "FROM pg_catalog.pg_tablespace "
1199 "WHERE spcname !~ '^pg_' "
1200 "ORDER BY 1");
1201 else
1202 res = executeQuery(conn, "SELECT oid, spcname, "
1203 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
1204 "spclocation, spcacl, '' as rspcacl, "
1205 "null, null "
1206 "FROM pg_catalog.pg_tablespace "
1207 "WHERE spcname !~ '^pg_' "
1208 "ORDER BY 1");
1209
1210 if (PQntuples(res) > 0)
1211 fprintf(OPF, "--\n-- Tablespaces\n--\n\n");
1212
1213 for (i = 0; i < PQntuples(res); i++)
1214 {
1215 PQExpBuffer buf = createPQExpBuffer();
1216 Oid spcoid = atooid(PQgetvalue(res, i, 0));
1217 char *spcname = PQgetvalue(res, i, 1);
1218 char *spcowner = PQgetvalue(res, i, 2);
1219 char *spclocation = PQgetvalue(res, i, 3);
1220 char *spcacl = PQgetvalue(res, i, 4);
1221 char *rspcacl = PQgetvalue(res, i, 5);
1222 char *spcoptions = PQgetvalue(res, i, 6);
1223 char *spccomment = PQgetvalue(res, i, 7);
1224 char *fspcname;
1225
1226 /* needed for buildACLCommands() */
1227 fspcname = pg_strdup(fmtId(spcname));
1228
1229 appendPQExpBuffer(buf, "CREATE TABLESPACE %s", fspcname);
1230 appendPQExpBuffer(buf, " OWNER %s", fmtId(spcowner));
1231
1232 appendPQExpBufferStr(buf, " LOCATION ");
1233 appendStringLiteralConn(buf, spclocation, conn);
1234 appendPQExpBufferStr(buf, ";\n");
1235
1236 if (spcoptions && spcoptions[0] != '\0')
1237 appendPQExpBuffer(buf, "ALTER TABLESPACE %s SET (%s);\n",
1238 fspcname, spcoptions);
1239
1240 if (!skip_acls &&
1241 !buildACLCommands(fspcname, NULL, NULL, "TABLESPACE",
1242 spcacl, rspcacl,
1243 spcowner, "", server_version, buf))
1244 {
1245 fprintf(stderr, _("%s: could not parse ACL list (%s) for tablespace \"%s\"\n"),
1246 progname, spcacl, spcname);
1247 PQfinish(conn);
1248 exit_nicely(1);
1249 }
1250
1251 if (!no_comments && spccomment && spccomment[0] != '\0')
1252 {
1253 appendPQExpBuffer(buf, "COMMENT ON TABLESPACE %s IS ", fspcname);
1254 appendStringLiteralConn(buf, spccomment, conn);
1255 appendPQExpBufferStr(buf, ";\n");
1256 }
1257
1258 if (!no_security_labels && server_version >= 90200)
1259 buildShSecLabels(conn, "pg_tablespace", spcoid,
1260 "TABLESPACE", spcname,
1261 buf);
1262
1263 fprintf(OPF, "%s", buf->data);
1264
1265 free(fspcname);
1266 destroyPQExpBuffer(buf);
1267 }
1268
1269 PQclear(res);
1270 fprintf(OPF, "\n\n");
1271 }
1272
1273
1274 /*
1275 * Dump commands to drop each database.
1276 */
1277 static void
dropDBs(PGconn * conn)1278 dropDBs(PGconn *conn)
1279 {
1280 PGresult *res;
1281 int i;
1282
1283 /*
1284 * Skip databases marked not datallowconn, since we'd be unable to connect
1285 * to them anyway. This must agree with dumpDatabases().
1286 */
1287 res = executeQuery(conn,
1288 "SELECT datname "
1289 "FROM pg_database d "
1290 "WHERE datallowconn "
1291 "ORDER BY datname");
1292
1293 if (PQntuples(res) > 0)
1294 fprintf(OPF, "--\n-- Drop databases (except postgres and template1)\n--\n\n");
1295
1296 for (i = 0; i < PQntuples(res); i++)
1297 {
1298 char *dbname = PQgetvalue(res, i, 0);
1299
1300 /*
1301 * Skip "postgres" and "template1"; dumpDatabases() will deal with
1302 * them specially. Also, be sure to skip "template0", even if for
1303 * some reason it's not marked !datallowconn.
1304 */
1305 if (strcmp(dbname, "template1") != 0 &&
1306 strcmp(dbname, "template0") != 0 &&
1307 strcmp(dbname, "postgres") != 0)
1308 {
1309 fprintf(OPF, "DROP DATABASE %s%s;\n",
1310 if_exists ? "IF EXISTS " : "",
1311 fmtId(dbname));
1312 }
1313 }
1314
1315 PQclear(res);
1316
1317 fprintf(OPF, "\n\n");
1318 }
1319
1320
1321 /*
1322 * Dump user-specific configuration
1323 */
1324 static void
dumpUserConfig(PGconn * conn,const char * username)1325 dumpUserConfig(PGconn *conn, const char *username)
1326 {
1327 PQExpBuffer buf = createPQExpBuffer();
1328 int count = 1;
1329
1330 for (;;)
1331 {
1332 PGresult *res;
1333
1334 if (server_version >= 90000)
1335 printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
1336 "setdatabase = 0 AND setrole = "
1337 "(SELECT oid FROM %s WHERE rolname = ", count, role_catalog);
1338 else if (server_version >= 80100)
1339 printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM %s WHERE rolname = ", count, role_catalog);
1340 else
1341 printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
1342 appendStringLiteralConn(buf, username, conn);
1343 if (server_version >= 90000)
1344 appendPQExpBufferChar(buf, ')');
1345
1346 res = executeQuery(conn, buf->data);
1347 if (PQntuples(res) == 1 &&
1348 !PQgetisnull(res, 0, 0))
1349 {
1350 resetPQExpBuffer(buf);
1351 makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
1352 "ROLE", username, NULL, NULL,
1353 buf);
1354 fprintf(OPF, "%s", buf->data);
1355 PQclear(res);
1356 count++;
1357 }
1358 else
1359 {
1360 PQclear(res);
1361 break;
1362 }
1363 }
1364
1365 destroyPQExpBuffer(buf);
1366 }
1367
1368
1369 /*
1370 * Dump contents of databases.
1371 */
1372 static void
dumpDatabases(PGconn * conn)1373 dumpDatabases(PGconn *conn)
1374 {
1375 PGresult *res;
1376 int i;
1377
1378 /*
1379 * Skip databases marked not datallowconn, since we'd be unable to connect
1380 * to them anyway. This must agree with dropDBs().
1381 *
1382 * We arrange for template1 to be processed first, then we process other
1383 * DBs in alphabetical order. If we just did them all alphabetically, we
1384 * might find ourselves trying to drop the "postgres" database while still
1385 * connected to it. This makes trying to run the restore script while
1386 * connected to "template1" a bad idea, but there's no fixed order that
1387 * doesn't have some failure mode with --clean.
1388 */
1389 res = executeQuery(conn,
1390 "SELECT datname "
1391 "FROM pg_database d "
1392 "WHERE datallowconn "
1393 "ORDER BY (datname <> 'template1'), datname");
1394
1395 for (i = 0; i < PQntuples(res); i++)
1396 {
1397 char *dbname = PQgetvalue(res, i, 0);
1398 const char *create_opts;
1399 int ret;
1400
1401 /* Skip template0, even if it's not marked !datallowconn. */
1402 if (strcmp(dbname, "template0") == 0)
1403 continue;
1404
1405 if (verbose)
1406 fprintf(stderr, _("%s: dumping database \"%s\"...\n"), progname, dbname);
1407
1408 /*
1409 * We assume that "template1" and "postgres" already exist in the
1410 * target installation. dropDBs() won't have removed them, for fear
1411 * of removing the DB the restore script is initially connected to. If
1412 * --clean was specified, tell pg_dump to drop and recreate them;
1413 * otherwise we'll merely restore their contents. Other databases
1414 * should simply be created.
1415 */
1416 if (strcmp(dbname, "template1") == 0 || strcmp(dbname, "postgres") == 0)
1417 {
1418 if (output_clean)
1419 create_opts = "--clean --create";
1420 else
1421 {
1422 create_opts = "";
1423 /* Since pg_dump won't emit a \connect command, we must */
1424 fprintf(OPF, "\\connect %s\n\n", dbname);
1425 }
1426 }
1427 else
1428 create_opts = "--create";
1429
1430 if (filename)
1431 fclose(OPF);
1432
1433 ret = runPgDump(dbname, create_opts);
1434 if (ret != 0)
1435 {
1436 fprintf(stderr, _("%s: pg_dump failed on database \"%s\", exiting\n"), progname, dbname);
1437 exit_nicely(1);
1438 }
1439
1440 if (filename)
1441 {
1442 OPF = fopen(filename, PG_BINARY_A);
1443 if (!OPF)
1444 {
1445 fprintf(stderr, _("%s: could not re-open the output file \"%s\": %s\n"),
1446 progname, filename, strerror(errno));
1447 exit_nicely(1);
1448 }
1449 }
1450
1451 }
1452
1453 PQclear(res);
1454 }
1455
1456
1457
1458 /*
1459 * Run pg_dump on dbname, with specified options.
1460 */
1461 static int
runPgDump(const char * dbname,const char * create_opts)1462 runPgDump(const char *dbname, const char *create_opts)
1463 {
1464 PQExpBuffer connstrbuf = createPQExpBuffer();
1465 PQExpBuffer cmd = createPQExpBuffer();
1466 int ret;
1467
1468 appendPQExpBuffer(cmd, "\"%s\" %s %s", pg_dump_bin,
1469 pgdumpopts->data, create_opts);
1470
1471 /*
1472 * If we have a filename, use the undocumented plain-append pg_dump
1473 * format.
1474 */
1475 if (filename)
1476 appendPQExpBufferStr(cmd, " -Fa ");
1477 else
1478 appendPQExpBufferStr(cmd, " -Fp ");
1479
1480 /*
1481 * Append the database name to the already-constructed stem of connection
1482 * string.
1483 */
1484 appendPQExpBuffer(connstrbuf, "%s dbname=", connstr);
1485 appendConnStrVal(connstrbuf, dbname);
1486
1487 appendShellString(cmd, connstrbuf->data);
1488
1489 if (verbose)
1490 fprintf(stderr, _("%s: running \"%s\"\n"), progname, cmd->data);
1491
1492 fflush(stdout);
1493 fflush(stderr);
1494
1495 ret = system(cmd->data);
1496
1497 destroyPQExpBuffer(cmd);
1498 destroyPQExpBuffer(connstrbuf);
1499
1500 return ret;
1501 }
1502
1503 /*
1504 * buildShSecLabels
1505 *
1506 * Build SECURITY LABEL command(s) for a shared object
1507 *
1508 * The caller has to provide object type and identity in two separate formats:
1509 * catalog_name (e.g., "pg_database") and object OID, as well as
1510 * type name (e.g., "DATABASE") and object name (not pre-quoted).
1511 *
1512 * The command(s) are appended to "buffer".
1513 */
1514 static void
buildShSecLabels(PGconn * conn,const char * catalog_name,Oid objectId,const char * objtype,const char * objname,PQExpBuffer buffer)1515 buildShSecLabels(PGconn *conn, const char *catalog_name, Oid objectId,
1516 const char *objtype, const char *objname,
1517 PQExpBuffer buffer)
1518 {
1519 PQExpBuffer sql = createPQExpBuffer();
1520 PGresult *res;
1521
1522 buildShSecLabelQuery(conn, catalog_name, objectId, sql);
1523 res = executeQuery(conn, sql->data);
1524 emitShSecLabels(conn, res, buffer, objtype, objname);
1525
1526 PQclear(res);
1527 destroyPQExpBuffer(sql);
1528 }
1529
1530 /*
1531 * Make a database connection with the given parameters. An
1532 * interactive password prompt is automatically issued if required.
1533 *
1534 * If fail_on_error is false, we return NULL without printing any message
1535 * on failure, but preserve any prompted password for the next try.
1536 *
1537 * On success, the global variable 'connstr' is set to a connection string
1538 * containing the options used.
1539 */
1540 static PGconn *
connectDatabase(const char * dbname,const char * connection_string,const char * pghost,const char * pgport,const char * pguser,trivalue prompt_password,bool fail_on_error)1541 connectDatabase(const char *dbname, const char *connection_string,
1542 const char *pghost, const char *pgport, const char *pguser,
1543 trivalue prompt_password, bool fail_on_error)
1544 {
1545 PGconn *conn;
1546 bool new_pass;
1547 const char *remoteversion_str;
1548 int my_version;
1549 const char **keywords = NULL;
1550 const char **values = NULL;
1551 PQconninfoOption *conn_opts = NULL;
1552 static bool have_password = false;
1553 static char password[100];
1554
1555 if (prompt_password == TRI_YES && !have_password)
1556 {
1557 simple_prompt("Password: ", password, sizeof(password), false);
1558 have_password = true;
1559 }
1560
1561 /*
1562 * Start the connection. Loop until we have a password if requested by
1563 * backend.
1564 */
1565 do
1566 {
1567 int argcount = 6;
1568 PQconninfoOption *conn_opt;
1569 char *err_msg = NULL;
1570 int i = 0;
1571
1572 if (keywords)
1573 free(keywords);
1574 if (values)
1575 free(values);
1576 if (conn_opts)
1577 PQconninfoFree(conn_opts);
1578
1579 /*
1580 * Merge the connection info inputs given in form of connection string
1581 * and other options. Explicitly discard any dbname value in the
1582 * connection string; otherwise, PQconnectdbParams() would interpret
1583 * that value as being itself a connection string.
1584 */
1585 if (connection_string)
1586 {
1587 conn_opts = PQconninfoParse(connection_string, &err_msg);
1588 if (conn_opts == NULL)
1589 {
1590 fprintf(stderr, "%s: %s", progname, err_msg);
1591 exit_nicely(1);
1592 }
1593
1594 for (conn_opt = conn_opts; conn_opt->keyword != NULL; conn_opt++)
1595 {
1596 if (conn_opt->val != NULL && conn_opt->val[0] != '\0' &&
1597 strcmp(conn_opt->keyword, "dbname") != 0)
1598 argcount++;
1599 }
1600
1601 keywords = pg_malloc0((argcount + 1) * sizeof(*keywords));
1602 values = pg_malloc0((argcount + 1) * sizeof(*values));
1603
1604 for (conn_opt = conn_opts; conn_opt->keyword != NULL; conn_opt++)
1605 {
1606 if (conn_opt->val != NULL && conn_opt->val[0] != '\0' &&
1607 strcmp(conn_opt->keyword, "dbname") != 0)
1608 {
1609 keywords[i] = conn_opt->keyword;
1610 values[i] = conn_opt->val;
1611 i++;
1612 }
1613 }
1614 }
1615 else
1616 {
1617 keywords = pg_malloc0((argcount + 1) * sizeof(*keywords));
1618 values = pg_malloc0((argcount + 1) * sizeof(*values));
1619 }
1620
1621 if (pghost)
1622 {
1623 keywords[i] = "host";
1624 values[i] = pghost;
1625 i++;
1626 }
1627 if (pgport)
1628 {
1629 keywords[i] = "port";
1630 values[i] = pgport;
1631 i++;
1632 }
1633 if (pguser)
1634 {
1635 keywords[i] = "user";
1636 values[i] = pguser;
1637 i++;
1638 }
1639 if (have_password)
1640 {
1641 keywords[i] = "password";
1642 values[i] = password;
1643 i++;
1644 }
1645 if (dbname)
1646 {
1647 keywords[i] = "dbname";
1648 values[i] = dbname;
1649 i++;
1650 }
1651 keywords[i] = "fallback_application_name";
1652 values[i] = progname;
1653 i++;
1654
1655 new_pass = false;
1656 conn = PQconnectdbParams(keywords, values, true);
1657
1658 if (!conn)
1659 {
1660 fprintf(stderr, _("%s: could not connect to database \"%s\"\n"),
1661 progname, dbname);
1662 exit_nicely(1);
1663 }
1664
1665 if (PQstatus(conn) == CONNECTION_BAD &&
1666 PQconnectionNeedsPassword(conn) &&
1667 !have_password &&
1668 prompt_password != TRI_NO)
1669 {
1670 PQfinish(conn);
1671 simple_prompt("Password: ", password, sizeof(password), false);
1672 have_password = true;
1673 new_pass = true;
1674 }
1675 } while (new_pass);
1676
1677 /* check to see that the backend connection was successfully made */
1678 if (PQstatus(conn) == CONNECTION_BAD)
1679 {
1680 if (fail_on_error)
1681 {
1682 fprintf(stderr,
1683 _("%s: could not connect to database \"%s\": %s"),
1684 progname, PQdb(conn) ? PQdb(conn) : "", PQerrorMessage(conn));
1685 exit_nicely(1);
1686 }
1687 else
1688 {
1689 PQfinish(conn);
1690
1691 free(keywords);
1692 free(values);
1693 PQconninfoFree(conn_opts);
1694
1695 return NULL;
1696 }
1697 }
1698
1699 /*
1700 * Ok, connected successfully. Remember the options used, in the form of a
1701 * connection string.
1702 */
1703 connstr = constructConnStr(keywords, values);
1704
1705 free(keywords);
1706 free(values);
1707 PQconninfoFree(conn_opts);
1708
1709 /* Check version */
1710 remoteversion_str = PQparameterStatus(conn, "server_version");
1711 if (!remoteversion_str)
1712 {
1713 fprintf(stderr, _("%s: could not get server version\n"), progname);
1714 exit_nicely(1);
1715 }
1716 server_version = PQserverVersion(conn);
1717 if (server_version == 0)
1718 {
1719 fprintf(stderr, _("%s: could not parse server version \"%s\"\n"),
1720 progname, remoteversion_str);
1721 exit_nicely(1);
1722 }
1723
1724 my_version = PG_VERSION_NUM;
1725
1726 /*
1727 * We allow the server to be back to 8.0, and up to any minor release of
1728 * our own major version. (See also version check in pg_dump.c.)
1729 */
1730 if (my_version != server_version
1731 && (server_version < 80000 ||
1732 (server_version / 100) > (my_version / 100)))
1733 {
1734 fprintf(stderr, _("server version: %s; %s version: %s\n"),
1735 remoteversion_str, progname, PG_VERSION);
1736 fprintf(stderr, _("aborting because of server version mismatch\n"));
1737 exit_nicely(1);
1738 }
1739
1740 PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL));
1741
1742 return conn;
1743 }
1744
1745 /* ----------
1746 * Construct a connection string from the given keyword/value pairs. It is
1747 * used to pass the connection options to the pg_dump subprocess.
1748 *
1749 * The following parameters are excluded:
1750 * dbname - varies in each pg_dump invocation
1751 * password - it's not secure to pass a password on the command line
1752 * fallback_application_name - we'll let pg_dump set it
1753 * ----------
1754 */
1755 static char *
constructConnStr(const char ** keywords,const char ** values)1756 constructConnStr(const char **keywords, const char **values)
1757 {
1758 PQExpBuffer buf = createPQExpBuffer();
1759 char *connstr;
1760 int i;
1761 bool firstkeyword = true;
1762
1763 /* Construct a new connection string in key='value' format. */
1764 for (i = 0; keywords[i] != NULL; i++)
1765 {
1766 if (strcmp(keywords[i], "dbname") == 0 ||
1767 strcmp(keywords[i], "password") == 0 ||
1768 strcmp(keywords[i], "fallback_application_name") == 0)
1769 continue;
1770
1771 if (!firstkeyword)
1772 appendPQExpBufferChar(buf, ' ');
1773 firstkeyword = false;
1774 appendPQExpBuffer(buf, "%s=", keywords[i]);
1775 appendConnStrVal(buf, values[i]);
1776 }
1777
1778 connstr = pg_strdup(buf->data);
1779 destroyPQExpBuffer(buf);
1780 return connstr;
1781 }
1782
1783 /*
1784 * Run a query, return the results, exit program on failure.
1785 */
1786 static PGresult *
executeQuery(PGconn * conn,const char * query)1787 executeQuery(PGconn *conn, const char *query)
1788 {
1789 PGresult *res;
1790
1791 if (verbose)
1792 fprintf(stderr, _("%s: executing %s\n"), progname, query);
1793
1794 res = PQexec(conn, query);
1795 if (!res ||
1796 PQresultStatus(res) != PGRES_TUPLES_OK)
1797 {
1798 fprintf(stderr, _("%s: query failed: %s"),
1799 progname, PQerrorMessage(conn));
1800 fprintf(stderr, _("%s: query was: %s\n"),
1801 progname, query);
1802 PQfinish(conn);
1803 exit_nicely(1);
1804 }
1805
1806 return res;
1807 }
1808
1809 /*
1810 * As above for a SQL command (which returns nothing).
1811 */
1812 static void
executeCommand(PGconn * conn,const char * query)1813 executeCommand(PGconn *conn, const char *query)
1814 {
1815 PGresult *res;
1816
1817 if (verbose)
1818 fprintf(stderr, _("%s: executing %s\n"), progname, query);
1819
1820 res = PQexec(conn, query);
1821 if (!res ||
1822 PQresultStatus(res) != PGRES_COMMAND_OK)
1823 {
1824 fprintf(stderr, _("%s: query failed: %s"),
1825 progname, PQerrorMessage(conn));
1826 fprintf(stderr, _("%s: query was: %s\n"),
1827 progname, query);
1828 PQfinish(conn);
1829 exit_nicely(1);
1830 }
1831
1832 PQclear(res);
1833 }
1834
1835
1836 /*
1837 * dumpTimestamp
1838 */
1839 static void
dumpTimestamp(const char * msg)1840 dumpTimestamp(const char *msg)
1841 {
1842 char buf[64];
1843 time_t now = time(NULL);
1844
1845 if (strftime(buf, sizeof(buf), PGDUMP_STRFTIME_FMT, localtime(&now)) != 0)
1846 fprintf(OPF, "-- %s %s\n\n", msg, buf);
1847 }
1848