Wednesday, August 27, 2008

Re: [HACKERS] pg_dump roles support

diff -ur postgresql-8.3.1.orig/doc/src/sgml/backup.sgml postgresql-8.3.1/doc/src/sgml/backup.sgml
--- postgresql-8.3.1.orig/doc/src/sgml/backup.sgml 2008-03-07 02:46:50.000000000 +0100
+++ postgresql-8.3.1/doc/src/sgml/backup.sgml 2008-08-27 15:29:26.000000000 +0200
@@ -68,7 +68,9 @@
<application>pg_dump</> will by default connect with the database
user name that is equal to the current operating system user name. To override
this, either specify the <option>-U</option> option or set the
- environment variable <envar>PGUSER</envar>. Remember that
+ environment variable <envar>PGUSER</envar>. It is possible to change
+ the current user identifier of the dump session by using the
+ <option>--role</option> option. Remember that
<application>pg_dump</> connections are subject to the normal
client authentication mechanisms (which are described in <xref
linkend="client-authentication">).
diff -ur postgresql-8.3.1.orig/doc/src/sgml/ref/pg_dump.sgml postgresql-8.3.1/doc/src/sgml/ref/pg_dump.sgml
--- postgresql-8.3.1.orig/doc/src/sgml/ref/pg_dump.sgml 2007-12-11 20:57:32.000000000 +0100
+++ postgresql-8.3.1/doc/src/sgml/ref/pg_dump.sgml 2008-08-27 15:58:05.000000000 +0200
@@ -522,6 +522,18 @@
</varlistentry>

<varlistentry>
+ <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the user identifier used by the dump session. This will cause
+ <application>pg_dump</application> to issue a
+ <command>SET ROLE TO <replaceable class="parameter">rolename</replaceable></command>
+ command just after a successful database connection.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-v</></term>
<term><option>--verbose</></term>
<listitem>
diff -ur postgresql-8.3.1.orig/doc/src/sgml/ref/pg_dumpall.sgml postgresql-8.3.1/doc/src/sgml/ref/pg_dumpall.sgml
--- postgresql-8.3.1.orig/doc/src/sgml/ref/pg_dumpall.sgml 2007-12-11 20:57:32.000000000 +0100
+++ postgresql-8.3.1/doc/src/sgml/ref/pg_dumpall.sgml 2008-08-27 15:49:18.000000000 +0200
@@ -248,6 +248,18 @@
</varlistentry>

<varlistentry>
+ <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the user identifier used by the dump session. This option will be passed
+ to <application>pg_dump</> and will cause <application>pg_dump</application> to issue a
+ <command>SET ROLE TO <replaceable class="parameter">rolename</replaceable></command>
+ command just after a successful database connection.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-v</></term>
<term><option>--verbose</></term>
<listitem>
diff -ur postgresql-8.3.1.orig/doc/src/sgml/release.sgml postgresql-8.3.1/doc/src/sgml/release.sgml
--- postgresql-8.3.1.orig/doc/src/sgml/release.sgml 2008-03-14 00:47:59.000000000 +0100
+++ postgresql-8.3.1/doc/src/sgml/release.sgml 2008-08-27 16:06:12.000000000 +0200
@@ -2395,6 +2395,13 @@

<listitem>
<para>
+ Add <literal>--role</> option to <application>pg_dump</application> and
+ <application>pg_dumpall</application> (Benedek Laszlo)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
Add <literal>--tablespaces-only</> and <literal>--roles-only</>
options to <application>pg_dumpall</application> (Dave Page)
</para>
diff -ur postgresql-8.3.1.orig/src/bin/pg_dump/pg_dump.c postgresql-8.3.1/src/bin/pg_dump/pg_dump.c
--- postgresql-8.3.1.orig/src/bin/pg_dump/pg_dump.c 2008-01-30 19:35:55.000000000 +0100
+++ postgresql-8.3.1/src/bin/pg_dump/pg_dump.c 2008-08-27 15:10:41.000000000 +0200
@@ -208,6 +208,7 @@
const char *pgport = NULL;
const char *username = NULL;
const char *dumpencoding = NULL;
+ const char *pgrole = NULL;
const char *std_strings;
bool oids = false;
TableInfo *tblinfo;
@@ -258,6 +259,7 @@
{"no-acl", no_argument, NULL, 'x'},
{"compress", required_argument, NULL, 'Z'},
{"encoding", required_argument, NULL, 'E'},
+ {"role", required_argument, NULL, 'r' + 0x80},
{"help", no_argument, NULL, '?'},
{"version", no_argument, NULL, 'V'},

@@ -437,6 +439,10 @@
/* This covers the long options equivalent to -X xxx. */
break;

+ case 'r' + 0x80: /* role */
+ pgrole = optarg;
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -539,6 +545,18 @@
exit(1);
}
}
+
+ /* Set the role if requested */
+ if (pgrole)
+ {
+ PQExpBuffer roleQry = createPQExpBuffer();
+ appendPQExpBuffer(roleQry, "SET ROLE TO %s;\n", fmtId(pgrole));
+ PGresult *res = PQexec(g_conn, roleQry->data);
+ check_sql_result(res, g_conn, roleQry->data, PGRES_COMMAND_OK);
+
+ PQclear(res);
+ destroyPQExpBuffer(roleQry);
+ }

/*
* Get the active encoding and the standard_conforming_strings setting, so
@@ -771,6 +789,7 @@
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
+ printf(_(" --role set role before dump\n"));

printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
diff -ur postgresql-8.3.1.orig/src/bin/pg_dump/pg_dumpall.c postgresql-8.3.1/src/bin/pg_dump/pg_dumpall.c
--- postgresql-8.3.1.orig/src/bin/pg_dump/pg_dumpall.c 2008-01-01 20:45:55.000000000 +0100
+++ postgresql-8.3.1/src/bin/pg_dump/pg_dumpall.c 2008-08-27 15:12:00.000000000 +0200
@@ -112,6 +112,7 @@
{"password", no_argument, NULL, 'W'},
{"no-privileges", no_argument, NULL, 'x'},
{"no-acl", no_argument, NULL, 'x'},
+ {"role", required_argument, NULL, 'r' + 0x80},

/*
* the following options don't have an equivalent short option letter
@@ -241,6 +242,14 @@
roles_only = true;
break;

+ case 'r' + 0x80:
+#ifndef WIN32
+ appendPQExpBuffer(pgdumpopts, " --role '%s'", optarg);
+#else
+ appendPQExpBuffer(pgdumpopts, " --role \"%s\"", optarg);
+#endif
+ break;
+
case 's':
schema_only = true;
appendPQExpBuffer(pgdumpopts, " -s");
@@ -505,7 +514,8 @@
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" OWNER TO commands\n"));
-
+ printf(_(" --role set role before dump\n"));
+
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
printf(_(" -l, --database=DBNAME specify an alternative default database\n"));
Hello,

daveg wrote:
>> I created a patch to set the role to a specified name just after the db
>> connection.
>>
> I was going to do this, but you have beat me to it. You will want to update
> the sgml documentation, and pg_dumpall as well.
>
> -dg
>
>
Ok, here is the next one.
pg_dumpall now just passes the --role option to pg_dump. What do you
think, is it enough
or it should issue the SET ROLE TO ... command in its own session too?

Laszlo Benedek

No comments: