Thread: Per-database and per-user GUC settings

As hinted somewhere within the schema-objects thread, I'm implementing GUC
settings that are stored in the pg_shadow and pg_database system catalogs
and are activated in sessions for that user or database.

The basic functionality is done, although two issues sort of need a show
of hands.  First, the order in which these settings are processed: I
figured user should be last.  I've also got these settings for each group,
but this would mean that if a user is a member of more than one group he
gets a rather random processing order.  Any comments on that?  If the
group thing stays, I think the most reasonable processing order is
database, groups, user, since it would be weird to have database between
groups and user.

Second, we need a few commands to set these things.  My first thought was

SET {USER name | DATABASE name} [SESSION] DEFAULT varname TO value;

but it would also make sense to have

ALTER {USER name | DATABASE name} SET DEFAULT varname TO value;

Or there could be other permutations that are more or less wordy.

Suggestions?  Perhaps a reference to another RDBMS can give a hint.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> The basic functionality is done, although two issues sort of need a show
> of hands.  First, the order in which these settings are processed: I
> figured user should be last.

Meaning the user setting wins if there's a conflict?  Fine.

> I've also got these settings for each group,
> but this would mean that if a user is a member of more than one group he
> gets a rather random processing order.

That bothers me; seems like it'll bite someone sooner or later.  And I
don't see a compelling reason to have per-group settings if we have the
other two.

One issue you didn't mention is what security level these options are
assumed to have by GUC.  That plays into what permissions are needed to
issue the SET/ALTER commands.
        regards, tom lane

Tom Lane writes:

> One issue you didn't mention is what security level these options are
> assumed to have by GUC.  That plays into what permissions are needed to
> issue the SET/ALTER commands.

Right.  My design was, the SET/ALTER commands are allowed to be executed
by the user for his own pg_shadow record, the database owner for his
pg_database record, and superusers for everything.  (Hmm, good we're not
doing the group thing.  Would have gotten tricky here.)

Normal users can only add USERSET settings.  Other settings provoke a
NOTICE at runtime (if they happen to sneak in somehow) and will otherwise
be ignored.

Superusers can also add SUSET records to their per-user settings.  I'm
currently unsure about whether to allow superusers to add SUSET settings
to the per-database settings, because it would mean that the database
session would behave differently depending on what user invokes it.  And
since it's not widely known what settings have what permission, I'm afraid
it could be confusing.  On the other hand, superusers should know what
they're doing.

-- 
Peter Eisentraut   peter_e@gmx.net

> Superusers can also add SUSET records to their per-user settings.  I'm
> currently unsure about whether to allow superusers to add SUSET settings
> to the per-database settings, because it would mean that the database
> session would behave differently depending on what user invokes it.

Imho if the dba adds SUSET's to the per-database settings it should be
set regardless of the user's privs. (Set with elevated rights)

This setting would then be forced on the user, because he himself 
does not have the privs to change it himself.

Andreas

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
>> Superusers can also add SUSET records to their per-user settings.  I'm
>> currently unsure about whether to allow superusers to add SUSET settings
>> to the per-database settings, because it would mean that the database
>> session would behave differently depending on what user invokes it.

> Imho if the dba adds SUSET's to the per-database settings it should be
> set regardless of the user's privs. (Set with elevated rights)

Yes, it would seem to make more sense to verify the privilege level of
the GUC variables during the SET/ALTER command, and then execute them
more-or-less unconditionally during actual startup. During the SET/ALTER
command you know who is trying to insert the value, and you can let
superusers and db owners do more than average users.  But once the
variable has been successfully inserted, it should apply to everyone.

Example application: suppose we had a SUSET-level variable that controls
priority level of a backend (which we don't, but it makes a good example
case here).  The superuser should be able to set this variable in the
per-user settings of a particular user; the user should then be unable
to change it himself.  Note this is a different case from Andreas'
example of a per-database setting, but I agree with his example too.
        regards, tom lane

Tom Lane writes:

> Yes, it would seem to make more sense to verify the privilege level of
> the GUC variables during the SET/ALTER command, and then execute them
> more-or-less unconditionally during actual startup.

OK, that makes sense to me, too.

-- 
Peter Eisentraut   peter_e@gmx.net

I've thought of some issues that I think will need to be addressed
before per-database/per-user GUC settings can become useful.

One thing that's bothered me for awhile is that GUC doesn't retain
any memory of how a variable acquired its present value.  It tries to
resolve conflicts between different sources of values just by processing
the sources in "the right order".  However this cannot work in general.
Some examples:

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.

2. User starts a session and says SET sort_mem=2000.  Again, he
successfully overrides the postgresql.conf value ... but only as
long as he doesn't get SIGHUP'd.

These problems will get very substantially worse once we add
per-database and per-user GUC settings to the set of possible
value sources.  I believe the correct fix is for GUC to define
a prioritized list of value sources (similar to the existing
PGC_ settings, but probably not quite the same) and remember which
source gave the current setting for each variable.  Comparing that
to the source of a would-be new value tells you whether to accept
or ignore the new value.  This would make GUC processing
order-insensitive which would be a considerable improvement (eg,
I think you could get rid of the ugly double-scan-of-options hack
in postmaster.c).

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.  Can we support this?  If the
HUP is received outside any transaction then I guess we could start
a temporary transaction to read the tables involved.  If we try to
process HUP at a command boundary inside a transaction then we risk
aborting the whole user's transaction if there's an error.  Arguably
HUP should not be accepted while a transaction is in progress anyway,
so the simplest answer might be to not process HUP until we are at
the idle loop and there's no open transaction block.

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.  Can we provide an escape hatch, or is he looking at an initdb
situation (without even the chance to run pg_dump first :-()?  I think
the GUC code presently tries to avoid any elog while processing
postgresql.conf, so that it won't be the cause of backend startup
failures, but I'm not convinced that that approach scales.  Certainly
if we are reading tables we cannot absolutely guarantee no elog.
        regards, tom lane

Tom Lane writes:

> One thing that's bothered me for awhile is that GUC doesn't retain
> any memory of how a variable acquired its present value.  It tries to
> resolve conflicts between different sources of values just by processing
> the sources in "the right order".  However this cannot work in general.
> Some examples:
>
> 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. ;-)

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.

> 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.  So once that session
has started, they are no longer relevant.  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.

Admins probably don't want to interfere with users' running sessions,
especially not asynchronously (from the user's point of view).  If they
don't like what the user is doing, kill the session.  Otherwise they may
do more harm than good.  Now that I write this, not propagating the SIGHUP
is something I would really favour.

> 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.

Consider, what is "messed up"?  We don't have options that prevent login.
Invalid option strings are effectively ignored.  If you cannot read
pg_database or pg_shadow you have more fundamental problems and you most
likely won't get to the options processing at all.

> Can we provide an escape hatch, or is he looking at an initdb
> situation (without even the chance to run pg_dump first :-()?

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.)

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.)

-- 
Peter Eisentraut   peter_e@gmx.net

> > 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. ;-)

I agree we should not propagate config changes to children on SIGHUP. 
The only major question I had was that command-line flags get wiped out
by postgresql.conf settings on SIGHUP.  I know someone pointed this
out but has a solution been proposed?  That would surprise any admin.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian wrote:
> > > 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. ;-)
> 
> I agree we should not propagate config changes to children on SIGHUP. 
> The only major question I had was that command-line flags get wiped out
> by postgresql.conf settings on SIGHUP.  I know someone pointed this
> out but has a solution been proposed?  That would surprise any admin.

Added to TODO:

* Prevent SIGHUP and 'pg_ctl reload' from changing command line  specified parameters to postgresql.conf defaults

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > I agree we should not propagate config changes to children on SIGHUP.
> > The only major question I had was that command-line flags get wiped out
> > by postgresql.conf settings on SIGHUP.  I know someone pointed this
> > out but has a solution been proposed?  That would surprise any admin.
> 
> I think it's desirable and fully intentional.  How else would you override
> command-line args without restarting the server?  I can see where there
> might be a concern, but in my mind, if you're using command-line arguments
> in permanent setups, you're not trying hard enough.

But right now, our command-line arguments override postgresql.conf only
on startup; if they reload, postgresql.conf overrides command-line args.
Seems very counter-intuitive to me.

I can't think of an acceptable solution so the best one may be to just
remove the command-line args completely.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian writes:

