Re: new --maintenance-db options

About the new --maintenance-db options:

Why was this option not added to createuser and dropuser? In the
original discussion[0] they were mentioned, but it apparently never made
it into the code.

I find the name to be unfortunate. For example, I think of running
vacuum as "maintenance". So running vacuumdb --maintenance-db=X would
imply that the vacuum maintenance is done on X. In fact, the whole
point of this option is to find out where the maintenance is to be run,
not to run the maintenance. Maybe something like --initial-db would be
better?

What is the purpose of these options? The initial discussion was
unclear on this. The documentation contains no explanation of why they
should be used. If we want to really support the case where both
postgres and template1 are removed, an environment variable might be
more useful than requiring this to be typed out for every command.

[0]: http://archives.postgresql.org/message-id/CA+TgmoaCjWSiS9nNqJGAamL1vg6C8B6O1nDgqnUCa2Gm00dNfg@mail.gmail.com

On Saturday, June 23, 2012, Peter Eisentraut wrote:

> About the new --maintenance-db options:
>
> Why was this option not added to createuser and dropuser? In the
> original discussion[0] they were mentioned, but it apparently never made
> it into the code.
>
> I find the name to be unfortunate. For example, I think of running
> vacuum as "maintenance". So running vacuumdb --maintenance-db=X would
> imply that the vacuum maintenance is done on X. In fact, the whole
> point of this option is to find out where the maintenance is to be run,
> not to run the maintenance. Maybe something like --initial-db would be
> better?
>

I'm not saying it's the best name, but I suspect the origin is pgAdmin
which has used 'Maintenance DB' for 10+ years. There's likely a certain
amount of familiarity with the term amonst pgAdmin users.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Sat, Jun 23, 2012 at 6:26 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> About the new --maintenance-db options:
>
> Why was this option not added to createuser and dropuser?  In the
> original discussion[0] they were mentioned, but it apparently never made
> it into the code.

Oops. That was an oversight.

> I find the name to be unfortunate.  For example, I think of running
> vacuum as "maintenance".  So running vacuumdb --maintenance-db=X would
> imply that the vacuum maintenance is done on X.  In fact, the whole
> point of this option is to find out where the maintenance is to be run,
> not to run the maintenance.  Maybe something like --initial-db would be
> better?

As Dave says, I picked this because pgAdmin has long used that terminology.

> What is the purpose of these options?  The initial discussion was
> unclear on this.  The documentation contains no explanation of why they
> should be used.  If we want to really support the case where both
> postgres and template1 are removed, an environment variable might be
> more useful than requiring this to be typed out for every command.
>
> [0]: http://archives.postgresql.org/message-id/CA+TgmoaCjWSiS9nNqJGAamL1vg6C8B6O1nDgqnUCa2Gm00dNfg@mail.gmail.com

Well, I would be opposed to having ONLY an environment variable,
because I think that anything that can be controlled via an
environment variable should be able to be overridden on the command
line. It might be OK to have both an environment variable AND a
command-line option, but I tend to thing it's too marginal to justify
that.

In retrospect, it seems as though it might have been a good idea to
make the postgres database read-only and undroppable, so that all
client utilities could count on being able to connect to it and get a
list of databases in the cluster without the need for all this
complexity. Or else having some other way for a client to
authenticate and list out all the available databases. In the absence
of such a mechanism, I don't think we can turn around and say that not
having a postgres database is an unsupported configuration, and
therefore we need some way to cope with it when it happens.

I think the original report that prompted this change was a complaint
that pg_upgrade failed when the postgres database had been dropped.
Now, admittedly, pg_upgrade fails for all kinds of crazy stupid
reasons and the chances of fixing that problem completely any time in
the next 5 years do not seem good, but that's not a reason not to keep
plugging the holes we can. Anyhow, the same commit that introduced
--maintenance-db "fixed" that problem by making arranging to try both
postgres and template1 before giving up... but have two hard-coded
database names either of which can be dropped or renamed seems only
marginally better than having one, hence the switch. Really, I think
pg_upgrade needs this option too, unless we're going to kill the
problem at its root by providing a reliable way to enumerate database
names without first knowing the name one that you can connect to.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Excerpts from Robert Haas's message of lun jun 25 11:57:36 -0400 2012:

