Skip to content

Commit b0a6ad7

Browse files
tglsfdctglsfdc
committed
Add a --role option to pg_dump, pg_dumpall, and pg_restore. This allows
performing dumps and restores in accordance with a security policy that forbids logging in directly as superuser, but instead specifies that you should log into an admin account and then SET ROLE to the superuser. In passing, clean up some ugly and mostly-broken code for quoting shell arguments in pg_dumpall. Benedek László, with some help from Tom Lane
1 parent f42a7f1 commit b0a6ad7

File tree

8 files changed

+194
-114
lines changed

8 files changed

+194
-114
lines changed

‎doc/src/sgml/ref/pg_dump.sgml

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dump.sgml,v 1.105 2008/08/26 00:03:15 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dump.sgml,v 1.106 2009/01/05 16:54:36 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -698,6 +698,23 @@ PostgreSQL documentation
698698
</para>
699699
</listitem>
700700
</varlistentry>
701+
702+
<varlistentry>
703+
<term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
704+
<listitem>
705+
<para>
706+
Specifies a role name to be used to create the dump.
707+
This option causes <application>pg_dump</> to issue a
708+
<command>SET ROLE</> <replaceable class="parameter">rolename</>
709+
command after connecting to the database. It is useful when the
710+
authenticated user (specified by <option>-U</>) lacks privileges
711+
needed by <application>pg_dump</>, but can switch to a role with
712+
the required rights. Some installations have a policy against
713+
logging in directly as a superuser, and use of this option allows
714+
dumps to be made without violating the policy.
715+
</para>
716+
</listitem>
717+
</varlistentry>
701718
</variablelist>
702719
</para>
703720
</refsect1>

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

Lines changed: 33 additions & 16 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.73 2008/08/29 17:28:43 alvherre Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.74 2009/01/05 16:54:36 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -129,7 +129,7 @@ PostgreSQL documentation
129129
</para>
130130
</listitem>
131131
</varlistentry>
132-
132+
133133
<varlistentry>
134134
<term><option>-f <replaceable class="parameter">filename</replaceable></option></term>
135135
<term><option>--file=<replaceable class="parameter">filename</replaceable></option></term>
@@ -183,7 +183,7 @@ PostgreSQL documentation
183183
Do not output commands to set
184184
ownership of objects to match the original database.
185185
By default, <application>pg_dumpall</application> issues
186-
<command>ALTER OWNER</> or
186+
<command>ALTER OWNER</> or
187187
<command>SET SESSION AUTHORIZATION</command>
188188
statements to set ownership of created schema elements.
189189
These statements
@@ -342,8 +342,8 @@ PostgreSQL documentation
342342

343343
<variablelist>
344344
<varlistentry>
345-
<term>-h <replaceable>host</replaceable></term>
346-
<term>--host=<replaceable>host</replaceable></term>
345+
<term><option>-h <replaceable>host</replaceable></option></term>
346+
<term><option>--host=<replaceable>host</replaceable></option></term>
347347
<listitem>
348348
<para>
349349
Specifies the host name of the machine on which the database
@@ -354,10 +354,10 @@ PostgreSQL documentation
354354
</para>
355355
</listitem>
356356
</varlistentry>
357-
357+
358358
<varlistentry>
359-
<term>-l <replaceable>dbname</replaceable></term>
360-
<term>--database=<replaceable>dbname</replaceable></term>
359+
<term><option>-l <replaceable>dbname</replaceable></option></term>
360+
<term><option>--database=<replaceable>dbname</replaceable></option></term>
361361
<listitem>
362362
<para>
363363
Specifies the name of the database to connect to to dump global
@@ -369,8 +369,8 @@ PostgreSQL documentation
369369
</varlistentry>
370370

371371
<varlistentry>
372-
<term>-p <replaceable>port</replaceable></term>
373-
<term>--port=<replaceable>port</replaceable></term>
372+
<term><option>-p <replaceable>port</replaceable></option></term>
373+
<term><option>--port=<replaceable>port</replaceable></option></term>
374374
<listitem>
375375
<para>
376376
Specifies the TCP port or local Unix domain socket file
@@ -382,8 +382,8 @@ PostgreSQL documentation
382382
</varlistentry>
383383

384384
<varlistentry>
385-
<term>-U <replaceable>username</replaceable></term>
386-
<term>--username=<replaceable>username</replaceable></term>
385+
<term><option>-U <replaceable>username</replaceable></option></term>
386+
<term><option>--username=<replaceable>username</replaceable></option></term>
387387
<listitem>
388388
<para>
389389
User name to connect as.
@@ -392,12 +392,12 @@ PostgreSQL documentation
392392
</varlistentry>
393393

394394
<varlistentry>
395-
<term>-W</term>
396-
<term>--password</term>
395+
<term><option>-W</option></term>
396+
<term><option>--password</option></term>
397397
<listitem>
398398
<para>
399399
Force <application>pg_dumpall</application> to prompt for a
400-
password before connecting to a database.
400+
password before connecting to a database.
401401
</para>
402402

403403
<para>
@@ -417,6 +417,23 @@ PostgreSQL documentation
417417
</para>
418418
</listitem>
419419
</varlistentry>
420+
421+
<varlistentry>
422+
<term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
423+
<listitem>
424+
<para>
425+
Specifies a role name to be used to create the dump.
426+
This option causes <application>pg_dumpall</> to issue a
427+
<command>SET ROLE</> <replaceable class="parameter">rolename</>
428+
command after connecting to the database. It is useful when the
429+
authenticated user (specified by <option>-U</>) lacks privileges
430+
needed by <application>pg_dumpall</>, but can switch to a role with
431+
the required rights. Some installations have a policy against
432+
logging in directly as a superuser, and use of this option allows
433+
dumps to be made without violating the policy.
434+
</para>
435+
</listitem>
436+
</varlistentry>
420437
</variablelist>
421438
</para>
422439
</refsect1>
@@ -503,6 +520,6 @@ PostgreSQL documentation
503520
Check <xref linkend="app-pgdump"> for details on possible
504521
error conditions.
505522
</para>
506-
</refsect1>
523+
</refsect1>
507524

508525
</refentry>

‎doc/src/sgml/ref/pg_restore.sgml

Lines changed: 28 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/ref/pg_restore.sgml,v 1.76 2008/09/07 19:12:57 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/ref/pg_restore.sgml,v 1.77 2009/01/05 16:54:36 tgl Exp $ -->
22

33
<refentry id="APP-PGRESTORE">
44
<refmeta>
@@ -135,7 +135,7 @@
135135
<listitem>
136136
<para>
137137
Exit if an error is encountered while sending SQL commands to
138-
the database. The default is to continue and to display a count of
138+
the database. The default is to continue and to display a count of
139139
errors at the end of the restoration.
140140
</para>
141141
</listitem>
@@ -261,7 +261,7 @@
261261
Do not output commands to set
262262
ownership of objects to match the original database.
263263
By default, <application>pg_restore</application> issues
264-
<command>ALTER OWNER</> or
264+
<command>ALTER OWNER</> or
265265
<command>SET SESSION AUTHORIZATION</command>
266266
statements to set ownership of created schema elements.
267267
These statements will fail unless the initial connection to the
@@ -429,6 +429,20 @@
429429
</listitem>
430430
</varlistentry>
431431

432+
<varlistentry>
433+
<term><option>-1</option></term>
434+
<term><option>--single-transaction</option></term>
435+
<listitem>
436+
<para>
437+
Execute the restore as a single transaction (that is, wrap the
438+
emitted commands in <command>BEGIN</>/<command>COMMIT</>). This
439+
ensures that either all the commands complete successfully, or no
440+
changes are applied. This option implies
441+
<option>--exit-on-error</>.
442+
</para>
443+
</listitem>
444+
</varlistentry>
445+
432446
</variablelist>
433447
</para>
434448

@@ -480,7 +494,7 @@
480494
<listitem>
481495
<para>
482496
Force <application>pg_restore</application> to prompt for a
483-
password before connecting to a database.
497+
password before connecting to a database.
484498
</para>
485499

486500
<para>
@@ -496,15 +510,18 @@
496510
</varlistentry>
497511

498512
<varlistentry>
499-
<term><option>-1</option></term>
500-
<term><option>--single-transaction</option></term>
513+
<term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
501514
<listitem>
502515
<para>
503-
Execute the restore as a single transaction (that is, wrap the
504-
emitted commands in <command>BEGIN</>/<command>COMMIT</>). This
505-
ensures that either all the commands complete successfully, or no
506-
changes are applied. This option implies
507-
<option>--exit-on-error</>.
516+
Specifies a role name to be used to perform the restore.
517+
This option causes <application>pg_restore</> to issue a
518+
<command>SET ROLE</> <replaceable class="parameter">rolename</>
519+
command after connecting to the database. It is useful when the
520+
authenticated user (specified by <option>-U</>) lacks privileges
521+
needed by <application>pg_restore</>, but can switch to a role with
522+
the required rights. Some installations have a policy against
523+
logging in directly as a superuser, and use of this option allows
524+
restores to be performed without violating the policy.
508525
</para>
509526
</listitem>
510527
</varlistentry>

