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

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

> I wrote:
> > "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> >> On Mon, Jun 8, 2020 at 2:36 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.