> Really, I think
> pg_upgrade needs this option too, unless we're going to kill the
> problem at its root by providing a reliable way to enumerate database
> names without first knowing the name one that you can connect to.

I think pg_upgrade could do this one task by using a standalone backend
instead of a full-blown postmaster. It should be easy enough ...

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Jun 25, 2012 at 2:49 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Robert Haas's message of lun jun 25 11:57:36 -0400 2012:
>> Really, I think
>> pg_upgrade needs this option too, unless we're going to kill the
>> problem at its root by providing a reliable way to enumerate database
>> names without first knowing the name one that you can connect to.
>
> I think pg_upgrade could do this one task by using a standalone backend
> instead of a full-blown postmaster.  It should be easy enough ...

Maybe, but it seems like baking even more hackery into a tool that's
already got too much hackery. It's also hard for pg_upgrade to know
things like - whether pg_hba.conf prohibits access to certain
users/databases/etc. or just requires the use of authentication
methods that happen to fail. From pg_upgrade's perspective, it would
be nice to have a flag that starts the server in some mode where
nobody but pg_upgrade can connect to it and all connections are
automatically allowed, but it's not exactly clear how to implement
"nobody but pg_upgrade can connect to it".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Excerpts from Robert Haas's message of lun jun 25 14:58:25 -0400 2012:
>
> On Mon, Jun 25, 2012 at 2:49 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
> > Excerpts from Robert Haas's message of lun jun 25 11:57:36 -0400 2012:
> >> Really, I think
> >> pg_upgrade needs this option too, unless we're going to kill the
> >> problem at its root by providing a reliable way to enumerate database
> >> names without first knowing the name one that you can connect to.
> >
> > I think pg_upgrade could do this one task by using a standalone backend
> > instead of a full-blown postmaster.  It should be easy enough ...
>
> Maybe, but it seems like baking even more hackery into a tool that's
> already got too much hackery. It's also hard for pg_upgrade to know
> things like - whether pg_hba.conf prohibits access to certain
> users/databases/etc. or just requires the use of authentication
> methods that happen to fail. From pg_upgrade's perspective, it would
> be nice to have a flag that starts the server in some mode where
> nobody but pg_upgrade can connect to it and all connections are
> automatically allowed, but it's not exactly clear how to implement
> "nobody but pg_upgrade can connect to it".

Well, have it specify a private socket directory, listen only on that
(not TCP), and bypass all pg_hba rules.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> From pg_upgrade's perspective, it would
> be nice to have a flag that starts the server in some mode where
> nobody but pg_upgrade can connect to it and all connections are
> automatically allowed, but it's not exactly clear how to implement
> "nobody but pg_upgrade can connect to it".

The implementation I've wanted to see for some time is that you can
start a standalone backend, but it speaks FE/BE protocol to its caller
(preferably over pipes, so that there is no issue whatsoever of where
you can securely put a socket or anything like that). Making that
happen might be a bit too much work if pg_upgrade were the only use
case, but there are a lot of people who would like to use PG as an
embedded database, and this might be close enough for such use-cases.

However, that has got little to do with whether --maintenance-db is a
worthwhile thing or not, because that's about external client-side
tools, not pg_upgrade.

regards, tom lane

From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> From pg_upgrade's perspective, it would
>> be nice to have a flag that starts the server in some mode where
>> nobody but pg_upgrade can connect to it and all connections are
>> automatically allowed, but it's not exactly clear how to implement
>> "nobody but pg_upgrade can connect to it".

> The implementation I've wanted to see for some time is that you can
> start a standalone backend, but it speaks FE/BE protocol to its caller
> (preferably over pipes, so that there is no issue whatsoever of where
> you can securely put a socket or anything like that).

Can't it be done like follow the FE/BE protocol, but call directly the
server API's
at required places.
This kind of implementation can be more performant than adding any
communication to it which will be
beneficial for embedded databases.

> Making that
> happen might be a bit too much work if pg_upgrade were the only use
> case, but there are a lot of people who would like to use PG as an
> embedded database, and this might be close enough for such use-cases.

Seeing PG to run as embedded database would be interesting for many people
using PG.
There is another use case of embedded databases that they allow another
remote connections
as well to monitor the operations in database. However that can be done in a
later version of implementation.

With Regards,
Amit Kapila.

Amit Kapila <amit(dot)kapila(at)huawei(dot)com> writes:
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
>> The implementation I've wanted to see for some time is that you can
>> start a standalone backend, but it speaks FE/BE protocol to its caller
>> (preferably over pipes, so that there is no issue whatsoever of where
>> you can securely put a socket or anything like that).

> Can't it be done like follow the FE/BE protocol, but call directly the
> server API's
> at required places.

That wouldn't be easier, nor cleaner, and it would open us up to
client-induced database corruption (from failure to follow APIs, crashes
in the midst of an operation, memory stomps, etc). We decided long ago
that we would never support truly embedded operation in the sense of PG
executing in the client's process/address space. I like the design
suggested above because it has many of the good properties of an
embedded database (in particular, no need to manage or contact a server)
but still keeps the client code at arm's length.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
Amit Kapila <amit(dot)kapila(at)huawei(dot)com> writes:
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
>>> The implementation I've wanted to see for some time is that you can
>>> start a standalone backend, but it speaks FE/BE protocol to its caller
>>> (preferably over pipes, so that there is no issue whatsoever of where
>>> you can securely put a socket or anything like that).

>> Can't it be done like follow the FE/BE protocol, but call directly the
>> server API's
>> at required places.

> That wouldn't be easier, nor cleaner, and it would open us up to
> client-induced database corruption (from failure to follow APIs, crashes
> in the midst of an operation, memory stomps, etc). We decided long ago
> that we would never support truly embedded operation in the sense of PG
> executing in the client's process/address space.

Okay.

> I like the design
> suggested above because it has many of the good properties of an
> embedded database (in particular, no need to manage or contact a server)
> but still keeps the client code at arm's length.

In such a case will that standalone backend manage other processes like (wal
writer, checkpoint, ...) or no background processes like in current --single
mode.

Can there be any performance advantage also in such a mode as compare to
current when client and server on same m/c and uses Domain Socket?

With Regards,
Amit Kapila.

On Sun, Jun 24, 2012 at 01:26:58AM +0300, Peter Eisentraut wrote:
> About the new --maintenance-db options:
>
> What is the purpose of these options? The initial discussion was
> unclear on this. The documentation contains no explanation of why they
> should be used. If we want to really support the case where both
> postgres and template1 are removed, an environment variable might be
> more useful than requiring this to be typed out for every command.

Yes, I had the same question about the usefulness/purpose of the option,
but was out-voted.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Mon, Jun 25, 2012 at 11:57:36AM -0400, Robert Haas wrote:
> In retrospect, it seems as though it might have been a good idea to
> make the postgres database read-only and undroppable, so that all
> client utilities could count on being able to connect to it and get a
> list of databases in the cluster without the need for all this
> complexity. Or else having some other way for a client to
> authenticate and list out all the available databases. In the absence
> of such a mechanism, I don't think we can turn around and say that not
> having a postgres database is an unsupported configuration, and
> therefore we need some way to cope with it when it happens.

Well, we certainly don't allow 'template1' to be dropped:

test=> DROP DATABASE template1;
ERROR: cannot drop a template database

