Re: BUG #15025: PSQL CLI - inconsistency when both -d and -U supplies a username

On Tue, Jan 23, 2018 at 01:00:13PM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15025
> Logged by: Akos Vandra
> Email address: akos(at)elegran(dot)com
> PostgreSQL version: 9.5.4
> Operating system: Debian
> Description:
>
> Repro:
>
> case 1: psql -U other_user -d "postgresql://some_user(at)host/db"
>
> case 2: psql -d "postgresql://some_user(at)host/db" -U other_user
>
> Expectation:
>
> Use whatever is given later:
>
> case 1: log in as user
> case 2: log in as other_user
>
> Actual:
>
> case 1: logs in as user
> case 2:
> - the password prompt asks for the pw of user
> - psql uses the password given to log in with other_user
> - if the pw is correct for user, or incorrect it displays that the
> password is incorrect for other_user
> - if the password is correct for other_user, it connects to the db as
> other_user
>
>
> $ psql "postgresql://user(at)host/db" -U other_user
> Password for user other_user:
> psql: FATAL: password authentication failed for user "user"
> FATAL: password authentication failed for user "user"

I was able to make a clearer example. First create two users:

CREATE USER user1 PASSWORD 'abc1';
CREATE USER user2 PASSWORD 'abc2';

then:

psql -d "postgresql://user2(at)momjian(dot)us/test" -U user1
--> Password for user user1:

but it wants the user2 password. Same with:

psql -U user1 -d "postgresql://user2(at)momjian(dot)us/test"
--> Password for user user1:

It doesn't matter whether -U is first or last, it always prompts for the
-U user, but connects as the -d user.

Because the URI is parsed by libpq, I don't think we can do any better
than just suppress the user name in the password prompt when a URI is
used. This is done in the attached patch, e.g.:
e.g.:

$psql -U user1 -d "postgresql://user2(at)momjian(dot)us/test"
Password:

Can someone tell me if I need to update the prompt in
psql/command.c::prompt_for_password()? I can't figure out how to
trigger that prompt.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +