Re: new --maintenance-db options

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