BUG #4040: psql should provide option to not prompt for password

"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

* 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

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]http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg18440.html, 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)

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,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 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

* 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

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

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)

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.

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.

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. +

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. +