so you could make the argument that making 'postgres' undroppable seem
reasonable. I should point out that it was EnterpriseDB that complained
about this related to their Advanced Server product, that doesn't have a
'postgres' database, but an 'edb' one. I said that was their problem,
but when community users said they also dropped the 'postgres' database,
it became a community problem too.

Where are we going on this for PG 9.2? 9.3? I hate to ship options in
9.2 that will be gone in 9.3.

FYI, we do allow the 'template1' database to be renamed:

test=> ALTER DATABASE template1 RENAME TO template2;
ALTER DATABASE

Oops. TODO?

> I think the original report that prompted this change was a complaint
> that pg_upgrade failed when the postgres database had been dropped.
> Now, admittedly, pg_upgrade fails for all kinds of crazy stupid
> reasons and the chances of fixing that problem completely any time in
> the next 5 years do not seem good, but that's not a reason not to keep
> plugging the holes we can. Anyhow, the same commit that introduced
> --maintenance-db "fixed" that problem by making arranging to try both
> postgres and template1 before giving up... but have two hard-coded
> database names either of which can be dropped or renamed seems only
> marginally better than having one, hence the switch. Really, I think

Actually, 'template1' can't be dropped like 'postgres', but can be
renamed (which I think needs fixing). I think falling back to template1
for missing 'postgres' database was the goal there.

> pg_upgrade needs this option too, unless we're going to kill the
> problem at its root by providing a reliable way to enumerate database
> names without first knowing the name one that you can connect to.

pg_upgrade doesn't use --maintenance-db because the tools now fallback
to template1, which again brings up the question of the usefulness of
the --maintenance-db options.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Mon, Jun 25, 2012 at 02:58:25PM -0400, Robert Haas wrote:
> On Mon, Jun 25, 2012 at 2:49 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
> > Excerpts from Robert Haas's message of lun jun 25 11:57:36 -0400 2012:
> >> Really, I think
> >> pg_upgrade needs this option too, unless we're going to kill the
> >> problem at its root by providing a reliable way to enumerate database
> >> names without first knowing the name one that you can connect to.
> >
> > I think pg_upgrade could do this one task by using a standalone backend
> > instead of a full-blown postmaster.  It should be easy enough ...
>
> Maybe, but it seems like baking even more hackery into a tool that's
> already got too much hackery. It's also hard for pg_upgrade to know
> things like - whether pg_hba.conf prohibits access to certain
> users/databases/etc. or just requires the use of authentication
> methods that happen to fail. From pg_upgrade's perspective, it would
> be nice to have a flag that starts the server in some mode where
> nobody but pg_upgrade can connect to it and all connections are
> automatically allowed, but it's not exactly clear how to implement
> "nobody but pg_upgrade can connect to it".

pg_upgrade already starts the postmaster with a -b option that disables
non-super-user logins:

/*
* Binary upgrades only allowed super-user connections
*/
if (IsBinaryUpgrade && !am_superuser)
{
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to connect in binary upgrade mode")));
}

It also uses port 50432 by default. Not sure what else we can do.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Mon, Jun 25, 2012 at 03:12:00PM -0400, Alvaro Herrera wrote:
>
> Excerpts from Robert Haas's message of lun jun 25 14:58:25 -0400 2012:
> >
> > On Mon, Jun 25, 2012 at 2:49 PM, Alvaro Herrera
> > <alvherre(at)commandprompt(dot)com> wrote:
> > > Excerpts from Robert Haas's message of lun jun 25 11:57:36 -0400 2012:
> > >> Really, I think
> > >> pg_upgrade needs this option too, unless we're going to kill the
> > >> problem at its root by providing a reliable way to enumerate database
> > >> names without first knowing the name one that you can connect to.
> > >
> > > I think pg_upgrade could do this one task by using a standalone backend
> > > instead of a full-blown postmaster.  It should be easy enough ...
> >
> > Maybe, but it seems like baking even more hackery into a tool that's
> > already got too much hackery. It's also hard for pg_upgrade to know
> > things like - whether pg_hba.conf prohibits access to certain
> > users/databases/etc. or just requires the use of authentication
> > methods that happen to fail. From pg_upgrade's perspective, it would
> > be nice to have a flag that starts the server in some mode where
> > nobody but pg_upgrade can connect to it and all connections are
> > automatically allowed, but it's not exactly clear how to implement
> > "nobody but pg_upgrade can connect to it".
>
> Well, have it specify a private socket directory, listen only on that
> (not TCP), and bypass all pg_hba rules.

