Thread: Final spec on per-database/per-user settings

Final spec on per-database/per-user settings

From
Peter Eisentraut
Date:
Here is what I have implemented for database and user-specific run-time
configuration settings.  Unless there are desired changes, I plan to check
this in tomorrow.

* Syntax

ALTER DATABASE dbname SET variable TO value;
ALTER DATABASE dbname SET variable TO DEFAULT;
ALTER DATABASE dbname RESET variable;
ALTER DATABASE dbname RESET ALL;

And analogously for USER.  The second and the third syntax are equivalent.

AFAIK, there's no precedent for this general feature, but the syntax was
sort of extrapolated from a leading vendor's ALTER USER name SET TIME ZONE
statement.  (Which, ironically, this patch won't provide, because the time
zone setting is handled separately.  I hope to fix this soon.)

* Semantics

Settings are processed just before the session loop starts, thus giving
the intended impression of a SET statement executed manually just before
the session start.  This naturally restricts the allowable set of options
to SUSET and USERSET.  However, these settings do establish a new default
for the purpose of RESET ALL.

(The truth is, these settings are processed somewhere in the middle of
InitPostgres().  This way we only have to read pg_shadow and pg_database
once per session startup.  This keeps the performance impact negligible.)

* Privileges

Users can change their own defaults.  Database owners can change the
defaults for the databases they own.  Superusers can change anything.

If the executing user is a superuser, he can change SUSET settings.  Else
one can only change USERSET settings.  This means that superusers can
"force" SUSET settings onto other people who can't unset them.

* Escape hatch

If you severely mess up your settings so that you can't log in you can
still start up a standalone backend.  These settings are not processed in
this case.

In the standalone backend we don't even read pg_shadow now, because we set
the uid to 1 in any case.  So the reasoning was that if we don't read
pg_shadow, we can't process the user options, and if we don't process the
user options we shouldn't process the database options either.

-- 
Peter Eisentraut   peter_e@gmx.net



Re: Final spec on per-database/per-user settings

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Here is what I have implemented for database and user-specific run-time
> configuration settings.  Unless there are desired changes, I plan to check
> this in tomorrow.

Semantics seem fine, but an implementation detail:

> (The truth is, these settings are processed somewhere in the middle of
> InitPostgres().  This way we only have to read pg_shadow and pg_database
> once per session startup.  This keeps the performance impact negligible.)

I trust the pg_database row is read during ReverifyMyDatabase, and isn't
the one fetched by GetRawDatabaseInfo...
        regards, tom lane