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