Re: alter user set local_preload_libraries.

Hello, I made a set of patches to fix this issue.

The attached files are the followings,

0001_Add_PGC_BACKEND_USERSET_v0.patch:

Add new GUC category PGC_BACKEND_USERSET and change
local_preload_libraries to use it.

0002_dblink_follow_change_of_set_config_options_v0.patch:
0003_postgres_fdw_follow_change_of_set_config_options_v0.patch

Change contrib modules to follow the change of
set_config_options.

regards,
--
Kyoaro Horiguchi
NTT Open Source Software Center

On Thu, Jul 3, 2014 at 1:05 PM, Kyotaro HORIGUCHI
<horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> o <CAHGQGwFYiANahR_u_cHnz-zOurj3yQMMnJrr9RwP7vPsVXtKUw(at)mail(dot)gmail(dot)com>
> <20408(dot)1404329822(at)sss(dot)pgh(dot)pa(dot)us>
> User-Agent: Mew version 6.5 on Emacs 22.2 / Mule 5.0
> =?iso-2022-jp?B?KBskQjpnGyhCKQ==?= / Meadow-3.01-dev (TSUBO-SUMIRE)
> Mime-Version: 1.0
> Content-Type: Text/Plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Hello, I'm getting confused.. The distinction between local_ and
> session_ seems to be obscure..
>
> At Wed, 02 Jul 2014 15:37:02 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in <20408(dot)1404329822(at)sss(dot)pgh(dot)pa(dot)us>
>> Well, there aren't that many PGC_BACKEND parameters.
>>
>> Two of them are log_connections and log_disconnections, which we've
>> been arguing over whether they should be controllable by non-superusers
>> in the first place. The only other ones are ignore_system_indexes and
>> post_auth_delay, which are debugging things that I can't see using with
>> ALTER. So this may be the only one where it's really of much interest.
>>
>> But I agree that the problem is triggered by the PGC_BACKEND categorization
>> and not the meaning of this specific GUC.
>
> I put some thoughts on this.
>
> The current behavior is:
>
> - Considering setting them in postgresql.conf, the two are
> different only in the restriction for the locaion of modules
> to be load. But setting them is allowed only for superuser
> equivalent(DBA) so the difference has no meaning.
>
> - Considering setting them on-session, only session_* can be
> altered by superuser, but no valuable result would be
> retrieved from that.
>
> - Considering setting them through pg_db_role_setting using
> ALTER DATABASE/USER statements, only superuser can set only
> session_* and both sessions for superuser and non-superuser
> can perform it. local_* cannot be set anyway.
>
> - Considering inserting directly into pg_db_role_setting, only
> superuser can insert any setting, including
> local_preload_libraries, but it fails on session start.
>
> | =# INSERT INTO pg_db_role_setting VALUES(0, 16384, '{local_preload_libraries=auto_explain}');
> | INSERT 0 1
> ...
> | $ psql postgres -U hoge
> | WARNING: parameter "local_preload_libraries" cannot be set after connection start.
>
> After all, I suppose the desirable requirements utilizing the
> both *_preload_libraries are,
>
> - (local|session)_preload_libraries shouldn't be altered
> on-session. (should have PGC_BACKEND)
>
> - ALTER ... SET local_preload_libraries should be done by any
> user, but specified modules should be within plugins
> directory.
>
> - ALTER ... SET session_preload_libraries should be set only by
> superuser, and modules in any directory can be specified.
>
> - Both (local|session)_preload_libraries should work at session
> start.
>
> - Direct setting of pg_db_role_setting by superuser allows
> arbitrary setting but by the superuser's own responsibility.
>
> The changes needed to achieve the above requirements are,
>
> - Now PGC_BACKEND and PGC_SUSET/USERSET are in orthogonal
> relationship, not in parallel. But it seems to big change for
> the fruits to reflect it in straightforward way. The new
> context PGC_BACKEND_USERSET seems to be enough.
>
> - set_config_option should allow PGC_BACKEND(_USERSET)
> variables to be checked (without changing) on-session.
>
> - PGC_BACKEND(_USERSET) variables should be allowed to be
> changed by set_config_option if teached that "now is on
> session starting". Now changeVal is not a boolean but
> tri-state variable including
> (exec-on-session|exec-on-session-start|check-only)
>
>
> The above description is based on 9.4 and later. local_* would be
> applicable on 9.3 and before, but PGC_BACKEND_USERSET is not
> needed because they don't have session_preload_libraries.
>
> 9.5dev apparently deserves to be applied. I personally think that
> applying to all live versions is desirable. But it seems to be a
> kind of feature change, enabling a function which cannot be used
> before..
>
> For 9.4, since session_preload_library have been introduced, the
> coexistence of current local_preload_library seems to be somewhat
> crooked. We might want to apply this for 9.4.
>
> For the earlier than 9.4, no one seems to have considered
> seriously to use local_preload_library via ALTER statements so
> far. Only document fix would be enough for them.
>
>
> Any suggestions?
>
> regards,
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers