Обсуждение: BUG #4040: psql should provide option to not prompt for password
The following bug has been logged online: Bug reference: 4040 Logged by: Mika Fischer Email address: mf+ubuntu@zoopnet.de PostgreSQL version: 8.3.0 Operating system: Ubuntu Linux 8.4 beta Description: psql should provide option to not prompt for password Details: Hi, I'm currently working on the bash-completion package. The problem with postgresql is that psql cannot safely be called because there is no way to know whether it will prompt for a password and there is also no way to avoid the prompt. Needless to say a password prompt is very bad in the context of tab-completion. Ideally, psql should provide an option --no-password which would cause it to never promt for a password, and in case one is needed, fail as if a wrong one was given. However if you can think of an easier way to accomplish this, I'd be grateful for pointers. Thanks for considering, Mika
"Mika Fischer" <mf+ubuntu@zoopnet.de> writes:
> I'm currently working on the bash-completion package. The problem with
> postgresql is that psql cannot safely be called because there is no way to
> know whether it will prompt for a password and there is also no way to avoid
> the prompt.
> Needless to say a password prompt is very bad in the context of
> tab-completion.
> Ideally, psql should provide an option --no-password which would cause it to
> never promt for a password, and in case one is needed, fail as if a wrong
> one was given.
Are you suggesting that the shell should invoke psql without any idea of
appropriate connection parameters? This seems utterly foolish.
regards, tom lane
Mika Fischer [2008-03-17 10:19 +0000]: > Description: psql should provide option to not prompt for password > Details: > > Hi, > > I'm currently working on the bash-completion package. The problem with > postgresql is that psql cannot safely be called because there is no way to > know whether it will prompt for a password and there is also no way to avoid > the prompt. > > Needless to say a password prompt is very bad in the context of > tab-completion. > > Ideally, psql should provide an option --no-password which would cause it to > never promt for a password, and in case one is needed, fail as if a wrong > one was given. > > However if you can think of an easier way to accomplish this, I'd be > grateful for pointers. Indeed I have a similar problem. I use psql to probe for actual availability of cluster startup in the integration scripts (pg_ctl does not provide that) and also stumbled over this. Earlier versions did not prompt if PGPASSWD was supplied, 8.3 changed this behaviour. That's why I applied the attached patch to the Debian/Ubuntu packages to restore the older behaviour, which works pretty well. I already proposed that some months ago [1], but didn't get very far. Martin [1] http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg18440.html -- Martin Pitt | http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) # Description: Change psql to not prompt for a password if PGPASSWORD is given, just as in earlier versions. Without that,there is no way to suppress the password input prompt, which is bad for scripts. --- postgresql-8.3RC1/src/bin/psql/startup.c 2008-01-04 14:48:17.000000000 +0100 +++ postgresql-8.3RC1/src/bin/psql/startup.c 2008-01-04 14:49:24.000000000 +0100 @@ -199,7 +199,7 @@ if (PQstatus(pset.db) == CONNECTION_BAD && PQconnectionNeedsPassword(pset.db) && - password == NULL && + password == NULL && !getenv("PGPASSWORD") && !feof(stdin)) { PQfinish(pset.db);
Вложения
Mika Fischer <mf+ubuntu@zoopnet.de> writes: > What the current code does is: > Run "psql -l" to get the list of local databases, and > run "psql -qtc 'select usename from pg_user' template1" to get the list > of users. If this fails the system users are used for completion. > I'm not at all a PostgreSQL expert so I can't even comment on whether > this is a smart thing to do or not. It's not, IMHO. You don't even know if the local database is where the user is intending to connect to. Moreover this presupposes some rather obsolete ideas about what connection parameters might need to be given on the command line. Something that might be more useful is to see if there's a connection service file http://developer.postgresql.org/pgdocs/postgres/libpq-pgservice.html and offer the names of service entries in it. regards, tom lane
On Mon, Mar 17, 2008 at 7:34 PM, Martin Pitt <martin@piware.de> wrote: > Mika Fischer [2008-03-17 10:19 +0000]: > > Description: psql should provide option to not prompt for > password > > Details: > > > > Hi, > > > > I'm currently working on the bash-completion package. The problem with > > postgresql is that psql cannot safely be called because there is no way > to > > know whether it will prompt for a password and there is also no way to > avoid > > the prompt. > > > > Needless to say a password prompt is very bad in the context of > > tab-completion. > > > > Ideally, psql should provide an option --no-password which would cause > it to > > never promt for a password, and in case one is needed, fail as if a > wrong > > one was given. > > > > However if you can think of an easier way to accomplish this, I'd be > > grateful for pointers. > > Indeed I have a similar problem. I use psql to probe for actual > availability of cluster startup in the integration scripts (pg_ctl > does not provide that) and also stumbled over this. > > Earlier versions did not prompt if PGPASSWD was supplied, 8.3 changed > this behaviour. That should be PGPASSWORD The 8.3 docs still mention it here: http://www.postgresql.org/docs/8.3/interactive/libpq-envars.html This applies to psql too, since psql uses libpq to communicate with the server. If you think that docs are out of sync, please raise a bug for that. Also, there's a workaround mentioned there (~/.pgpass file), see if that helps. Best regards, --=20 gurjeet[.singh]@EnterpriseDB.com singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17=B0 29' 34.37"N, 78=B0 30' 59.76"E - Hyderabad * 18=B0 32' 57.25"N, 73=B0 56' 25.42"E - Pune 37=B0 47' 19.72"N, 122=B0 24' 1.69" W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Mika Fischer wrote:
> I'm currently working on the bash-completion package. The problem with
> postgresql is that psql cannot safely be called because there is no way to
> know whether it will prompt for a password and there is also no way to avoid
> the prompt.
Hmm, why do you need to connect to a database? Some time ago I came up
with this:
_postgres()
{
local cur;
COMPREPLY=()
cur=${COMP_WORDS[COMP_CWORD]}
prev=${COMP_WORDS[COMP_CWORD-1]}
if [ "$prev" == "-c" ]; then
COMPREPLY=($(compgen -S= -onospace -W '`command postgres --describe-config | cut -d" " -f1`' --
$cur));
return 0
fi
COMPREPLY=($(compgen -W '-A -B -c -d -D -e -E -F -N -o -P -s -S -f -i -O -t -W --describe-config --help
--version'-- $cur))
return 0
}
complete -F _postgres postgres postmaster
What else do you need?
(Hmm, it doesn't work correctly in the french locale ... it seems
someone decided to add some newlines in there.)
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Martin Pitt <martin@piware.de> writes:
> if (PQstatus(pset.db) == CONNECTION_BAD &&
> PQconnectionNeedsPassword(pset.db) &&
> - password == NULL &&
> + password == NULL && !getenv("PGPASSWORD") &&
> !feof(stdin))
> {
> PQfinish(pset.db);
What exactly do you think that accomplishes? AFAICS
PQconnectionNeedsPassword can't possibly return true if there was a
password available from PGPASSWORD (regardless of whether it was
correct or not).
regards, tom lane
Mika Fischer <mf+ubuntu@zoopnet.de> writes: > * Tom Lane <tgl@sss.pgh.pa.us> [2008-03-17 15:28]: >> Something that might be more useful is to see if there's a connection >> service file >> http://developer.postgresql.org/pgdocs/postgres/libpq-pgservice.html >> and offer the names of service entries in it. > Yes, that also seems to be useful. What is the recommended way to use > these service names? > 'psql "service=name"' or 'psql -d name'? psql service=name is the shortest way. You don't need quotes as long as there's not any funny characters in the name. regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [2008-03-17 15:28]: > Mika Fischer <mf+ubuntu@zoopnet.de> writes: > > What the current code does is: > > Run "psql -l" to get the list of local databases, and > > run "psql -qtc 'select usename from pg_user' template1" to get the list > > of users. If this fails the system users are used for completion. > > > I'm not at all a PostgreSQL expert so I can't even comment on whether > > this is a smart thing to do or not. > > It's not, IMHO. You don't even know if the local database is where the > user is intending to connect to. Well, if the user has already given a -h parameter I could notice and disable the completion. If he has not and tries to complete for -U, but later wants to supply a -h option, then there's not much I can do about it but I don't think much harm is done. > Moreover this presupposes some rather obsolete ideas about what > connection parameters might need to be given on the command line. What exactly do you mean by that? Do you mean that this would be better? psql -q -t -c 'select usename from pg_user' -d template1 > Something that might be more useful is to see if there's a connection > service file > http://developer.postgresql.org/pgdocs/postgres/libpq-pgservice.html > and offer the names of service entries in it. Yes, that also seems to be useful. What is the recommended way to use these service names? 'psql "service=name"' or 'psql -d name'? It would still be nice if for the local database you could do: psql very<TAB> and it would complete to very_long_database_name But it seems impossible to do this nicely... Regards, Mika
* Alvaro Herrera <alvherre@commandprompt.com> [2008-03-17 15:43]:
> Hmm, why do you need to connect to a database? Some time ago I came up
> with this:
>
> _postgres()
> {
> local cur;
> COMPREPLY=()
> cur=${COMP_WORDS[COMP_CWORD]}
> prev=${COMP_WORDS[COMP_CWORD-1]}
> if [ "$prev" == "-c" ]; then
> COMPREPLY=($(compgen -S= -onospace -W '`command postgres --describe-config | cut -d" " -f1`' --
$cur));
> return 0
> fi
> COMPREPLY=($(compgen -W '-A -B -c -d -D -e -E -F -N -o -P -s -S -f -i -O -t -W --describe-config --help
--version'-- $cur))
> return 0
> }
> complete -F _postgres postgres postmaster
>
> What else do you need?
The idea was to do completion for psql, in particular of usernames and
databases. See my other mail for details.
Regards,
Mika
* Tom Lane <tgl@sss.pgh.pa.us> [2008-03-17 14:44]: > "Mika Fischer" <mf+ubuntu@zoopnet.de> writes: > > I'm currently working on the bash-completion package. The problem with > > postgresql is that psql cannot safely be called because there is no way to > > know whether it will prompt for a password and there is also no way to avoid > > the prompt. > > > Needless to say a password prompt is very bad in the context of > > tab-completion. > > > Ideally, psql should provide an option --no-password which would cause it to > > never promt for a password, and in case one is needed, fail as if a wrong > > one was given. > > Are you suggesting that the shell should invoke psql without any idea of > appropriate connection parameters? This seems utterly foolish. Well, this is a best-effort kind of thing. If it doesn't work nothing's lost. If it does work, it's convenient for the user. So I don't see much wrong with it. If you think this is a bad idea for some reason please elaborate. What the current code does is: Run "psql -l" to get the list of local databases, and run "psql -qtc 'select usename from pg_user' template1" to get the list of users. If this fails the system users are used for completion. I'm not at all a PostgreSQL expert so I can't even comment on whether this is a smart thing to do or not. But is does work if the user is not prompted for a password. Any suggestions and comments are appreciated. Regards, Mika
Am Montag, 17. März 2008 schrieb Tom Lane: > It's not, IMHO. You don't even know if the local database is where the > user is intending to connect to. Moreover this presupposes some rather > obsolete ideas about what connection parameters might need to be given > on the command line. "psql <TAB>" to complete the local database names is a very useful feature that has existed and been in use for a long time. Even if you like to use psql in a different way, there are many people that use it this way.
Tom Lane [2008-03-17 10:48 -0400]:
> Martin Pitt <martin@piware.de> writes:
> > if (PQstatus(pset.db) == CONNECTION_BAD &&
> > PQconnectionNeedsPassword(pset.db) &&
> > - password == NULL &&
> > + password == NULL && !getenv("PGPASSWORD") &&
> > !feof(stdin))
> > {
> > PQfinish(pset.db);
>
> What exactly do you think that accomplishes? AFAICS
> PQconnectionNeedsPassword can't possibly return true if there was a
> password available from PGPASSWORD (regardless of whether it was
> correct or not).
I don't claim to understand the complete code behind
PQconnectionNeedsPassword(). I just found that in at least 8.3RC1,
this did return True if pg_hba.conf set password authentication and
none was provided. I tried every trick that came into my mind,
redirecting stdin, using PGPASSWORD, and I think even a fake empty
.pgpass file, nothing worked.
Martin
--
Martin Pitt | http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)
Peter Eisentraut wrote:
> Am Montag, 17. März 2008 schrieb Tom Lane:
> > It's not, IMHO. You don't even know if the local database is where the
> > user is intending to connect to. Moreover this presupposes some rather
> > obsolete ideas about what connection parameters might need to be given
> > on the command line.
>
> "psql <TAB>" to complete the local database names is a very useful feature
> that has existed and been in use for a long time.
Agreed. If it can be made to work with remote database when the user
already provided the -h option, even better. (And while at it,
providing options for -h completion from .pgpass would be neat too.)
BTW while reading the psql manpage I noticed this statement:
A popular application of this facility is to refer to the last
inserted OID in subsequent statements to build a foreign key
scenario.
(This refers to :foo interpolation.) Talking about popular application
of OIDs in FKs seems so 90's. Should we remove that phrase?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Am Montag, 17. März 2008 schrieb Mika Fischer: > Ideally, psql should provide an option --no-password which would cause it > to never promt for a password, and in case one is needed, fail as if a > wrong one was given. Something like ssh's BatchMode would be nice.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> BTW while reading the psql manpage I noticed this statement:
> A popular application of this facility is to refer to the last
> inserted OID in subsequent statements to build a foreign key
> scenario.
> (This refers to :foo interpolation.) Talking about popular application
> of OIDs in FKs seems so 90's. Should we remove that phrase?
Yeah, probably; it's been a very long time since that was considered
a good way to do things. Can you think of some other example to replace
that with?
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > BTW while reading the psql manpage I noticed this statement: > > > A popular application of this facility is to refer to the last > > inserted OID in subsequent statements to build a foreign key > > scenario. > > > (This refers to :foo interpolation.) Talking about popular application > > of OIDs in FKs seems so 90's. Should we remove that phrase? > > Yeah, probably; it's been a very long time since that was considered > a good way to do things. Can you think of some other example to replace > that with? I have removed the mention of OID usage with the attached patch. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/ref/psql-ref.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v retrieving revision 1.201 diff -c -c -r1.201 psql-ref.sgml *** doc/src/sgml/ref/psql-ref.sgml 5 May 2008 01:38:08 -0000 1.201 --- doc/src/sgml/ref/psql-ref.sgml 8 May 2008 00:19:55 -0000 *************** *** 2440,2448 **** </para> <para> ! A popular application of this facility is to refer to the last ! inserted <acronym>OID</acronym> in subsequent statements to build a ! foreign key scenario. Another possible use of this mechanism is to copy the contents of a file into a table column. First load the file into a variable and then proceed as above: <programlisting> --- 2440,2446 ---- </para> <para> ! One possible use of this mechanism is to copy the contents of a file into a table column. First load the file into a variable and then proceed as above: <programlisting>
Peter Eisentraut wrote: > Am Montag, 17. M?rz 2008 schrieb Mika Fischer: > > Ideally, psql should provide an option --no-password which would cause it > > to never promt for a password, and in case one is needed, fail as if a > > wrong one was given. > > Something like ssh's BatchMode would be nice. Do we want to implement this? I haven't heard a huge number of requests for --no-password. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +