Subject: Re: Cannot connect local with ttrust (pg_upgrade assumes it)

From: Francisco Olarte <[email-masked]>
Date: Wed, 9 Apr 2025 19:03:35 +0200

On Wed, 9 Apr 2025 at 18:20, Abraham, Danny <[email-masked]> wrote:
> Asking for help with inability to connect local using trust
> Fail
> #local   all         all                               trust
> #local   all   dbauser   peer
> OK
> #host    all          all         127.0.0.1/32          trust

How are you connecting? Your symptoms matches confusing local with
TCP/IP to localhost. They are not the same thing.
Also, are you on windows? ( which does not have unix sockets ).

local
This record matches connection attempts using Unix-domain sockets.
Without a record of this type, Unix-domain socket connections are
disallowed.
host
This record matches connection attempts made using TCP/IP. host
records match SSL or non-SSL connection attempts as well as GSSAPI
encrypted or non-GSSAPI encrypted connection attempts.

On a unix host psql -h localhost will use the host line, psql -h
/socket/path will go to the local one, and no -h defaults to socket.
On a windows host it default to tcp/ip to localhost.

Francisco Olarte.

---

From: "Abraham, Danny" <[email-masked]>
Date: Wed, 9 Apr 2025 17:44:27 +0000

Thanks for the answers.
All the 3 lines are, of course, uncommented for the test.
I am using Linux, socket is in /tmp, i see the .s files , but local trust still does not connect.

Sent from Workspace ONE Boxer

On Apr 9, 2025 20:04, Francisco Olarte <[email-masked]> wrote:

local

Francisco Olarte.

---

From: "Abraham, Danny" <[email-masked]>
Date: Wed, 9 Apr 2025 17:45:44 +0000

Failure eans going down the hba list and asking for the password.

local

Francisco Olarte.

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 9 Apr 2025 10:47:03 -0700

> All the 3 lines are, of course, uncommented for the test.

Then provide the actual configuration you are working with.

> trust still does not connect.

Provide:

a) The connection string you are using.

b) The error you get.

-- 
Adrian Klaver
[email-masked]

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 9 Apr 2025 10:49:23 -0700

> Failure eans going down the hba list and asking for the password.

In pg_hba.conf first match wins, sounds like you have password auth line 
before the trust lines.

You need to provide the complete pg_hba.conf auth lines you are working 
with.

---

From: "Abraham, Danny" <[email-masked]>
Date: Wed, 9 Apr 2025 19:21:14 +0000

Thanks again.
Here is the example:
dba-tlv-x6y64k% cat pg_hba.conf
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         all                               trust
host    all         all         0.0.0.0/0                   scram-sha-256

dba-tlv-x6y64k% pg_ctl reload
server signaled
dba-tlv-x6y64k% psql -Upostgres
Password for user postgres:
psql (17.4)
Type "help" for help.

ctrlmdb=#
PSQL should not have asked for the password.
Thanks
Danny

-----Original Message-----
From: Adrian Klaver <[email-masked]> 
Sent: Wednesday, April 9, 2025 8:49 PM
To: Abraham, Danny <[email-masked]>; Francisco Olarte <[email-masked]>
Cc: [email-masked]
Subject: Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)

> Failure eans going down the hba list and asking for the password.

In pg_hba.conf first match wins, sounds like you have password auth line before the trust lines.

You need to provide the complete pg_hba.conf auth lines you are working with.

>> Asking for help with inability to connect local using trust Fail 
>> #local   all         all                               trust #local   
>> all   dbauser   peer OK #host    all          all         
>> 127.0.0.1/32          trust
>

---

From: "Abraham, Danny" <[email-masked]>
Date: Wed, 9 Apr 2025 19:23:41 +0000

Thanks again.
Here is the example:

ctrlmdb=#

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 9 Apr 2025 12:31:38 -0700

> PSQL should not have asked for the password.

Do you have more then one instance of Postgres running on this machine?

If so are you editing the correct pg_hba.conf?

How did you install Postgres and what Linux distro(& version) are you using?

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 9 Apr 2025 12:34:01 -0700

> PSQL should not have asked for the password.

Suggests there are environment variables coming into play making
non-default choices for you.  Namely, making you not use local as the
connection method.

David J.

---

From: "Abraham, Danny" <[email-masked]>
Date: Wed, 9 Apr 2025 20:40:00 +0000

