Subject: Re: BUG #16486: Prompted password is ignored when password specified
 in connection string
To: Tom Lane <tgl@sss.pgh.pa.us>,
 "David G. Johnston" <david.g.johnston@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
References: <16486-b9c93d71c02c4907@postgresql.org>
 <2090524.1591652199@sss.pgh.pa.us>
 <CAKFQuwbk=SDVVhe=P=eAsX4Zip_0tR+vZOOMM-VvHAH0=We=_g@mail.gmail.com>
 <2110959.1591655226@sss.pgh.pa.us> <2142411.1591667958@sss.pgh.pa.us>
From: Alexander Lakhin <exclusion@gmail.com>
Message-ID: <f33b151d-39f9-758a-0f68-795b36d7a73d@gmail.com>
Date: Tue, 9 Jun 2020 07:00:01 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.7.0
MIME-Version: 1.0
In-Reply-To: <2142411.1591667958@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk

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