pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
psql trying twice to connect to local DB
4+ messages / 3 participants
[nested] [flat]

* psql trying twice to connect to local DB
@ 2023-10-07 22:44 Steve Baldwin <steve.baldwin@gmail.com>
  2023-10-07 23:13 ` Re: psql trying twice to connect to local DB Steve Baldwin <steve.baldwin@gmail.com>
  2023-10-07 23:14 ` Re: psql trying twice to connect to local DB Tom Lane <tgl@sss.pgh.pa.us>
  2023-10-08 02:39 ` Re: psql trying twice to connect to local DB Ron <ronljohnsonjr@gmail.com>
  0 siblings, 3 replies; 4+ messages in thread

From: Steve Baldwin @ 2023-10-07 22:44 UTC (permalink / raw)
  To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

I have a local DB (15.2) running in a docker container.

If I make a connection to that DB from most clients I see log entries like
this:

2023-10-07 22:32:26.518
UTC,,,16278,"172.21.0.1:33192",6521dc7a.3f96,1,"",2023-10-07
22:32:26 UTC,,0,LOG,00000,"connection received: host=172.21.0.1
port=33192",,,,,,,,,"","not initialized",,0
2023-10-07 22:32:26.529 UTC,"b2bc_owner","b2bcreditonline",16278,"
172.21.0.1:33192",6521dc7a.3f96,2,"authentication",2023-10-07 22:32:26
UTC,4/340,0,LOG,00000,"connection authenticated: identity=""b2bc_owner""
method=scram-sha-256
(/var/lib/postgresql/data/pg_hba.conf:100)",,,,,,,,,"","client backend",,0
2023-10-07 22:32:26.529 UTC,"b2bc_owner","b2bcreditonline",16278,"
172.21.0.1:33192",6521dc7a.3f96,3,"authentication",2023-10-07 22:32:26
UTC,4/340,0,LOG,00000,"connection authorized: user=b2bc_owner
database=b2bcreditonline application_name=crystal",,,,,,,,,"","client
backend",,0

If however I use psql I see two connection log entries - sometimes almost a
second apart. For example:

2023-10-07 22:33:16.214
UTC,,,16431,"172.21.0.1:48550",6521dcac.402f,1,"",2023-10-07
22:33:16 UTC,,0,LOG,00000,"connection received: host=172.21.0.1
port=48550",,,,,,,,,"","not initialized",,0
2023-10-07 22:33:17.130
UTC,,,16432,"172.21.0.1:48558",6521dcad.4030,1,"",2023-10-07
22:33:17 UTC,,0,LOG,00000,"connection received: host=172.21.0.1
port=48558",,,,,,,,,"","not initialized",,0
2023-10-07 22:33:17.151 UTC,"b2bc_owner","b2bcreditonline",16432,"
172.21.0.1:48558",6521dcad.4030,2,"authentication",2023-10-07 22:33:17
UTC,4/354,0,LOG,00000,"connection authenticated: identity=""b2bc_owner""
method=scram-sha-256
(/var/lib/postgresql/data/pg_hba.conf:100)",,,,,,,,,"","client backend",,0
2023-10-07 22:33:17.152 UTC,"b2bc_owner","b2bcreditonline",16432,"
172.21.0.1:48558",6521dcad.4030,3,"authentication",2023-10-07 22:33:17
UTC,4/354,0,LOG,00000,"connection authorized: user=b2bc_owner
database=b2bcreditonline application_name=psql",,,,,,,,,"","client
backend",,0

The version of psql is 15.4.

Obviously this isn't a major problem, but I'm curious if I've got something
configured incorrectly, or just something I missed from the docs.

Thanks,

Steve

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: psql trying twice to connect to local DB
  2023-10-07 22:44 psql trying twice to connect to local DB Steve Baldwin <steve.baldwin@gmail.com>
@ 2023-10-07 23:13 ` Steve Baldwin <steve.baldwin@gmail.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Steve Baldwin @ 2023-10-07 23:13 UTC (permalink / raw)
  To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

It seems this is not restricted to local connections. Here's the log
entries from making a connection with the same psql client to a remote
(RDS) instance:

2023-10-07 22:47:06.506
UTC,,,23005,"10.117.100.26:55302",6521dfea.59dd,1,"",2023-10-07
22:47:06 UTC,,0,LOG,00000,"connection received: host=10.117.100.26
port=55302",,,,,,,,,"","not initialized",,0
2023-10-07 22:47:08.479
UTC,,,23010,"10.117.100.26:55306",6521dfec.59e2,1,"",2023-10-07
22:47:08 UTC,,0,LOG,00000,"connection received: host=10.117.100.26
port=55306",,,,,,,,,"","not initialized",,0
2023-10-07 22:47:09.992 UTC,"b2bc_owner","b2bcreditonline",23010,"
10.117.100.26:55306",6521dfec.59e2,2,"authentication",2023-10-07 22:47:08
UTC,29/840773,0,LOG,00000,"connection authenticated:
identity=""b2bc_owner"" method=md5
(/rdsdbdata/config/pg_hba.conf:14)",,,,,,,,,"","client backend",,0
2023-10-07 22:47:09.993 UTC,"b2bc_owner","b2bcreditonline",23010,"
10.117.100.26:55306",6521dfec.59e2,3,"authentication",2023-10-07 22:47:08
UTC,29/840773,0,LOG,00000,"connection authorized: user=b2bc_owner
database=b2bcreditonline application_name=psql SSL enabled
(protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384,
bits=256)",,,,,,,,,"","client backend",,0

