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

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(榊) / 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
retrievedfrom 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
sessionstarting". 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