‎src/bin/pg_dump/pg_backup.h

Lines changed: 2 additions & 1 deletion
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.h,v 1.47 2008/04/13 03:49:21 tgl Exp $
18+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_backup.h,v 1.48 2009/01/05 16:54:36 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -89,6 +89,7 @@ typedef struct _restoreOptions
8989
int use_setsessauth;/* Use SET SESSION AUTHORIZATION commands
9090
* instead of OWNER TO */
9191
char *superuser; /* Username to use as superuser */
92+
char *use_role; /* Issue SET ROLE to this */
9293
int dataOnly;
9394
int dropSchema;
9495
char *filename;

‎src/bin/pg_dump/pg_backup_archiver.c

Lines changed: 6 additions & 3 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.159 2008/12/19 16:25:17 petere Exp $
18+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_backup_archiver.c,v 1.160 2009/01/05 16:54:36 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -462,9 +462,8 @@ NewRestoreOptions(void)
462462

463463
opts = (RestoreOptions *) calloc(1, sizeof(RestoreOptions));
464464

465+
/* set any fields that shouldn't default to zeroes */
465466
opts->format = archUnknown;
466-
opts->suppressDumpWarnings = false;
467-
opts->exit_on_error = false;
468467

469468
return opts;
470469
}
@@ -2146,6 +2145,10 @@ _doSetFixedOutputState(ArchiveHandle *AH)
21462145
ahprintf(AH, "SET standard_conforming_strings = %s;\n",
21472146
AH->public.std_strings ? "on" : "off");
21482147

2148+
/* Select the role to be used during restore */
2149+
if (AH->ropt && AH->ropt->use_role)
2150+
ahprintf(AH, "SET ROLE %s;\n", fmtId(AH->ropt->use_role));
2151+
21492152
/* Make sure function checking is disabled */
21502153
ahprintf(AH, "SET check_function_bodies = false;\n");
21512154

‎src/bin/pg_dump/pg_dump.c

Lines changed: 19 additions & 3 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.511 2009/01/01 17:23:54 momjian Exp $
15+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.512 2009/01/05 16:54:37 tgl Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -227,6 +227,7 @@ main(int argc, char **argv)
227227
bool outputBlobs = false;
228228
int outputNoOwner = 0;
229229
char *outputSuperuser = NULL;
230+
char *use_role = NULL;
230231
int my_version;
231232
int optindex;
232233
RestoreOptions *ropt;
@@ -274,6 +275,7 @@ main(int argc, char **argv)
274275
{"disable-triggers", no_argument, &disable_triggers, 1},
275276
{"lock-wait-timeout", required_argument, NULL, 2},
276277
{"no-tablespaces", no_argument, &outputNoTablespaces, 1},
278+
{"role", required_argument, NULL, 3},
277279
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
278280

279281
{NULL, 0, NULL, 0}
@@ -447,11 +449,14 @@ main(int argc, char **argv)
447449
/* This covers the long options equivalent to -X xxx. */
448450
break;
449451

450-
case 2:
451-
/* lock-wait-timeout */
452+
case 2: /* lock-wait-timeout */
452453
lockWaitTimeout = optarg;
453454
break;
454455

456+
case 3: /* SET ROLE */
457+
use_role = optarg;
458+
break;
459+
455460
default:
456461
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
457462
exit(1);
@@ -570,6 +575,16 @@ main(int argc, char **argv)
570575
std_strings = PQparameterStatus(g_conn, "standard_conforming_strings");
571576
g_fout->std_strings = (std_strings && strcmp(std_strings, "on") == 0);
572577

578+
/* Set the role if requested */
579+
if (use_role && g_fout->remoteVersion >= 80100)
580+
{
581+
PQExpBuffer query = createPQExpBuffer();
582+
583+
appendPQExpBuffer(query, "SET ROLE %s", fmtId(use_role));
584+
do_sql_command(g_conn, query->data);
585+
destroyPQExpBuffer(query);
586+
}
587+
573588
/* Set the datestyle to ISO to ensure the dump's portability */
574589
do_sql_command(g_conn, "SET DATESTYLE = ISO");
575590

@@ -807,6 +822,7 @@ help(const char *progname)
807822
printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n"));
808823
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
809824
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
825+
printf(_(" --role=ROLENAME do SET ROLE before dump\n"));
810826
printf(_(" --use-set-session-authorization\n"
811827
" use SESSION AUTHORIZATION commands instead of\n"
812828
" ALTER OWNER commands to set ownership\n"));

0 commit comments

Comments
 (0)