BUG #16486: Prompted password is ignored when password specified in connection string

PG Bug reporting form <noreply@postgresql.org> writes:

Assuming that we have the user u1 with the password p1, the following
command:
psql -U u1 'dbname=postgres password=p1' -W
prompts for a password but the input password is effectively ignored.

The entire point of the -W switch is that it prompts whether or not
the password is going to be used for anything. If you don't like that,
don't use -W.

regards, tom lane

On Mon, Jun 8, 2020 at 2:36 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

PG Bug reporting form <noreply@postgresql.org> writes:

Assuming that we have the user u1 with the password p1, the following
command:
psql -U u1 'dbname=postgres password=p1' -W
prompts for a password but the input password is effectively ignored.

The entire point of the -W switch is that it prompts whether or not
the password is going to be used for anything. If you don't like that,
don't use -W.

A more intuitive interpretation of -W is that it prompts for a password,
regardless of valid values being provided by other configuration, and uses
that password exclusively to attempt to connect to the server.

The documentation doesn't actually say which one of those two
interpretations is correct.

And regardless of which interpretation is intended the OP seems to
demonstrate that either interpretation is reality depending on
which combination of configuration sources provide the non-prompted
password. That inconsistency seems undesirable.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Mon, Jun 8, 2020 at 2:36 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

The entire point of the -W switch is that it prompts whether or not
the password is going to be used for anything.

A more intuitive interpretation of -W is that it prompts for a password,
regardless of valid values being provided by other configuration, and uses
that password exclusively to attempt to connect to the server.
The documentation doesn't actually say which one of those two
interpretations is correct.

Hmm ... I had thought that the docs explained -W in more or less the same
way I did above, but I see that (at least on the psql page) things are
indeed pretty vague. I'll see about improving that.

regards, tom lane

I wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Mon, Jun 8, 2020 at 2:36 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

The entire point of the -W switch is that it prompts whether or not
the password is going to be used for anything.

A more intuitive interpretation of -W is that it prompts for a password,
regardless of valid values being provided by other configuration, and uses
that password exclusively to attempt to connect to the server.
The documentation doesn't actually say which one of those two
interpretations is correct.

Hmm ... I had thought that the docs explained -W in more or less the same
way I did above, but I see that (at least on the psql page) things are
indeed pretty vague. I'll see about improving that.

Concretely, it looks like we need edits as attached for psql, and
likewise for all our other programs with similar options.

The thing that was really missing here IMO is the specification that a
conninfo string overrides other command-line parameters. It's somewhat
debatable whether a -W prompt is a "command line parameter", but it
acts that way for this purpose. In any case, I'm disinclined to document
that specific interaction, because it would amount to blessing a pretty
damfool practice, which is to make your password visible on the program's
command line. (I wonder whether section 33.1.2 ought to specifically
caution against putting passwords into command-line conninfo strings.)

regards, tom lane

On Mon, Jun 8, 2020 at 6:59 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

I wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Mon, Jun 8, 2020 at 2:36 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

The entire point of the -W switch is that it prompts whether or not
the password is going to be used for anything.

A more intuitive interpretation of -W is that it prompts for a password,
regardless of valid values being provided by other configuration, and

uses

that password exclusively to attempt to connect to the server.
The documentation doesn't actually say which one of those two
interpretations is correct.

Hmm ... I had thought that the docs explained -W in more or less the same
way I did above, but I see that (at least on the psql page) things are
indeed pretty vague. I'll see about improving that.

Concretely, it looks like we need edits as attached for psql, and
likewise for all our other programs with similar options.

