Re: Should SET ROLE inherit config params?

Josh Berkus wrote:
>
> > Josh, this isn't a rejection. Both Tom and I asked for more exploration
> > of the implications of doing as you suggest. Tom has been more helpful
> > than I was in providing some scenarios that would cause problems. It is
> > up to you to solve the problems, which is often possible.
>
> OK, well, barring the context issues, what do people think of the idea?
>
> What I was thinking was that this would be a setting on the SET ROLE
> statement, such as:
>
> SET ROLE special WITH SETTINGS
>
> ... or similar; I'd need to find an existing keyword which works.
>
> I think this bypasses a lot of the issues which Tom raises, but I'd want
> to think about the various permutations some more.

I have added the following TODO:

    Allow role-specific ALTER ROLE SET variable settings to be processed
    independently of login; SET ROLE does not process role-specific variable
    settings

        * http://archives.postgresql.org/message-id/49B82CD7.20802@agliodbs.com

and the attached patch which better documents our current behavior.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/alter_role.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/alter_role.sgml,v
retrieving revision 1.11
diff -c -c -r1.11 alter_role.sgml
*** doc/src/sgml/ref/alter_role.sgml    14 Nov 2008 10:22:45 -0000    1.11
--- doc/src/sgml/ref/alter_role.sgml    28 Mar 2009 03:23:57 -0000
***************
*** 79,97 ****
     password is <literal>MD5</>-encrypted.
    </para>

!   <para>
!    The remaining variants change a role's session default for
!    a specified configuration variable.  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 <command>postgres</command> command line.
!    (For a role without <literal>LOGIN</> privilege, session defaults have
!    no effect.)
!    Ordinary roles can change their own session defaults.
!    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
     set if a superuser issues the command.
    </para>
   </refsect1>
--- 79,96 ----
     password is <literal>MD5</>-encrypted.
    </para>

!   <para>
!    The remaining variants change a role's session default for a
!    specified configuration variable. 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.
!    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
     set if a superuser issues the command.
    </para>
   </refsect1>
***************
*** 163,168 ****
--- 162,173 ----
         </para>

         <para>
+         Role-specific variable setting take effect only at login;
+         <xref linkend="sql-set-role" endterm="sql-set-role-title">
+         does not process role-specific variable settings.
+        </para>
+
+        <para>
          See <xref linkend="sql-set" endterm="sql-set-title"> and <xref
          linkend="runtime-config"> for more information about allowed
          parameter names and values.
Index: doc/src/sgml/ref/set_role.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/set_role.sgml,v
retrieving revision 1.6
diff -c -c -r1.6 set_role.sgml
*** doc/src/sgml/ref/set_role.sgml    14 Nov 2008 10:22:47 -0000    1.6
--- doc/src/sgml/ref/set_role.sgml    28 Mar 2009 03:23:57 -0000
***************
*** 92,97 ****
--- 92,104 ----
    </para>

    <para>
+    <command>SET ROLE</> does not process session variables as specified by
+    the role's <xref linkend="sql-alterrole"
+    endterm="sql-alterrole-title"> settings;  this only happens during
+    login.
+   </para>
+
+   <para>
     <command>SET ROLE</> cannot be used within a
     <literal>SECURITY DEFINER</> function.
    </para>