Re: BUG #16486: Prompted password is ignored when password specifiedin connection string - Mailing list pgsql-bugs

From David G. Johnston
Subject Re: BUG #16486: Prompted password is ignored when password specifiedin connection string
Date
Msg-id CAKFQuwYPhBuszZaXuKmYu7=pq7DLp+LtesWCMVH2asDSFNoS+g@mail.gmail.com
Whole thread Raw
In response to Re: BUG #16486: Prompted password is ignored when password specified in connection string  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
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.

pgsql-bugs by date:

Previous
From: Thomas Munro
Date:
Subject: Re: Potential G2-item cycles under serializable isolation
Next
From: Alexander Lakhin
Date:
Subject: Re: BUG #16486: Prompted password is ignored when password specifiedin connection string