For my own understanding if nothing else. The proposal requires the reader
to infer the following reality (if this is documented succinctly somewhere
I haven't stumbled across it):

There are 5 sources of passwords:

conninfo (cli, service file, URI)
-W
PGPASSWORD
.pgpass
fallback prompt

The first one to provide a non-empty password value is attempted and either
succeeds or fails - subsequent sources are not considered upon failure.

The thing that was really missing here IMO is the specification that a

conninfo string overrides other command-line parameters.

To confirm, the service file is treated no differently than specifying
these directly on a command line.

It's somewhat

debatable whether a -W prompt is a "command line parameter", but it
acts that way for this purpose.

Makes sense, it is a "key/value" parameter just that the value comes from
stdin instead of the command itself.

In any case, I'm disinclined to document
that specific interaction, because it would amount to blessing a pretty
damfool practice, which is to make your password visible on the program's
command line.

(I wonder whether section 33.1.2 ought to specifically

caution against putting passwords into command-line conninfo strings.)

Except it doesn't if it's coming from a service file or injected using an
environment variable supplied URI, right?
I'd rather document how it works and describe why it should be avoided.

There aren't many complaints/questions in this area so probably a wholesale
reworking and centralization is not worth the effort.

I suppose my final answer for the wording (assuming I understand this
correctly) would be:

"If you specify -W and conninfo does not supply a password the password
entered here will be used regardless of whether other sources supply a
password. If conninfo supplies a password psql will still prompt you for
one but will ignore it." (reworded to avoid "you"...)

David J.

Hello Tom,
09.06.2020 04:59, Tom Lane wrote:

The thing that was really missing here IMO is the specification that a
conninfo string overrides other command-line parameters. It's somewhat
debatable whether a -W prompt is a "command line parameter", but it
acts that way for this purpose. In any case, I'm disinclined to document
that specific interaction, because it would amount to blessing a pretty
damfool practice, which is to make your password visible on the program's
command line. (I wonder whether section 33.1.2 ought to specifically
caution against putting passwords into command-line conninfo strings.)

I don't think that the main issue is with blessing such a practice,
because the documentation already says about PGPASSWORD:
Use of this environment variable is not recommended for security
reasons, as some operating systems allow non-root users to see process
environment variables via ps; ...
So using the PGPASSWORD is not blessed either.
And I wonder, what if we had, say PGCONNURI environment variable? Should
the password specified inside be not overridable too?

By the way, similar behaviour could also be observed with PGSERVICE:
echo -e "[mydb]\nhost=localhost\nuser=u1\npassword=p1\n" >/tmp/service.conf
PGSERVICEFILE=/tmp/service.conf PGSERVICE="mydb" psql -W
In this case psql effectively ignores the input password too. So the
visibility of the password is not main criteria to make it dominant.

Best regards,
Alexander

On Mon, Jun 8, 2020 at 09:59:18PM -0400, Tom Lane wrote:

I wrote:

Hmm ... I had thought that the docs explained -W in more or less the same
way I did above, but I see that (at least on the psql page) things are
indeed pretty vague. I'll see about improving that.

Concretely, it looks like we need edits as attached for psql, and
likewise for all our other programs with similar options.

The thing that was really missing here IMO is the specification that a
conninfo string overrides other command-line parameters. It's somewhat
debatable whether a -W prompt is a "command line parameter", but it
acts that way for this purpose. In any case, I'm disinclined to document
that specific interaction, because it would amount to blessing a pretty
damfool practice, which is to make your password visible on the program's
command line. (I wonder whether section 33.1.2 ought to specifically
caution against putting passwords into command-line conninfo strings.)

I used a later version of Tom's patch to add documentation of how
connection strings can override command-line options, plus it has the
password description improvements.

I would like to apply this to all supported versions.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

On Tue, Sep 1, 2020 at 10:38 PM Bruce Momjian <bruce@momjian.us> wrote:

I used a later version of Tom's patch to add documentation of how
connection strings can override command-line options, plus it has the
password description improvements.

I would like to apply this to all supported versions.

Not related to the proposed change, this sentence is the same before and
after, but makes me wonder:

However, <application>psql</application>

will waste a connection attempt finding out that the server wants a
password. In some cases it is worth typing <option>-W</option> to
avoid
the extra connection attempt.

AFAIK, there is nothing in the protocol that forces psql to make an extra
connection attempt. If libpq provided a means to pass a password-prompt
callback, no extra connection would be needed I think?

Regards,
--
Alex

On Wed, Sep 2, 2020 at 09:54:37AM +0200, Oleksandr Shulgin wrote:

On Tue, Sep 1, 2020 at 10:38 PM Bruce Momjian <bruce@momjian.us> wrote:

I used a later version of Tom's patch to add documentation of how
connection strings can override command-line options, plus it has the
password description improvements.

I would like to apply this to all supported versions.

Not related to the proposed change, this sentence is the same before and after,
but makes me wonder:

However, <application>psql</application>

� � � � will waste a connection attempt finding out that the server wants a
� � � � password.� In some cases it is worth typing <option>-W</option> to
avoid
� � � � the extra connection attempt.

AFAIK, there is nothing in the protocol that forces psql to make an extra
connection attempt.� If libpq provided a means to pass a password-prompt
callback, no extra connection would be needed I think?

That is true. I don't think we want the backend to be kept open waiting
for someone to type a password, which is why, I think, we don't do it
that way.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

Bruce Momjian <bruce@momjian.us> writes:

On Wed, Sep 2, 2020 at 09:54:37AM +0200, Oleksandr Shulgin wrote:

AFAIK, there is nothing in the protocol that forces psql to make an extra
connection attempt.  If libpq provided a means to pass a password-prompt
callback, no extra connection would be needed I think?

That is true. I don't think we want the backend to be kept open waiting
for someone to type a password, which is why, I think, we don't do it
that way.

Yeah, I think this is an ancient value judgement. Sure, it's pretty
questionable whether spawning a backend a second time is better than
tying one up waiting for a human to type a password. (It's going to
be spending a lot of time waiting for that human later on, anyway.)
The real point IMO is that libpq's connection API is already
unreasonably complicated, and providing a callback of this sort
would make it even messier. A different client library might make
a different choice, perhaps.

regards, tom lane

Bruce Momjian <bruce@momjian.us> writes:

I used a later version of Tom's patch to add documentation of how
connection strings can override command-line options, plus it has the
password description improvements.

<option>--all</option>) is not used, the database name is read
from the environment variable <envar>PGDATABASE</envar>.  If
that is not set, the user name specified for the connection is
-        used.
+        used.  <link linkend="libpq-connstring">Connction strings</link>
+        can also be used for connection specifications, and these can
+        override other command-line options.
</para>
</listitem>
</varlistentry>

* Spellcheck (not "Connction" please)

* I do not like the wording "can override", because that's just impossibly
vague. It leaves the reader wondering whether there are cases where that
doesn't happen.

I'd suggest wording more like

The <replaceable>dbname</replaceable> can be a
<link ...>connection string</link>. If so, connection parameters
found in the connection string will override any conflicting command
line options, such as <option>--username</option>.

regards, tom lane

On Wed, Sep 2, 2020 at 03:47:40PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

I used a later version of Tom's patch to add documentation of how
connection strings can override command-line options, plus it has the
password description improvements.

<option>--all</option>) is not used, the database name is read
from the environment variable <envar>PGDATABASE</envar>.  If
that is not set, the user name specified for the connection is
-        used.
+        used.  <link linkend="libpq-connstring">Connction strings</link>
+        can also be used for connection specifications, and these can
+        override other command-line options.
</para>
</listitem>
</varlistentry>

* Spellcheck (not "Connction" please)

* I do not like the wording "can override", because that's just impossibly
vague. It leaves the reader wondering whether there are cases where that
doesn't happen.

I'd suggest wording more like

The <replaceable>dbname</replaceable> can be a
<link ...>connection string</link>. If so, connection parameters
found in the connection string will override any conflicting command
line options, such as <option>--username</option>.

Sure, makes sense. Updated patch attached.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

On Thu, Sep 3, 2020 at 12:05 PM Bruce Momjian <bruce@momjian.us> wrote:

I'd suggest wording more like

The <replaceable>dbname</replaceable> can be a
<link ...>connection string</link>. If so, connection parameters
found in the connection string will override any conflicting command
line options, such as <option>--username</option>.

Sure, makes sense. Updated patch attached.

I read through the patch and it looks good to me.

David J.

On Thu, Sep 3, 2020 at 03:05:38PM -0400, Bruce Momjian wrote:

On Wed, Sep 2, 2020 at 03:47:40PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

I used a later version of Tom's patch to add documentation of how
connection strings can override command-line options, plus it has the
password description improvements.

<option>--all</option>) is not used, the database name is read
from the environment variable <envar>PGDATABASE</envar>.  If
that is not set, the user name specified for the connection is
-        used.
+        used.  <link linkend="libpq-connstring">Connction strings</link>
+        can also be used for connection specifications, and these can
+        override other command-line options.
</para>
</listitem>
</varlistentry>

* Spellcheck (not "Connction" please)

* I do not like the wording "can override", because that's just impossibly
vague. It leaves the reader wondering whether there are cases where that
doesn't happen.

I'd suggest wording more like

The <replaceable>dbname</replaceable> can be a
<link ...>connection string</link>. If so, connection parameters
found in the connection string will override any conflicting command
line options, such as <option>--username</option>.

Sure, makes sense. Updated patch attached.

Patch applied to all supported versions; thanks for the report.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

On Fri, Oct 02, 2020 at 10:19:58PM -0400, Bruce Momjian wrote:

On Thu, Sep 3, 2020 at 03:05:38PM -0400, Bruce Momjian wrote:

On Wed, Sep 2, 2020 at 03:47:40PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

I used a later version of Tom's patch to add documentation of how
connection strings can override command-line options, plus it has the
password description improvements.

<option>--all</option>) is not used, the database name is read
from the environment variable <envar>PGDATABASE</envar>.  If
that is not set, the user name specified for the connection is
-        used.
+        used.  <link linkend="libpq-connstring">Connction strings</link>
+        can also be used for connection specifications, and these can
+        override other command-line options.
</para>
</listitem>
</varlistentry>

* Spellcheck (not "Connction" please)

...

Sure, makes sense. Updated patch attached.

Patch applied to all supported versions; thanks for the report.

Unfortunately that included the typo Tom pointed out :(

On Mon, Nov 2, 2020 at 12:33:33AM -0600, Justin Pryzby wrote:

On Fri, Oct 02, 2020 at 10:19:58PM -0400, Bruce Momjian wrote:

On Thu, Sep 3, 2020 at 03:05:38PM -0400, Bruce Momjian wrote:

On Wed, Sep 2, 2020 at 03:47:40PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

I used a later version of Tom's patch to add documentation of how
connection strings can override command-line options, plus it has the
password description improvements.

<option>--all</option>) is not used, the database name is read
from the environment variable <envar>PGDATABASE</envar>.  If
that is not set, the user name specified for the connection is
-        used.
+        used.  <link linkend="libpq-connstring">Connction strings</link>
+        can also be used for connection specifications, and these can
+        override other command-line options.
</para>
</listitem>
</varlistentry>

* Spellcheck (not "Connction" please)

...

Sure, makes sense. Updated patch attached.

Patch applied to all supported versions; thanks for the report.

Unfortunately that included the typo Tom pointed out :(

Oops, I missed that. Fixed, thanks.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee