Skip to content

Commit 4599521

Browse files
bmomjianbmomjian
committed
Here is another patch that fixes a stack of pg_dump bugs:
* Fix help text ordering * Add back --set-session-authorization to pg_dumpall. Updated the docs for that. Updated help for that. * Dump ALTER USER commands for the cluster owner ("pgsql"). These are dumped AFTER the create user and create database commands in case the permissions to do these have been revoked. * Dump ALTER OWNER for public schema (because it's possible to change it). This was done by adding TOC entries for the public schema, and filtering them out at archiver time. I also save the owner in the TOC entry just for the public schema. * Suppress dumping single quotes around schema_path and DateStyle options when they are set using ALTER USER or ALTER DATABASE. Added a comment to the steps in guc.c to remind people to update that list. * Fix dumping in --clean mode against a pre-7.3 server. It just sets all drop statements to assume the public schema, allowing it to restore without error. * Cleaned up text output. eg. Don't output -- Tablespaces comment if there are none. Same for groups and users. * Make the commands to DELETE FROM pg_shadow and DELETE FROM pg_group only be output when -c mode is enabled. I'm not sure why that hasn't been done before?!?! This should be good for application asap, after which I will start on regression dumping 7.0-7.4 databases. Christopher Kings-Lynne
1 parent 465edca commit 4599521

File tree

5 files changed

+104
-77
lines changed

5 files changed

+104
-77
lines changed

‎doc/src/sgml/ref/pg_dumpall.sgml

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.45 2004/07/12 14:35:43 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.46 2004/07/19 21:39:46 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -280,11 +280,14 @@ PostgreSQL documentation
280280
<term><option>--use-set-session-authorization</></term>
281281
<listitem>
282282
<para>
283-
This option is obsolete but still accepted for backwards
284-
compatibility with <application>pg_dump</application>.
283+
Output SQL standard SET SESSION AUTHORIZATION commands instead
284+
of OWNER TO commands. This makes the dump more standards compatible,
285+
but depending on the history of the objects in the dump, may not
286+
restore properly.
285287
</para>
286288
</listitem>
287289
</varlistentry>
290+
288291
</variablelist>
289292
</para>
290293

‎src/backend/utils/misc/guc.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Written by Peter Eisentraut <peter_e@gmx.net>.
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.220 2004/07/19 02:47:10 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.221 2004/07/19 21:39:47 momjian Exp $
1414
*
1515
*--------------------------------------------------------------------
1616
*/
@@ -335,6 +335,9 @@ const char *const config_type_names[] =
335335
* 6. Add it to src/bin/psql/tab-complete.c, if it's a USERSET option.
336336
*
337337
* 7. Don't forget to document the option.
338+
*
339+
* 8. If it's a new GUC_LIST option you must edit pg_dumpall.c to ensure
340+
* it is not single quoted at dump time.
338341
*/
339342

340343

‎src/bin/pg_dump/pg_backup_archiver.c

Lines changed: 14 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
*
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_backup_archiver.c,v 1.89 2004/07/19 21:02:17 tgl Exp $
18+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_backup_archiver.c,v 1.90 2004/07/19 21:39:47 momjian Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -2356,7 +2356,8 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isDat
23562356
strcmp(te->desc, "TABLE") == 0 ||
23572357
strcmp(te->desc, "TYPE") == 0 ||
23582358
strcmp(te->desc, "VIEW") == 0 ||
2359-
strcmp(te->desc, "SEQUENCE") == 0
2359+
strcmp(te->desc, "SEQUENCE") == 0 ||
2360+
(strcmp(te->desc, "SCHEMA") == 0 && strcmp(te->tag, "public") == 0) /* Only public schema */
23602361
))
23612362
{
23622363
char *temp = _getObjectFromDropStmt(te->dropStmt, te->desc);
@@ -2376,15 +2377,18 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isDat
23762377
/*
23772378
* Really crude hack for suppressing AUTHORIZATION clause of CREATE SCHEMA
23782379
* when --no-owner mode is selected. This is ugly, but I see no other
2379-
* good way ...
2380+
* good way ... Also, avoid dumping the public schema as it will already be
2381+
* created.
23802382
*/
2381-
if (AH->ropt && AH->ropt->noOwner && strcmp(te->desc, "SCHEMA") == 0)
2382-
{
2383-
ahprintf(AH, "CREATE SCHEMA %s;\n\n\n", te->tag);
2384-
}
2385-
else
2386-
{
2387-
ahprintf(AH, "%s\n\n", te->defn);
2383+
if (strcmp(te->tag, "public") != 0) {
2384+
if (AH->ropt && AH->ropt->noOwner && strcmp(te->desc, "SCHEMA") == 0)
2385+
{
2386+
ahprintf(AH, "CREATE SCHEMA %s;\n\n\n", te->tag);
2387+
}
2388+
else
2389+
{
2390+
ahprintf(AH, "%s\n\n", te->defn);
2391+
}
23882392
}
23892393
}
23902394
else if (isData) {

‎src/bin/pg_dump/pg_dump.c

Lines changed: 16 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
* by PostgreSQL
1313
*
1414
* IDENTIFICATION
15-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.379 2004/07/13 03:00:17 momjian Exp $
15+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.380 2004/07/19 21:39:48 momjian Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -1492,7 +1492,7 @@ getNamespaces(int *numNamespaces)
14921492
nsinfo[0].dobj.catId.tableoid = 0;
14931493
nsinfo[0].dobj.catId.oid = 0;
14941494
AssignDumpId(&nsinfo[0].dobj);
1495-
nsinfo[0].dobj.name = strdup("");
1495+
nsinfo[0].dobj.name = strdup("public");
14961496
nsinfo[0].usename = strdup("");
14971497
nsinfo[0].nspacl = strdup("");
14981498
nsinfo[0].nsptablespace = strdup("");
@@ -4381,39 +4381,31 @@ dumpNamespace(Archive *fout, NamespaceInfo *nspinfo)
43814381
qnspname = strdup(fmtId(nspinfo->dobj.name));
43824382

43834383
/*
4384-
* If it's the PUBLIC namespace, suppress the CREATE SCHEMA record
4385-
* for it, since we expect PUBLIC to exist already in the
4386-
* destination database. But do emit ACL in case it's not standard,
4387-
* likewise comment.
4388-
*
43894384
* Note that ownership is shown in the AUTHORIZATION clause,
43904385
* while the archive entry is listed with empty owner (causing
43914386
* it to be emitted with SET SESSION AUTHORIZATION DEFAULT).
43924387
* This seems the best way of dealing with schemas owned by
43934388
* users without CREATE SCHEMA privilege. Further hacking has
43944389
* to be applied for --no-owner mode, though!
43954390
*/
4396-
if (strcmp(nspinfo->dobj.name, "public") != 0)
4397-
{
4398-
appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
4391+
appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
43994392

4400-
appendPQExpBuffer(q, "CREATE SCHEMA %s AUTHORIZATION %s",
4401-
qnspname, fmtId(nspinfo->usename));
4393+
appendPQExpBuffer(q, "CREATE SCHEMA %s AUTHORIZATION %s",
4394+
qnspname, fmtId(nspinfo->usename));
44024395

4403-
/* Add tablespace qualifier, if not default */
4404-
if (strlen(nspinfo->nsptablespace) != 0)
4405-
appendPQExpBuffer(q, " TABLESPACE %s",
4406-
fmtId(nspinfo->nsptablespace));
4396+
/* Add tablespace qualifier, if not default */
4397+
if (strlen(nspinfo->nsptablespace) != 0)
4398+
appendPQExpBuffer(q, " TABLESPACE %s",
4399+
fmtId(nspinfo->nsptablespace));
44074400

4408-
appendPQExpBuffer(q, ";\n");
4401+
appendPQExpBuffer(q, ";\n");
44094402

4410-
ArchiveEntry(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId,
4411-
nspinfo->dobj.name,
4412-
NULL, "",
4413-
false, "SCHEMA", q->data, delq->data, NULL,
4414-
nspinfo->dobj.dependencies, nspinfo->dobj.nDeps,
4415-
NULL, NULL);
4416-
}
4403+
ArchiveEntry(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId,
4404+
nspinfo->dobj.name,
4405+
NULL, strcmp(nspinfo->dobj.name, "public") == 0 ? nspinfo->usename : "",
4406+
false, "SCHEMA", q->data, delq->data, NULL,
4407+
nspinfo->dobj.dependencies, nspinfo->dobj.nDeps,
4408+
NULL, NULL);
44174409

44184410
/* Dump Schema Comments */
44194411
resetPQExpBuffer(q);

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 64 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1994, Regents of the University of California
77
*
88
*
9-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.44 2004/07/12 14:35:45 momjian Exp $
9+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.45 2004/07/19 21:39:48 momjian Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -44,7 +44,7 @@ static const char *progname;
4444

4545
static void help(void);
4646

47-
static void dumpUsers(PGconn *conn);
47+
static void dumpUsers(PGconn *conn, bool initdbonly);
4848
static void dumpGroups(PGconn *conn);
4949
static void dumpTablespaces(PGconn *conn);
5050
static void dumpCreateDB(PGconn *conn);
@@ -257,7 +257,7 @@ main(int argc, char *argv[])
257257
if (disable_triggers)
258258
appendPQExpBuffer(pgdumpopts, " -X disable-triggers");
259259
if (use_setsessauth)
260-
/* no-op, still allowed for compatibility */ ;
260+
appendPQExpBuffer(pgdumpopts, " -X use-set-session-authorization");
261261

262262
if (optind < argc)
263263
{
@@ -279,18 +279,19 @@ main(int argc, char *argv[])
279279

280280
if (!data_only)
281281
{
282-
dumpUsers(conn);
282+
/* Dump all users excluding the initdb user */
283+
dumpUsers(conn, false);
283284
dumpGroups(conn);
284285
if (server_version >= 70500)
285286
dumpTablespaces(conn);
287+
if (!globals_only)
288+
dumpCreateDB(conn);
289+
/* Dump alter command for initdb user */
290+
dumpUsers(conn, true);
286291
}
287292

288293
if (!globals_only)
289-
{
290-
if (!data_only)
291-
dumpCreateDB(conn);
292294
dumpDatabases(conn);
293-
}
294295

295296
PQfinish(conn);
296297

@@ -310,26 +311,29 @@ help(void)
310311
printf(_("Usage:\n"));
311312
printf(_(" %s [OPTION]...\n"), progname);
312313

313-
printf(_("\nOptions:\n"));
314+
printf(_("\nGeneral options:\n"));
315+
printf(_(" -i, --ignore-version proceed even when server version mismatches\n"
316+
" pg_dumpall version\n"));
317+
printf(_(" --help show this help, then exit\n"));
318+
printf(_(" --version output version information, then exit\n"));
319+
printf(_("\nOptions controlling the output content:\n"));
314320
printf(_(" -a, --data-only dump only the data, not the schema\n"));
315321
printf(_(" -c, --clean clean (drop) databases prior to create\n"));
316322
printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n"));
317323
printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
318324
printf(_(" -g, --globals-only dump only global objects, no databases\n"));
319-
printf(_(" -i, --ignore-version proceed even when server version mismatches\n"
320-
" pg_dumpall version\n"));
321-
printf(_(" -s, --schema-only dump only the schema, no data\n"));
322-
printf(_(" -S, --superuser=NAME specify the superuser user name to use in the dump\n"));
323325
printf(_(" -o, --oids include OIDs in dump\n"));
324326
printf(_(" -O, --no-owner do not output commands to set object ownership\n"));
325-
printf(_(" -v, --verbose verbose mode\n"));
327+
printf(_(" -s, --schema-only dump only the schema, no data\n"));
328+
printf(_(" -S, --superuser=NAME specify the superuser user name to use in the dump\n"));
326329
printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
327330
printf(_(" -X disable-dollar-quoting, --disable-dollar-quoting\n"
328331
" disable dollar quoting, use SQL standard quoting\n"));
329332
printf(_(" -X disable-triggers, --disable-triggers\n"
330-
" disable triggers during data-only restore\n"));
331-
printf(_(" --help show this help, then exit\n"));
332-
printf(_(" --version output version information, then exit\n"));
333+
" disable triggers during data-only restore\n"));
334+
printf(_(" -X use-set-session-authorization, --use-set-session-authorization\n"
335+
" use SESSION AUTHORIZATION commands instead of\n"
336+
" OWNER TO commands\n"));
333337

334338
printf(_("\nConnection options:\n"));
335339
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
@@ -344,39 +348,52 @@ help(void)
344348

345349

346350
/*
347-
* Dump users (but not the user created by initdb).
351+
* Dump users
352+
* Is able to dump all non initdb users or just the initdb user.
348353
*/
349354
static void
350-
dumpUsers(PGconn *conn)
355+
dumpUsers(PGconn *conn, bool initdbonly)
351356
{
352357
PGresult *res;
353358
int i;
354359

355-
printf("--\n-- Users\n--\n\n");
356-
printf("DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');\n\n");
357-
358360
if (server_version >= 70100)
359361
res = executeQuery(conn,
360362
"SELECT usename, usesysid, passwd, usecreatedb, "
361-
"usesuper, valuntil "
362-
"FROM pg_shadow "
363-
"WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0')");
363+
"usesuper, valuntil, "
364+
"(usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0')) AS clusterowner "
365+
"FROM pg_shadow");
364366
else
365367
res = executeQuery(conn,
366368
"SELECT usename, usesysid, passwd, usecreatedb, "
367-
"usesuper, valuntil "
368-
"FROM pg_shadow "
369-
"WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template1')");
369+
"usesuper, valuntil, "
370+
"(usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template1')) AS clusterowner "
371+
"FROM pg_shadow");
372+
373+
if (PQntuples(res) > 0 || (!initdbonly && output_clean))
374+
printf("--\n-- Users\n--\n\n");
375+
if (!initdbonly && output_clean)
376+
printf("DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');\n\n");
370377