Compared to a connection made from our application:

2023-10-07 22:28:26.565
UTC,,,22121,"10.120.166.33:47748",6521db8a.5669,1,"",2023-10-07
22:28:26 UTC,,0,LOG,00000,"connection received: host=10.120.166.33
port=47748",,,,,,,,,"","not initialized",,0
2023-10-07 22:28:26.583 UTC,"b2bc_api","b2bcreditonline",22121,"
10.120.166.33:47748",6521db8a.5669,2,"authentication",2023-10-07 22:28:26
UTC,18/1263698,0,LOG,00000,"connection authenticated: identity=""b2bc_api""
method=md5 (/rdsdbdata/config/pg_hba.conf:14)",,,,,,,,,"","client
backend",,0
2023-10-07 22:28:26.583 UTC,"b2bc_api","b2bcreditonline",22121,"
10.120.166.33:47748",6521db8a.5669,3,"authentication",2023-10-07 22:28:26
UTC,18/1263698,0,LOG,00000,"connection authorized: user=b2bc_api
database=b2bcreditonline SSL enabled (protocol=TLSv1.2,
cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256)",,,,,,,,,"","client
backend",,0

It seems psql 16.0 (homebrew) behaves the same.

Steve

On Sun, Oct 8, 2023 at 9:44 AM Steve Baldwin <steve.baldwin@gmail.com>
wrote:

> I have a local DB (15.2) running in a docker container.
>
> If I make a connection to that DB from most clients I see log entries like
> this:
>
> 2023-10-07 22:32:26.518 UTC,,,16278,"172.21.0.1:33192",6521dc7a.3f96,1,"",2023-10-07
> 22:32:26 UTC,,0,LOG,00000,"connection received: host=172.21.0.1
> port=33192",,,,,,,,,"","not initialized",,0
> 2023-10-07 22:32:26.529 UTC,"b2bc_owner","b2bcreditonline",16278,"
> 172.21.0.1:33192",6521dc7a.3f96,2,"authentication",2023-10-07 22:32:26
> UTC,4/340,0,LOG,00000,"connection authenticated: identity=""b2bc_owner""
> method=scram-sha-256
> (/var/lib/postgresql/data/pg_hba.conf:100)",,,,,,,,,"","client backend",,0
> 2023-10-07 22:32:26.529 UTC,"b2bc_owner","b2bcreditonline",16278,"
> 172.21.0.1:33192",6521dc7a.3f96,3,"authentication",2023-10-07 22:32:26
> UTC,4/340,0,LOG,00000,"connection authorized: user=b2bc_owner
> database=b2bcreditonline application_name=crystal",,,,,,,,,"","client
> backend",,0
>
> If however I use psql I see two connection log entries - sometimes almost
> a second apart. For example:
>
> 2023-10-07 22:33:16.214 UTC,,,16431,"172.21.0.1:48550",6521dcac.402f,1,"",2023-10-07
> 22:33:16 UTC,,0,LOG,00000,"connection received: host=172.21.0.1
> port=48550",,,,,,,,,"","not initialized",,0
> 2023-10-07 22:33:17.130 UTC,,,16432,"172.21.0.1:48558",6521dcad.4030,1,"",2023-10-07
> 22:33:17 UTC,,0,LOG,00000,"connection received: host=172.21.0.1
> port=48558",,,,,,,,,"","not initialized",,0
> 2023-10-07 22:33:17.151 UTC,"b2bc_owner","b2bcreditonline",16432,"
> 172.21.0.1:48558",6521dcad.4030,2,"authentication",2023-10-07 22:33:17
> UTC,4/354,0,LOG,00000,"connection authenticated: identity=""b2bc_owner""
> method=scram-sha-256
> (/var/lib/postgresql/data/pg_hba.conf:100)",,,,,,,,,"","client backend",,0
> 2023-10-07 22:33:17.152 UTC,"b2bc_owner","b2bcreditonline",16432,"
> 172.21.0.1:48558",6521dcad.4030,3,"authentication",2023-10-07 22:33:17
> UTC,4/354,0,LOG,00000,"connection authorized: user=b2bc_owner
> database=b2bcreditonline application_name=psql",,,,,,,,,"","client
> backend",,0
>
> The version of psql is 15.4.
>
> Obviously this isn't a major problem, but I'm curious if I've got
> something configured incorrectly, or just something I missed from the docs.
>
> Thanks,
>
> Steve
>

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: psql trying twice to connect to local DB
  2023-10-07 22:44 psql trying twice to connect to local DB Steve Baldwin <steve.baldwin@gmail.com>
