Re: BUG #15025: PSQL CLI - inconsistency when both -d and -Usupplies 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@elegran.com
> PostgreSQL version: 9.5.4
> Operating system:   Debian
> Description:        
> 
> Repro:
> 
> case 1: psql -U other_user -d "postgresql://some_user@host/db" 
> 
> case 2: psql -d "postgresql://some_user@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@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@momjian.us/test" -U user1
-->    Password for user user1:

but it wants the user2 password.  Same with:

    psql -U user1 -d "postgresql://user2@momjian.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@momjian.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@momjian.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 +