Thread: Cannot connect local with ttrust (pg_upgrade assumes it)
Hi, 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
On 4/9/25 09:20, Abraham, Danny wrote: > Hi, > Asking for help with inability to connect local using trust > > The # are comments that would 'hide' the lines below from being looked at for authentication purposes. Is this really how the lines are in your pg_hba.conf file? > Fail > #local all all trust > #local all dbauser peer > > OK > #host all all 127.0.0.1/32 trust > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Apr 9, 2025 at 9:20 AM Abraham, Danny <danny_abraham@bmc.com> wrote:
Fail
Failures include messages indicating why. You should always share such messages. Showing the command that produced the failure is also advised.
David J.
On Wed, 9 Apr 2025 at 18:20, Abraham, Danny <danny_abraham@bmc.com> 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.
Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
"Abraham, Danny"
Date:
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 <folarte@peoplecall.com> wrote:
On Wed, 9 Apr 2025 at 18:20, Abraham, Danny <danny_abraham@bmc.com> 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.
> 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.
Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
"Abraham, Danny"
Date:
Failure eans going down the hba list and asking for the password.
Sent from Workspace ONE Boxer
On Apr 9, 2025 20:04, Francisco Olarte <folarte@peoplecall.com> wrote:
On Wed, 9 Apr 2025 at 18:20, Abraham, Danny <danny_abraham@bmc.com> 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.
> 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.
Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
Adrian Klaver
Date:
On 4/9/25 10:44 AM, Abraham, Danny wrote: > Thanks for the answers. > All the 3 lines are, of course, uncommented for the test. Then provide the actual configuration you are working with. > I am using Linux, socket is in /tmp, i see the .s files , but local > trust still does not connect. Provide: a) The connection string you are using. b) The error you get. > > Sent from Workspace ONE Boxer > > On Apr 9, 2025 20:04, Francisco Olarte <folarte@peoplecall.com> wrote: > On Wed, 9 Apr 2025 at 18:20, Abraham, Danny <danny_abraham@bmc.com> 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. -- Adrian Klaver adrian.klaver@aklaver.com
Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
Adrian Klaver
Date:
On 4/9/25 10:45 AM, Abraham, Danny wrote: > 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. > > > Sent from Workspace ONE Boxer > > On Apr 9, 2025 20:04, Francisco Olarte <folarte@peoplecall.com> wrote: > On Wed, 9 Apr 2025 at 18:20, Abraham, Danny <danny_abraham@bmc.com> 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. -- Adrian Klaver adrian.klaver@aklaver.com
RE: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
"Abraham, Danny"
Date:
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 <adrian.klaver@aklaver.com> Sent: Wednesday, April 9, 2025 8:49 PM To: Abraham, Danny <danny_abraham@bmc.com>; Francisco Olarte <folarte@peoplecall.com> Cc: pgsql-general@lists.postgresql.org Subject: Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it) On 4/9/25 10:45 AM, Abraham, Danny wrote: > 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. > > > Sent from Workspace ONE Boxer > > On Apr 9, 2025 20:04, Francisco Olarte <folarte@peoplecall.com> wrote: > On Wed, 9 Apr 2025 at 18:20, Abraham, Danny <danny_abraham@bmc.com> 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. -- Adrian Klaver adrian.klaver@aklaver.com
FW: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
"Abraham, Danny"
Date:
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 <adrian.klaver@aklaver.com> Sent: Wednesday, April 9, 2025 8:49 PM To: Abraham, Danny <danny_abraham@bmc.com>; Francisco Olarte <folarte@peoplecall.com> Cc: pgsql-general@lists.postgresql.org Subject: Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it) On 4/9/25 10:45 AM, Abraham, Danny wrote: > 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. > > > Sent from Workspace ONE Boxer > > On Apr 9, 2025 20:04, Francisco Olarte <folarte@peoplecall.com> wrote: > On Wed, 9 Apr 2025 at 18:20, Abraham, Danny <danny_abraham@bmc.com> 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. -- Adrian Klaver adrian.klaver@aklaver.com
Re: FW: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
Adrian Klaver
Date:
On 4/9/25 12:23, Abraham, Danny 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 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. 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? > Thanks > Danny > > -- Adrian Klaver adrian.klaver@aklaver.com
Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
"David G. Johnston"
Date:
On Wed, Apr 9, 2025, 12:21 Abraham, Danny <danny_abraham@bmc.com> 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 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
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.
RE: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
"Abraham, Danny"
Date:
Thanks again.
- No other PG instances on this Linux machine.
- RHEL9
- Our compiled PG which is embedded within our product/site
- I am not aware of an env variable that changes the top down matching on pg_hba.conf
Danny
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Wednesday, April 9, 2025 10:34 PM
To: Abraham, Danny <danny_abraham@bmc.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Francisco Olarte <folarte@peoplecall.com>; pgsql-general@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
On Wed, Apr 9, 2025, 12:21 Abraham, Danny <danny_abraham@bmc.com> 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 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
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.
Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
Adrian Klaver
Date:
On 4/9/25 13:40, Abraham, Danny wrote: > 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 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 <david.g.johnston@gmail.com> > *Sent:* Wednesday, April 9, 2025 10:34 PM > *To:* Abraham, Danny <danny_abraham@bmc.com> > *Cc:* Adrian Klaver <adrian.klaver@aklaver.com>; Francisco Olarte > <folarte@peoplecall.com>; pgsql-general@lists.postgresql.org > *Subject:* Re: [EXTERNAL] Re: Cannot connect local with ttrust > (pg_upgrade assumes it) > > On Wed, Apr 9, 2025, 12:21 Abraham, Danny <danny_abraham@bmc.com > <mailto:danny_abraham@bmc.com>> 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 > > 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 > > 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. > -- Adrian Klaver adrian.klaver@aklaver.com
Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
Adrian Klaver
Date:
On 4/9/25 13:40, Abraham, Danny wrote: > Thanks again. > 4. I am not aware of an env variable that changes the top down matching > on pg_hba.conf 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. > > Danny > -- Adrian Klaver adrian.klaver@aklaver.com
RE: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
"Abraham, Danny"
Date:
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 <adrian.klaver@aklaver.com> Sent: Thursday, April 10, 2025 12:00 AM To: Abraham, Danny <danny_abraham@bmc.com>; David G. Johnston <david.g.johnston@gmail.com>; Francisco Olarte <folarte@peoplecall.com>;pgsql-general@lists.postgresql.org Subject: Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it) On 4/9/25 13:40, Abraham, Danny wrote: > Thanks again. > 4. I am not aware of an env variable that changes the top down matching > on pg_hba.conf Should have added to previous post: 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. > > Danny > -- Adrian Klaver adrian.klaver@aklaver.com
Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
Francisco Olarte
Date:
On Wed, 9 Apr 2025 at 19:44, Abraham, Danny <danny_abraham@bmc.com> wrote: > Thanks for the answers. > All the 3 lines are, of course, uncommented for the test. That was assumed. > I am using Linux, socket is in /tmp, i see the .s files , but local trust still does not connect. You forgot to answer the first and most important question. > How are you connecting? Your symptoms matches confusing local with > TCP/IP to localhost. They are not the same thing. For what we know you maybe trying to connect to the database using "echo hi", and then the what will surprise us is you were able to connect via tcpip. Francisco Olarte.
Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
From
Francisco Olarte
Date:
Abraham: On Thu, 10 Apr 2025 at 13:30, Francisco Olarte <folarte@peoplecall.com> wrote: > You forgot to answer the first and most important question. > > How are you connecting? Your symptoms matches confusing local with > > TCP/IP to localhost. They are not the same thing. After reading more messages it seems you use plain psql -U. Can you try to do \conninfo after connecting to see how you are connecting? - If it says tcp, then it is a problem with how psql determines where to connect. - If it says local then it may be an hba problem. Francisco Olarte.