Thanks again.

  1.  No other PG instances on this Linux machine.
  2.  RHEL9
  3.  Our compiled PG which is embedded within our product/site
  4.  I am not aware of an env variable that changes the top down matching on pg_hba.conf

Danny

From: David G. Johnston <[email-masked]>
Sent: Wednesday, April 9, 2025 10:34 PM
To: Abraham, Danny <[email-masked]>
Cc: Adrian Klaver <[email-masked]>; Francisco Olarte <[email-masked]>; [email-masked]
Subject: Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)

On Wed, Apr 9, 2025, 12:21 Abraham, Danny <[email-masked]<mailto:[email-masked]>> wrote:
Thanks again.
Here is the example:
dba-tlv-x6y64k% cat pg_hba.conf
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         all                               trust
host    all         all         0.0.0.0/0<https://urldefense.com/v3/__http:/0.0.0.0/0__;!!PEZBYkTc!e_x_LCIABYSpqVum5S6Hr0TtEs6P4NaumHqEqyXEa3ErrZTPMmnUeC33JmyNsqr1kGf2DxtZKMbJ_ofkSSjZQC4hsuUN$>                   scram-sha-256

ctrlmdb=#

Suggests there are environment variables coming into play making non-default choices for you.  Namely, making you not use local as the connection method.

David J.

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 9 Apr 2025 13:45:42 -0700

>  4. I am not aware of an env variable that changes the top down matching

 From here:

https://www.postgresql.org/docs/current/libpq-envars.html

"The following environment variables can be used to select default 
connection parameter values, which will be used by PQconnectdb, 
PQsetdbLogin and PQsetdb if no value is directly specified by the 
calling code.

PGHOST behaves the same as the host connection parameter.

...
"

So if you have PGHOST set to say localhost it would be added to:

psql -Upostgres

> 
> Danny
> 
> *From:*David G. Johnston <[email-masked]>
> *Sent:* Wednesday, April 9, 2025 10:34 PM
> *To:* Abraham, Danny <[email-masked]>
> *Cc:* Adrian Klaver <[email-masked]>; Francisco Olarte 
> <[email-masked]>; [email-masked]
> *Subject:* Re: [EXTERNAL] Re: Cannot connect local with ttrust 
> (pg_upgrade assumes it)
> 
>     host    all         all 0.0.0.0/0
>     <https://urldefense.com/v3/__http:/0.0.0.0/0__;!!PEZBYkTc!e_x_LCIABYSpqVum5S6Hr0TtEs6P4NaumHqEqyXEa3ErrZTPMmnUeC33JmyNsqr1kGf2DxtZKMbJ_ofkSSjZQC4hsuUN$>                   scram-sha-256
>

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 9 Apr 2025 13:59:40 -0700

>  4. I am not aware of an env variable that changes the top down matching

Should have added to previous post:

 From here:

https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

a) Set log_connections and log_disconnections to on if they are not 
already and reload the server conf.

b) Then look at Postgres log to see what the connection settings are for 
the connections in question.

---

From: "Abraham, Danny" <[email-masked]>
Date: Wed, 9 Apr 2025 21:53:27 +0000

Thanks a lot.
As you mentioned , I have not cleared the environment vars prior to the call.

==>
dba-tlv-x6y64k% cat d2

unsetenv PGPORT
unsetenv PGSYSCONFDIR
unsetenv PGUSER
unsetenv PGDATABASE
unsetenv PGSERVICE
unsetenv PGHOST

psql -Upostgres -p5548 -dpostgres

dba-tlv-x6y64k% source d2
psql (17.4)
Type "help" for help.

postgres=# \q
==>

-----Original Message-----
From: Adrian Klaver <[email-masked]> 
Sent: Thursday, April 10, 2025 12:00 AM
To: Abraham, Danny <[email-masked]>; David G. Johnston <[email-masked]>; Francisco Olarte <[email-masked]>; [email-masked]
Subject: Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)

 From here:

https://urldefense.com/v3/__https://www.postgresql.org/docs/current/runtime-config-logging.html*RUNTIME-CONFIG-LOGGING-WHAT__;Iw!!PEZBYkTc!fcUdccPVwqMg5ysFSTgyVxuCPBLFdOMxwtMMj5-n6H2aJ4D-3IzJZsdkw3lk43mUrVgdNhgWhX0O-ZGdU-6PAaXcmFI$ 

a) Set log_connections and log_disconnections to on if they are not already and reload the server conf.

b) Then look at Postgres log to see what the connection settings are for the connections in question.