Skip site navigation (1) Skip section navigation (2)

Final spec on per-database/per-user settings

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Final spec on per-database/per-user settings
Date: 2002-03-01 01:59:16
Message-ID: Pine.LNX.4.30.0202281816170.691-100000@peter.localdomain (view raw or flat)
Thread:
Lists: pgsql-hackers
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(at)gmx(dot)net


Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2002-03-01 02:08:28
Subject: More general configurability of session startup actions
Previous:From: Dann CorbitDate: 2002-02-28 23:44:54
Subject: Re: Database Caching

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group