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

On 11/12/14 1:01 PM, Fujii Masao wrote:
> On Wed, Nov 5, 2014 at 1:22 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
>> On 10/9/14 1:58 PM, Fujii Masao wrote:
>>> Also I think that it's useful to allow ALTER ROLE/DATABASE SET to
>>> set PGC_BACKEND and PGC_SU_BACKEND parameters. So, what
>>> about applying the attached patch? This patch allows that and
>>> changes the context of session_preload_libraries to PGC_SU_BACKEND.
>>
>> After looking through this again, I wonder whether there is any reason
>> why ignore_system_indexes cannot be plain PGC_USERSET.  With this
>> change, we'd allow setting it via ALTER ROLE, but the access to
>> pg_db_role_setting happens before it.
> 
> Even without the patch, we can set ignore_system_indexes
> at the startup of the connection because it's defined with
> PGC_BACKEND context, but the access to system tables
> can happen before that. Am I missing something?

Let's think about what would happen if we allowed PGC_BACKEND settings
to be changed by ALTER ROLE.

Here is the current set of PGC_BACKEND and PGC_SU_BACKEND settings:

- ignore_system_indexes
Reason: "interesting" consequences if changed later

- post_auth_delay
Reason: changing later would have no effect

- local_preload_libraries
Reason: changing later would have no effect

- log_connections
Reason: changing later would have no effect

- log_disconnections
Reason: dubious / consistency with log_connections?

Only ignore_system_indexes is really in the spirit of the original
PGC_BACKEND setting, namely for settings that unprivileged users can set
at the beginning of a session but should not change later.  We used to
have "fsync" in that category, for example, because changing that was
not considered safe at some time.  We used to have a lot more of these,
but not many stood the test of time.

The other settings are really just things that take effect during
session start but don't hurt when changed later.  The problem is that
the order of these relative to ALTER ROLE processing is really an
implementation detail or a best effort type of thing.  For example, it
looks as though we are making an effort to process post_auth_delay
really late, after ALTER ROLE processing (even though we currently don't
allow it to be set that way; strange), but there is no reason why that
has to be so.  One could reasonably think that "post auth" is really
early, before catalog access starts.  On the other hand, log_connections
is processed really early, so ALTER ROLE would have no effect.

This is going to end up inconsistent one way or the other.

My radical proposal therefore would have been to embrace this
inconsistency and get rid of PGC_BACKEND and PGC_SU_BACKEND altogether,
relying on users interpreting the parameter names to indicate that
changing them later may or may not have an effect.  Unfortunately, the
concerns about ignore_system_indexes prevent that.

We could think of another way to address those concerns, e.g., with an
ad hoc way in an assign hook.

The other proposal would be to keep PGC_BACKEND and PGC_SU_BACKEND as
special-case warts, perhaps document them as such, but change everything
to use something else as much as possible, namely

post_auth_delay -> PGC_USERSET
local_preload_libraries -> PGC_USERSET
log_disconnections -> PGC_SUSET