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.

2 files changed, 97 insertions, 25 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 15dab71cc0..487dd7e169 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.208 2009/10/05 19:24:32 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.209 2009/10/07 22:14:14 alvherre Exp $ -->
 <!--
  Documentation of the system catalogs, directed toward PostgreSQL developers
  -->
@@ -204,6 +204,11 @@
      </row>
 
      <row>
+      <entry><link linkend="catalog-pg-db-role-setting"><structname>pg_db_role_setting</structname></link></entry>
+      <entry>per-role and per-database settings</entry>
+     </row>
+
+     <row>
       <entry><link linkend="catalog-pg-shdepend"><structname>pg_shdepend</structname></link></entry>
       <entry>dependencies on shared objects</entry>
      </row>
@@ -2137,13 +2142,6 @@
      </row>
 
      <row>
-      <entry><structfield>datconfig</structfield></entry>
-      <entry><type>text[]</type></entry>
-      <entry></entry>
-      <entry>Session defaults for run-time configuration variables</entry>
-     </row>
-
-     <row>
       <entry><structfield>datacl</structfield></entry>
       <entry><type>aclitem[]</type></entry>
       <entry></entry>
@@ -4106,6 +4104,65 @@
 
  </sect1>
 
+ <sect1 id="catalog-pg-db-role-setting">
+  <title><structname>pg_db_role_setting</structname></title>
+
+  <indexterm zone="catalog-pg-db-role-setting">
+   <primary>pg_db_role_setting</primary>
+  </indexterm>
+
+  <para>
+   The catalog <structname>pg_db_role_setting</structname> records the default
+   values that have been set for run-time configuration variables,
+   for each role and database combination.
+  </para>
+
+  <para>
+   Unlike most system catalogs, <structname>pg_db_role_setting</structname>
+   is shared across all databases of a cluster: there is only one
+   copy of <structname>pg_db_role_setting</structname> per cluster, not
+   one per database.
+  </para>
+
+  <table>
+   <title><structname>pg_db_role_setting</> Columns</title>
+
+   <tgroup cols="4">
+    <thead>
+     <row>
+      <entry>Name</entry>
+      <entry>Type</entry>
+      <entry>References</entry>
+      <entry>Description</entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry><structfield>setdatabase</structfield></entry>
+      <entry><type>oid</type></entry>
+      <entry><literal><link linkend="catalog-pg-database"><structname>pg_database</structname></link>.oid</literal></entry>
+      <entry>The OID of the database the setting is applicable to, or zero if not database-specific</entry>
+     </row>
+
+     <row>
+      <entry><structfield>setrole</structfield></entry>
+      <entry><type>oid</type></entry>
+      <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
+      <entry>The OID of the role the setting is applicable to, or zero if not role-specific</entry>
+     </row>
+
+     <row>
+      <entry><structfield>setconfig</structfield></entry>
+      <entry><type>text[]</type></entry>
+      <entry></entry>
+      <entry>Defaults for run-time configuration variables</entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+ </sect1>
+
 
  <sect1 id="catalog-pg-shdepend">
   <title><structname>pg_shdepend</structname></title>
@@ -6558,13 +6615,6 @@
      </row>
 
      <row>
-      <entry><structfield>rolconfig</structfield></entry>
-      <entry><type>text[]</type></entry>
-      <entry></entry>
-      <entry>Session defaults for run-time configuration variables</entry>
-     </row>
-
-     <row>
       <entry><structfield>oid</structfield></entry>
       <entry><type>oid</type></entry>
       <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index 9be5812463..2d09de10c4 100644
--- a/doc/src/sgml/ref/alter_role.sgml
+++ b/doc/src/sgml/ref/alter_role.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/alter_role.sgml,v 1.14 2009/09/19 10:23:26 petere Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/alter_role.sgml,v 1.15 2009/10/07 22:14:16 alvherre Exp $
 PostgreSQL documentation
 -->
 
@@ -37,10 +37,10 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replace
 
 ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
 
-ALTER ROLE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
-ALTER ROLE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
-ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET <replaceable>configuration_parameter</replaceable>
-ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET ALL
+ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
+ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
+ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET <replaceable>configuration_parameter</replaceable>
+ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET ALL
 </synopsis>
  </refsynopsisdiv>
 
@@ -80,14 +80,16 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET ALL
   </para>
 
   <para> 
-   The remaining variants change a role's session default for a
-   specified configuration variable. Whenever the role subsequently
+   The remaining variants change a role's session default for a configuration variable 
+   for all databases or, when the <literal>IN DATABASE</literal> clause is specified,
+   for the named database. Whenever the role subsequently
    starts a new session, the specified value becomes the session
    default, overriding whatever setting is present in
    <filename>postgresql.conf</> or has been received from the postgres
    command line. This only happens at login time, so configuration
    settings associated with a role to which you've <xref
-   linkend="sql-set-role" endterm="sql-set-role-title"> will be ignored.
+   linkend="sql-set-role" endterm="sql-set-role-title"> will be ignored. Settings set to
+   a role directly are overridden by any database specific settings attached to a role.
    Superusers can change anyone's session defaults. Roles having
    <literal>CREATEROLE</> privilege can change defaults for non-superuser
    roles. Certain variables cannot be set this way, or can only be
@@ -146,6 +148,15 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET ALL
      </varlistentry>
 
      <varlistentry>
+       <term><replaceable>database_name</replaceable></term>
+       <listitem>
+         <para>
+           The name of the database the configuration variable should be set in.
+         </para>
+       </listitem>
+     </varlistentry>
+
+     <varlistentry>
       <term><replaceable>configuration_parameter</replaceable></term>
       <term><replaceable>value</replaceable></term>
       <listitem>
@@ -159,6 +170,8 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET ALL
         <literal>RESET ALL</literal> to clear all role-specific settings.
         <literal>SET FROM CURRENT</> saves the session's current value of
         the parameter as the role-specific value.
+        If used in conjunction with <literal>IN DATABASE</literal>, the configuration
+        parameter is set or removed for the given role and database only.
        </para>
 
        <para>
@@ -207,8 +220,8 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET ALL
    It is also possible to tie a
    session default to a specific database rather than to a role; see
    <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">.
-   Role-specific settings override database-specific
-   ones if there is a conflict.
+   If there is a conflict, database-role-specific settings override role-specific
+   ones, which in turn override database-specific ones.
   </para>
  </refsect1>
 
@@ -263,6 +276,15 @@ ALTER ROLE miriam CREATEROLE CREATEDB;
 ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
 </programlisting>
   </para>
+
+  <para>
+    Give a role a non-default, database-specific setting of the 
+  <xref linkend="guc-client-min-messages"> parameter:
+
+<programlisting>
+ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>