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 peerOK
#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.
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.
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.
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 trustHow 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
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 trustHow 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
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 trustHow 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
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 trustHow 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
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-256dba-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
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-256dba-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.
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 <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.
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-256dba-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
DannySuggests 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
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
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:
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
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.
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.