Thread: Re: [PATCHES] default database creation with initdb
-----Original Message----- From: pgsql-hackers-owner@postgresql.org on behalf of Andreas Pflug Sent: Sun 6/19/2005 12:23 AM To: Tom Lane Cc: Robert Treat; Magnus Hagander; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [PATCHES] default database creation with initdb > This contradicts my intention to have users *not* to write to it, but > reserve it for system like stuff. You might take everything that's not > in postgres binary as non-system, but the average user's perception is > different. The main intention of my original post was to suggest a way of keeping users out of template1. This still remains the mainissue imo, and one that a 'default' database would resolve perfectly well. > Apparently we really need two initdb created databases for all purposes. Whether or not users should write to the default db is another issue altogether, and one that I'd rather not see causingthis idea to be rejected or get delayed past freeze. If 'default' is writeable, then so what if users use it? It won'tstop pgAdmin from working, and it won't stop CREATE DATABASE from working. If we /really/ don't want them looking atwhat we're writing to the cluster, then we can just as easily agree a standard name for a database with phpPgAdmin andany other interested projects, and all co-exist in that. The first time any of the products is used, it creates the databaseif required. The important thing is that we have a default database for users to connect to from initdb time, and that it isn't template1. Now, as you (Andreas) already seem to have written the easy part of the patch, are you going to check the rest of the docs& sources for references to template1 (and correct where required), or shall I take some time tomorrow? :-) Regards, Dave.
Dave Page wrote: > > Whether or not users should write to the default db is another issue > altogether, and one that I'd rather not see causing this idea to be > rejected or get delayed past freeze. +1 > If 'default' is writeable, then > so what if users use it? It won't stop pgAdmin from working, and it > won't stop CREATE DATABASE from working. If we /really/ don't want > them looking at what we're writing to the cluster, then we can just > as easily agree a standard name for a database with phpPgAdmin and > any other interested projects, and all co-exist in that. The first > time any of the products is used, it creates the database if > required. We can hide that db from using by declaring it a system db anyway, which would prevent most users from using it. > The important thing is that we have a default database for users to > connect to from initdb time, and that it isn't template1. Jup. > Now, as you (Andreas) already seem to have written the easy part of > the patch, are you going to check the rest of the docs & sources for > references to template1 (and correct where required), or shall I take > some time tomorrow? :-) There's still the interesting idea of naming that db "postgres", which I'd even prefer over default because it includes the idea of default db since dbname=username is a common assumption in pgsql tools and also indicates "this is not for everybody, but just for me, the admin". I'm fine with any name though. Can't tell whether I could find time for reviewing the docs the next days (more interesting for feature freeze is having fixed the implementation anyway). I'm much more concerned about the instrumentation patch which apparently is going to be ignored until after feature freeze again as it was last year for 8.0. Regards, Andreas > > Regards, Dave.
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Can't tell whether I could find time for reviewing the docs the next
> days (more interesting for feature freeze is having fixed the
> implementation anyway).
Of the sixty-odd files that mention template1 in current CVS, only about
half are documentation. If you think a patch that patches only initdb
is enough to get this "feature" in, you are very mistaken ... even if we
were inclined to accept patches that blatantly omit documentation, which
as a rule we do not.
regards, tom lane
Tom Lane wrote: > Andreas Pflug <pgadmin@pse-consulting.de> writes: > >>Can't tell whether I could find time for reviewing the docs the next >>days (more interesting for feature freeze is having fixed the >>implementation anyway). > > > Of the sixty-odd files that mention template1 in current CVS, only about > half are documentation. The decision which files should be changed must be taken. e.g. createdb, dropdb will use template1 hardcoded. Is it acceptable that those tools fail if the "postgres" database isn't present any more? Regards, Andreas
Andreas Pflug wrote: > Tom Lane wrote: > >> Andreas Pflug <pgadmin@pse-consulting.de> writes: >> >>> Can't tell whether I could find time for reviewing the docs the next >>> days (more interesting for feature freeze is having fixed the >>> implementation anyway). >> >> >> >> Of the sixty-odd files that mention template1 in current CVS, only about >> half are documentation. > > > The decision which files should be changed must be taken. e.g. > createdb, dropdb will use template1 hardcoded. Is it acceptable that > those tools fail if the "postgres" database isn't present any more? > > How about template1 as a fallback? cheers andrew
Andrew Dunstan wrote: > >> >> >> The decision which files should be changed must be taken. e.g. >> createdb, dropdb will use template1 hardcoded. Is it acceptable that >> those tools fail if the "postgres" database isn't present any more? >> >> > > How about template1 as a fallback? Fallback is a fine idea, but this brings up another problem I'm currently facing: how to identify the problem the connection has from libpq? If the problem is a wrong password, we certainly don't want to try again. I browsed the sources over and over, but apparently there's no machine readable return code to distinguish the reason of connection failure apart from examining the errormessage string. I have the same problem in pgAdmin, where I try to give extended messages like http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/trunk/pgadmin3/docs/en_US/hints/conn-listen.html?rev=4056&view=markup Regards, Andreas
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Fallback is a fine idea, but this brings up another problem I'm
> currently facing: how to identify the problem the connection has from
> libpq? If the problem is a wrong password, we certainly don't want to
> try again. I browsed the sources over and over, but apparently there's
> no machine readable return code to distinguish the reason of connection
> failure apart from examining the errormessage string.
If it's a server-side failure it should have a SQLSTATE code. I think
it'd be OK to look for ERRCODE_UNDEFINED_DATABASE to determine this.
However that still leaves us with an issue:
$ psql -U foopsql: FATAL: database "foo" does not exist
The "real" problem here is that there's no user foo, but the backend is
currently coded in such a way that it detects the bad implied database
name first (at least in non-password-based auth methods). Not sure if
this is a big problem for code that's not defaulting the database name
though.
regards, tom lane
Tom Lane wrote: > Andreas Pflug <pgadmin@pse-consulting.de> writes: > >>Fallback is a fine idea, but this brings up another problem I'm >>currently facing: how to identify the problem the connection has from >>libpq? If the problem is a wrong password, we certainly don't want to >>try again. I browsed the sources over and over, but apparently there's >>no machine readable return code to distinguish the reason of connection >>failure apart from examining the errormessage string. > > > If it's a server-side failure it should have a SQLSTATE code. I think > it'd be OK to look for ERRCODE_UNDEFINED_DATABASE to determine this. SQLSTATE? Specifically, I'm talking about "no pg_hba.conf entry for ....", "Ident authentication failed.." (both server sice) and "Is the server running on host ..." from libpq which I receive from PQerrorMessage after PQconnectdb; I only receive CONNECTION_BAD from PQstatus where I'd like it a little more specific (maybe an PQextendedStatus) Regards, Andreas
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Tom Lane wrote:
>> If it's a server-side failure it should have a SQLSTATE code.
> Specifically, I'm talking about
> "no pg_hba.conf entry for ....",
ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION
> "Ident authentication failed.." (both server sice)
Ditto. Do you need to know the difference? What exactly would client
code do differently for these two cases?
> and "Is the server running on host ..." from libpq
libpq doesn't currently assign SQLSTATEs to internally detected errors
... someday someone should fix that.
regards, tom lane
Tom Lane wrote: > Andreas Pflug <pgadmin@pse-consulting.de> writes: > >>Tom Lane wrote: >> >>>If it's a server-side failure it should have a SQLSTATE code. > > >>Specifically, I'm talking about > > >>"no pg_hba.conf entry for ....", > > > ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION > > >>"Ident authentication failed.." (both server sice) > > > Ditto. Do you need to know the difference? What exactly would client > code do differently for these two cases? Display different hints how to cope with this. The hint will arise for newbies, and it would be confusing to discuss ident auth when a missing pg_hba.conf line is the problem, and vice versa. > >>and "Is the server running on host ..." from libpq > > > libpq doesn't currently assign SQLSTATEs to internally detected errors > ... someday someone should fix that. Ok, I'm blind. Where in h**l do I find the SQLSTATE from a PGconn?!? Regards, Andreas