This could be added to the poststmaster -b behavior, but I am concerned
about the security of this. We sugest 'trust', but admins can adjust as
needed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Fri, Jun 29, 2012 at 3:32 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Mon, Jun 25, 2012 at 11:57:36AM -0400, Robert Haas wrote:
>> In retrospect, it seems as though it might have been a good idea to
>> make the postgres database read-only and undroppable, so that all
>> client utilities could count on being able to connect to it and get a
>> list of databases in the cluster without the need for all this
>> complexity. Or else having some other way for a client to
>> authenticate and list out all the available databases. In the absence
>> of such a mechanism, I don't think we can turn around and say that not
>> having a postgres database is an unsupported configuration, and
>> therefore we need some way to cope with it when it happens.
>
> Well, we certainly don't allow 'template1' to be dropped:
>
> test=> DROP DATABASE template1;
> ERROR: cannot drop a template database
>
> so you could make the argument that making 'postgres' undroppable seem
> reasonable. I should point out that it was EnterpriseDB that complained
> about this related to their Advanced Server product, that doesn't have a
> 'postgres' database, but an 'edb' one. I said that was their problem,
> but when community users said they also dropped the 'postgres' database,
> it became a community problem too.
>
> Where are we going on this for PG 9.2? 9.3? I hate to ship options in
> 9.2 that will be gone in 9.3.
>
> FYI, we do allow the 'template1' database to be renamed:
>
> test=> ALTER DATABASE template1 RENAME TO template2;
> ALTER DATABASE
>
> Oops. TODO?

Not only that, but you can change datistemplate and then drop it OR
rename it. We don't have a rule that says "you can't drop template1".
We have a rule that says "you can't drop template databases".
template1 is merely the default template database, but the user can
create more, and they can get rid of, rename, or modify that one. I
imagine that most people don't, but let's not make up an imaginary
rule that template1 always has to exist, because it doesn't.

Also, even if it does exist, it may have datallowconn = false (I think
I've actually seen this, on a system that also had no postgres
database), or pg_hba.conf may exclude connections to it, or it may be
screwed up in a hundred other ways (databases that can't be connected
to because the system catalogs are screwed up are not terribly rare).
So in my opinion, any code that relies on the existence of, ability to
connect to, or sane state of a database with any particular name is
plain broken, because somebody somewhere is going to have an
installation where it isn't.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Sun, 2012-06-24 at 01:26 +0300, Peter Eisentraut wrote:
> About the new --maintenance-db options:
>
> Why was this option not added to createuser and dropuser? In the
> original discussion[0] they were mentioned, but it apparently never
> made it into the code.

What should we do with this? Add the option to createuser and dropuser
now, and think about a more permanent/useful/complete solution in 9.3?

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On Sun, 2012-06-24 at 01:26 +0300, Peter Eisentraut wrote:
>> About the new --maintenance-db options:
>>
>> Why was this option not added to createuser and dropuser? In the
>> original discussion[0] they were mentioned, but it apparently never
>> made it into the code.

> What should we do with this? Add the option to createuser and dropuser
> now, and think about a more permanent/useful/complete solution in 9.3?

IMO it is now too late for 9.2 ... especially if you're of the opinion
that the current design is bad. Propagating a wrong choice into even
more places doesn't seem like a step forward.

regards, tom lane