Re: pg_dump roles support - Mailing list pgsql-hackers

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

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"));