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.

3 files changed, 78 insertions, 3 deletions

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index d94d8b80c5..cea3942f01 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 1644623812..0c49d812ee 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 169ceb3739..aaef69d703 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);