Re: alter user set local_preload_libraries.

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