Re: pg_dump roles support - Mailing list pgsql-hackers

Hello,

Stephen Frost wrote:
 > As I discuss above, it'd be really nice have a --role or similar option
 > to ask pg_dump to set role to a particular user before dumping the
 > database.

I created a patch to set the role to a specified name just after the db connection.
Please review it for possible upstream inclusion.

Regards,
Laszlo Benedek


--- 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-26 12:26:56.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'},
         {"help", no_argument, NULL, '?'},
         {"version", no_argument, NULL, 'V'},

@@ -302,7 +304,7 @@
         }
     }

-    while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:RsS:t:T:U:vWxX:Z:",
+    while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:r:RsS:t:T:U:vWxX:Z:",
                             long_options, &optindex)) != -1)
     {
         switch (c)
@@ -374,6 +376,10 @@
                 pgport = optarg;
                 break;

+            case 'r':            /* role */
+                pgrole = optarg;
+                break;
+
             case 'R':
                 /* no-op, still accepted for backwards compatibility */
                 break;
@@ -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,8 @@
     printf(_("  --use-set-session-authorization\n"
              "                              use SESSION AUTHORIZATION commands instead of\n"
     "                              ALTER OWNER commands to set ownership\n"));
+    printf(_("  -r, --role                  set role before dump\n"));
+

     printf(_("\nConnection options:\n"));
     printf(_("  -h, --host=HOSTNAME      database server host or socket directory\n"));