> I agree we should not propagate config changes to children on SIGHUP.
> The only major question I had was that command-line flags get wiped out
> by postgresql.conf settings on SIGHUP.  I know someone pointed this
> out but has a solution been proposed?  That would surprise any admin.

I think it's desirable and fully intentional.  How else would you override
command-line args without restarting the server?  I can see where there
might be a concern, but in my mind, if you're using command-line arguments
in permanent setups, you're not trying hard enough.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.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
supportchanging shared_buffers  after shared memory initialization; while things would still work  if different
backendswere 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,
backendoptions, 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

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I can't think of an acceptable solution so the best one may be to just
> > remove the command-line args completely.
> 
> I've been proposing a workable implementation in this very thread.

Which is to track where the setting came from, right?  I was thinking it
wasn't workable because people were complaining about it.  :-)

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Peter Eisentraut <peter_e@gmx.net> writes:
> I think it's desirable and fully intentional.  How else would you override
> command-line args without restarting the server?

I say you wouldn't.  If it's on the command line then it overrides
postgresql.conf for that run of the postmaster; full stop, no "if"s.
If you wanted it to be changeable within-run by postgresql.conf then you
should have edited postgresql.conf to start with.

As the implementation currently works, applying a setting via the
command line is entirely unsafe unless you then back it up with fixing
the value in postgresql.conf; otherwise an unrelated change to
postgresql.conf, perhaps much later, blows the command-line setting
out of the water.  You might as well eliminate command-line settings
entirely, if they have to be accompanied by a matching change in
postgresql.conf in order to work reliably.

IMHO the only reason we haven't gotten squawks about this is that
(a) the majority of uses of command-line arguments are for options
that can't be changed after postmaster start anyway (-i, -p, -B, -N);
(b) since the factory-default postgresql.conf has all settings commented
out, it doesn't actually cause reversions to happen at SIGHUP.

If any significant number of people were actually being exposed to the
current behavior, we'd be getting complaints; lots of 'em.  At the
moment it's only a corner case because of considerations (a) and (b),
so few people have had it happen to them.

> but in my mind, if you're using command-line arguments
> in permanent setups, you're not trying hard enough.

You're essentially saying that command-line arguments are only useful
for prototyping changes that you intend to make in postgresql.conf
immediately thereafter.  Again, why have we got a command-line option
facility at all?  Might as well make the change in postgresql.conf
to begin with, if that's the viewpoint.
        regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> I've been proposing a workable implementation in this very thread.

> Which is to track where the setting came from, right?  I was thinking it
> wasn't workable because people were complaining about it.  :-)

Peter's complaining because he thinks the current behavior is OK.
AFAICT he isn't saying that my idea wouldn't make the behavior be
what you and I want, but that he doesn't like that behavior.
        regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I can't think of an acceptable solution so the best one may be to just
> remove the command-line args completely.

I've been proposing a workable implementation in this very thread.
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> I've been proposing a workable implementation in this very thread.
> 
> > Which is to track where the setting came from, right?  I was thinking it
> > wasn't workable because people were complaining about it.  :-)
> 
> Peter's complaining because he thinks the current behavior is OK.
> AFAICT he isn't saying that my idea wouldn't make the behavior be
> what you and I want, but that he doesn't like that behavior.

Getting back to propogating SIGHUP to the children, if I have issued a
SET in my session, does a postmaster SIGHUP wipe that out, and even if
it doesn't, what if I do a SHOW early in my session, see the setting is
OK, then find later that is is changed, for example, the ONLY
inheritance setting.  I guess what I am saying is that I see session
stability as a feature, rather than propogating changes to running
children, which I think could cause more harm than good.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Getting back to propogating SIGHUP to the children, if I have issued a
> SET in my session, does a postmaster SIGHUP wipe that out,

At present, yes it does, if postgresql.conf contains a conflicting
setting.  I think everyone agrees that that's wrong.  Peter proposes
to fix it by disabling backend response to SIGHUP entirely.  I propose
to fix it by changing the code to explicitly understand that SET
overrides postgresql.conf values, regardless of when postgresql.conf
is scanned.

> and even if
> it doesn't, what if I do a SHOW early in my session, see the setting is
> OK, then find later that is is changed, for example, the ONLY
> inheritance setting.

That's a powerful example but I think it's a red herring.  Changing the
system-wide default for a semantically critical value is not something
that one would do lightly or without preparation.  If your applications
assume a particular inheritance setting, they're not going to be any
less broken if they connect immediately after the postgresql.conf change
occurs than if they connect just beforehand.  An app that depends on a
specific setting should SET that value, not just SHOW it ... and if it
has SET the value, then that is what it will get for the duration of
its run, under either my proposal or Peter's.

Similarly, under my proposal per-user or per-database GUC settings would
override postgresql.conf settings even after SIGHUP, because the code
would explicitly treat them as doing so.  Under Peter's proposal the
only way to maintain that priority relationship is to disable backends
from responding to SIGHUP altogether.

I believe that disabling SIGHUP response in backends is throwing the
baby out with the bathwater.  There are plenty of examples where you
absolutely do want backends to respond to configuration changes: think
about any of the debug-logging or statistics-gathering options.  If you
are trying to crank up the debug/stats level in order to find out why
your system is misbehaving *now*, it isn't going to help you if the
existing backends steadfastly ignore your attempt to change the setting.

A possible compromise is to distinguish between options that change
application-visible semantics (like inheritance, datestyle, etc)
and those that do not (logging options, sort_mem, the planner cost
settings, etc).  It would be reasonable to treat the former as
"frozen after backend start unless changed via SET".  It is not
reasonable to put the same straitjacket on all system settings,
however.  I believe that my proposal could easily be extended to
support such behavior.  Peter's approach is all-or-nothing.
        regards, tom lane

Tom Lane writes:

> 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:

(Nice explanation -- I'm beginning to buy it ;-) )

> 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.

So basically we map PGC_POSTMASTER => "fixed at postmaster start",
PGC_BACKEND => "fixed at backend start", PGC_{SIGHUP,SUSET,USERSET} =>
"changeable anytime".

Obviously, PGC_POSTMASTER is always system-wide and PGC_BACKEND,
PGC_SUSET, and PGC_USERSET are always per-backend.  For PGC_SIGHUP, we
currently have this comment in the code:

/** Hmm, the idea of the SIGHUP context is "ought to be global,* but can be changed after postmaster start". But
there's*nothing that prevents a crafty administrator from sending* SIGHUP signals to individual backends only.*/
 

In fact, the options that are PGC_SIGHUP seem to be relying on this
global theme heavily, so let's count PGC_SIGHUP into system-wide.

So basically, you have four useful combinations of these settings, which
correspond to PGC_POSTMASTER, PGC_BACKEND, PGC_SIGHUP, and
PGC_{SUSET,USERSET}.

As for database-wide, I'm not sure how to interpret that.  Does that mean,
this parameter must be the same for all concurrent sessions on the same
database?  Is that something we ought to have?

> 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".

Superuser vs ordinary user doesn't have a lot of options:  ordinary users
only get a shot at a subset of the "change anytime" + "per-backend
settings"  settings, which splits up PGC_SUSET and PGC_USERSET.

Current session vs whole installation comes with the territory.  What you
can set at postmaster start necessarily affects the whole installation.
Same with SIGHUP (probably, see concerns above).  The rest is restricted
to the running session.  (Unless you want to propagate changes from one
session to another -- that seems a little too far out for me.)

I don't see "database owner" as an independent concept:  if a database
owner is an otherwise ordinary user he only gets to change the
ordinary-user settings.  You could invent a granularity between that, but
I'd like to see a pressing need first.  That would get too complicated to
manage, I think.

So basically, I think the current five levels of PGC_* should cover 1 and
2 OK.  Again, extensions are possible, but I don't see a need yet.

> 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).

OK, so what's the order:

1. run-time SET
2. per-user setting
3. per-database setting
4. backend options from client
5. postmaster command line
6. config file
7. wired-in default

Meaning, any setting if provided can only take effect if the previous
source of the setting had a higher number.

The given list represents the current state of affairs plus the
per-user/database settings inserted and the postmaster command line moved
from 6.5 to 5.

As for implementation, we could just insert an int field with these
numbers (or some macros) into the struct config_generic, and so the big
struct initializers in guc.c would all start out as 7.  The
SetConfigOption code would simply need an extra comparison to check
whether it can proceed.

Another nice thing about this approach is that the current "bool
makeDefault" parameter, which decides whether to save the new setting as
default for RESET ALL, could be folded into "source > 1" (since
everything except SET establishes a session default).

> > 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.

Maybe there should be an option to turn this on or off, depending on what
the default is.  Not sure yet.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> So basically, you have four useful combinations of these settings, which
> correspond to PGC_POSTMASTER, PGC_BACKEND, PGC_SIGHUP, and
> PGC_{SUSET,USERSET}.

Check.

> As for database-wide, I'm not sure how to interpret that.  Does that mean,
> this parameter must be the same for all concurrent sessions on the same
> database?  Is that something we ought to have?

Well, we haven't got it now, and I'm not sure whether there's anything
we'd really need it for.  I was speculating that we might want it for
something to do with schema paths or some such ... but it's hard to see
why those couldn't be treated as per-backend.  Certainly anything that
could be user-settable wouldn't be database-wide.

>> 2. From a security/privilege point of view, who should have the right to
>> change a given setting, and over what span of application?

> I don't see "database owner" as an independent concept:

Well, it is in terms of who gets to set the per-database GUC settings,
but I guess that doesn't directly relate to the privilege levels of the
individual settings.

> So basically, I think the current five levels of PGC_* should cover 1 and
> 2 OK.  Again, extensions are possible, but I don't see a need yet.

Okay, we can live with that for now, until/unless we see a reason to
invent a level that has something to do with per-database settings.

> OK, so what's the order:

> 1. run-time SET
> 2. per-user setting
> 3. per-database setting
> 4. backend options from client
> 5. postmaster command line
> 6. config file
> 7. wired-in default

Not sure; shouldn't backend options from client supersede the settings
taken from tables?  I'd be inclined to move up your #4 to between 1 and 2.
Otherwise this looks good.

> Meaning, any setting if provided can only take effect if the previous
> source of the setting had a higher number.

Same or higher number (to allow repeated SETs or config file changes).

> Another nice thing about this approach is that the current "bool
> makeDefault" parameter, which decides whether to save the new setting as
> default for RESET ALL, could be folded into "source > 1" (since
> everything except SET establishes a session default).

Right, I always felt that makeDefault was just a kluge to deal with one
of the deficiencies of the processing-order-sensitive implementation.

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

> Maybe there should be an option to turn this on or off, depending on what
> the default is.  Not sure yet.

That makes a lot of sense.  The standalone-backend environment is
already weird enough to make it mistake-prone; not loading your normal
settings would only make it more so.  I'd vote for having standalone
backends load your normal settings by default, but offer a command-line
switch to suppress that behavior; people would only use the switch if
their settings were too broken to load.
        regards, tom lane

test. Sorry.

Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > I think it's desirable and fully intentional.  How else would you override
> > command-line args without restarting the server?
> 
> I say you wouldn't.  If it's on the command line then it overrides
> postgresql.conf for that run of the postmaster; full stop, no "if"s.
> If you wanted it to be changeable within-run by postgresql.conf then you
> should have edited postgresql.conf to start with.
> 
> As the implementation currently works, applying a setting via the
> command line is entirely unsafe unless you then back it up with fixing
> the value in postgresql.conf; otherwise an unrelated change to
> postgresql.conf, perhaps much later, blows the command-line setting
> out of the water.  You might as well eliminate command-line settings
> entirely, if they have to be accompanied by a matching change in
> postgresql.conf in order to work reliably.
> 
> IMHO the only reason we haven't gotten squawks about this is that
> (a) the majority of uses of command-line arguments are for options
> that can't be changed after postmaster start anyway (-i, -p, -B, -N);
> (b) since the factory-default postgresql.conf has all settings commented
> out, it doesn't actually cause reversions to happen at SIGHUP.
> 
> If any significant number of people were actually being exposed to the
> current behavior, we'd be getting complaints; lots of 'em.  At the
> moment it's only a corner case because of considerations (a) and (b),
> so few people have had it happen to them.
> 
> > but in my mind, if you're using command-line arguments
> > in permanent setups, you're not trying hard enough.
> 
> You're essentially saying that command-line arguments are only useful
> for prototyping changes that you intend to make in postgresql.conf
> immediately thereafter.  Again, why have we got a command-line option
> facility at all?  Might as well make the change in postgresql.conf
> to begin with, if that's the viewpoint.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026