And here's the last necessary bit, which is pg_dump support for all
this.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
*** src/bin/pg_dump/pg_dumpall.c 11 Jun 2009 14:49:07 -0000 1.126
--- src/bin/pg_dump/pg_dumpall.c 30 Sep 2009 14:32:47 -0000
***************
*** 43,50 ****
static void dumpCreateDB(PGconn *conn);
static void dumpDatabaseConfig(PGconn *conn, const char *dbname);
static void dumpUserConfig(PGconn *conn, const char *username);
static void makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
! const char *type, const char *name);
static void dumpDatabases(PGconn *conn);
static void dumpTimestamp(char *msg);
static void doShellQuoting(PQExpBuffer buf, const char *str);
--- 43,52 ----
static void dumpCreateDB(PGconn *conn);
static void dumpDatabaseConfig(PGconn *conn, const char *dbname);
static void dumpUserConfig(PGconn *conn, const char *username);
+ static void dumpDbRoleConfig(PGconn *conn);
static void makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
! const char *type, const char *name, const char *type2,
! const char *name2);
static void dumpDatabases(PGconn *conn);
static void dumpTimestamp(char *msg);
static void doShellQuoting(PQExpBuffer buf, const char *str);
***************
*** 501,506 ****
--- 503,515 ----
/* Dump CREATE DATABASE commands */
if (!globals_only && !roles_only && !tablespaces_only)
dumpCreateDB(conn);
+
+ /* Dump role/database settings */
+ if (!tablespaces_only)
+ {
+ if (server_version >= 80500)
+ dumpDbRoleConfig(conn);
+ }
}
if (!globals_only && !roles_only && !tablespaces_only)
***************
*** 1325,1339 ****
{
PGresult *res;
! printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
appendStringLiteralConn(buf, dbname, conn);
appendPQExpBuffer(buf, ";");
res = executeQuery(conn, buf->data);
! if (!PQgetisnull(res, 0, 0))
{
makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "DATABASE", dbname);
PQclear(res);
count++;
}
--- 1334,1357 ----
{
PGresult *res;
! if (server_version >= 80500)
! printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
! "setrole = 0 AND setdatabase = (SELECT oid FROM pg_database WHERE datname = ", count);
! else
! printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
appendStringLiteralConn(buf, dbname, conn);
+
+ if (server_version >= 80500)
+ appendPQExpBuffer(buf, ")");
+
appendPQExpBuffer(buf, ";");
res = executeQuery(conn, buf->data);
! if (PQntuples(res) == 1 &&
! !PQgetisnull(res, 0, 0))
{
makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "DATABASE", dbname, NULL, NULL);
PQclear(res);
count++;
}
***************
*** 1362,1379 ****
{
PGresult *res;
! if (server_version >= 80100)
printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM pg_authid WHERE rolname = ", count);
else
printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
appendStringLiteralConn(buf, username, conn);
res = executeQuery(conn, buf->data);
if (PQntuples(res) == 1 &&
!PQgetisnull(res, 0, 0))
{
makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "ROLE", username);
PQclear(res);
count++;
}
--- 1380,1403 ----
{
PGresult *res;
! if (server_version >= 80500)
! printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
! "setdatabase = 0 AND setrole = "
! "(SELECT oid FROM pg_authid WHERE rolname = ", count);
! else if (server_version >= 80100)
printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM pg_authid WHERE rolname = ", count);
else
printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
appendStringLiteralConn(buf, username, conn);
+ if (server_version >= 80500)
+ appendPQExpBuffer(buf, ")");
res = executeQuery(conn, buf->data);
if (PQntuples(res) == 1 &&
!PQgetisnull(res, 0, 0))
{
makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "ROLE", username, NULL, NULL);
PQclear(res);
count++;
}
***************
*** 1388,1400 ****
}
/*
* Helper function for dumpXXXConfig().
*/
static void
makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
! const char *type, const char *name)
{
char *pos;
char *mine;
--- 1412,1458 ----
}
+ /*
+ * Dump user-and-database-specific configuration
+ */
+ static void
+ dumpDbRoleConfig(PGconn *conn)
+ {
+ PQExpBuffer buf = createPQExpBuffer();
+ PGresult *res;
+ int i;
+
+ printfPQExpBuffer(buf, "SELECT rolname, datname, unnest(setconfig) "
+ "FROM pg_db_role_setting, pg_authid, pg_database "
+ "WHERE setrole = pg_authid.oid AND setdatabase = pg_database.oid");
+ res = executeQuery(conn, buf->data);
+
+ if (PQntuples(res) > 0)
+ {
+ fprintf(OPF, "--\n-- Per-Database Role Settings \n--\n\n");
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ makeAlterConfigCommand(conn, PQgetvalue(res, i, 2),
+ "ROLE", PQgetvalue(res, i, 0),
+ "DATABASE", PQgetvalue(res, i, 1));
+ }
+
+ fprintf(OPF, "\n\n");
+ }
+
+ PQclear(res);
+ destroyPQExpBuffer(buf);
+ }
+
/*
* Helper function for dumpXXXConfig().
*/
static void
makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
! const char *type, const char *name,
! const char *type2, const char *name2)
{
char *pos;
char *mine;
***************
*** 1407,1412 ****
--- 1465,1472 ----
*pos = 0;
appendPQExpBuffer(buf, "ALTER %s %s ", type, fmtId(name));
+ if (type2 != NULL && name2 != NULL)
+ appendPQExpBuffer(buf, "IN %s %s ", type2, fmtId(name2));
appendPQExpBuffer(buf, "SET %s TO ", fmtId(mine));
/*
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
