Re: Per-database and per-user GUC settings - Mailing list pgsql-hackers

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