BUG #4040: psql should provide option to not prompt for password
BUG #4040: psql should provide option to not prompt for password
От:
"Mika Fischer" <mf+ubuntu@zoopnet.de>
Дата:
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
Re: BUG #4040: psql should provide option to not prompt for password
От:
Mika Fischer <mf+ubuntu@zoopnet.de>
Дата:
* Tom Lane [2008-03-17 14:44]: > "Mika Fischer" 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
Re: BUG #4040: psql should provide option to not prompt for password
От:
Martin Pitt <martin@piware.de>
Дата:
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);
Re: BUG #4040: psql should provide option to not prompt for password
От:
Alvaro Herrera <alvherre@commandprompt.com>
Дата:
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 supportRe: BUG #4040: psql should provide option to not prompt for password
От:
Mika Fischer <mf+ubuntu@zoopnet.de>
Дата:
* Tom Lane [2008-03-17 15:28]: > Mika Fischer 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 and it would complete to very_long_database_name But it seems impossible to do this nicely... Regards, Mika
Re: BUG #4040: psql should provide option to not prompt for password
От:
Mika Fischer <mf+ubuntu@zoopnet.de>
Дата:
* Alvaro Herrera [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,
MikaRe: BUG #4040: psql should provide option to not prompt for password
От:
Martin Pitt <martin@piware.de>
Дата:
Tom Lane [2008-03-17 10:48 -0400]:
> Martin Pitt 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)Re: BUG #4040: psql should provide option to not prompt for password
От:
Alvaro Herrera <alvherre@commandprompt.com>
Дата:
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 " 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.Re: BUG #4040: psql should provide option to not prompt for password
От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
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 " 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.
Re: BUG #4040: psql should provide option to not prompt for password
От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
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.
Re: BUG #4040: psql should provide option to not prompt for password
От:
Bruce Momjian <bruce@momjian.us>
Дата:
Tom Lane wrote:
> Alvaro Herrera 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 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 ****
! A popular application of this facility is to refer to the last
! inserted OID 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:
--- 2440,2446 ----
! 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:
Re: BUG #4040: psql should provide option to not prompt for password
От:
Bruce Momjian <bruce@momjian.us>
Дата:
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 http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Re: BUG #4040: psql should provide option to not prompt for password
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Alvaro Herrera 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
Re: BUG #4040: psql should provide option to not prompt for password
От:
"Gurjeet Singh" <singh.gurjeet@gmail.com>
Дата:
On Mon, Mar 17, 2008 at 7:34 PM, Martin Pitt 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 deviceRe: BUG #4040: psql should provide option to not prompt for password
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Mika Fischer" 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
Re: BUG #4040: psql should provide option to not prompt for password
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Mika Fischer 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
Re: BUG #4040: psql should provide option to not prompt for password
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Martin Pitt 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 laneRe: BUG #4040: psql should provide option to not prompt for password
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Mika Fischer writes: > * Tom Lane [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
