Re: Per-database and per-user GUC settings

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> 1. postgresql.conf contains a setting for some variable, say
>> sort_mem=1000. DBA starts postmaster with a command-line option to
>> override the variable, say --sort_mem=2000. Works fine, until he
>> SIGHUPs the postmaster for some unrelated reason, at which point
>> sort_mem snaps back to 1000.

> This sort of thing was once considered a feature, until someone came along
> and overloaded SIGHUP for unreleated things. ;-)

Overloaded? SIGHUP still means what it's usually taken to mean, ie,
"re-read your configuration files". Whether the configuration data
lives in one file or several doesn't mean much AFAICS. In particular,
if I override a config setting with a command-line switch, I wouldn't
expect that overriding to stop working when I try to change some
unrelated value in postgresql.conf.

> However, possibly this could be covered if we just didn't propagate the
> SIGHUP signal to the postmaster's children. For pg_hba.conf and friends,
> you don't need it at all once a session is started, and for
> postgresql.conf, the value of the "feature" is at least doubtful.
> Intuitively, the admin would probably expect his new settings to take
> effect in newly started sessions. If he wants to alter existing sessions
> it's probably best to signal the backend processes explicitly.

I disagree with these conclusions entirely. If SIGHUP causes an
already-running postmaster to respond to config-file changes, why not
already-running backends too? If we had a monolithic server
implementation that didn't have any clear distinction between parent
and child processes, surely you'd not make the above argument. But
more to the point, I can see clear usefulness to the DBA in being able
to get existing backends to respond to config changes; I can't see any
clear usefulness in failing to do so.

>> Another thought: DBAs will probably expect that if they change
>> per-database/per-user GUC settings, they can SIGHUP to make existing
>> backends take on those settings.

> I must disagree with this expectation.

> SIGHUP is restricted to re-reading configuations files. The
> per-database/per-user settings behave, in my mind, like SET commands
> executed immediately before the session loop starts.

I think you are allowing implementation simplicity to color your idea of
what the DBA would like to have happen.

> Imagine, a user edits his own
> convenience settings and the admin jumps in and edits some other unrelated
> setting in the same array -- all the sudden the user's new settings get
> activated.

That's a fair point, but I don't envision the superuser inserting values
into other people's per-user GUC settings as a routine matter. That
seems to me to be roughly comparable to Unix root setting a new password
for someone; it's not done lightly. When it is done, arbitrary delays
until the setting takes effect aren't considered acceptable. The same
goes for changes to postgresql.conf.

In any case, I think we're talking at cross purposes, as none of the
above seems to me to be an argument against the point I was making.
Let me try to state it more clearly. It seems to me that the existing
GucContext mechanism folds together three considerations that are
logically distinct, thereby making the implementation both confusing
and restrictive:

1. When is it possible/rational to change a given setting? Two examples:
the implementation doesn't physically support changing shared_buffers
after shared memory initialization; while things would still work
if different backends were running with different log_pid settings,
it's not really sensible for them to do so. Useful concepts here
include "fixed at postmaster start", "fixed at backend start", and
"changeable anytime", and "system-wide" vs "per-backend". With the
addition of per-DB GUC settings, "database-wide" enters the
vocabulary too.

2. From a security/privilege point of view, who should have the right to
change a given setting, and over what span of application? Right now
the only concepts here are "superuser" vs "ordinary user" and
"current session" vs "whole installation". Adding database-wide GUC
settings at least introduces the new concepts of "database owner" and
"within database".

3. Where did a given setting of a variable come from? (wired-in
default, postmaster command line, config file, backend options, SET
command, soon to be augmented by per-DB and per-user table entries)

My argument is that consideration 3 should only be used directly to
determine which source wins when there is a conflict between different
valid sources of a given value (where validity is determined by
considerations 1 and 2). By making that comparison explicit, rather
than trying to use processing order as a substitute for it, we can avoid
the problems the current implementation has with dynamic changes of the
configuration source data.

You seem to be arguing that the current implementation isn't broken
because we can define away the need to support dynamic changes of
configuration data, but I don't buy that; at least not overall, even
if there are good arguments for restricting changes of particular
variables in particular scenarios.

>> The whole subject of reacting to errors in the per-database/per-user GUC
>> settings needs more thought, too. Worst case scenario: superuser messes
>> up his own per-user GUC settings to the point that he can't log in
>> anymore.

> Yes, that is one of my concerns too, but I don't see me rewiring the whole
> exception handling in the backend because of this.

No, of course not; my point was to make sure that there is some kind of
recovery path if things get horribly messed up.

> If the database settings are still messed up, you still have template1.
> (You generally wouldn't put actual settings into template1. -- You might
> as well put them into postgresql.conf then.)

Right. Should we wire that restriction into the code as a safety measure?
postgresql.conf can be changed without a functioning database system,
but a blown setting for template1 might really mess you up.

> If template1 is blocked or the user's settings are messed up, you have a
> more fundamental problem, but it's not dissimilar to deleting all your
> users. We have an escape hatch for that: Start a standalone backend.
> (No options would be processed in that case.)

Okay, if it's agreed that standalone backends ignore these settings then
I think we can survive a screwup.

regards, tom lane