Обсуждение: BUG #17032: wrong password cause double connect and double error

The following bug has been logged on the website:

Bug reference:      17032
Logged by:          RekGRpth
Email address:      rekgrpth@gmail.com
PostgreSQL version: 13.3
Operating system:   docker alpine
Description:

1) create user test with password test and database test with owner test
2) add md5 auth for this user in pg_hba.conf like host test test
172.19.0.0/16 md5
3) compile test program src/postgres/src/test/examples/testlibpq.c
4) run test program with argument "host=postgres user=test password=wrong"
5) get double error on client side:
Connection to database failed: FATAL:  password authentication failed for
user "test"
FATAL:  password authentication failed for user "test"
6) and get double connection and double error on server side:
2021-05-25 16:49:42.868 +05 [46654] postgres(43832)
[unknown]@[unknown]/[unknown] LOG:  connection received: host=postgres
port=43832
2021-05-25 16:49:42.876 +05 [46654] postgres(43832) test@test/[unknown]
FATAL:  password authentication failed for user "test"
2021-05-25 16:49:42.876 +05 [46654] postgres(43832) test@test/[unknown]
DETAIL:  Password does not match for user "test".
    Connection matched pg_hba.conf line 99: "host test test 172.19.0.0/16
md5"
2021-05-25 16:49:42.877 +05 [46655] postgres(43834)
[unknown]@[unknown]/[unknown] LOG:  connection received: host=postgres
port=43834
2021-05-25 16:49:42.878 +05 [46655] postgres(43834) test@test/[unknown]
FATAL:  password authentication failed for user "test"
2021-05-25 16:49:42.878 +05 [46655] postgres(43834) test@test/[unknown]
DETAIL:  Password does not match for user "test".
    Connection matched pg_hba.conf line 99: "host test test 172.19.0.0/16 md5"

Ok, thanks. But, Why try to re-establish a known unsuccessful
connection? (because wrong password)

вт, 25 мая 2021 г. в 19:02, Jeff Janes <jeff.janes@gmail.com>:
>
> On Tue, May 25, 2021 at 7:55 AM PG Bug reporting form <noreply@postgresql.org> wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference:      17032
>> Logged by:          RekGRpth
>> Email address:      rekgrpth@gmail.com
>> PostgreSQL version: 13.3
>> Operating system:   docker alpine
>> Description:
>>
>> 1) create user test with password test and database test with owner test
>> 2) add md5 auth for this user in pg_hba.conf like host test test
>> 172.19.0.0/16 md5
>> 3) compile test program src/postgres/src/test/examples/testlibpq.c
>> 4) run test program with argument "host=postgres user=test password=wrong"
>> 5) get double error on client side:
>
>
> You have described the behavior, but not what about it is buggy.  This looks expected to me.
>
> It tries to connect with encryption and fails, then tries to connect without encryption and also fails.
>
> If you set sslmode to 'disable' or to 'require' (or higher) then you will only get one connection attempt.  But if it
is'allow' or 'prefer' (the default), then you will get two. 
>
> Cheers,
>
> Jeff

PG Bug reporting form <noreply@postgresql.org> writes:
> 5) get double error on client side:

That's normal behavior, if you're using an SSL-capable libpq over a TCP
socket; it will try once with SSL and once without.  (This isn't quite
as insane as it perhaps sounds, because the server might have different
pg_hba rules for the two cases.)  If you don't want that, you can force
the matter using the sslmode connection option.

            regards, tom lane

On Tue, May 25, 2021 at 07:10:11PM +0500, RekGRpth wrote:
> Ok, thanks. But, Why try to re-establish a known unsuccessful
> connection? (because wrong password)

Because that's the way the default behavior of sslmode works, and
because it is simpler to work this way in libpq by making all failures
reported by the backend work the same way on the client side:
https://www.postgresql.org/docs/devel/libpq-connect.html#LIBPQ-CONNECT-SSLMODE

(I agree that this is an unsafe default choice, and we've discussed
about changing to a saner default.)
--
Michael