Make it possibly to specify GUC params per user and per database.

Create a new catalog pg_db_role_setting where they are now stored, and better
encapsulate the code that deals with settings into its realm.  The old
datconfig and rolconfig columns are removed.

psql has gained a \drds command to display the settings.

Backwards compatibility warning: while the backwards-compatible system views
still have the config columns, they no longer completely represent the
configuration for a user or database.

Catalog version bumped.

6 files changed, 157 insertions, 18 deletions

diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index a9a27625707..f8aa9e64b91 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -8,7 +8,7 @@
  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.c,v 1.49 2009/10/05 19:24:45 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.c,v 1.50 2009/10/07 22:14:24 alvherre Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -942,7 +942,8 @@ AddAcl(PQExpBuffer aclbuf, const char *keyword, const char *subname)
  *
  * Scan a wildcard-pattern string and generate appropriate WHERE clauses
  * to limit the set of objects returned.  The WHERE clauses are appended
- * to the already-partially-constructed query in buf.
+ * to the already-partially-constructed query in buf.  Returns whether
+ * any clause was added.
  *
  * conn: connection query will be sent to (consulted for escaping rules).
  * buf: output parameter.
@@ -961,7 +962,7 @@ AddAcl(PQExpBuffer aclbuf, const char *keyword, const char *subname)
  * Formatting note: the text already present in buf should end with a newline.
  * The appended text, if any, will end with one too.
  */
-void
+bool
 processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
 					  bool have_where, bool force_escape,
 					  const char *schemavar, const char *namevar,
@@ -973,9 +974,11 @@ processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
 	bool		inquotes;
 	const char *cp;
 	int			i;
+	bool		added_clause = false;
 
 #define WHEREAND() \
-	(appendPQExpBufferStr(buf, have_where ? "  AND " : "WHERE "), have_where = true)
+	(appendPQExpBufferStr(buf, have_where ? "  AND " : "WHERE "), \
+	 have_where = true, added_clause = true)
 
 	if (pattern == NULL)
 	{
@@ -985,7 +988,7 @@ processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
 			WHEREAND();
 			appendPQExpBuffer(buf, "%s\n", visibilityrule);
 		}
-		return;
+		return added_clause;
 	}
 
 	initPQExpBuffer(&schemabuf);
@@ -1142,5 +1145,6 @@ processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
 	termPQExpBuffer(&schemabuf);
 	termPQExpBuffer(&namebuf);
 
+	return added_clause;
 #undef WHEREAND
 }
diff --git a/src/bin/pg_dump/dumputils.h b/src/bin/pg_dump/dumputils.h
index a5bfe1bcfda..0b73701eb8a 100644
--- a/src/bin/pg_dump/dumputils.h
+++ b/src/bin/pg_dump/dumputils.h
@@ -8,7 +8,7 @@
  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.h,v 1.26 2009/10/05 19:24:45 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.h,v 1.27 2009/10/07 22:14:24 alvherre Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -40,7 +40,7 @@ extern bool buildDefaultACLCommands(const char *type, const char *nspname,
 						const char *acls, const char *owner,
 						int remoteVersion,
 						PQExpBuffer sql);
-extern void processSQLNamePattern(PGconn *conn, PQExpBuffer buf,
+extern bool processSQLNamePattern(PGconn *conn, PQExpBuffer buf,
 					  const char *pattern,
 					  bool have_where, bool force_escape,
 					  const char *schemavar, const char *namevar,
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index f0a4d67d2af..5567b07cafc 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  *
- * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.127 2009/10/05 19:24:46 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.128 2009/10/07 22:14:24 alvherre Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -43,8 +43,10 @@ static void dropDBs(PGconn *conn);
 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 *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,6 +503,13 @@ main(int argc, char *argv[])
 		/* Dump CREATE DATABASE commands */
 		if (!globals_only && !roles_only && !tablespaces_only)
 			dumpCreateDB(conn);
+
+		/* Dump role/database settings */
+		if (!tablespaces_only && !roles_only)
+		{
+			if (server_version >= 80500)
+				dumpDbRoleConfig(conn);
+		}
 	}
 
 	if (!globals_only && !roles_only && !tablespaces_only)
@@ -1325,15 +1334,24 @@ dumpDatabaseConfig(PGconn *conn, const char *dbname)
 	{
 		PGresult   *res;
 
-		printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
+		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 (!PQgetisnull(res, 0, 0))
+		if (PQntuples(res) == 1 &&
+			!PQgetisnull(res, 0, 0))
 		{
 			makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
-								   "DATABASE", dbname);
+								   "DATABASE", dbname, NULL, NULL);
 			PQclear(res);
 			count++;
 		}
@@ -1362,18 +1380,24 @@ dumpUserConfig(PGconn *conn, const char *username)
 	{
 		PGresult   *res;
 
-		if (server_version >= 80100)
+		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);
+								   "ROLE", username, NULL, NULL);
 			PQclear(res);
 			count++;
 		}
@@ -1388,13 +1412,47 @@ dumpUserConfig(PGconn *conn, const char *username)
 }
 
 
+/*
+ * 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 *type, const char *name,
+					   const char *type2, const char *name2)
 {
 	char	   *pos;
 	char	   *mine;
@@ -1407,6 +1465,8 @@ makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
 
 	*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));
 
 	/*
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index d94d8b80c52..cea3942f013 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 2000-2009, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/command.c,v 1.208 2009/10/05 19:24:46 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/command.c,v 1.209 2009/10/07 22:14:24 alvherre Exp $
  */
 #include "postgres_fe.h"
 #include "command.h"
@@ -411,6 +411,19 @@ exec_command(const char *cmd,
 			case 's':
 				success = listTables(&cmd[1], pattern, show_verbose, show_system);
 				break;
+			case 'r':
+				if (cmd[2] == 'd' && cmd[3] == 's')
+				{
+					char	   *pattern2 = NULL;
+
+					if (pattern)
+						pattern2 = psql_scan_slash_option(scan_state,
+														  OT_NORMAL, NULL, true);
+					success = listDbRoleSettings(pattern, pattern2);
+				}
+				else
+					success = PSQL_CMD_UNKNOWN;
+				break;
 			case 'u':
 				success = describeRoles(pattern, show_verbose);
 				break;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1644623812c..0c49d812eea 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -8,7 +8,7 @@
  *
  * Copyright (c) 2000-2009, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.227 2009/10/05 19:24:46 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.228 2009/10/07 22:14:24 alvherre Exp $
  */
 #include "postgres_fe.h"
 
@@ -2243,6 +2243,65 @@ add_role_attribute(PQExpBuffer buf, const char *const str)
 	appendPQExpBufferStr(buf, str);
 }
 
+/*
+ * \drds
+ */
+bool
+listDbRoleSettings(const char *pattern, const char *pattern2)
+{
+	PQExpBufferData	buf;
+	PGresult	   *res;
+	printQueryOpt myopt = pset.popt;
+
+	initPQExpBuffer(&buf);
+
+	if (pset.sversion >= 80500)
+	{
+		bool	havewhere;
+
+		printfPQExpBuffer(&buf, "SELECT rolname AS role, datname AS database,\n"
+						  "pg_catalog.array_to_string(setconfig, E'\\n') AS settings\n"
+						  "FROM pg_db_role_setting AS s\n"
+						  "LEFT JOIN pg_database ON pg_database.oid = setdatabase\n"
+						  "LEFT JOIN pg_roles ON pg_roles.oid = setrole\n");
+		havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false,
+										  NULL, "pg_roles.rolname", NULL, NULL);
+		processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false,
+							  NULL, "pg_database.datname", NULL, NULL);
+		appendPQExpBufferStr(&buf, "ORDER BY role, database");
+	}
+	else
+	{
+		fprintf(pset.queryFout,
+				_("No per-database role settings support in this server version.\n"));
+		return false;
+	}
+
+	res = PSQLexec(buf.data, false);
+	if (!res)
+		return false;
+
+	if (PQntuples(res) == 0 && !pset.quiet)
+	{
+		if (pattern)
+			fprintf(pset.queryFout, _("No matching settings found.\n"));
+		else
+			fprintf(pset.queryFout, _("No settings found.\n"));
+	}
+	else
+	{
+		myopt.nullPrint = NULL;
+		myopt.title = _("List of settings");
+		myopt.translate_header = true;
+
+		printQuery(res, &myopt, pset.queryFout, pset.logfile);
+	}
+
+	PQclear(res);
+	resetPQExpBuffer(&buf);
+	return true;
+}
+
 
 /*
  * listTables()
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 169ceb3739a..aaef69d703d 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 2000-2009, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/describe.h,v 1.41 2009/10/05 19:24:46 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/describe.h,v 1.42 2009/10/07 22:14:24 alvherre Exp $
  */
 #ifndef DESCRIBE_H
 #define DESCRIBE_H
@@ -27,6 +27,9 @@ extern bool describeOperators(const char *pattern, bool showSystem);
 /* \du, \dg */
 extern bool describeRoles(const char *pattern, bool verbose);
 
+/* \drds */
+extern bool listDbRoleSettings(const char *pattern1, const char *pattern2);
+
 /* \z (or \dp) */
 extern bool permissionsList(const char *pattern);