371378
for (i = 0; i < PQntuples(res); i++)
372379
{
373-
PQExpBuffer buf = createPQExpBuffer();
374380
const char *username;
375-
381+
bool clusterowner;
382+
PQExpBuffer buf = createPQExpBuffer();
376383
username = PQgetvalue(res, i, 0);
377-
appendPQExpBuffer(buf, "CREATE USER %s WITH SYSID %s",
378-
fmtId(username),
379-
PQgetvalue(res, i, 1));
384+
clusterowner = (strcmp(PQgetvalue(res, i, 6), "t") == 0);
385+
386+
/* Check which pass we're on */
387+
if ((initdbonly && !clusterowner) || (!initdbonly && clusterowner)) continue;
388+
389+
/* Dump ALTER USER for the cluster owner and CREATE USER for all other users */
390+
if (!clusterowner)
391+
appendPQExpBuffer(buf, "CREATE USER %s WITH SYSID %s",
392+
fmtId(username),
393+
PQgetvalue(res, i, 1));
394+
else
395+
appendPQExpBuffer(buf, "ALTER USER %s WITH",
396+
fmtId(username));
380397

381398
if (!PQgetisnull(res, i, 2))
382399
{
@@ -422,11 +439,13 @@ dumpGroups(PGconn *conn)
422439
PGresult *res;
423440
int i;
424441

425-
printf("--\n-- Groups\n--\n\n");
426-
printf("DELETE FROM pg_group;\n\n");
427-
428442
res = executeQuery(conn, "SELECT groname, grosysid, grolist FROM pg_group");
429443

444+
if (PQntuples(res) > 0 || output_clean)
445+
printf("--\n-- Groups\n--\n\n");
446+
if (output_clean)
447+
printf("DELETE FROM pg_group;\n\n");
448+
430449
for (i = 0; i < PQntuples(res); i++)
431450
{
432451
PQExpBuffer buf = createPQExpBuffer();
@@ -478,8 +497,6 @@ dumpTablespaces(PGconn *conn)
478497
PGresult *res;
479498
int i;
480499

481-
printf("--\n-- Tablespaces\n--\n\n");
482-
483500
/*
484501
* Get all tablespaces except built-in ones (which we assume are named
485502
* pg_xxx)
@@ -489,6 +506,9 @@ dumpTablespaces(PGconn *conn)
489506
"spclocation, spcacl "
490507
"FROM pg_catalog.pg_tablespace "
491508
"WHERE spcname NOT LIKE 'pg\\_%'");
509+
510+
if (PQntuples(res) > 0)
511+
printf("--\n-- Tablespaces\n--\n\n");
492512

493513
for (i = 0; i < PQntuples(res); i++)
494514
{
@@ -758,7 +778,12 @@ makeAlterConfigCommand(const char *arrayitem, const char *type, const char *name
758778
*pos = 0;
759779
appendPQExpBuffer(buf, "ALTER %s %s ", type, fmtId(name));
760780
appendPQExpBuffer(buf, "SET %s TO ", fmtId(mine));
761-
appendStringLiteral(buf, pos + 1, false);
781+
/* Some GUC variable names are 'LIST' type and hence must not be quoted. */
782+
if (strcasecmp(mine, "DateStyle") == 0
783+
|| strcasecmp(mine, "search_path") == 0)
784+
appendPQExpBuffer(buf, "%s", pos + 1);
785+
else
786+
appendStringLiteral(buf, pos + 1, false);
762787
appendPQExpBuffer(buf, ";\n");
763788

764789
printf("%s", buf->data);

0 commit comments

Comments
 (0)