@ 2023-10-07 23:14 ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2023-10-07 23:14 UTC (permalink / raw)
  To: Steve Baldwin <steve.baldwin@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

Steve Baldwin <steve.baldwin@gmail.com> writes:
> If I make a connection to that DB from most clients I see log entries like
> this:
> ...
> If however I use psql I see two connection log entries - sometimes almost a
> second apart. For example:

These log entries show that you're using password-based authentication.
I believe what is happening is that psql tries to connect, discovers
that it needs a password, and then tries again after it's got a
password.  The delay would correspond to the time for you to type
your password (I take it you're a fast typist).

If you don't like the extra log entries, there's a command-line
switch that forces psql to prompt for password before connecting
the first time.  Of course, that might result in prompting for
a password that's not really needed.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: psql trying twice to connect to local DB
  2023-10-07 22:44 psql trying twice to connect to local DB Steve Baldwin <steve.baldwin@gmail.com>
@ 2023-10-08 02:39 ` Ron <ronljohnsonjr@gmail.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Ron @ 2023-10-08 02:39 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 10/7/23 17:44, Steve Baldwin wrote:
> I have a local DB (15.2) running in a docker container.
>
> If I make a connection to that DB from most clients I see log entries like 
> this:
>
> 2023-10-07 22:32:26.518 UTC,,,16278,"172.21.0.1:33192 
> <http://172.21.0.1:33192>",6521dc7a.3f96,1,"",2023-10-07 22:32:26 
> UTC,,0,LOG,00000,"connection received: host=172.21.0.1 
> port=33192",,,,,,,,,"","not initialized",,0
> 2023-10-07 22:32:26.529 
> UTC,"b2bc_owner","b2bcreditonline",16278,"172.21.0.1:33192 
> <http://172.21.0.1:33192>",6521dc7a.3f96,2,"authentication",2023-10-07 
> 22:32:26 UTC,4/340,0,LOG,00000,"connection authenticated: 
> identity=""b2bc_owner"" method=scram-sha-256 
> (/var/lib/postgresql/data/pg_hba.conf:100)",,,,,,,,,"","client backend",,0
> 2023-10-07 22:32:26.529 
> UTC,"b2bc_owner","b2bcreditonline",16278,"172.21.0.1:33192 
> <http://172.21.0.1:33192>",6521dc7a.3f96,3,"authentication",2023-10-07 
> 22:32:26 UTC,4/340,0,LOG,00000,"connection authorized: user=b2bc_owner 
> database=b2bcreditonline application_name=crystal",,,,,,,,,"","client 
> backend",,0
>
> If however I use psql I see two connection log entries - sometimes almost 
> a second apart. For example:
>
> 2023-10-07 22:33:16.214 UTC,,,16431,"172.21.0.1:48550 
> <http://172.21.0.1:48550>",6521dcac.402f,1,"",2023-10-07 22:33:16 
> UTC,,0,LOG,00000,"connection received: host=172.21.0.1 
> port=48550",,,,,,,,,"","not initialized",,0
> 2023-10-07 22:33:17.130 UTC,,,16432,"172.21.0.1:48558 
> <http://172.21.0.1:48558>",6521dcad.4030,1,"",2023-10-07 22:33:17 
> UTC,,0,LOG,00000,"connection received: host=172.21.0.1 
> port=48558",,,,,,,,,"","not initialized",,0
> 2023-10-07 22:33:17.151 
> UTC,"b2bc_owner","b2bcreditonline",16432,"172.21.0.1:48558 
> <http://172.21.0.1:48558>",6521dcad.4030,2,"authentication",2023-10-07 
> 22:33:17 UTC,4/354,0,LOG,00000,"connection authenticated: 
> identity=""b2bc_owner"" method=scram-sha-256 
> (/var/lib/postgresql/data/pg_hba.conf:100)",,,,,,,,,"","client backend",,0
> 2023-10-07 22:33:17.152 
> UTC,"b2bc_owner","b2bcreditonline",16432,"172.21.0.1:48558 
> <http://172.21.0.1:48558>",6521dcad.4030,3,"authentication",2023-10-07 
> 22:33:17 UTC,4/354,0,LOG,00000,"connection authorized: user=b2bc_owner 
> database=b2bcreditonline application_name=psql",,,,,,,,,"","client backend",,0
>
> The version of psql is 15.4.
>
> Obviously this isn't a major problem, but I'm curious if I've got 
> something configured incorrectly, or just something I missed from the docs.

As follow-up to Tom's response: create a .pgpass file with an entry for 
b2bc_owner.  There might still be two entries, but the time between the two 
of them should be instantaneous.

-- 
Born in Arizona, moved to Babylonia.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2023-10-08 02:39 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-07 22:44 psql trying twice to connect to local DB Steve Baldwin <steve.baldwin@gmail.com>
2023-10-07 23:13 ` Steve Baldwin <steve.baldwin@gmail.com>
2023-10-07 23:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-08 02:39 ` Ron <ronljohnsonjr@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox