Re: alter user set local_preload_libraries. - Mailing list pgsql-hackers

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.kyotaro@lab.ntt.co.jp> wrote:
> o       <CAHGQGwFYiANahR_u_cHnz-zOurj3yQMMnJrr9RwP7vPsVXtKUw@mail.gmail.com>
>         <20408.1404329822@sss.pgh.pa.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@sss.pgh.pa.us> wrote in <20408.1404329822@sss.pgh.pa.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers