Re: pg_dump roles support

[Benedek László]

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers